JDBC CallableStatement – Stored Procedure OUT parameter example

For stored procedure returns OUT parameters, it must be

  1. Registered via JDBC CallableStatement.registerOutParameter(index,sqlType).
  2. Get it back via CallableStatement.getDataType(index).

See code snippets

//getDBUSERByUserId is a stored procedure
String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
callableStatement.setInt(1, 10);
callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
callableStatement.registerOutParameter(4, java.sql.Types.DATE);
 
// execute getDBUSERByUserId store procedure
callableStatement.executeUpdate();
 
String userName = callableStatement.getString(2);
String createdBy = callableStatement.getString(3);
Date createdDate = callableStatement.getDate(4);

JDBC CallableStatement Example

See a full JDBC CallableStatement example for OUT parameter.

1. Stored Procedure

A stored procedure in Oracle database, with IN and OUT parameters. Later, calls it via JDBC.

CREATE OR REPLACE PROCEDURE getDBUSERByUserId(
	   p_userid IN DBUSER.USER_ID%TYPE,
	   o_username OUT DBUSER.USERNAME%TYPE,
	   o_createdby OUT  DBUSER.CREATED_BY%TYPE,
	   o_date OUT DBUSER.CREATED_DATE%TYPE)
IS
BEGIN
 
  SELECT USERNAME , CREATED_BY, CREATED_DATE
  INTO o_username, o_createdby,  o_date 
  FROM  DBUSER WHERE USER_ID = p_userid;
 
END;
/

2. Calls Stored Procedure via CallableStatement

JDBC example to call a stored procedure via CallableStatement.

File : JDBCCallableStatementOUTParameterExample.java

package com.mkyong.jdbc;
 
import java.sql.CallableStatement;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.SQLException;
 
public class JDBCCallableStatementOUTParameterExample {
 
	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 {
 
			callOracleStoredProcOUTParameter();
 
		} catch (SQLException e) {
 
			System.out.println(e.getMessage());
 
		}
 
	}
 
	private static void callOracleStoredProcOUTParameter() throws SQLException {
 
		Connection dbConnection = null;
		CallableStatement callableStatement = null;
 
		String getDBUSERByUserIdSql = "{call getDBUSERByUserId(?,?,?,?)}";
 
		try {
			dbConnection = getDBConnection();
			callableStatement = dbConnection.prepareCall(getDBUSERByUserIdSql);
 
			callableStatement.setInt(1, 10);
			callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
			callableStatement.registerOutParameter(3, java.sql.Types.VARCHAR);
			callableStatement.registerOutParameter(4, java.sql.Types.DATE);
 
			// execute getDBUSERByUserId store procedure
			callableStatement.executeUpdate();
 
			String userName = callableStatement.getString(2);
			String createdBy = callableStatement.getString(3);
			Date createdDate = callableStatement.getDate(4);
 
			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 (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://download.oracle.com/javase/6/docs/api/java/sql/CallableStatement.html
  2. http://docsrv.sco.com/JDK_guide/jdbc/getstart/callablestatement.doc.html
  3. 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: pay per day loans plan()

  • Pingback: Instagram followers kopen()

  • Pingback: water ionizer machine()

  • Pingback: alkaline water benefits()

  • Pingback: bedste laan lige nu()

  • Pingback: stop parking()

  • Pingback: stop parking()

  • Pingback: fue()

  • Pingback: great prices on DIRECTV()

  • Pingback: Click Here()

  • Pingback: Warm ash-blonde teenager working a fuck-stick()

  • Pingback: watch movies online free()

  • Pingback: streaming movies()

  • Pingback: Blue Coaster33()

  • Jon

    Look this framework (https://code.google.com/p/lindbergframework/). Easy handling of stored procedures and functions, including treatment of cursors automatically.

    https://code.google.com/p/lindbergframework/

  • Prashant

    Hi Mk
    I want to call a oracle function from java, that function returns boolean data type.
    but JDBC doesn’t support boolean data type i.e. registerOutParameter from OracleCallableStatement cannot be used with OralceTypes.BOOLEAN
    so can you help?
    Thank in advance

  • jguillen

    thanks a lot pretty helpful

  • http://mkyong.com shivarul

    am sorry guys :) hi guys i tried this code and getting error(Parameter index out of range (1 > number of parameters, which is 0).(db values username,pwd,fname,lname,address,email) can anyone help me for this error :) :)

  • http://mkyong.com shivarul

    hi guys i tired this code and getting error(Parameter index out of range (1 > number of parameters, which is 0).(db values username,pwd,fname,lname,address,email)

  • http://javavm.net dev

    helped me

  • http://javavm.net dev

    this is good example. saved my time :)

  • ravi

    what is return type of CallableStatement in JDBC

  • Akhil

    Hi I love your website..I get all the answers to my queries directly and conveniently…
    Just have one query..regarding the above post..Hope you can help me..

    I used the same code for calling an SP with IN and OUT parameters, but I get the below Exception:

    Exception occured: com.jnetdirect.jsql.x: Statement.executeUpdate result set generated

    can you help…

    regards,
    Akhil John.

  • Carlos

    Thanks. This helped me get my stored proc working.

  • Pingback: JDBC Tutorials()