create or replace and resolve java source named refcur as
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;

/*
 *  Mapping REF CURSOR to java.sql.Resultset
 */

public class refcur
{
  /*
   * Procedure returning a REF CURSOR via OUT parameter
   */
  public static void refcurproc (ResultSet rs[])
    throws SQLException
  {
    Connection conn = null;  
    conn = DriverManager.getConnection("jdbc:oracle:kprb:");
    ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
    Statement stmt = conn.createStatement();
    ((OracleStatement)stmt).setRowPrefetch(1);
    ResultSet rset = stmt.executeQuery("select * from EMP order by empno");
    rs[0] = rset;
    // fetch one row
    if (rset.next())
    {
      System.out.println("Ename = " + rset.getString(2));
    }
   }

  /*
   * Function returning a REF CURSOR 
   */
  public static ResultSet refcurfunc ()
    throws SQLException
  {
    Connection conn = null;  
    conn = DriverManager.getConnection("jdbc:oracle:kprb:");
    ((OracleConnection)conn).setCreateStatementAsRefCursor(true);
    Statement stmt = conn.createStatement();
    ((OracleStatement)stmt).setRowPrefetch(1);
    ResultSet rset = stmt.executeQuery("select * from EMP order by empno");
    // fetch one row
    if (rset.next())
    {
      System.out.println("Ename = " + rset.getString(2));
    }
    return rset;
   }
}
/
show errors;


create or replace package refcur_pkg as
type EmpCurTyp IS REF CURSOR;
  function rcfunc return sys_refcursor;
  procedure rcproc (rc OUT sys_refcursor);
end refcur_pkg;
/
show errors;

create or replace package body refcur_pkg as 
 
procedure rcproc(rc OUT sys_refcursor)
 as language java 
 name 'refcur.refcurproc(java.sql.ResultSet[])';
 
function rcfunc return sys_refcursor
 as language java 
 name 'refcur.refcurfunc() returns java.sql.ResultSet';

end refcur_pkg;
/
show errors;

---
--- A demo PL/SQL for calling the Java procedure/function
--- then processing the refcursor. 
--- A Java app would retrieve the result set from the Ref Cursor
---

set serveroutput on
call dbms_java.set_output(50000);

declare
  employee emp%ROWTYPE;
  rc sys_refcursor;
begin
  dbms_output.put_line(' ** Calling REF CURSOR PROCEDURE' );
  refcur_pkg.rcproc(rc);
---
--- Alternatively the refcurfunc could be called as follows 
--- rc = refcur_pkg.rcfunc();
---
  LOOP
   fetch rc into employee;
   exit when rc%notfound;
   dbms_output.put_line(' Name = ' || employee.ENAME ||
            ' Department = ' || employee.DEPTNO);
  end loop;
close rc;
end;
/
show errors;

** Calling REF CURSOR PROCEDURE
Ename = SMITH
 Name = ALLEN Department = 30
 Name = WARD Department = 30
 Name = JONES Department = 20
 Name = MARTIN Department = 30
 Name = BLAKE Department = 30
 Name = CLARK Department = 10
 Name = SCOTT Department = 20
 Name = KING Department = 10
 Name = TURNER Department = 30
 Name = ADAMS Department = 20
 Name = JAMES Department = 30
 Name = FORD Department = 20
 Name = MILLER Department = 10