Oracle REF CURSOR

With the REF_CURSOR you can return a recordset/cursor from a stored procedure.

There are 2 basic types: Strong ref cursor and weak ref cursor
For the strong ref cursor the returning columns with datatype and length need to be known at compile time.
For the weak ref cursor the structure does not need to be known at compile time.

The STRONG_REF_CURSOR and until Oracle 9i also the weak-type need to be declared in a package structure lik this:

create or replace package REFCURSOR_PKG as
  TYPE WEAK8i_REF_CURSOR IS REF CURSOR;
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;
end REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:

/** until Oracle 9 */
create or replace procedure test( p_deptno IN number
                                , p_cursor OUT 
REFCURSOR_PKG.WEAK8i_REF_CURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;


Since Oracle 9i you can use SYS_REFCURSOR as the type for the returning 
REF_CURSOR.

/** From Oracle 9 */
create or replace procedure test( p_deptno IN number
                                , p_cursor OUT SYS_REFCURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;


/* Strong type */

create or replace procedure test( p_deptno IN number
                                , p_cursor OUT REFCURSOR_PKG.STRONG 
REF_CURSOR)
is
begin
  open p_cursor FOR 
  select *
  from   emp
  where  deptno = p_deptno;
end test;

Selecting the ref_cursor from JDBC

To get the cursor from Java you can use the following JDBC-code:

public void method() throws SQLException{
  Connection conn = getConnection();
  CallableStatement cstmt = null;
  ResultSet rs = null;
  int deptno = 10;
  Object temp;
  try{
      cstmt = conn.prepareCall("begin  test(?,?); end;");
      cstmt.setInt(1, deptno);
      cstmt.registerOutParameter(2, OracleTypes.CURSOR); 
      cstmt.execute();
      rs = (ResultSet) cstmt.getObject(2);
      ResultSetMetaData rsm = rs.getMetaData();
      int columnCount = rsm.getColumnCount();
      while (rs.next()){
         for (int j=0;j< columnCount;j++){
            temp = rs.getObject(j+1);
         }
      }
  } finally {
      if (!rs==null){
        rs.close();
      }
      if (!stmt==null){
        stmt.close();
      }
      if (!conn==null){
        conn.close();
      }  
  }
}

Calling ref-cursor from pl/sql

create or replace procedure test_call is
  c_cursor REFCURSOR_PKG.STRONG_REF_CURSOR;
  r_emp    c_emp%rowtype;
begin
  test(10,c_cursor);
  loop
    fetch c_cursor into r_emp;
    exit when c_cursor%notfound;
    dbms_output.put_line(r_emp.name);
  end loop;
  close c_cursor;
end test_call;