The Database Design Resource Center


Oracle DBA | SQL Server DBA | Application Architecture
Software tools | Programming tips | Database fundamentals


Using a ref cursor in Oracle PL/SQL

Using a ref cursor is an easy way to give access to the content of a database table from eiter PL/SQL or Java, or most other programming languages, for that matter.

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: Alf A. Pedersen www.databasedesign-resource.com
-- ** Version: : Apr. 10 2006
-- **
-- ***********************************************************************
-- ** 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: Alf A. Pedersen www.databasedesign-resource.com
-- ** Version: : Apr. 10 2006
-- **
-- ***********************************************************************

-- ***********************************************************************
-- ** 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


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials

Database Normalization eBook:


Database Normalization eBook



Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.