JDBC CallableStatement Stored Procedure IN and OUT Parameter Example

 
In this tutorial we will learn how to call Oracle stored procedure via JDBC CallableStatement, and how to get the returned OUT parameters from stored procedure.
 
** UPDATE: Complete JDBC tutorial now available here.
 
In order to retrieve the stored procedure returned output (via OUT parameters), we must use the below functions
 

CallableStatement.registerOutParameter(index,sqlType);
CallableStatement.getDataType(index);

 
Where the method registerOutParameter is used for registering the OUT parameter value and the method getDataType is used for retrieving the output from stored procedure.
 
Stored Procedure
 
Lets say we have a stored procedure getEMPLOYEEByUserId in Oracle database with IN and OUT parameters as shown below.
 

CREATE OR REPLACE PROCEDURE getEMPLOYEEByUserId(
	   userid IN EMPLOYEE.USER_ID%TYPE,
	   username OUT EMPLOYEE.USERNAME%TYPE,
	   createdby OUT  EMPLOYEE.CREATED_BY%TYPE) IS
BEGIN
 
  SELECT USERNAME, CREATED_BY
  INTO username, createdby
  FROM  EMPLOYEE WHERE USER_ID = userid;
 
END;
/

 
Now we shall call this stored procedure from our java program to select a record.

 
JDBC CallableStatement example for IN and OUT parameter.
 
File: JDBCCallableStatementOUTParameter.java
 

package com.simplecode.jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class JDBCCallableStatementOUTParameter
{
private static final String dbDriver = "oracle.jdbc.driver.OracleDriver";
private static String serverName = "127.0.0.1";
private static String portNumber = "1521";
private static String sid = "XE";
private static final String dbUrl = "jdbc:oracle:thin:@" + serverName + ":"
			+ portNumber + ":" + sid;
private static final String dbUser = "system";
private static final String dbPassword = "admin";
 
public static void main(String[] argc)
{
	try
	{
		callStoredProcOUTParameter();
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
}

private static void callStoredProcOUTParameter() throws SQLException
{
	Connection dbConnection = null;
	CallableStatement callableStmt = null;

	String getByUserIdStroredProc = "{call getEMPLOYEEByUserId(?,?,?)}";

	try
	{
		dbConnection = getDBConnection();
		callableStmt = dbConnection.prepareCall(getByUserIdStroredProc);

		callableStmt.setInt(1, 1000);
		callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
		callableStmt.registerOutParameter(3, java.sql.Types.VARCHAR);

		// execute getEMPLOYEEByUserId store procedure
		callableStmt.execute();
		
		String userName = callableStmt.getString(2);
		String createdBy = callableStmt.getString(3);

		System.out.println("UserName : " + userName);
		System.out.println("CreatedBy : " + createdBy);
	}
	
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
	
	finally
	{

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

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

private static Connection getDBConnection()
{

	Connection dbConnection = null;
	try
	{
		Class.forName(dbDriver);
	}
	catch (ClassNotFoundException e)
	{
		System.err.println(e.getMessage());
	}

	try
	{
	dbConnection = DriverManager.getConnection(dbUrl, dbUser, dbPassword);
	return dbConnection;
	}
	catch (SQLException e)
	{
		System.err.println(e.getMessage());
	}
	return dbConnection;
}
}

Download It – JDBCCallableStatementOUTParameter

About Mohaideen Jamil