How to Efficiently Match Large String Lists with an Oracle Database Using Only Read Privileges?

Efficiently match large string lists using Oracle database

Introduction:

When dealing with large data sets, you need to efficiently match strings with data stored in the database. This article explores alternatives to traditional approaches with limited database permissions that prevent the creation of temporary tables or direct manipulation of database structures.

Problem Statement:

How to efficiently match a large list of string IDs to a table in an Oracle database when you only have read access? Due to the size of the list, hardcoding IDs is not feasible.

Option 1: Use collections

Oracle provides a mechanism called "sets" that can be used as an alternative to temporary tables. A collection is a data structure that can hold a series of values. In this example, the collection can be populated with string IDs. The following PL/SQL code snippet demonstrates how to use collections:

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;

Option 2: Use Java arrays

If Java is available, the same method can be implemented through Java code. The following code snippet demonstrates how to use a Java array to pass a list of string IDs to a SQL query:

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( :your_collection ) 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);
        }
    }
}

Both options provide an efficient way to match large lists of string IDs to data in an Oracle database without the need for temporary tables or modifications to the database structure.

The above is the detailed content of How to Efficiently Match Large String Lists with an Oracle Database Using Only Read Privileges?. For more information, please follow other related articles on the PHP Chinese website!