JDBC- Calling Oracle database STORED PROCEDURE, FUNCTION and CURSOR example - pass IN OUT parameter using CallableStatement in java
Contents of this page >>
  1. JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java JDBC



  1. JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java JDBC
  1. JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java JDBC
  1. JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
  1. JDBC- Call/Execute Oracle CURSOR in java



1. JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - 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 IN parameter in java-
1) set methods are used for setting IN parameter values.
(Must know : set methods are inherited from java.sql.PreparedStatement)


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22), SALARY number(8), CREATION_DATE date);
--Procedure accepts id, name, salary and creation_date as IN parameter inserts them in EMPLOYEE table
create or replace PROCEDURE MYPROC_EMPLOYEE_INSERT_IN(
  p_id IN EMPLOYEE.ID%TYPE,
  p_name IN EMPLOYEE.NAME%TYPE,
  p_salary IN EMPLOYEE.SALARY%TYPE,
  p_creation_date IN EMPLOYEE.CREATION_DATE%TYPE)
IS
BEGIN
 INSERT INTO EMPLOYEE (ID, NAME, SALARY, CREATION_DATE)
 VALUES (p_id, p_name, p_salary, p_creation_date);
 COMMIT;
END;


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



Example/ Full Programs JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - 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 CallableStatementTestProcedureIN {
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_INSERT_IN(?,?,?,?)}");
         
          //IN parameter -
          //   1) set methods are used for setting IN parameter values of Stored procedure
          callableStmt.setInt(1, 11);
          callableStmt.setString(2, "ankit");
          callableStmt.setString(3, "12000");
          callableStmt.setDate(4, new java.sql.Date( new java.util.Date().getTime() ));
          // execute database Stored procedure
          callableStmt.executeUpdate();
         
          System.out.println("Stored procedure executed successfully, "
                   + "data has been inserted in 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!
Stored procedure executed successfully, data has been inserted in Employee table
*/


Additionally, you may use below script for executing above Stored Procedure as a standalone procedure from oracle SQL developer.
--call IN PROCEDURE
BEGIN
 MYPROC_EMPLOYEE_INSERT_IN(11, 'ankit', 12000, sysdate);
END;




2. 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;


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 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
*/




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;




3. JDBC- Calling Oracle database STORED PROCEDURE- IN 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 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;



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 CallableStatementTestProcedureINOUT {
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;



4. JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
The java.sql.CallableStatement is an interface which can be used to execute SQL FUNCTIONS in java.


--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;
--Function accepts id as IN parameter and returns salary
create or replace
FUNCTION MYFUNCTION(
     p_id IN EMPLOYEE.ID%TYPE)
RETURN NUMBER
IS
 p_salary EMPLOYEE.SALARY%TYPE := 0;
BEGIN
 SELECT SALARY
 INTO p_salary
 FROM EMPLOYEE where ID = p_id;
 RETURN p_salary;
END;



Example/ Full Programs JDBC- Calling Oracle database FUNCTION - 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 CallableStatementTestFunction {
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 MYFUNCTION(?)}");
          //Database function will return value as OUT parameter
          callableStmt.registerOutParameter(1, java.sql.Types.NUMERIC);
         
          //IN parameter -
          //set methods are used for setting IN parameter values of Stored procedure
          callableStmt.setInt(2, 11);
         
          //Execute database Function,
          callableStmt.executeUpdate();
          // Then retrieve values returned by method using using get methods.
          System.out.println("salary = " + callableStmt.getInt(1));
         
          System.out.println("Function executed successfully, "
                   + "salary 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!
salary = 12000
Function executed successfully, salary has been fetched from Employee table
*/


Additionally, you may use below script for executing above Function as a standalone function from oracle SQL developer.
--call function
DECLARE
     p_salary EMPLOYEE.SALARY%TYPE;
BEGIN
 p_salary  := MYFUNC_EMPLOYEE_FETCH_IN(11);
 DBMS_OUTPUT.PUT_LINE('p_salary = '  || p_salary);
END;



5. JDBC- Call/Execute Oracle CURSOR in java
The java.sql.CallableStatement is an interface which can be used to execute SQL stored procedures in java.

There is no direct way we could execute CURSOR in java jdbc, So, we will execute SQL stored procedures which will execute CURSOR.


--Before executing java program execute these database scripts  >
create table EMPLOYEE(ID number(4), NAME varchar2(22));
insert into EMPLOYEE values(7, 'ankit');
insert into EMPLOYEE values(8, 'rohit');
commit;


Procedure accepts id as IN parameter, selects data from EMPLOYEE table using CURSOR and populate OUT parameters name
create or replace PROCEDURE MYPROC_EMPLOYEE_CURSOR(
  p_id IN EMPLOYEE.ID%TYPE,
  p_name OUT EMPLOYEE.NAME%TYPE)
IS
 CURSOR cur is
 select ID, NAME from EMPLOYEE where ID =  p_id;
BEGIN
 for cur1 in cur loop
p_name := cur1.name;
DBMS_OUTPUT.PUT_LINE('p_name = '  || p_name);
 end loop;
END;



Example/ Full Programs JDBC- Call/Execute Oracle CURSOR 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 CursorCallableStatementExample {
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_CURSOR(?,?)}");
          //IN parameter -
          //   1) set methods are used for setting IN parameter values of Stored procedure
          callableStmt.setInt(1, 7);
         
          //OUT parameter -
          //   1) OUT parameters must be registered in java before executing the stored procedure,
          callableStmt.registerOutParameter(2, java.sql.Types.VARCHAR);
         
          //OUT parameter -
          //   2) Execute database stored procedure, it will execute CURSOR
          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("Stored procedure and CURSOR 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
Stored procedure and CURSOR executed successfully, data has been fetched from Employee table
*/



So, in this post we learned Calling Oracle database STORED PROCEDURE and pass its IN parameter from java, Calling Oracle database STORED PROCEDURE- OUT parameter, Calling Oracle database STORED PROCEDURE- IN OUT parameter, Calling Oracle database FUNCTION and Call/Execute Oracle CURSOR in java JDBC.



RELATED LINKS>

Oracle 11g and SQL Developer installation and setup on 32/64bit windows - explained step by step with screenshots
java.sql.PreparedStatement - using executeUpdate  and executeQuery methods - CREATE, SELECT, INSERT, UPDATE and DELETE

JDBC- Insert/Store/save FILE in database

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

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

JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
Must read : Execute database STORED PROCEDURE -  OUT parameter and IN OUT parameter || call FUNCTION from java

Labels:
eEdit
Must read for you :