Cursor with variable in an "IN CLAUSE"

SQL> with t as
(SELECT ',001,002,003,004,005,006,007,008,ALL,' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, INSTR(sList, ',', 1, LEVEL) + 1,
INSTR(sList, ',', 1, LEVEL + 1) - INSTR(sList, ',', 1, LEVEL) - 1)
2 3 4 5 6 FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE(sList,',')) - 1 7 ;

LEVEL SUBSTR(SLIST,INSTR(SLIST,',',1,LEVEL)
---------- -------------------------------------
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.

SQL>

SQL> with t as
(SELECT q'!('001','002','003','004','005','006','007','008','ALL')!' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, 3 + 6*(LEVEL - 1), 3)
FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE( 2 3 4 5 sList, ',')) + 1
6 7 ;

LEVEL SUB
---------- ---
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.


SQL> with t as
(SELECT ',001,002,003,004,005,006,007,008,ALL,' sList
FROM dual)
SELECT LEVEL, SUBSTR(sList, INSTR(sList, ',', 1, LEVEL) + 1, 3)
FROM t
CONNECT BY LEVEL <= LENGTH(sList) - LENGTH(REPLACE(s 2 3 4 5 6 List,',')) - 1;

LEVEL SUB
---------- ---
1 001
2 002
3 003
4 004
5 005
6 006
7 007
8 008
9 ALL

9 rows selected.

SQL>