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.

Solution 1

The problem is PSTRING is a single variable not an array. So your statement actually equates to

.... where IT.SL_NO = PSTRING

That's why it works when you pass 4 and fails where you pass 1,2.

You get no value in using dynamic SQL the way you do (we can open ref cursor without using strings). However, taking advantage of dynamic SQL is one way of solving your problem:

query_string := 'Select IT.SL_NO from ITEM_DETAILS IT where IT.SL_NO in ('
                   ||PSTRING||
                   ') order by IT.SL_NO';
OPEN cur_out FOR query_string;

Alternatively you can use a string tokenizer to turn the string into, well, into tokens. Unfortunately Oracle doesn't have a standard built-in but there are various workarounds for different versions of the database. Adrian Billington has a good round-up on his site. Using one of those approaches allows you to ditch the dynamic SQL:

OPEN cur_out FOR select IT.SL_NO from ITEM_DETAILS IT 
   where IT.SL_NO in ( select * from table ( your_string_tokenizer( PSTRING ) ) ) 
   order by IT.SL_NO;

Solution 2

   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