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