Main Tutorials

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 Author

author image
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

Subscribe
Notify of
21 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
Mr.Dany
7 years ago

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
9 years ago

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?

ali amiri
9 years ago

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

Quinn
4 years ago

Hello when I try to return a string from my stored procedure call it keeps returning None. Could you help me figure out what I am doing wrong. I have posted the question in stackoverflow at
https://stackoverflow.com/questions/58866448/output-value-from-a-stored-procedure-returning-none-when-called-from-automation

Sandeep Reddy
5 years ago

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
7 years ago

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
9 years ago

I am getting the error:

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

naveen
9 years ago

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

Vashu
9 years ago

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

Romano
9 years ago

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
10 years ago

Why are you using executeUpdate ?

Shouldn’t you use executeQuery here ?

Jon
10 years ago

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
10 years ago

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
10 years ago

thanks a lot pretty helpful

shivarul
11 years ago

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
11 years ago

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
11 years ago

helped me

dev
11 years ago

this is good example. saved my time 🙂

ravi
11 years ago

what is return type of CallableStatement in JDBC

Carlos
11 years ago

Thanks. This helped me get my stored proc working.

Akhil
11 years ago

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.