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 


 

 

 


 

 

 
 

PL/SQL resultset tips


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 used with native dynamic SQL in much the same way as shown in previous sections of this chapter.  The dynamic_cursor_variables.sql script shows how this is done.

dynamic_cursor_variables.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR; 

  PROCEDURE get_cursor (p_id      IN   NUMBER,
                        p_cursor  OUT  SYS_REFCURSOR) AS
    l_sql  VARCHAR2(32767);
  BEGIN
    l_sql := 'SELECT * FROM cursor_variable_test';   

    IF p_id = 0 THEN
      OPEN p_cursor FOR l_sql;
    ELSE
      l_sql := l_sql || ' WHERE id = :p_id';
      OPEN p_cursor FOR l_sql USING p_id;
    END IF;
  END get_cursor;

  PROCEDURE display_cursor (p_cursor  IN  SYS_REFCURSOR) AS
    l_row  cursor_variable_test%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.put_line('Start display_cursor');
    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_cursor;
BEGIN
  get_cursor(p_id     => 0,
             p_cursor => l_cursor);    

  display_cursor(p_cursor => l_cursor); 

  CLOSE l_cursor; 

  get_cursor(p_id     => 2,
             p_cursor => l_cursor);    

  display_cursor(p_cursor => l_cursor);
 
  CLOSE l_cursor;
END;
/

Two procedures are defined in the declaration section of this native dynamic SQL block.  The get_cursor procedure builds one of two query strings, depending on the input parameters, and uses this to open a cursor variable.  The display_cursor procedure simply displays the contents of the cursor variable.  The output from this native dynamic SQL script is displayed below.

SQL> @dynamic_cursor_variables.sql
Start display_cursor
1 : One
2 : Two
3 : Three
Start display_cursor
2 : Two

With such a simple example it would be possible to avoid dynamic SQL altogether, as shown by the non_dynamic_cursor_variables.sql script, but it serves to illustrate the technique in question.

non_dynamic_cursor_variables.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR; 

  PROCEDURE get_cursor (p_id      IN   NUMBER,
                        p_cursor  OUT  SYS_REFCURSOR) AS
  BEGIN
    IF p_id = 0 THEN
      OPEN p_cursor FOR
        SELECT *
        FROM   cursor_variable_test;
    ELSE
      OPEN p_cursor FOR
        SELECT *
        FROM   cursor_variable_test
        WHERE  id = p_id;
    END IF;
  END get_cursor;

  PROCEDURE display_cursor (p_cursor  IN  SYS_REFCURSOR) AS
    l_row  cursor_variable_test%ROWTYPE;
  BEGIN
    DBMS_OUTPUT.put_line('Start display_cursor');
    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_cursor;
BEGIN
  get_cursor(p_id     => 0,
             p_cursor => l_cursor);    

  display_cursor(p_cursor => l_cursor);

  CLOSE l_cursor; 

  get_cursor(p_id     => 2,
             p_cursor => l_cursor);    

  display_cursor(p_cursor => l_cursor); 

  CLOSE l_cursor;
END;
/

In most of the examples shown so far, the resultsets returned by a single procedure had the same return type. However in some situations, a procedure can be used to return variant resutlsets; in other words a variety of return types.  This is easily possible when using weakly typed cursor variables, as shown by the variant_resultset.sql script.

variant_resultset.sql

CREATE OR REPLACE PROCEDURE get_variant_cursor(p_type    IN   VARCHAR2,
                                               p_cursor  OUT  SYS_REFCURSOR) AS
BEGIN
  IF p_type = 'CVT_ID' THEN
    OPEN p_cursor FOR
      SELECT id
      FROM   cursor_variable_test
      ORDER BY id;  

  ELSIF p_type = 'CVT_DESCRIPTION' THEN
    OPEN p_cursor FOR
      SELECT description
      FROM   cursor_variable_test
      ORDER BY description;

  ELSE
    OPEN p_cursor FOR
      'SELECT * FROM ' || p_type;
  END IF;
