How to Load a Large Number of Strings to Match With Oracle Database

How to load a large number of strings to match with oracle database?

Use a collection

VARIABLE cursor REFCURSOR;

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

  FOR i IN 1 .. 10000 LOOP
    -- Populate the collection.
    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;

Or doing the same thing via 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( :your_collection ) c ON t.id = c.COLUMN_VALUE");

            // Passing an array to the procedure - 
            ((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);
        }
    }
}

Storing a list of codes into a variable in Oracle SQL

A fancy approach is this

WITH CODE_VALUES AS 
  ( SELECT DISTINCT COLUMN_VALUE AS CODE_VALUE
      FROM TABLE (sys.dbms_debug_vc2coll ('G31',
                                      'G310',
                                      'G311',
                                      'G312',
                                      'G318',
                                      'G319',
                                      'G239',
                                      'G122',
                                      'G710',
                                      'B20',
                                      'B22',
                                      'B23',
                                      'B24',
                                      'G35',
                                      'C811',
                                      'G37',
                                      'G375',
                                      'K702',
                                      'K741'))
  )
  SELECT *
    FROM CODE_VALUES -- + the rest of your query

You could do the same thing with successive union's against "dual" too

WITH CODE_VALUES AS 
    ( SELECT 'ABC' AS code_value FROM dual UNION 
      SELECT 'CDE' AS code_value FROM dual
    )

If this is going to get used across multiple operational queries it's probably best just to store them in a table.

How to search for multiple strings in very large database

The easiest way is:
1.) adding an index to the columns you like to search trough
2.) using oracle text as @lalitKumarB wrote

The most powerful way is:
3.) use an separate search engine (solr, elaticsearch).

But, probably you have to change you application in order to explicit use the search index for searching trough the data,...

I had the same situation some years before. Trying to search text in an big database. After a wile I found out, that database based search will never reach the performance of an dedicate search engine. And: you will have much more search features working out of the box, if you use solr (for example), like spelling correction, "More like this", ...

One option is to hold the data on orcale, searching in solr and return the ID of the document in order to only load the one row form oracle, the is referenced by the ID.
2nd option is to keep oracle as base datapool for your search engine and search in solr (or elasticsearch) in order to return the whole document/row from solr, not only the ID. So you don't need to load the data from the database any more.

The best option depends on your needs.

SQL Dynamic binding in a IN list

I tried the collection, but I cannot perform any "create or replace" since our DBA does not allow it.

You can use a built-in collection type, such as odcivarchar2list:

DECLARE
schemaToAnalyze  VARCHAR2(16);
categoryToSearch VARCHAR2(16);
listOfWords      SYS.ODCIVARCHAR2LIST;
BEGIN
categoryToSearch := 'my_category';
--schemaToAnalyze  := 'my_schema.my_table';
listOfWords      := SYS.ODCIVARCHAR2LIST('WORD_1', 'WORD_2', 'WORD_3', 'WORD_4', 'WORD_5', 'WORD_6');
FOR my_object IN (SELECT my_field FROM my_table -- schemaToAnalyze
                  WHERE other_field = categoryToSearch
                  AND my_field IN ( SELECT * FROM TABLE(listOfWords) )
                  GROUP BY my_field ORDER BY my_field)
LOOP
    dbms_output.put_line(my_object.my_field);
END LOOP;
END;
/

db<>fiddle

SQL Get List of Substrings Then Use as Values for LIKE

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Strains (id, name ) AS
SELECT 562,   'B6;129 (Bnip3 KO)' FROM DUAL UNION ALL
SELECT 563,   'B6;129 (BNIP3 Wt) [pregnant]' FROM DUAL UNION ALL
SELECT 564,   'B6;129 (BNIP3 Wt) [older than 21 days]' FROM DUAL UNION ALL
SELECT 720,   'BALB/C T(x:11)38H (T38H)' FROM DUAL UNION ALL
SELECT 721,   'BALB/C [older than 21 days]' FROM DUAL;

CREATE TABLE Links (id, protocol_id, strain_id ) AS
SELECT 1,     61846,           563 FROM DUAL UNION ALL
SELECT 2,     13487,           564 FROM DUAL UNION ALL
SELECT 3,     79465,           721 FROM DUAL UNION ALL
SELECT 4,     41699,           720 FROM DUAL;

CREATE TABLE Animals (id, group_id, strain_id ) AS
SELECT 24,    9666,            563 FROM DUAL UNION ALL
SELECT 25,    9666,            720 FROM DUAL;

Query 1:

SELECT l.protocol_id
FROM   links l
       INNER JOIN strains s
       ON s.id = l.strain_id
       INNER JOIN (
         SELECT REGEXP_SUBSTR( s.name, '\(.*?\)', 1, l.COLUMN_VALUE ) AS id
         FROM   strains s,
                TABLE( 
                  CAST(
                    MULTISET(
                      SELECT LEVEL
                      FROM   DUAL
                      CONNECT BY LEVEL <= REGEXP_COUNT( s.name, '\(.*?\)' )
                    ) AS SYS.ODCINUMBERLIST
                  )
                ) l,
                animals a
         WHERE  a.strain_id = s.id
         AND    a.group_id = 9666
       ) t
       ON s.name LIKE '%' || t.id || '%'

Results:

| PROTOCOL_ID |
|-------------|
|       61846 |
|       13487 |
|       41699 |
|       41699 |

PL/SQL - Use List Variable in Where In Clause

Use a collection:

CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);

Or use a built-in type like SYS.ODCIVARCHAR2LIST or SYS.ODCINUMBERLIST:

VARIABLE cursor REFCURSOR;

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

  your_collection(  1) := 'Some value';
  your_collection(  2) := 'Some other value';
  -- ...
  your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );

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

PRINT cursor;

---

Related Topics