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