Contents of this page >>
- JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - CallableStatement example in java JDBC
- JDBC- Calling Oracle database STORED PROCEDURE- OUT parameter - CallableStatement example in java JDBC
- JDBC- Calling Oracle database STORED PROCEDURE- IN OUT parameter - CallableStatement example in java JDBC
- JDBC- Calling Oracle database FUNCTION - CallableStatement example in java
- 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-
- Like IN parameters, set methods are used for setting IN OUT parameter values.
- Like OUT parameters, IN OUT parameters must be registered in java before executing the stored procedure,
- Execute database stored procedure,
- 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
--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: