create or replace and compile java source named "MyNestedCursorDemo" as

   import java.io.*;
   import java.sql.*;
   import oracle.sql.*;
   import oracle.jdbc.driver.*;
   import oracle.jdbc.OracleTypes;

   public class NestedCursorDemo {

     public static void CursorTest() throws SQLException {

        String query = 
            "begin " + "open ? for " + "'select department_id" + ",department_name" + 
            ",      cursor(select employee_id, last_name, department_id" + 
            "              from   employees e" + 
            "              where  e.department_id = d.department_id) employees " + 
            "from departments d " + "where department_id=60';" + "end;";

        System.out.println(" query = " + query);

        Connection conn = new OracleDriver().defaultConnection();

        Statement trace = conn.createStatement();

        CallableStatement cstmt = conn.prepareCall(query);

        cstmt.registerOutParameter(1, OracleTypes.CURSOR);
        cstmt.execute();

        ResultSet rs = (ResultSet)cstmt.getObject(1);

        for (int i = 0; rs.next(); i++) {
            System.out.println("departmentid   " + rs.getString(1));
            System.out.println("departmentname " + rs.getString(2));
            ResultSet rs2 = (ResultSet)rs.getObject(3);
            for (int j = 0; rs2.next(); j++) {
                System.out.println("  employeeid   " + rs2.getString(1));
                System.out.println("  lastname     " + rs2.getString(2));
                System.out.println("  departmentid " + rs2.getString(3));
                System.out.println("  --------");
            }
            ;
            rs2.close();
        }
        ;
        rs.close();
     }
   }
/
show errors

create or replace
procedure show_nested_refcursors
as language java
name 'NestedCursorDemo.CursorTest()';
/

set serveroutput on
exec show_nested_refcursors

CALL DBMS_JAVA.SET_OUTPUT(1000000);

SQL> exec show_nested_refcursors

query = begin open ? for 'select department_id,department_name,
cursor(select employee_id, last_name, department_id              from
employees e              where  e.department_id = d.department_id) employees
from departments d where department_id=60';end;
departmentid   60
departmentname IT
employeeid   103
lastname     Hunold
departmentid 60
--------
employeeid   104
lastname     Ernst
departmentid 60
--------
employeeid   105
lastname     Austin
departmentid 60
--------
employeeid   106
lastname     Pataballa
departmentid 60
--------
employeeid   107
lastname     Lorentz
departmentid 60
--------
 
PL/SQL procedure successfully completed.
 
SQL>