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

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
ali amiri
Guest
ali amiri

Thanks man, Every time I want a quick and direct answer there is not anyplace like your website.

Mr.Dany
Guest
Mr.Dany

Very thanks. it’s very useful. i’m using ms-sql and java and i’m confused in getting the result, but rather your article helped me.

karthik
Guest
karthik

Hi I want to call a stored procedure with “OUT” parameters in a loop(Batch Updating). Could you plz suggest as addBatch() is throwing an error.

Mathew
Guest
Mathew

How do you handle an Output parameter that is of Oracle type sys_refcursor? – callableStatement.registerOutParameter(5, java.sql.Types.?); – What would be the Java.sql.Type for a sys_refcursor?

Narayan Joshi
Guest
Narayan Joshi

I am getting the error:

java.sql.SQLException: java.lang.ClassCastException: [[B cannot be cast to com.mysql.jdbc.ResultSetRow

naveen
Guest
naveen

Thanks. one doubt. do we need to connection.commit() after executing an sp.

Vashu
Guest
Vashu

How to Call Stored Procedure using spring JDBC Template ?????

Romano
Guest
Romano

Thanks for your code Mk, it really helps.
(If I may, you can remove the first return dbConnection from the getDbConnection() second’s try/catch block.)

Chirag
Guest
Chirag

Why are you using executeUpdate ?

Shouldn’t you use executeQuery here ?

Jon
Guest
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
Guest
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
Guest
jguillen

thanks a lot pretty helpful

shivarul
Guest
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 :) :)

shivarul
Guest
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)

dev
Guest
dev

helped me

dev
Guest
dev

this is good example. saved my time :)

ravi
Guest
ravi

what is return type of CallableStatement in JDBC

Akhil
Guest
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
Guest
Carlos

Thanks. This helped me get my stored proc working.

trackback
JDBC Tutorials

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