Unanswered: Variable parameters with the SQL IN function
Hi all, I am writing a stored procedure which receives a collection of program codes(char(5)). I want to open a cursor that selects the records having their program code in the list of codes I received in parameter.
The query looks like this:
SELECT ... FROM ... WHERE program IN (var, var, var);
I can't find how to use the IN function with a list of variable. Seems like it can only be used with fixed values. Am I wrong?
Is there a way to check if the program code of a record is in a list of program codes?
v_bigstring LONG := '#';
FOR i IN 1..collection.count LOOP
v_bigstring := v_bigstring || collection(i) || '#';
SELECT ... FROM ... WHERE INSTR(v_bigstring,'#'||program||'#') > 0;
This works by building a big string like #abc#919#hello#xxx# and then using INSTR to see if the required value is there delimited by #.
Well it seems to work well that way (i mean when the select clause is inside the begin section of the procedure). But when the select clause is for use with a cursor it won't work. Seems it doesn't like the 'instr' function or something...