JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java



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-

  1. Like IN parameters, set methods are used for setting IN OUT parameter values.

  1. Like OUT parameters, IN OUT parameters must be registered in java before executing the stored procedure,

  1. Execute database stored procedure,

  1. 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

JDBC tutorial- Insert/Store/save FILE in database

JDBC- Batch PreparedStatement program/example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


Difference between CLOB and CLOB data type in Oracle


JDBC tutorial- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java

JDBC tutorial- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java

JDBC tutorial program- Calling Oracle database FUNCTION - CallableStatement example in java


No comments:

Post a Comment