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 in java.



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



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



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


RELATED LINKS>

JDBC- Batch PreparedStatement example- Execute INSERT query(DML command) using PreparedStatement's addBatch() and executeBatch() methods in java


12 Differences between Statement, PreparedStatement and CallableStatement in java


JDBC- Calling Oracle database STORED PROCEDURE and pass its IN parameter from java - 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 for you :
Labels:

No comments:

Post a Comment