JDBC CallableStatement – Stored Procedure OUT parameter example

A JDBC CallableStatement example to call a stored procedure which accepts IN and OUT parameters.

Tested with Java 8 and Oracle database 19c

pom.xml

	<dependency>
		<groupId>com.oracle</groupId>
		<artifactId>ojdbc</artifactId>
		<version>8</version>
		<scope>system</scope>
		<systemPath>path.to/ojdbc8.jar</systemPath>
	</dependency>

1. JDBC CallableStatement

1.1 A PL/SQL stored procedure which accepts IN and OUT parameters.


	CREATE OR REPLACE PROCEDURE get_employee_by_id(
	   p_id IN EMPLOYEE.ID%TYPE,
       o_name OUT EMPLOYEE.NAME%TYPE,
	   o_salary OUT EMPLOYEE.SALARY%TYPE,
	   o_date OUT EMPLOYEE.CREATED_DATE%TYPE)
    AS
    BEGIN

    SELECT NAME , SALARY, CREATED_DATE INTO o_name, o_salary, o_date from EMPLOYEE WHERE ID = p_id;

    END;

1.2 JDBC example to call above stored procedure.

StoreProcedureOutParameter.java

package com.mkyong.jdbc.callablestatement;

import java.math.BigDecimal;
import java.sql.*;

public class StoreProcedureOutParameter {

    public static void main(String[] args) {

        String createSP = "CREATE OR REPLACE PROCEDURE get_employee_by_id( "
                + " p_id IN EMPLOYEE.ID%TYPE, "
                + " o_name OUT EMPLOYEE.NAME%TYPE, "
                + " o_salary OUT EMPLOYEE.SALARY%TYPE, "
                + " o_date OUT EMPLOYEE.CREATED_DATE%TYPE) "
                + " AS "
                + " BEGIN "
                + "     SELECT NAME, SALARY, CREATED_DATE INTO o_name, o_salary, o_date from EMPLOYEE WHERE ID = p_id; "
                + " END;";

        String runSP = "{ call get_employee_by_id(?,?,?,?) }";

        try (Connection conn = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orcl", "system", "Password123");
             Statement statement = conn.createStatement();
             CallableStatement callableStatement = conn.prepareCall(runSP)) {

            // create or replace stored procedure
            statement.execute(createSP);

            callableStatement.setInt(1, 3);

            callableStatement.registerOutParameter(2, java.sql.Types.VARCHAR);
            callableStatement.registerOutParameter(3, Types.DECIMAL);
            callableStatement.registerOutParameter(4, java.sql.Types.DATE);

            // run it
            callableStatement.executeUpdate();

            // java.sql.SQLException: operation not allowed: Ordinal binding and Named binding cannot be combined!
            /*String name = callableStatement.getString("NAME");
            BigDecimal salary = callableStatement.getBigDecimal("SALARY");
            Timestamp createdDate = callableStatement.getTimestamp("CREATED_DATE");*/

            String name = callableStatement.getString(2);
            BigDecimal salary = callableStatement.getBigDecimal(3);
            Timestamp createdDate = callableStatement.getTimestamp(4);

            System.out.println("name: " + name);
            System.out.println("salary: " + salary);
            System.out.println("createdDate: " + createdDate);

        } catch (SQLException e) {
            System.err.format("SQL State: %s\n%s", e.getSQLState(), e.getMessage());
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
}

Download Source Code

References

About the Author

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

Comments

avatar
21 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
18 Comment authors
Sandeep ReddyMr.DanykarthikMathewNarayan Joshi Recent comment authors
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.

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?

Sandeep Reddy
Guest
Sandeep Reddy

Hi,
i have written a stored proc and calling the stored proc from java and by callablestatement.execute()method here the retrieval is success but it is returning the null resultset. when i execute the stored proc in database the execution is fine and retrieval of data is success. can anybody help me with this.
Thanks in advance

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.

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

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. […]

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.