We can open the cursor and execute the queries in that environment and read the output of the query from the cursor. The syntax is use to create a cursor is as follows:

create or replace package package_name as
TYPE type_name IS REF CURSOR END;
The cursor is used to retrieve the ResultSet object from a database through CallableStatement.

For Example:

We have created cursor and function in database to access the ResultSet object representing all the accounts of the given account_type. (Which is related to my previous article ‘Callable Statement in Java’ ).



Now, we have used this function in the java program  are as follow

import java.sql.*;
import java.util.*;
public class CallableStmtWithCursor {
public static void main(String [] args) throws Exception{
 Properties p = new Properties();
 p.put("user","system");
 p.put("password","oracle");
 oracle.jdbc.driver.OracleDriver 
 od= new oracle.jdbc.driver.OracleDriver();
 Connection con = od.connect("jdbc:oracle:thin:@localhost:1521:xe",p);
 System.out.println(" Connect ");
 CallableStatement cs= 
 con.prepareCall("{call ?:=getAcountDetails(?)}");
 cs.registerOutParameter(1,oracle.jdbc.driver.OracleTypes.CURSOR);
 cs.setInt(1,Integer.parseInt(args[0]));
 cs.execute();
 ResultSet rs= (ResultSet) cs.getObject(1);
 while(rs.next()){
  System.out.print(rs.getInt(1)+"\t");
  System.out.print(rs.getString(2)+"\t");
      System.out.print(rs.getDouble(3));
 }
 con.close();
}
}


  Modified On Dec-14-2017 01:22:49 AM

Leave Comment