Home > DeveloperSection > Beginner > Using Cursor in CallableStatement

Using Cursor in CallableStatement


Java Java  J2EE  Database Design  Database Schema 
Ratings:
0 Comment(s)
 847  View(s)
Rate this:


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



Don't want to miss updates? Please click the below button!

Follow MindStick