END get_variant_cursor;
/

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_row     cursor_variable_test%ROWTYPE;
BEGIN

  get_variant_cursor(p_type   => 'CVT_ID',
                     p_cursor => l_cursor); 

  l_row := NULL;        
  LOOP
    FETCH l_cursor
    INTO  l_row.id;
    EXIT WHEN l_cursor%NOTFOUND;   

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

  CLOSE l_cursor; 

  get_variant_cursor(p_type   => 'CVT_DESCRIPTION',
                     p_cursor => l_cursor); 

  l_row := NULL;            
  LOOP
    FETCH l_cursor

    INTO  l_row.description;
    EXIT WHEN l_cursor%NOTFOUND;   

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

  CLOSE l_cursor;

  get_variant_cursor(p_type   => 'cursor_variable_test',
                     p_cursor => l_cursor);
  l_row := NULL;            
  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;
/

DROP PROCEDURE get_variant_cursor;

The variant_resultset.sql native dynamic SQL script creates a stored procedure called get_variant_cursor.  As the name implies, this procedure returns a variety of cursors depending on the input parameter, using both regular and dynamic SQL.  The script defines an anonymous block to test the procedure and then drops the procedure.  The results from the script are displayed below and clearly show that the procedure is able to return variant resultsets.

SQL> @variant_resultset.sql

Procedure created.

1 :
2 :
3 :
: One
: Three
: Two
1 : One
2 : Two
3 : Three

PL/SQL procedure successfully completed.

Procedure dropped.

An explanation regarding the restrictions associated with cursor variables is presented in the next section.

Restrictions When Using Cursor Variables

There are a number of restrictions associated with the use of cursor variables including:

  • Cursor variables cannot be declared in a package specification, as shown by the following example.

SQL> CREATE PACKAGE cursor_variable_api AS
  2     TYPE t_ref_cursor IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  3     g_ref_cursor  t_ref_cursor;
  4  END cursor_variable_api;
  5  /

Warning: Package created with compilation errors.

SQL> show errors
Errors for PACKAGE CURSOR_VARIABLE_API:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/18     PL/SQL: Declaration ignored
3/18     PLS-00994: Cursor Variables cannot be declared as part of a package

  • Cursor variables cannot be passed to a procedure via a database link.

  • Host cursor variables can only be used in a server-side fetch if it is opened during the same server call.

  • Comparison operators cannot be used to test cursor variables for equality, inequality or nullity.

SQL> DECLARE
  2    l_cursor_1  SYS_REFCURSOR;
  3    l_cursor_2  SYS_REFCURSOR;
  4  BEGIN
  5    OPEN l_cursor_1 FOR
  6      SELECT *
  7      FROM   cursor_variable_test;
  8     
  9    l_cursor_2 := l_cursor_1;
 10   
 11    IF l_cursor_1 = l_cursor_2 THEN
 12      DBMS_OUTPUT.put_line('Equal');
 13    ELSE
 14      DBMS_OUTPUT.put_line('Not Equal');
 15    END IF;
 16   
 17    CLOSE l_cursor_1;
 18  END;
 19  /
  IF l_cursor_1 = l_cursor_2 THEN
                *
ERROR at line 11:
ORA-06550: line 11, column 17:
PLS-00306: wrong number or types of arguments in call to '='
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored

* Assigning NULL values to an open cursor variable results in a nasty error, as shown below.

SQL> DECLARE
  2    l_cursor  SYS_REFCURSOR;
  3  BEGIN
  4    OPEN l_cursor FOR
  5      SELECT *
  6      FROM   cursor_variable_test;
  7     
  8    l_cursor := NULL;
  9   
 10    CLOSE l_cursor;
 11  END;
 12  /
ERROR:
ORA-03113: end-of-file on communication channel

  • Cursor variable values cannot be stored in a database column.

  • Cursor variable values cannot be stored in a PL/SQL collection of any type (associative array, nested table or varray).

  • Cursor variables and cursors do a similar job, but they are not interchangeable.

The following section explores the use of cursor expressions and how they relate to cursor variables.

 

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 (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