I have an Oracle stored procedure with an OUT parameter as ref cursor. I want to use the resultset in another stored procedure. I tried using both the FOR Loop and OPEN Cursor syntax, but it is not accessible. Can you help?
A ref cursor is already an open handle to a cursor in memory. Therefore you can’t use the cursor FOR loop (which implicitly opens a cursor locally) and OPEN (it’s already open!).
So, your other stored procedure can assume the ref cursor is already open, and simply fetch repetitively from it (or better yet, use Fetch-Bulk Collect into a collection).
Be sure to CLOSE the ref cursor after you’ve fetched all the rows you were after.
Dig Deeper on Using Oracle PL-SQL
Related Q&A from Dan Clamage
A reader asks Oracle expert Dan Clamage about a single date condition mucking up a dynamic query. Continue Reading
One reader asks expert Daniel Clamage about the PL/SQL to_date and to_char functions and how to properly convert date and string values. Continue Reading
Oracle PL-SQL expert Daniel Clamage answers a question about a problem with doing an “insert” in PL-SQL to create a table dynamically. Continue Reading