| Using cursors and ref cursors [message #191125] |
Mon, 04 September 2006 21:28  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
