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:

"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

HOME \| NEWSLETTER \| WATERCOOLER
	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
Theory & Practice DB Normalization Analysis Phase Database Keys DB Glossary Appl.Architecture Oracle DBA MySQL DBA SQL Server DBA Install Oracle Install SQL Server Proj.Management Oracle Constraint Programming Tips 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.

Using an Oracle ref cursor in Java

How can you use an Oracle ref cursor in Java?

Java example code

Free eBook

What visitors say...

Database Normalization eBook: