Oracle - Understanding the 'Using' Keyword...?

Question

I'm trying to open a REF CURSOR using a select statement like....

OPEN myREF FOR SELECT * FROM MYTABLE WHERE MYID IN (:Values) USING myPassedInValList;

But this only work when myPassedInValList has only a single ID in it. I'm trying to use a list like '1','2','5', etc...

Can someone tell me why this doesn't work.

I tried to Google but 'Using' is an awfully common word :(

Answer 1

To the best of my knowledge, this is not possible. You could try to get around it with dynamic SQL: concatenate the string OPEN myREF FOR SELECT * FROM MYTABLE WHERE MYID IN ( to the list of actual values, then open the cursor.

http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/dynamic.htm#i13057

/*you could build this string dynamically by looking through myPassedInValList and
concatenating them in the IN list.*/
v_stmt_str := 'SELECT * FROM MYTABLE WHERE MYID IN (''1'',''2'')'

/*Open cursor & specify bind argument in USING clause:*/
OPEN v_emp_cursor FOR v_stmt_str;

Answer 2

You can't use a user-defined type (presumably myPassedInValList is a varray or nested table?) in SQL like that. In order to reference the values from the UDT, you'll need to use the table function, like so:

OPEN myREF 
     FOR SELECT * 
         FROM MYTABLE 
         WHERE MYID IN (select * from table(:Values)) 
     USING myPassedInValList;

The table function will translate the array into a table-like object in memory, which allows you to access it's contents in SQL.

This will only work if your UDT is defined as a database object (CREATE TYPE...). If it's defined in you package, the SQL won't be able to see the definition.