Using a ref cursor in Oracle PL/SQL

The following example shows how you can define a global ref cursour in your Package Specification, and how it is transformed to a local cursor for any given function in the Package Body:
CREATE OR REPLACE PACKAGE GetRefCursors IS

-- ***********************************************************************
-- ** Author: John Doe www.databasedesign-resource.com
-- ** Version: : Apr. 10 2019
-- **
-- ***********************************************************************
-- ** General global cursor for all functions returning result sets.

TYPE csGetResultSet is REF CURSOR;

-- ***********************************************************************
-- ** Get all accounts for a given interval
-- ** In parameters:
-- ** First account
-- ** Last account
-- ** Returns:
-- ** Ref Cursor for the given account interval.
-- ***********************************************************************

function sfGetAccountInterval
( pFirstAccount in ACCOUNTS.ACCOUNT_NO%type
,pLastAccount in ACCOUNTS.ACCOUNT_NO%type)
return csGetResultSet;

end GetRefCursors;
/

CREATE OR REPLACE package body GetRefCursors is
-- ***********************************************************************
-- ** Author: John Doe www.databasedesign-resource.com
-- ** Version: : Apr. 10 2019
-- **
-- ***********************************************************************

-- ***********************************************************************
-- ** Get all accounts for a given interval
-- ** In parameters:
-- ** First account
-- ** Last account
-- ** Returns:
-- ** Ref Cursor for the given account interval.
-- ***********************************************************************

function sfGetAccountInterval
( pFirstAccount in ACCOUNTS.ACCOUNT_NO%type
,pLastAccount in ACCOUNTS.ACCOUNT_NO%type)
return csGetResultSet is

csGetAccounts csGetResultSet;

begin

open csGetAccounts for

SELECT accounts.account_no,accounts.name
FROM accounts
WHERE accounts.account_no BETWEEN pFirstAccount AND pLastAccount
ORDER BY accounts.account_no;

return csGetAccounts;

end sfGetAccountInterval;

end GetRefCursors;
/

The global cursour csGetResultSet has to be specified in the Package Specification, but is redirected to the local cursor csGetAccounts so it can be returned to the calling module from within that function.

This can be used as a template to build a framework for handling all SELECT access to your tables through such a mechanism. This way you will deliver a generic and consistent interface for all applications and/or modules that need to access data within your different tables.

Not to mention: The DBA can easily tune the various SELECT statements without even touching the application(s), no matter how many different modules that are accessing a given table.

Time saved, and secure, identical and (hopefully) error-free code is delivered to anyone from just one place.

If you need to learn how you can use a ref cursor in Oracle using Java, read here: Oracle ref cursor in Java

Other important resources on this site:

Oracle DBA
SQL Server DBA
Programming tips
Database fundamentals

Return to Programming