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



In this tutorial we will learn how to call Oracle database STORED PROCEDURE and passing OUT parameter - CallableStatement program/example in java JDBC.


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 OUT parameter in java-
1) OUT parameters must be registered in java before executing
the stored procedure,
2) Execute database stored procedure,
3) Then retrieve values of OUT parameters using using get methods.


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22), SALARY number(8), CREATION_DATE date);

insert into EMPLOYEE values(11, 'ankit', 12000, sysdate);

commit;


--Procedure accepts id as IN parameter, selects data from EMPLOYEE table on basis of id and
--populate following OUT parameters > name, salary and creation_date
create or replace PROCEDURE MYPROC_EMPLOYEE_SELECT_OUT(
  p_id IN EMPLOYEE.ID%TYPE,
  p_name OUT EMPLOYEE.NAME%TYPE,
  p_salary OUT EMPLOYEE.SALARY%TYPE,
  p_creation_date OUT EMPLOYEE.CREATION_DATE%TYPE)
IS
BEGIN
 SELECT NAME, SALARY, CREATION_DATE
 INTO p_name, p_salary, p_creation_date
 FROM EMPLOYEE where ID = p_id;
END;


--If table already exists then execute the DROP command >
drop table EMPLOYEE;


Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE- 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 CallableStatementExampleProcedureOUT {
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!");
         
          callableStmt = con.prepareCall("{call MYPROC_EMPLOYEE_SELECT_OUT(?,?,?,?)}");
          //IN parameter -
          //   1) set methods are used for setting IN parameter values of Stored procedure
          callableStmt.setInt(1, 11);
         
          //OUT parameter -
          // 1) OUT parameters must be registered in java before executing the stored procedure,
          callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
          callableStmt.registerOutParameter(3, java.sql.Types.NUMERIC);
          callableStmt.registerOutParameter(4, java.sql.Types.DATE);
         
          //OUT parameter -
          //   2) Execute database stored procedure,
          callableStmt.executeUpdate();
          //OUT parameter -
          //   3) Then retrieve values of OUT parameters using using get methods.
          System.out.println("name = " + callableStmt.getString(2));
          System.out.println("salary = " + callableStmt.getInt(3));
          System.out.println("creationDate = " +callableStmt.getDate(4));//returns java.Sql.Date
         
          System.out.println("Stored procedure executed successfully, "
              + "data has been fetched from Employee table");
     } 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!
name = ankit
salary = 12000
creationDate = 2015-07-27
Stored procedure executed successfully, data has been fetched from Employee table
*/



Must read : Execute database STORED PROCEDURE - IN parameter and IN OUT parameter || call FUNCTION from java JDBC.



Additionally, you may use below script for executing above Stored Procedure as a standalone procedure from oracle SQL developer.
--call OUT PROCEDURE
DECLARE
     p_name EMPLOYEE.NAME%TYPE;
     p_salary EMPLOYEE.SALARY%TYPE;
     p_creation_date EMPLOYEE.CREATION_DATE%TYPE;
BEGIN
 MYPROC_EMPLOYEE_SELECT_OUT(11, p_name, p_salary, p_creation_date);
 DBMS_OUTPUT.PUT_LINE('p_name = '  || p_name);
 DBMS_OUTPUT.PUT_LINE('p_salary = '  || p_salary);
 DBMS_OUTPUT.PUT_LINE('p_creation_date = '  || p_creation_date);
END;



So in this tutorial we learned how to call Oracle database STORED PROCEDURE and passing OUT parameter - CallableStatement program/example in java JDBC.


RELATED LINKS>

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


12 Differences between Statement, PreparedStatement and CallableStatement in java


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- IN OUT parameter - CallableStatement example in java


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


No comments:

Post a Comment