articles

Using Cursor in CallableStatement

Anupam Mishra4905 07-Nov-2015

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’ ).


Using Cursor in CallableStatement


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();
}
}



Leave Comment

Comments

Liked By