Different Approaches to Dynamic IN Clause

View All Scripts Login and Run Script

Script Name Different Approaches to Dynamic IN Clause
Description The IN clause is a handy SQL feature, but (a) you can only have a maximum of 1000 items in the IN clause, and (b) sometimes you need to construct the IN clause dynamically. This script demonstrates a variety of ways you can do this.
Category PL/SQL General
Contributor Steven Feuerstein (Oracle)
Created Tuesday December 22, 2015

Statement 1

Set Up DB Objects Used in Package

CREATE TABLE in_clause_tab ( 
   ID INTEGER, 
   title VARCHAR2(100), 
   description VARCHAR2(2000))

Table created.

Statement 2

Set Up DB Objects Used in Package

BEGIN 
   INSERT INTO in_clause_tab 
        VALUES (1, 'How do I write a loop?' 
              , 'What are the different ways I can write a loop in PL/SQL'); 
 
   INSERT INTO in_clause_tab 
        VALUES (2, 'Use cursor FOR loop?' 
              , 'Is there a reason any longer to use a cursor FOR loop, since I can now use BULK COLLECT?'); 
 
   INSERT INTO in_clause_tab 
        VALUES (3, 'Which version of PL/SQL is best?' 
              , 'What is the best version of PL/SQL?'); 
 
   FOR indx IN 1 .. 10000 
   LOOP 
      INSERT INTO in_clause_tab 
           VALUES (indx + 3, 'Title ' || indx, 'Description ' || indx); 
   END LOOP; 
 
   COMMIT; 
END;

1 row(s) inserted.

Statement 3

Set Up DB Objects Used in Package

CREATE TYPE in_clause_ot AS OBJECT ( 
   ID            INTEGER 
 , title         VARCHAR2 (100) 
 , description   VARCHAR2 (2000) 
)

Type created.

Statement 4

Set Up DB Objects Used in Package

CREATE OR REPLACE TYPE in_clause_tab_nt IS TABLE OF in_clause_ot;

Type created.

Statement 5
Set Up DB Objects Used in Package
```
CREATE OR REPLACE TYPE pky_nt IS TABLE OF INTEGER; 
```
Type created.

Statement 6

An API to the different approaches offered. The static IN list is not included.

Package Specification of IN Clause "Driver"

CREATE OR REPLACE PACKAGE in_clause  
IS  
   -- Returns a cursor variable based on the weak ref cursor type.  
   FUNCTION nds_list ( list_in IN VARCHAR2 )  
      RETURN sys_refcursor;  
  
   FUNCTION nds_bulk_list ( list_in IN VARCHAR2 )  
      RETURN in_clause_tab_nt;  
  
   -- Use nds_bulk_list as a table function and return a cursor variable.  
   FUNCTION nds_bulk_list2 ( list_in IN VARCHAR2 )  
      RETURN sys_refcursor;  
  
   FUNCTION dbms_sql_list ( list_in IN VARCHAR2 )  
      RETURN in_clause_tab_nt;  
  
   FUNCTION nested_table_list ( list_in IN pky_nt )  
      RETURN sys_refcursor;  
  
   FUNCTION nested_table_list2 ( list_in IN pky_nt )  
      RETURN sys_refcursor;  
  
   FUNCTION member_of_list ( list_in IN pky_nt )  
      RETURN sys_refcursor;  
  
   /* Disabled until time when DBMS_UTILITY is executable in LiveSQL 
   PROCEDURE test_varieties (  
      iterations_in    IN   PLS_INTEGER DEFAULT 1  
    , list_in          IN   VARCHAR2 DEFAULT '1,3'  
    , show_timing_in   IN   BOOLEAN DEFAULT FALSE  
    , show_data_in     IN   BOOLEAN DEFAULT FALSE  
   ); */ 
END in_clause;

Package created.

Statement 7

Implementing the Different Approaches

