Using a sys_refcursor Output Parameter from a Oracle Stored Procedure

by EvanJPalmer

In your SQL IDE, such as PL SQL Developer it’s not entirely straight forward to access the results of an Oracle stored procedure, when the results are returned via an output parameter of type sys_refcursor.

In short, here’s the code:


DECLARE
l_rc sys_refcursor;
l_rec my_rules%rowtype;
BEGIN
mypackage."MyProcName"( l_rc );
LOOP
FETCH l_rc INTO l_rec;
EXIT WHEN l_rc%NOTFOUND;

dbms_output.put_line( l_rec.field_one || ' ' || l_rec.field_two );
END LOOP;

CLOSE l_rc;
END;

An explanation:

Firstly we need to declare some variables to use. l_rc is a cursor that we will pass to the procedure, to store the output results.
l_rec is a record into which we’ll store each iteration of the cursor.

After the declaration we execute the package, populating the cursor.

We then loop over the cursor, storing the value in to record, or row, that we declared above, ending when the cursor is empty.

Remember to close the cursor to release the context area in memory.

Thank you to Justin on StackOverflow for answering my question.

As an aside, moving from SQL Server to Oracle has been interesting. I’ve found several commands or concepts that are trivial in SQL Server to be a little challenging in Oracle.

  • No auto increment for primary keys (before version 12) have to use triggers instead
  • The concept of Packages, with a public spec and a private body
  • We don’t have to tell parameters how long their nvarchar2 is, but we do in the table (this is cool logically, but feels inconsistent)
  • The concept of the ORA file
Advertisements