dynamic query in oracle procedure using cursor

Question

I am using a stored procedure to select records using a cursor. This procedure gets record id as input.

Here is the code:

create or replace
procedure GET_ITEM_DETAILS_ALL
(
  cur_out out sys_refcursor,
  PSTRING VARCHAR2 DEFAULT NULL
)
is
  query_string  VARCHAR2(1000);
Begin
  query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (:sl) order by IT.SL_NO';
  OPEN cur_out FOR query_string USING PSTRING;
End;

And for calling / testing the procedure I use the below code:

VAR R REFCURSOR
EXEC GET_ITEM_DETAILS_ALL (:R, '4')
PRINT R 

The problem is when I pass a single integer number such as '4' or '2', the procedure returns data, but when I pass '1,2' or '3,4', it shows error.

Answer 1

   OPEN cur_out FOR Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in (SELECT REGEXP_SUBSTR(sl,'[^,]+', 1, LEVEL) FROM DUAL CONNECT BY REGEXP_SUBSTR(sl, '[^,]+', 1, LEVEL) IS NOT NULL ) order by IT.SL_NO