CREATE OR REPLACE PACKAGE BODY in_clause 
IS 
   FUNCTION nds_list ( list_in IN VARCHAR2 ) 
      RETURN sys_refcursor 
   IS 
      retval sys_refcursor; 
   BEGIN 
      OPEN retval FOR    'SELECT * FROM in_clause_tab WHERE id IN (' 
                      || list_in 
                      || ')'; 
 
      RETURN retval; 
   END nds_list; 
 
   FUNCTION nds_bulk_list ( list_in IN VARCHAR2 ) 
      RETURN in_clause_tab_nt 
   IS 
      retval in_clause_tab_nt; 
   BEGIN 
      EXECUTE IMMEDIATE    
        'SELECT in_clause_ot (id, title, description)  
           FROM in_clause_tab WHERE id IN (' || list_in || ')' 
      BULK COLLECT INTO retval; 
 
      RETURN retval; 
   END nds_bulk_list; 
 
   FUNCTION nds_bulk_list2 ( list_in IN VARCHAR2 ) 
      RETURN sys_refcursor 
   IS 
      retval sys_refcursor; 
   BEGIN 
      OPEN retval FOR 
         SELECT ic.ID, ic.title, ic.description 
           FROM TABLE ( nds_bulk_list ( list_in )) ic; 
 
      RETURN retval; 
   END nds_bulk_list2; 
 
   FUNCTION dbms_sql_list ( list_in IN VARCHAR2 ) 
      RETURN in_clause_tab_nt 
   IS 
      l_query VARCHAR2 ( 32767 ) 
                               := 'SELECT * FROM in_clause_tab WHERE id IN ('; 
      l_cur PLS_INTEGER := DBMS_SQL.open_cursor; 
      l_feedback PLS_INTEGER; 
      l_ids pky_nt := pky_nt ( ); 
      l_row PLS_INTEGER; 
      l_onerow in_clause_tab%ROWTYPE; 
      retval in_clause_tab_nt := in_clause_tab_nt ( ); 
 
      PROCEDURE string_to_list ( 
         str                 IN       VARCHAR2 
       , list_inout          IN OUT   pky_nt 
       , delim               IN       VARCHAR2 DEFAULT ',' 
       , append_to_list_in   IN       BOOLEAN DEFAULT TRUE 
      ) 
      IS 
         l_loc PLS_INTEGER; 
         l_row PLS_INTEGER; 
         l_startloc PLS_INTEGER := 1; 
         l_item VARCHAR2 ( 32767 ); 
      BEGIN 
         IF append_to_list_in 
         THEN 
            l_row := NVL ( list_inout.LAST, 0 ) + 1; 
         ELSE 
            list_inout.DELETE; 
            l_row := 1; 
         END IF; 
 
         IF str IS NOT NULL 
         THEN 
            LOOP 
               l_loc := INSTR ( str, delim, l_startloc ); 
 
               IF l_loc = l_startloc 
               THEN 
                  l_item := NULL; 
               ELSIF l_loc = 0 
               THEN 
                  l_item := SUBSTR ( str, l_startloc ); 
               ELSE 
                  l_item := SUBSTR ( str, l_startloc, l_loc - l_startloc ); 
               END IF; 
 
               list_inout.EXTEND; 
               list_inout ( l_row ) := l_item; 
 
               IF l_loc = 0 
               THEN 
                  EXIT; 
               ELSE 
                  l_startloc := l_loc + 1; 
                  l_row := l_row + 1; 
               END IF; 
            END LOOP; 
         END IF; 
      END string_to_list; 
   BEGIN 
      -- Parse the delimited list to the collection. 
      string_to_list ( list_in, l_ids ); 
      l_row := l_ids.FIRST; 
 
      -- Build the list of bind variables. 
      WHILE ( l_row IS NOT NULL ) 
      LOOP 
         l_query := l_query || ':bv' || l_row || ','; 
         l_row := l_ids.NEXT ( l_row ); 
      END LOOP; 
 
      l_query := RTRIM ( l_query, ',' ) || ')'; 
      -- Define the columns to be queried. 
      DBMS_SQL.parse ( l_cur, l_query, DBMS_SQL.native ); 
      DBMS_SQL.define_column ( l_cur, 1, 1 ); 
      DBMS_SQL.define_column ( l_cur, 2, 'a', 100 ); 
      DBMS_SQL.define_column ( l_cur, 3, 'a', 2000 ); 
      -- Bind each variable in the provided list. 
      l_row := l_ids.FIRST; 
 
      WHILE ( l_row IS NOT NULL ) 
      LOOP 
         DBMS_SQL.bind_variable ( l_cur, ':bv' || l_row, l_ids ( l_row )); 
         l_row := l_ids.NEXT ( l_row ); 
      END LOOP; 
 
      -- Execute and then fetch each row. 
      l_feedback := DBMS_SQL.EXECUTE ( l_cur ); 
 
      LOOP 
         l_feedback := DBMS_SQL.fetch_rows ( l_cur ); 
         EXIT WHEN l_feedback = 0; 
         -- Retrieve individual column values and move them to the nested table. 
         DBMS_SQL.column_value ( l_cur, 1, l_onerow.ID ); 
         DBMS_SQL.column_value ( l_cur, 2, l_onerow.title ); 
         DBMS_SQL.column_value ( l_cur, 3, l_onerow.description ); 
         retval.EXTEND; 
         retval ( retval.LAST ) := 
            in_clause_ot ( l_onerow.ID, l_onerow.title, l_onerow.description ); 
      END LOOP; 
 
      DBMS_SQL.close_cursor ( l_cur ); 
      RETURN retval; 
   END dbms_sql_list; 
 
   FUNCTION nested_table_list ( list_in IN pky_nt ) 
      RETURN sys_refcursor 
   IS 
      retval sys_refcursor; 
   BEGIN 
      OPEN retval FOR 
         SELECT * 
           FROM in_clause_tab 
          WHERE ID IN ( SELECT column_value 
                         FROM TABLE ( list_in )); 
 
      RETURN retval; 
   END nested_table_list; 
 
   FUNCTION nested_table_list2 ( list_in IN pky_nt ) 
      RETURN sys_refcursor 
   IS 
      retval sys_refcursor; 
   BEGIN 
      OPEN retval FOR 
         SELECT in_clause_tab.* 
           FROM in_clause_tab, ( SELECT column_value 
                                  FROM TABLE ( list_in )) 
          WHERE in_clause_tab.ID = column_value; 
 
      RETURN retval; 
   END nested_table_list2; 
 
   FUNCTION member_of_list ( list_in IN pky_nt ) 
      RETURN sys_refcursor 
   IS 
      retval sys_refcursor; 
   BEGIN 
      OPEN retval FOR 
         SELECT * 
           FROM in_clause_tab 
          WHERE ID MEMBER OF list_in; 
 
      RETURN retval; 
   END member_of_list; 
 
   /* Disabled until DBMS_UTILITY available in LiveSQL 
   PROCEDURE test_varieties ( 
      iterations_in    IN   PLS_INTEGER DEFAULT 1 
    , list_in          IN   VARCHAR2 DEFAULT '1,3' 
    , show_timing_in   IN   BOOLEAN DEFAULT FALSE 
    , show_data_in     IN   BOOLEAN DEFAULT FALSE 
   ) 
   IS 
      l_start_time PLS_INTEGER; 
 
      PROCEDURE start_timing 
      IS 
      BEGIN 
         l_start_time := DBMS_UTILITY.get_cpu_time; 
      END start_timing; 
 
      PROCEDURE show_elapsed 
      IS 
      BEGIN 
         DBMS_OUTPUT.put_line (    '  Elapsed CPU time: ' 
                                || TO_CHAR (   DBMS_UTILITY.get_cpu_time 
                                             - l_start_time 
                                           ) 
                              ); 
      END show_elapsed; 
 
      PROCEDURE init_test ( str_in IN VARCHAR2 ) 
      IS 
      BEGIN 
         IF show_timing_in 
         THEN 
            start_timing; 
         END IF; 
 
         DBMS_OUTPUT.put_line ( str_in ); 
      END init_test; 
 
      PROCEDURE finish_test 
      IS 
      BEGIN 
         IF show_timing_in 
         THEN 
            show_elapsed; 
         END IF; 
      END finish_test; 
 
      PROCEDURE test_nds_list 
      IS 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from NDS_LIST' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := nds_list ( list_in ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_nds_list; 
 
      PROCEDURE test_nds_bulk_list 
      IS 
         l_array in_clause_tab_nt; 
         l_row PLS_INTEGER; 
      BEGIN 
         init_test ( 'Output from NDS_BULK_LIST' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_array := nds_bulk_list ( list_in ); 
            l_row := l_array.FIRST; 
 
            WHILE ( l_row IS NOT NULL ) 
            LOOP 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_array ( l_row ).title ); 
               END IF; 
 
               l_row := l_array.NEXT ( l_row ); 
            END LOOP; 
         END LOOP; 
 
         finish_test; 
      END test_nds_bulk_list; 
 
      PROCEDURE test_nds_bulk_list2 
      IS 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from NDS_BULK_LIST2' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := nds_bulk_list2 ( list_in ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_nds_bulk_list2; 
 
      PROCEDURE test_dbms_sql_list 
      IS 
         l_array in_clause_tab_nt; 
         l_row PLS_INTEGER; 
      BEGIN 
         init_test ( 'Output from DBMS_SQL_LIST' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_array := dbms_sql_list ( list_in ); 
            l_row := l_array.FIRST; 
 
            WHILE ( l_row IS NOT NULL ) 
            LOOP 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_array ( l_row ).title ); 
               END IF; 
 
               l_row := l_array.NEXT ( l_row ); 
            END LOOP; 
         END LOOP; 
 
         finish_test; 
      END test_dbms_sql_list; 
 
      PROCEDURE test_nested_table_list 
      IS 
         l_list pky_nt := pky_nt ( 1, 3 ); 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from NESTED_TABLE_LIST' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := nested_table_list ( l_list ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_nested_table_list; 
 
      PROCEDURE test_nested_table_list2 
      IS 
         l_list pky_nt := pky_nt ( 1, 3 ); 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from NESTED_TABLE_LIST - Join' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := nested_table_list2 ( l_list ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_nested_table_list2; 
 
      PROCEDURE test_member_of_list 
      IS 
         l_list pky_nt := pky_nt ( 1, 3 ); 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from MEMBER_OF_LIST' ); 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := member_of_list ( l_list ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_member_of_list; 
 
      PROCEDURE test_static_in_list 
      IS 
         l_list pky_nt := pky_nt ( ); 
         l_cv sys_refcursor; 
         l_one in_clause_tab%ROWTYPE; 
      BEGIN 
         init_test ( 'Output from STATIC_IN_LIST' ); 
         l_list.EXTEND ( 1000 ); 
         l_list ( 1 ) := 1; 
         l_list ( 2 ) := 3; 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := static_in_list ( l_list ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
         -- 
         init_test ( 'Output from STATIC_IN_LIST_SMALL(50)' ); 
         l_list.EXTEND ( 50 ); 
         l_list ( 1 ) := 1; 
         l_list ( 2 ) := 3; 
 
         FOR indx IN 1 .. iterations_in 
         LOOP 
            l_cv := static_in_list ( l_list ); 
 
            LOOP 
               FETCH l_cv 
                INTO l_one; 
 
               EXIT WHEN l_cv%NOTFOUND; 
 
               IF indx = 1 AND show_data_in 
               THEN 
                  DBMS_OUTPUT.put_line ( '  ' || l_one.title ); 
               END IF; 
            END LOOP; 
 
            CLOSE l_cv; 
         END LOOP; 
 
         finish_test; 
      END test_static_in_list; 
   BEGIN 
      test_nds_list; 
      test_nds_bulk_list; 
      test_nds_bulk_list2; 
      test_dbms_sql_list; 
      test_nested_table_list; 
      test_nested_table_list2; 
      test_member_of_list; 
      test_static_in_list; 
   END test_varieties; 
*/ 
/* Example of results of testing: 
 
11.2 
 
Output from NDS_LIST 
  Elapsed CPU time: 200 
Output from NDS_BULK_LIST 
  Elapsed CPU time: 217 
Output from NDS_BULK_LIST2 
  Elapsed CPU time: 233 
Output from DBMS_SQL_LIST 
  Elapsed CPU time: 223 
Output from NESTED_TABLE_LIST 
  Elapsed CPU time: 186 
Output from NESTED_TABLE_LIST - Join 
  Elapsed CPU time: 181 
Output from MEMBER_OF_LIST 
  Elapsed CPU time: 879 
Output from STATIC_IN_LIST 
  Elapsed CPU time: 284 
Output from STATIC_IN_LIST_SMALL(50) 
  Elapsed CPU time: 250 
   
 
10gR1 
 
Output from NDS_LIST 
  Elapsed CPU time: 254 
Output from NDS_BULK_LIST 
  Elapsed CPU time: 220 
Output from NDS_BULK_LIST2 
  Elapsed CPU time: 269 
Output from DBMS_SQL_LIST 
  Elapsed CPU time: 269 
Output from NESTED_TABLE_LIST 
  Elapsed CPU time: 315 
Output from MEMBER_OF_LIST 
  Elapsed CPU time: 1066 
Output from STATIC_IN_LIST 
  Elapsed CPU time: 421 
 
10gR2 - 1000 iterations, short list 
 
Output from NDS_LIST 
  Elapsed CPU time: 259 
Output from NDS_BULK_LIST 
  Elapsed CPU time: 217 
Output from NDS_BULK_LIST2 
  Elapsed CPU time: 277 
Output from DBMS_SQL_LIST 
  Elapsed CPU time: 251 
Output from NESTED_TABLE_LIST 
  Elapsed CPU time: 337 
Output from NESTED_TABLE_LIST - Join 
  Elapsed CPU time: 331 
Output from NESTED_TABLE_LIST w/o CAST 
  Elapsed CPU time: 323 
Output from MEMBER_OF_LIST 
  Elapsed CPU time: 1062 
Output from STATIC_IN_LIST 
  Elapsed CPU time: 367 
Output from STATIC_IN_LIST_SMALL(50) 
  Elapsed CPU time: 367 
   
10gR2 - 10000 iterations, longer list 
 
Output from NDS_LIST 
  Elapsed CPU time: 314 
Output from NDS_BULK_LIST 
  Elapsed CPU time: 272 
Output from NDS_BULK_LIST2 
  Elapsed CPU time: 357 
Output from DBMS_SQL_LIST 
  Elapsed CPU time: 485 
Output from NESTED_TABLE_LIST 
  Elapsed CPU time: 369 
Output from NESTED_TABLE_LIST - Join 
  Elapsed CPU time: 363 
Output from NESTED_TABLE_LIST w/o CAST 
  Elapsed CPU time: 331 
Output from MEMBER_OF_LIST 
  Elapsed CPU time: 1054 
Output from STATIC_IN_LIST 
  Elapsed CPU time: 374 
Output from STATIC_IN_LIST_SMALL(50) 
  Elapsed CPU time: 381 
     
10gR2 - 10000 iterations 
 
Output from NDS_LIST 
  Elapsed CPU time: 2700 
Output from NDS_BULK_LIST 
  Elapsed CPU time: 2203 
Output from NDS_BULK_LIST2 
  Elapsed CPU time: 2902 
Output from DBMS_SQL_LIST 
  Elapsed CPU time: 2523 
Output from NESTED_TABLE_LIST 
  Elapsed CPU time: 3387 
Output from NESTED_TABLE_LIST - Join 
  Elapsed CPU time: 3333 
Output from NESTED_TABLE_LIST w/o CAST 
  Elapsed CPU time: 3482 
Output from MEMBER_OF_LIST 
  Elapsed CPU time: 11141 
Output from STATIC_IN_LIST 
  Elapsed CPU time: 4012 
Output from STATIC_IN_LIST_SMALL(50) 
  Elapsed CPU time: 4413 
*/ 
END in_clause;

Package Body created.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database
Latest Database Tutorials