Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Cursor Variables as Parameters

Oracle Tips by Burleson Consulting


The following Tip is from the outstanding book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006:

Cursor variables can be passed as formal parameters in database procedures and functions.  This feature is commonly exploited allowing stored procedures to return resultsets to client side programs.  The get_resultset.sql script creates a stored procedure that returns a weakly typed cursor variable as an out parameter.


-- Create a stored procedure to return a cursor variable.
CREATE OR REPLACE PROCEDURE get_resultset (p_max_id     IN   NUMBER,
                                           p_resultset  OUT  SYS_REFCURSOR) AS
  OPEN p_resultset FOR
    SELECT *
    FROM   cursor_variable_test
    WHERE  id <= p_max_id
    ORDER BY id;
END get_resultset;

The procedure can be tested from PL/SQL using the resultset_plsql_test.sql script, which loops through the resulting cursor displaying the values of each row.


  l_cursor  SYS_REFCURSOR;
  l_row     cursor_variable_test%ROWTYPE;
  get_resultset (p_max_id    => 2,
                 p_resultset => l_cursor);      

    FETCH l_cursor
    INTO  l_row;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE( || ' : ' || l_row.description);
  CLOSE l_cursor;

The results from this script are displayed below.

SQL> @resultset_plsql_test.sql
1 : One
2 : Two

PL/SQL procedure successfully completed.

The following examples show how the cursor variable can be returned as an "ADO Recordset" and a "Java ResultSet" using VBScript and Java respectively. 

First the resultset_ado_test.asp script uses Active Server Pages (ASP) to provide an equivalent to the resultset_plsql_test.sql script.

* resultset_ado_test.asp

Dim conn, cmd, rs

Set conn = Server.CreateObject("adodb.connection")
conn.Open "DSN=DB10G;UID=myuser;PWD=mypassword"

Set cmd = Server.CreateObject ("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "get_resultset"
cmd.CommandType = 4 'adCmdStoredProc

Dim param1
Set param1 = cmd.CreateParameter ("p_max_id", adInteger, adParamInput)
cmd.Parameters.Append param1
param1.Value = 2

Set rs = cmd.Execute

Do Until rs.BOF Or rs.EOF
  Response.Write(rs("id") & " : " & rs("description"))

Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing

Next the script uses Java to provide an equivalent to the resultset_plsql_test.sql script.

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:@DB10G", "myuser",
      CallableStatement stmt = conn.prepareCall("BEGIN get_resultset(?, ?); END;");
      stmt.setInt(1, 2); // P_MAX_ROWS
      stmt.registerOutParameter(2, OracleTypes.CURSOR); // P_RESULTSET - REF CURSOR
      ResultSet rs = ((OracleCallableStatement)stmt).getCursor(2);
      while ( {
        System.out.println(rs.getString("id") + " : " + rs.getString("description"));
      rs = null;
      stmt = null;
      conn = null;
    catch (SQLException e) {

  public static void main (String[] args) {
    new TestResultSet();

For the sake of completeness, the cursor_variable_input_param.sql script provides an example of how a cursor variable can be used as an input parameter for a stored procedure.



  TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  l_cursor  t_ref_cursor;

  PROCEDURE display_results (p_cursor  IN  t_ref_cursor) AS
    l_row  cursor_variable_test%ROWTYPE;
      FETCH p_cursor
      INTO  l_row;

      EXIT WHEN p_cursor%NOTFOUND;

      DBMS_OUTPUT.put_line( || ' : ' || l_row.description);
  END display_results;

  DBMS_OUTPUT.put_line('Query 1');

  OPEN l_cursor FOR
    SELECT *
    FROM   cursor_variable_test
    WHERE  id <= 2;

  display_results(p_cursor => l_cursor);

  CLOSE l_cursor;

  DBMS_OUTPUT.put_line('Query 2');

  OPEN l_cursor FOR
    SELECT *
    FROM   cursor_variable_test;

  display_results(p_cursor => l_cursor);

  CLOSE l_cursor;

The cursor_variable_input_param.sql script defines an anonymous block that contains a declaration of a stored procedure called display_results. This procedure accepts a strongly typed cursor variable as an input parameter.  The body of the block opens and passes two separate cursors to the procedure, after which the cursors are closed.  The output from this script is shown below. 

SQL> @cursor_variable_input_param.sql
Query 1
1 : One
2 : Two
Query 2
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

This output clearly shows that the cursor variables passed to the procedure are working as expected.

The way cursor variable parameters are used affects how and where they should be defined so that they have the correct scope.  The following scenarios should help you make the correct decision:

  • You only plan to use weakly typed cursor variable parameters You can dispense with REF CURSOR definitions and use the SYS_REFCURSOR type.  As this is a built-in type, it is always in scope and can be used for parameters only referenced from within server-based PL/SQL and parameters referenced by client-based code.

  • You plan to use strongly typed cursor variable parameters only referenced within a single package The REF CURSOR definitions can be placed in the package body as it is not referenced externally by any code.

  • You plan to use strongly typed cursor variable parameters referenced by other packages and client based code The REF CURSOR definitions must be placed in a package specification so that they are in scope for all code.

The following section addresses the usage of cursor variables in PL/SQL code.


This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational