how to use cursor variable with dynamic SQL in PL/SQL

From: Robert Yeh <robert.yeh_at_qwest.com>
Date: Sat, 21 Jul 2001 21:47:39 GMT
Message-ID: <9f5v9l$5b7$1_at_bob.news.rcn.net>


[Quoted] [Quoted] I was surprised that I did not find a way to do this in the document. I try [Quoted] [Quoted] to pass a cursor variable back to a calling program. Normally it is easy. I [Quoted] [Quoted] define a variable with ref cursor type. Then I open the cursor with a select [Quoted] statement like this:

[Quoted] [Quoted] OPEN my_cursor_variable FOR select * from emp; [Quoted] 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.

[Quoted] Any one get any idea?

Thanks Received on Sat Jul 21 2001 - 23:47:39 CEST

Original text of this message