Using Cursor in CallableStatement



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 Nov-22-2017 02:09:28 PM

Leave Comment