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

If you want to use dbms_sql
you need to have the following code
declare
cur_handle binary_integer;
sqlstr varchar2(2000);
begin
cur_handle := dbms_sql.open;
dbms_sql.parse(cur_handle, sqlstr, dbms_sql.native)

(check the Oracle Supplied Packages references )

It appears open for must have a static statement or anything which evaluates to a string, so no variables.

Hth,

Sybrand Bakker, Oracle DBA

"Robert Yeh" <robert.yeh_at_qwest.com> wrote in message news:9f66jj$7cm$1_at_bob.news.rcn.net...
> I try
> OPEN my_cursor_variable FOR :sql_command;
>
> It gave me the same error message.
>
> DBMS_SQL.PARSE does not return me a cursor reference so I can pass it to
> other program.
>
>
> Sybrand Bakker wrote in message ...
> >
> >"Robert Yeh" <robert.yeh_at_qwest.com> wrote in message
> >news:9f5v9l$5b7$1_at_bob.news.rcn.net...
> >> 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?
> >>
> >> Thanks
> >>
> >>
> >IIRC it should be :sql_command
> >your command string is the second parameter in a call to dbms_sql.parse
> >but as you can use Native Dynamic Sql, you should use that. It is
 supposed
> >to be more efficient.
> >
> >Hth,
> >
> >Sybrand Bakker, Oracle DBA
> >
> >
> >
>
>
Received on Sat Jul 21 2001 - 23:47:46 CEST