Manage Learn to apply best practices and optimize your operations.

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 editor@searchoracle.com.




This was last published in January 2010

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.