JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in 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 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;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

/** Copyright (c), AnkitMittal JavaMadeSoEasy.com */

public class CallableStatementTestProcedureOUT {

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

*/