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
Francois Degrelle's blog 
