How to Efficiently Match Many Strings Against a Database Without Table Creation Privileges?

Efficiently match large numbers of strings in Oracle database without table creation permissions

In Oracle SQL Developer, you may need to match a large number of strings to the database, but lack the necessary permissions to create or edit tables. This situation requires alternative methods to perform the matching process efficiently.

Use collections

One solution is to use collections, which allow you to create temporary containers to store data values. Oracle provides two types of collections:

1. Oracle Variable Arrays (VARRAYs): These arrays store a fixed number of elements of a single data type.
2. Oracle Named Collections (Collections): These are unordered, resizable containers that can hold elements of different data types.

Using collections you can load your IDs into a container and then perform a join operation on the database table. Here is an example using Oracle named collections:

<code class="language-sql">VARIABLE cursor REFCURSOR;

DECLARE
  your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
  your_collection.EXTEND( 10000 );

  -- 填充集合。
  FOR i IN 1 .. 10000 LOOP
    your_collection(i) := DBMS_RANDOM.STRING( 'x', 20 );
  END LOOP;

  OPEN :cursor FOR
  SELECT t.*
  FROM   your_table t
         INNER JOIN
         TABLE( your_collection ) c
         ON t.id = c.COLUMN_VALUE;
END;
/

PRINT cursor;</code>

Java methods

Alternatively, if you have Java development skills, you can use Java to load your IDs into an array and pass them as parameters to the prepared statement that performs the join operation:

<code class="language-java">import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jdbc.OraclePreparedStatement;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class TestDatabase2 {
    public static void main(String args[]){
        try{
            Class.forName("oracle.jdbc.OracleDriver");

            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:XE","username","password");

            String[] ids = { "1", "2", "3" };

            ArrayDescriptor des = ArrayDescriptor.createDescriptor("SYS.ODCIVARCHAR2LIST", con);

            PreparedStatement st = con.prepareStatement("SELECT t.* FROM your_table t INNER JOIN TABLE( ? ) c ON t.id = c.COLUMN_VALUE");

            // 将数组传递给过程 - 
            ((OraclePreparedStatement) st).setARRAYAtName( "your_collection", new ARRAY( des, con, ids ) );
            ResultSet cursor = st.executeQuery();

            while ( cursor.next() )
            {
                int id = cursor.getInt(1);
                double column1 = cursor.getDouble(2);
                double column2 = cursor.getDouble(3);

                System.out.println( String.format( "Id: %5d", id ) );
                System.out.println( String.format( "  Column1: %s", column1 ) );
                System.out.println( String.format( "  Column2: %s", column2 ) );
            }
        } catch(ClassNotFoundException | SQLException e) {
            System.out.println(e);
        }
    }
}</code>

By leveraging collections or Java programming, you can efficiently match large numbers of strings to database tables without requiring table creation or editing permissions.

The above is the detailed content of How to Efficiently Match Many Strings Against a Database Without Table Creation Privileges?. For more information, please follow other related articles on the PHP Chinese website!