How to use a stored procedure to fetch from ref cursor in Oracle

Oracle expert Dan Clamage explains how to use a stored procedure to fetch from a ref cursor in Oracle in this tip.

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.

Have a question for Dan Clamage? Send an e-mail to [email protected].




Dig Deeper on Using Oracle PL-SQL