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>