开发者

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

开发者 https://www.devze.com 2023-01-09 23:56 出处:网络
I\'m trying to open a REF CURS开发者_StackOverflowOR using a select statement like.... OPEN myREF FOR SELECT * FROM MYTABLE WHERE MYID IN (:Values) USING myPassedInValList;

I'm trying to open a REF CURS开发者_StackOverflowOR 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 :(


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.


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;
0

精彩评论

暂无评论...
验证码 换一张
取 消