8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Using Ref Cursors To Return Recordsets
Since Oracle 7.3 the REF CURSOR
type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR
type, meaning we no longer have to define our own REF CURSOR
types.
Related articles.
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Complex Recordsets
- Dynamic SQL Enhancements in Oracle 11g
- Implicit Statement Results in Oracle Database 12c Release 1 (12.1)
Using Ref Cursors
The example below uses a ref cursor to return a subset of the records in the EMP
table.
The following procedure opens a query using a SYS_REFCURSOR
output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.
CREATE OR REPLACE PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE, p_recordset OUT SYS_REFCURSOR) 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 l_cursor SYS_REFCURSOR; l_ename emp.ename%TYPE; l_empno emp.empno%TYPE; l_deptno emp.deptno%TYPE; BEGIN get_emp_rs (p_deptno => 30, p_recordset => l_cursor); LOOP FETCH l_cursor INTO l_ename, l_empno, l_deptno; EXIT WHEN l_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno); END LOOP; CLOSE l_cursor; END; /
The examples below are very old. The ASP example is pre .NET. I have no idea if they are still valid syntax.
The cursor can be used as an ADO Recordset in ASP.
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 = "get_emp_rs" 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 get_emp_rs(?, ?); 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(); } }
If you are using a version of Oracle before 9i, then create the following package and replace any references to SYS_REFCURSOR
with TYPES.cursor_type
.
CREATE OR REPLACE PACKAGE types AS TYPE cursor_type IS REF CURSOR; END Types; /
11g Updates
Oracle 11g allows the two-way conversion between ref cursors to DBMS_SQL
cursors, as described here.
12c Updates
Oracle 12c allows implicit statements results, similar to that seen in Transact-SQL, as described here.
For more information see:
- Returning REF CURSORs from PL/SQL : Functions, Procedures and Implicit Statement Results
- Complex Recordsets
- Dynamic SQL Enhancements in Oracle 11g
- Implicit Statement Results in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...