 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. I showed you how to write a function that returns an Oracle ref cursor. Now let's see how you can use it in your Java application:
Java example code package demo.sample;
import java.sql.*;
import java.util.*;
/**
* A simple sample class to illustrate how to use Stored Procedures/functions,
* and use an Oracle ref cursor in Java.
* To use the sample code - you will have to create a Connection object,
* and pass it as a parameter in the constructor of the class.
*/
public class AccountManager extends Object
{
Connection dbConnection;
public AccountManager(Connection
connection)
{
dbConnection = connection;
}
/**
* Get all accounts for for a given interval.
* @param int fromAccount - First account.
* @param int toAccount - Last account.
* @return Returns an ArrayList with
String-objects for all accounts in a given
intervall.
* @throws Exception
*/
public List getAccountInterval(int fromAccount, int toAccount) throws Exception
{
//Prepare the call to the stored function.
CallableStatement cstmt =
dbConnection.prepareCall("{? = call
GetRefCursors.sfGetAccountInterval(?, ?)}");
cstmt.setFetchSize(100);
//The statement will return a "ref cursor"
- or a ResultSet in Java terms...
cstmt.registerOutParameter(1,
oracle.jdbc.driver.OracleTypes.CURSOR);
//Set the stored function's in parameters
cstmt.setInt(2, fromAccount);
cstmt.setInt(3, toAccount);
//... and call the stored function...
cstmt.executeQuery();
//Get the ResultSet
ResultSet rs = (ResultSet)
cstmt.getObject(1);
//Create a list to hold the account information.
List accounts = new ArrayList();
//Iterate the ResultSet to fetch the accounts.
while (rs.next())
{
//Put information about each account into the list.
accounts.add(rs.getString("account_no") + " - " + rs.getString("name"));
}
//And then we tidy up by closing the ResultSet and the Statement.
rs.close();
cstmt.close();
//Return the newly created list of accounts.
return accounts;
}
} Now, this should hopefully set straight how you can utilize an Oracle ref cursor in Java. Feel free to print this out for your convenience.
You should, however, relate this subject to the page where I describe how you create the database package and function to create the cursor itself: |
