In this tutorial we will learn how to call Oracle database STORED PROCEDURE and passing IN OUT parameter - CallableStatement program/example in java JDBC.
Must read : Execute database STORED PROCEDURE - IN parameter and OUT parameter || call FUNCTION from java
The java.sql.CallableStatement is an interface which can be used to execute SQL stored procedures in java.
How to deal with SQL stored procedures IN OUT parameter in java-
- Like IN parameters, set methods are used for setting IN OUT parameter values.
- Like OUT parameters, IN OUT parameters must be registered in java before executing the stored procedure,
- Execute database stored procedure,
- Then like OUT parameters, retrieve values of IN OUT parameters using using get methods.
--Before executing java program execute these database scripts >
None (database table has not been used in below program), only execute below Stored Procedure.
--Procedure accepts p_num and adds 1000 to it (p_num is IN OUT parameter)
create or replace
PROCEDURE MYPROC_IN_OUT(
p_num IN OUT NUMBER)
IS
BEGIN
p_num := p_num + 1000;
END;
|
--If table already exists then execute the DROP command >
drop table EMPLOYEE;
Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */
public class CallableStatementExampleProcedureINOUT {
public static void main(String... arg) {
Connection con = null;
CallableStatement callableStmt = null;
try {
// registering Oracle driver class
Class.forName("oracle.jdbc.driver.OracleDriver");
// getting connection
con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl",
"ankit", "Oracle123");
System.out.println("Connection established successfully!");
int passValueAsInPara = 4000;
callableStmt = con.prepareCall("{call MYPROC_IN_OUT(?)}");
System.out.println("value of IN OUT before calling stored procedure "
+ "MYPROC_IN_OUT = " + passValueAsInPara );
//1) set method is used for setting IN OUT parameter value
callableStmt.setInt(1, passValueAsInPara);
//2) IN OUT parameters must be registered in java
// before executing the stored procedure
callableStmt.registerOutParameter(1, java.sql.Types.NUMERIC);
//3) Execute database stored procedure (procedure will add 1000 to passed value),
callableStmt.executeUpdate();
System.out.println("Stored procedure executed successfully, "
+ "IN OUT parameter was passed and retrieved");
//4) Then retrieve values of IN OUT parameters using using get method.
System.out.println("value of IN OUT after calling stored procedure "
+ "from MYPROC_IN_OUT = " + callableStmt.getInt(1));
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
finally{
try {
if(callableStmt!=null) callableStmt.close(); //close CallableStatement
if(con!=null) con.close(); // close connection
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
/*OUTPUT
Connection established successfully!
value of IN OUT before calling stored procedure MYPROC_IN_OUT = 4000
Stored procedure executed successfully, IN OUT parameter was passed and retrieved
value of IN OUT after calling stored procedure from MYPROC_IN_OUT = 5000
*/
|
Additionally, you may use below script for executing above Stored Procedure as a standalone procedure from oracle SQL developer.
--call IN OUT PROCEDURE
DECLARE
p_num NUMBER(5) := 4000;
BEGIN
MYPROC_IN_OUT(p_num);
DBMS_OUTPUT.PUT_LINE('p_num = ' || p_num);
END;
|
In this tutorial we learned how to call Oracle database STORED PROCEDURE and passing IN OUT parameter - CallableStatement program/example in java JDBC.
RELATED LINKS>
java.sql.PreparedStatement - using executeUpdate and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE