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
$ git clone https://github.com/mkyong/java-jdbc.git
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
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
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.
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.
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?
I am getting the error:
java.sql.SQLException: java.lang.ClassCastException: [[B cannot be cast to com.mysql.jdbc.ResultSetRow
Thanks man, Every time I want a quick and direct answer there is not anyplace like your website.
Thanks. one doubt. do we need to connection.commit() after executing an sp.
How to Call Stored Procedure using spring JDBC Template ?????
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.)
Why are you using executeUpdate ?
Shouldn’t you use executeQuery here ?
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/
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
thanks a lot pretty helpful
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 🙂 🙂
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)
helped me
this is good example. saved my time 🙂
what is return type of CallableStatement in JDBC
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.
Thanks. This helped me get my stored proc working.