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


In this tutorial we will learn how to call Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement 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 IN parameter in java jdbc-
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;



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

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


So, in this tutorial we learned how to call Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example 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


No comments:

Post a Comment