| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Cursor Variables and Dynamic SQL
A copy of this was sent to George <info_at_expohire.com.au>
(if that email address didn't require changing)
On Thu, 11 Jun 1998 17:05:49 +1000, you wrote:
>Is it possible to use an OPEN CURSOR command in DBMS_SQL.parse() and
>return the cursor to a cursor variable??
>
>eg.
>
>PACKAGE type_dec
> TYPE result_cursor IS REF CURSOR;
>END;
>
>
>PROCEDURE dynamic_cursor_test(tcurList IN OUT type_dec.result_cursor)
>DECLARE
>
> cid INTEGER;
> cursor_record cursor_variable%ROWTYPE;
>
>BEGIN
> cid = DBMS_SQL.open_cursor;
> DBMS_SQL.parse(cid,'OPEN tcurList FOR
> SELECT .......',DBMS_SQL.v7);
> DBMS_SQL.close_cursor(cid);
>END;
>
>
>Does the tcurlist parameter now point to a cursor??
>
No, this cannot be done.
The problem is that when you call dbms_sql, the local variables in dynamic_cursor_test have gone out of scope (dbms_sql is the current scope, the local variables in dynamic_cursor_test are not visible).
Usually the workaround for this (local variables not in scope) is to put the variables you want to 'see' in dbms_sql in a package spec. Currently, cursor variables cannot be declared in a spec
SQL> create or replace PACKAGE type_dec
2 as
3 TYPE result_cursor IS REF CURSOR;
4
4 ACursor result_cursor;
5 END;
6 /
Warning: Package created with compilation errors.
SQL> show errors
Errors for PACKAGE TYPE_DEC:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/11 PLS-00994: Cursor Variables cannot be declared as part of a
package
4/11 PL/SQL: Declaration ignored
>
>Thanks in advance
>George
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Jun 11 1998 - 07:56:15 CDT
 |
 |