| 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: ref cursor with Dynamic SQL
A copy of this was sent to merex_at_my-deja.com
(if that email address didn't require changing)
On Wed, 04 Aug 1999 17:57:48 GMT, you wrote:
>I am using a ref cursor so that I can return a ResultSet to a Java
>program. I used Thomas Kyte's sample code.
>
>This ref cursor must be created with dynamic sql. I am having
>trouble with part of the follwing PL/SQL(look for the ???).
>
prior to Oracle8i, release 8.1, it is impossible to dynamically open a ref cursor in plsql.
In Oracle8i, release 8.1, you can code:
is
begin
open l_cursor for 'select * from ' || some_variable; end;
for example. the "open l_cursor" can be followed either by static sql (as in 8.0 and before) or a string (new to 8.1).
>I know about the DBMS_SQL package. Does it work with ref cursors?
>
No it does not. Here is a possible solution that you can try prior to 8.1 and see if it satisfies your needs:
SQL> create or replace package types
2 as
3 type refCur is ref cursor;
4 end;
5 /
Package created.
SQL>
SQL> drop table demo;
Table dropped.
SQL> create table demo as select * from all_users where 1=0;
Table created.
SQL> SQL> SQL> variable r refcursor SQL> SQL> create or replace procedure get_rs( p_cursor in out types.refCur ) 2 as 3 exec_cursor integer default dbms_sql.open_cursor; 4 rows_processed number default 0;5 begin
6 dbms_sql.parse(exec_cursor, 7 'insert into demo 8 select * 9 from all_users 10 where rownum < 10', 11 dbms_sql.native ); 12 12 rows_processed := dbms_sql.execute(exec_cursor); 13 dbms_sql.close_cursor( exec_cursor ); 14 14 open p_cursor for select * from demo; 15 15 delete from demo;
Procedure created.
SQL>
SQL> exec get_rs( :r )
PL/SQL procedure successfully completed.
SQL> select * from demo;
no rows selected
SQL> print r
USERNAME USER_ID CREATED ------------------------------ ---------- --------- SYS 0 01-SEP-97 SYSTEM 5 01-SEP-97 DBSNMP 17 01-SEP-97 TRACESVR 19 01-SEP-97 WEB$RPPRASAD 1791 01-SEP-97 WEB$GBRADSHA 1792 01-SEP-97 WEB$JBROTHER 1813 01-SEP-97 WEB$KKISER 1793 01-SEP-97 WEB$GDEYOUNG 1794 01-SEP-97
9 rows selected.
we can dynamically build the select into an INSERT statement and execute that. that puts the data into a 'temp' table (demo in this example). we open the ref cursor against that table and then delete the data in the temp table.
due to our multi-versioning and read consistency ,the ref cursor will see the table as it existed when the ref cursor was opened. even though you deleted the data, the ref cursor will still see it. so the demo table always *appears* to be empty in all cases, only the ref cursor can "see" data in it. The data will be available in the ref cursor for at least as long as the transaction is open (until you commit or rollback) and in most every case, for much longer (if you hit an ORA-1555 it means you committed and the data we need is no longer in the rollback segment -- as long as you don't commit, it'll be there)
when you get oracle8i, release 8.1, you will alter this procedure (if you want) to be simply:
SQL> create or replace procedure get_rs( p_cursor in out types.refCur )
2 as
3 begin
4 open p_cursor for 'select * 5 from all_users 6 where rownum < 10';7
Procedure created.
and it'll work just the same...
>Thanks in advance,
>Ross Cormier
>
>create or replace package hp3000_pkg as
> type cursorType is ref cursor;
> function gotoRow(vendnum in integer, rowcount in integer) return
>hp3000_pkg.cursortype;
>end;
>
>CREATE or replace PACKAGE BODY hp3000_pkg AS
> function gotoRow(vendnum in integer, rowcount in integer) return
>hp3000_pkg.cursortype
> as
> l_cursor cursorType;
> c integer;
> l_rec d_po%rowtype;
> begin
> --Here is the line I am having trouble with. How do you do this
>dynamically?????
> open l_cursor for select vend_num from d_po where vend_num =
>vendnum;
> loop
> fetch l_cursor into l_rec;
> exit when l_cursor%NOTFOUND;
> c := c + 1;
> if c = rowcount then
> exit;
> end if;
> end loop;
> return l_cursor;
> end gotoRow;
>end hp3000_pkg;
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 04 1999 - 13:33:53 CDT
 |
 |