Using Ref Cursors To Return Recordsets

Since Oracle 7.3 REF CURSORS have been available which allow recordsets to be returned from stored procedures, functions and packages. The example below uses a ref cursor to return a subset of the records in the EMP table.

First, a package definition is needed to hold the ref cursor type.

CREATE OR REPLACE PACKAGE Types AS

  TYPE cursor_type IS REF CURSOR;

END Types;

/

Note. In Oracle9i the SYS_REFCURSOR type has been added making this first step unnecessary. If you are using Oracle9i or later simply ignore this first package and replace any references to Types.cursor_type with SYS_REFCURSOR.

Next a procedure is defined to use the ref cursor.

CREATE OR REPLACE

PROCEDURE GetEmpRS (p_deptno    IN  emp.deptno%TYPE,

                    p_recordset OUT Types.cursor_type) AS

BEGIN

  OPEN p_recordset FOR

    SELECT ename,

           empno,

           deptno

    FROM   emp

    WHERE  deptno = p_deptno

    ORDER BY ename;

END GetEmpRS;

/

The resulting cursor can be referenced from PL/SQL as follows.

SET SERVEROUTPUT ON SIZE 1000000

DECLARE

  v_cursor  Types.cursor_type;

  v_ename   emp.ename%TYPE;

  v_empno   emp.empno%TYPE;

  v_deptno  emp.deptno%TYPE;

BEGIN

  GetEmpRS (p_deptno    => 30,

            p_recordset => v_cursor);

  LOOP

    FETCH v_cursor

    INTO  v_ename, v_empno, v_deptno;

    EXIT WHEN v_cursor%NOTFOUND;

    DBMS_OUTPUT.PUT_LINE(v_ename || ' | ' || v_empno || ' | ' || v_deptno);

  END LOOP;

  CLOSE v_cursor;

END;

/

In addition the cursor can be used as an ADO Recordset.

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")

conn.Open "DSN=TSH1;UID=scott;PWD=tiger"

Set cmd = Server.CreateObject ("ADODB.Command")

Set cmd.ActiveConnection = conn

cmd.CommandText = "GetEmpRS"

cmd.CommandType = 4 'adCmdStoredProc

Dim param1

Set param1 = cmd.CreateParameter ("deptno", adInteger, adParamInput)

cmd.Parameters.Append param1

param1.Value = 30

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF

  -- Do something

  rs.MoveNext

Loop

rs.Close

conn.Close

Set rs     = nothing

Set param1 = nothing

Set cmd    = nothing

Set conn   = nothing

The cursor can also be referenced as a Java ResultSet.

import java.sql.*;

import oracle.jdbc.*;

public class TestResultSet  {

  public TestResultSet() {

    try {

      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());

      Connection conn = DriverManager.getConnection("jdbc:oracle:oci:@w2k1", "scott", "tiger");

      CallableStatement stmt = conn.prepareCall("BEGIN GetEmpRS(?, ?); END;");

      stmt.setInt(1, 30); // DEPTNO

      stmt.registerOutParameter(2, OracleTypes.CURSOR); //REF CURSOR

      stmt.execute();

      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);

      while (rs.next()) {

        System.out.println(rs.getString("ename") + ":" + rs.getString("empno") + ":" + rs.getString("deptno"));

      }

      rs.close();

      rs = null;

      stmt.close();

      stmt = null;

      conn.close();

      conn = null;

    }

    catch (SQLException e) {

      System.out.println(e.getLocalizedMessage());

    }

  }

  public static void main (String[] args) {

    new TestResultSet();

  }

}