Re: how to use cursor variable with dynamic SQL in PL/SQL
Date: Sat, 21 Jul 2001 21:47:48 GMT
Message-ID: <9f68f902q7a_at_drn.newsguy.com>
In article <9f5v9l$5b7$1_at_bob.news.rcn.net>, "Robert says...
>
>I was surprised that I did not find a way to do this in the document. I try
>to pass a cursor variable back to a calling program. Normally it is easy. I
>define a variable with ref cursor type. Then I open the cursor with a select
>statement like this:
>
>OPEN my_cursor_variable FOR select * from emp;
>return my_cursor_variable;
>
>Now my SQL statement is dynamically constructed. I compose the sql
>statement and put the sql statement in a variable sql_command.
>
>OPEN my_cursor_variable FOR sql_command.
>This gave me an error saying it is expecting for a select.
>
>I did not see a way in DBMS_SQL package which I can use the cursor variable.
>
>Any one get any idea?
>
my idea - you don't say (no one seems to) what version you are on but it sounds like version 8.0 or before.
Dynamically opening a ref cursor:
ps$tkyte_at_ORA8I.WORLD> variable x refcursor
ops$tkyte_at_ORA8I.WORLD> set autoprint on
ops$tkyte_at_ORA8I.WORLD> declare
2 sql_statement varchar2(100) default 'select * from dual';
3 begin
4 open :x for sql_statement;
5 end;
6 /
PL/SQL procedure successfully completed.
D
-
X
was added in Oracle8i (8.1) and is quite impossible to do prior to that.
see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:562395023420
for methods to do this in a fashion in 8.0 and before.
>Thanks
>
>
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://asktom.oracle.com/ http://asktom.oracle.com/~tkyte/ Oracle Magazine: http://www.oracle.com/oramag [Quoted] Opinions are mine and do not necessarily reflect those of Oracle Corp Received on Sat Jul 21 2001 - 23:47:48 CEST