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
Tags :

About the Author

mkyong
Founder of Mkyong.com and HostingCompass.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

  • Pingback: Blue Coaster33()

  • 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

    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
    # The crash happened outside the Java Virtual Machine in native code.
    # See problematic frame for where to report the bug.
    #

    Need Help !

  • 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

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

  • 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

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

  • Pingback: JDBC Tutorials()