Home » SQL & PL/SQL » SQL & PL/SQL » Using cursors and ref cursors
Using cursors and ref cursors [message #191125] Mon, 04 September 2006 21:28 Go to next message
smaddock
Messages: 1
Registered: September 2006
Location: Australia
Junior Member
Hi,

This is my first post on an Oracle forum as I cannot find a solution to the issue I'm having with using cursors and ref cursors.

The below code example is an overview of what I am trying to accomplish:

CREATE OR REPLACE PACKAGE SMALLPKG IS
TYPE genCursor IS REF CURSOR;
CURSOR mycur(x IN varchar) is
SELECT <lots of fields>
FROM <lots of tables>
WHERE field = x and ...;
PROCEDURE myproc1(x IN VARCHAR);
PROCEDURE myproc2(y IN VARCHAR, genCur OUT genCursor);
END;
/

CREATE OR REPLACE PACKAGE BODY SMALLPKG IS
PROCEDURE myproc1(x IN VARCHAR)
IS
myrec mycur%ROWTYPE;
BEGIN
FOR myrec IN mycur(x)
LOOP
<do lots of processing on record>
END LOOP;
END myproc1;
END;
/

Now the reason for the cursor 'mycur' is so data can be accessed by the procedure 'myproc1', and other PL/SQL scripts that access the same data. However, I would also like to be able to return the cursor 'mycur' as a ref cursor as I have a C++ application that requires a ref cursor.

Is there anyway to write 'myproc2' to do this? That being, use a cursor to return a ref cursor, rather than the usual:
open genCur for <select ...>;
?

Thanks,
Simon
Re: Using cursors and ref cursors [message #191438 is a reply to message #191125] Wed, 06 September 2006 06:37 Go to previous message
rigatonip
Messages: 50
Registered: December 2005
Member
Tom Kyte is doing something similar in this post. Maybe it will give you some ideas.

http://asktom.oracle.com/pls/ask/f?p=4950:8:17426964455252612343::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:246014735810
Previous Topic: how to get data from database to excel
Next Topic: Handing null nested table
Goto Forum:
  


Current Time: Sat Dec 03 05:52:54 CST 2016

Total time taken to generate the page: 0.11214 seconds