Oracle Cursor Query

 FROM: http://snipplr.com/view/9047/oracle-cursor-query/

 

 

 

1. import java.sql.*;
2. import java.io.*;
3. import oracle.jdbc.driver.*;
4.  
5.  
6. class curvar
7. {
8.   public static void main (String args [])
9.                      throws SQLException, ClassNotFoundException
10.   {
11.       String driver_class = "oracle.jdbc.driver.OracleDriver";
12.       String connect_string = "jdbc:oracle:thin:@slackdog:1521:oracle8";
13.  
14.       String query = "begin :1 := sp_listEmp; end;";
15.       Connection conn;
16.  
17.       Class.forName(driver_class);
18.       conn = DriverManager.getConnection(connect_string, "scott", "tiger");
19.  
20.       CallableStatement cstmt = conn.prepareCall(query);
21.       cstmt.registerOutParameter(1,OracleTypes.CURSOR);
22.       cstmt.execute();
23.       ResultSet rset = (ResultSet)cstmt.getObject(1);
24.  
25.       while (rset.next ())
26.         System.out.println( rset.getString (1) );
27.       cstmt.close();
28.   }
29. }

-----------------------------------------------------------

import java.sql.*;import java.io.*;import oracle.jdbc.driver.*;class RefCursor{public static void main (String args [])throws SQLException, ClassNotFoundException{ String query = "begin "+ "open ? for "+ "'select dname, CURSOR(select ename "+ "from emp "+ "where emp.deptno=dept.deptno) "+ "from dept'; "+ "end;"; DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Connection conn= DriverManager.getConnection ("jdbc:oracle:thin:@aria-dev:1521:ora817dev", "scott", "tiger"); Statement trace = conn.createStatement(); CallableStatement cstmt = conn.prepareCall(query); cstmt.registerOutParameter(1,OracleTypes.CURSOR); cstmt.execute(); ResultSet rset= (ResultSet)cstmt.getObject(1); for(int i = 0; rset.next(); i++ ) { System.out.println( rset.getString(1) ); ResultSet rset2 = (ResultSet)rset.getObject(2); for( int j = 0; rset2.next(); j++ ) System.out.println( " " + rset2.getString(1) ); rset2.close(); } rset.close(); cstmt.close();}}