A dynamic "IN" clause within a PL/SQL block

It is not the first time i see some posts on forums that ask:

How can I make a dynamic IN clause within a PL/SQL block when the list of values for the IN clause is provided with a string or a table column ?

I have seen, on the ORAQA a post that explains "How to SELECT from a comma delimited list"
with the following instruction:

select * from table(sys.dbms_debug_vc2coll(1,2,'a'))

But it is not possible to populate the collection with the content of another string.

For example, the following code does not give the correct result:

SQL> set serveroutput on
SQL>
SQL> Declare
  2    LC$List  Varchar2(30) := '10,30' ;
  3    ttab sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll( LC$List ) ;
  4    Cursor Cur is Select * from table( cast(ttab  as sys.dbms_debug_vc2coll ) );
  5  Begin
  6    For C in Cur Loop
  7        Dbms_Output.put_Line( C.COLUMN_VALUE ) ;
  8    End loop ;
  9  End ;
 10 
 11  /
10,30

PL/SQL procedure successfully completed.

SQL>

because only the first element of the collection is initialized with the value '10,30'

So we have to populate the collection with every single value, which is done with the following function:

CREATE OR REPLACE FUNCTION Dynamic_In ( PC$list IN VARCHAR2 )
RETURN sys.dbms_debug_vc2coll
IS
  ttab sys.dbms_debug_vc2coll := sys.dbms_debug_vc2coll() ;
  LC$Token  VARCHAR2(100) ;
  i         PLS_INTEGER := 1 ;
BEGIN
  -- Populate the collection --
  LOOP
    LC$Token := Split( PC$List, i , ',') ;
    EXIT WHEN LC$Token IS NULL ;
    ttab.extend ;
    ttab(ttab.COUNT) := LC$Token ;
    i := i + 1 ;
  END LOOP ;
  RETURN ttab ;
END ;

Finally, we can use this function to get the correct result

SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    LC$List  VARCHAR2(30) := '10,30' ;
  3    CURSOR Cur IS
  4    SELECT * FROM DEPT
  5    WHERE  DEPTNO IN ( SELECT * FROM TABLE( CAST ( Dynamic_In(LC$List) AS sys.dbms_debug_vc2coll ) ) ) ;
  6  BEGIN
  7    FOR C IN Cur LOOP
  8      Dbms_output.put_line( C.deptno || ' -> ' || C.Loc ) ;
  9    END LOOP ;
 10  END ;
 11 
 12  /
10 -> NEW YORK
30 -> CHICAGO

Procédure PL/SQL terminée avec succès.

SQL>

In this example, the list of values for the IN clause is stored in a varchar2 variable, but it can be read from a table column as well.

You can find the source of the Split() function here

Francois

Comments

[mccalla]

Hi,   Thanks so much for posting your solution.  It really works!  Saved me a lot of time.Thanks again!

[McM]

Without using the IN operator, DECLARE    LC$List  VARCHAR2(30) := '10,30' ; BEGIN     LC$List := ','|| LC$List ||','; -- to enclose numbers     FOR c IN (SELECT * FROM DEPT                 WHERE  INSTR(LC$List, ','|| DEPTNO ||',') > 0             )     LOOP       DBMS_OUTPUT.PUT_LINE( C.deptno || ' -> ' || C.Loc ) ;     END LOOP ; END ;

[Francois Degrelle]

Excellent ;D

[adsm]

You can also buid your IN list by concataning varchar2 elements and then use your concatenated string in an 'execute immediate' statement.