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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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.

get_resultset.sql

-- 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
BEGIN
  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.

resultset_plsql_test.sql

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  get_resultset (p_max_id    => 2,
                 p_resultset => l_cursor);      

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

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"))
  rs.MoveNext
Loop

rs.Close
conn.Close
Set rs     = nothing
Set param1 = nothing
Set cmd    = nothing
Set conn   = nothing

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

resultset_java_test.java

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

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.

cursor_variable_input_param.sql

SET SERVEROUTPUT ON SIZE 1000000

DECLARE
  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;
  BEGIN
    LOOP
      FETCH p_cursor
      INTO  l_row;

      EXIT WHEN p_cursor%NOTFOUND;

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

BEGIN
  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;
END;
/

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