JDBC CallableStatement – Stored Procedure CURSOR example

For Oracle stored procedure returns CURSOR parameter, you can

  1. Registered via JDBC CallableStatement.registerOutParameter(index,OracleTypes.CURSOR).
  2. Get it back via callableStatement.getObject(index).

See code snippets


//getDBUSERCursor is a stored procedure
String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);
callableStatement.setString(1, "mkyong");
callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

// execute getDBUSERCursor store procedure
callableStatement.executeUpdate();

// get cursor and cast it to ResultSet
rs = (ResultSet) callableStatement.getObject(2);

// loop it like normal
while (rs.next()) {
	String userid = rs.getString("USER_ID");
	String userName = rs.getString("USERNAME");
}			

JDBC CallableStatement CURSOR Example

See a full JDBC CallableStatement example for OUT CURSOR parameter.

1. Stored Procedure

A Oracle stored procedure, with one IN and one OUT CURSOR parameter. Later, calls it via JDBC.


CREATE OR REPLACE PROCEDURE getDBUSERCursor(
	   p_username IN DBUSER.USERNAME%TYPE,
	   c_dbuser OUT SYS_REFCURSOR)
IS
BEGIN

  OPEN c_dbuser FOR
  SELECT * FROM DBUSER WHERE USERNAME LIKE p_username || '%';

END;
/

2. Calls Stored Procedure via CallableStatement

JDBC example to call above stored procedure, cast the returned CURSOR to ResultSet and loop through the records sequentially.

File : JDBCCallableStatementCURSORExample.java


package com.mkyong.jdbc;

import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import oracle.jdbc.OracleTypes;

public class JDBCCallableStatementCURSORExample {

	private static final String DB_DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String DB_CONNECTION = "jdbc:oracle:thin:@localhost:1521:MKYONG";
	private static final String DB_USER = "user";
	private static final String DB_PASSWORD = "password";

	public static void main(String[] argv) {

		try {

			callOracleStoredProcCURSORParameter();

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

	}

	private static void callOracleStoredProcCURSORParameter()
			throws SQLException {

		Connection dbConnection = null;
		CallableStatement callableStatement = null;
		ResultSet rs = null;

		String getDBUSERCursorSql = "{call getDBUSERCursor(?,?)}";

		try {
			dbConnection = getDBConnection();
			callableStatement = dbConnection.prepareCall(getDBUSERCursorSql);

			callableStatement.setString(1, "mkyong");
			callableStatement.registerOutParameter(2, OracleTypes.CURSOR);

			// execute getDBUSERCursor store procedure
			callableStatement.executeUpdate();

			// get cursor and cast it to ResultSet
			rs = (ResultSet) callableStatement.getObject(2);

			while (rs.next()) {
				String userid = rs.getString("USER_ID");
				String userName = rs.getString("USERNAME");
				String createdBy = rs.getString("CREATED_BY");
				String createdDate = rs.getString("CREATED_DATE");

				System.out.println("UserName : " + userid);
				System.out.println("UserName : " + userName);
				System.out.println("CreatedBy : " + createdBy);
				System.out.println("CreatedDate : " + createdDate);
			}

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		} finally {

			if (rs != null) {
				rs.close();
			}

			if (callableStatement != null) {
				callableStatement.close();
			}

			if (dbConnection != null) {
				dbConnection.close();
			}

		}

	}

	private static Connection getDBConnection() {

		Connection dbConnection = null;

		try {

			Class.forName(DB_DRIVER);

		} catch (ClassNotFoundException e) {

			System.out.println(e.getMessage());

		}

		try {

			dbConnection = DriverManager.getConnection(
				DB_CONNECTION, DB_USER,DB_PASSWORD);
			return dbConnection;

		} catch (SQLException e) {

			System.out.println(e.getMessage());

		}

		return dbConnection;

	}

}

Done.

Reference

  1. http://www.oradev.com/ref_cursor.jsp
  2. http://download.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html
  3. http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html
  4. http://onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

Leave a Reply

avatar
newest oldest most voted
Danny
Guest
Danny

Hi, is in mySQL is the same sintax?

Julian
Guest
Julian

Hi there. Same question. Why do you use callableStatement.executeUpdate. But my other question is, why even use a CallableStatement? Why not just execute a “Call procedure()”, as one would use for a normal in Java code defined query? It works by just executing a “Call procedure()” just fine. Tested it. If you call the procedure from the command line or DB IDE, and do the same with the same query used in the procedure, and they return the exact same output, it works fine.

sam
Guest
sam

Here all the examples are given considering oracle database only.
It will b really helpful if u can post some of them for mysql also.

thanks in advance……

Aashish K Shandilya
Guest
Aashish K Shandilya
Hi there ! I am using the same program to fetch data from Oracle DB in java with CURSOR. The error that I am getting is – A fatal error has been detected by the Java Runtime Environment: # # EXCEPTION_ACCESS_VIOLATION (0xc0000005) at pc=0x61afe929, pid=7356, tid=7248 # # JRE version: 6.0_21-b07 # Java VM: Java HotSpot(TM) Client VM (17.0-b17 mixed mode, sharing windows-x86 ) # Problematic frame: # C [oracommon11.dll+0x14e929] # # An error report file with more information is saved as: # F:\MyTeam02\workspace\Electronic_Voting_System\hs_err_pid7356.log # # If you would like to submit a bug report, please visit: # http://java.sun.com/webapps/bugreport/crash.jsp #… Read more »
Vivek
Guest
Vivek

Hi,

How to flush the out parameter ?
If I call “callOracleStoredProcCURSORParameter” method twice in my code it duplicate the resultsetvalue.
Any solution for this ?

Oleg Aizatulin
Guest
Oleg Aizatulin

Why do you use callableStatement.executeUpdate instead of callableStatement.execute?

Shivkumar Vishweshwaraiah
Guest
Shivkumar Vishweshwaraiah

Could you please provide code listing for a Spring JdbcTemplate Stored Procedure call (for Oracle)? Also note that the stored procedure has both input and output parameters, AND it returns a result set (not via OUT parameters).

Umair Aslam
Guest
Umair Aslam

Can you please provide the script for mysql stored procedure of above example ??

trackback
JDBC Tutorials

[…] JDBC CallableStatement – Stored Procedure CURSOR example Stored procedure CURSOR parameter via JDBC CallableStatement. […]

Julian
Guest
Julian
I see no reason why you can’t just execute it. Below is a test which returns a list for a dropdown selection into a web page retrieved from a MySQL database. And you can still catch you exceptions. It might look strange at the database connection part, but that is just because it connects through a Glassfish web server connection pool and not straight through JDBC. Also it must look like a lot of code, but with the right IDE, such as Netbeans, 90% of it is written for you. ##### JAVA MODEL ##### package com.bitts.model; import java.sql.Date; public class… Read more »
Manikanta
Guest
Manikanta

OracleTypes.CURSOR shows error oracle types cannot be resolved to type please provide solution