The Database Design Resource Center


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


Using an Oracle ref cursor in Java

How can you use an Oracle ref cursor in Java?

On a previous page, 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 applications:

Java example code

package demo.sample;

import java.sql._.css;
import java.util._.css;

/**
* 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: Ref cursor in Oracle

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 /
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.