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 Attributes and Cursor Variable Usage


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:

With the exception of their definition and how they are opened, the usage of cursor variables matches that of explicit cursors.  The cursor_attributes.sql script shows the use of the %ISOPEN, %FOUND, %NOTFOUND and %ROWCOUNT cursor attributes with a cursor variable.

cursor_attributes.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  IF NOT l_cursor%ISOPEN THEN
    OPEN l_cursor FOR
      SELECT *
      FROM   cursor_variable_test
      ORDER BY id DESC;
  END IF; 

  LOOP
    FETCH l_cursor
    INTO  l_row;   

    -- Use both checks to prove a point.
    EXIT WHEN NOT l_cursor%FOUND;
    EXIT WHEN l_cursor%NOTFOUND;   

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

  CLOSE l_cursor;
EXCEPTION
  WHEN OTHERS THEN
    IF l_cursor%ISOPEN THEN
      CLOSE l_cursor;
    END IF;
END;
/

The results from this script show that the cursor attributes are working as expected.

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

PL/SQL procedure successfully completed.

When using strongly typed ref cursors, variables must have matching datatypes not only matching column lists for assignments to be successful, as shown by the cursor_variable_assignment.sql script.

cursor_variable_assignment.sql

SET SERVEROUTPUT ON
DECLARE
  TYPE t_strong_1 IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;

  l_start  t_strong_1;
  l_end    t_strong_1;
BEGIN
  OPEN l_start FOR
    SELECT *
    FROM   cursor_variable_test;

  l_end := l_start;
  DBMS_OUTPUT.put_line('Strong assignment with matching datatype successful.');   

  CLOSE l_start;
END;
/

DECLARE
  TYPE t_strong_1 IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;

  l_start  t_strong_1;
  l_end    SYS_REFCURSOR;
BEGIN
  OPEN l_start FOR
    SELECT *
    FROM   cursor_variable_test;

  l_end := l_start;
  DBMS_OUTPUT.put_line('Weak assignment successful.');   

  CLOSE l_start;
END;
/

DECLARE
  TYPE t_strong_1 IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;
  TYPE t_strong_2 IS REF CURSOR RETURN cursor_variable_test%ROWTYPE;

  l_start  t_strong_1;
  l_end    t_strong_2;
BEGIN
  OPEN l_start FOR
    SELECT *
    FROM   cursor_variable_test;

  l_end := l_start;
  DBMS_OUTPUT.put_line('Strong assignment with non-matching datatype successful.');

  CLOSE l_start;
END;
/

The cursor_variable_assignment.sql script defines three blocks, each containing a slightly different assignment.  The first block assigns a strongly typed cursor variable to another with the same datatype definition.  The second block assigns a strongly typed cursor variable to a weakly typed cursor variable.  The third block assigns a strongly typed cursor variable to another strongly typed cursor variable with a different datatype but the same return clause.  The output from this script is shown below.

SQL> @cursor_variable_assignment.sql
Strong assignment with matching datatype successful.

PL/SQL procedure successfully completed.

Weak assignment successful.

PL/SQL procedure successfully completed.

  l_end := l_start;
           *
ERROR at line 12:
ORA-06550: line 12, column 12:
PLS-00382: expression is of wrong type
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored

As expected, the first two blocks run successfully while the third block produces a compilation error.

With the exception of the %ISOPEN attribute, any attempt to interact with a cursor variable that has not been opened results in an INVALID_CURSOR error, as shown by the invalid_cursor.sql script.

invalid_cursor.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_row     cursor_variable_test%ROWTYPE;
BEGIN
  IF NOT l_cursor%ISOPEN THEN
    DBMS_OUTPUT.put_line('Expecting a runtime error.');
  END IF;
 
  FETCH l_cursor
  INTO  l_row;

  CLOSE l_cursor;
END;
/

The output from this script clearly demonstrates this behavior.

SQL> @invalid_cursor.sql
Expecting a runtime error.
DECLARE
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 9

Assignments between cursor variables are by value, so after an assignment has occurred, manipulation of one cursor variable does not affect the state of the other.  This feature is demonstrated by the cursor_assignments_by_value.sql script that defines two cursor variables, assigns one to the other, opens one cursor variable and checks both their states.

cursor_assignments_by_value.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor_1  SYS_REFCURSOR;
  l_cursor_2  SYS_REFCURSOR;
 
  PROCEDURE check_cursor (p_text    IN  VARCHAR2,
                          p_cursor  IN  SYS_REFCURSOR) AS
  BEGIN
    IF p_cursor%ISOPEN THEN
      DBMS_OUTPUT.put_line(p_text || ' : OPEN');
    ELSE
      DBMS_OUTPUT.put_line(p_text || ' : CLOSED');
    END IF;
  END check_cursor;
BEGIN

  DBMS_OUTPUT.put_line('Assign value of l_cursor_1 to l_cursor_2');

  l_cursor_2 := l_cursor_1;

  check_cursor('l_cursor_1', l_cursor_1);
  check_cursor('l_cursor_2', l_cursor_2);

  DBMS_OUTPUT.put_line('Open l_cursor_1');

  OPEN l_cursor_1 FOR
    SELECT *
    FROM   cursor_variable_test;

  check_cursor('l_cursor_1', l_cursor_1);
  check_cursor('l_cursor_2', l_cursor_2);

  CLOSE l_cursor_1;
END;
/

The output of this script is displayed below and clearly demonstrates the independence of the cursor variables.

SQL> @cursor_assignments_by_value.sql
Assign value of l_cursor_1 to l_cursor_2
l_cursor_1 : CLOSED
l_cursor_2 : CLOSED
Open l_cursor_1
l_cursor_1 : OPEN
l_cursor_2 : CLOSED

PL/SQL procedure successfully completed.

Weakly typed cursor variables can point to any cursor so there are no compile time checks of the return types they may encounter.  As a result, it is possible that an unexpected return type may be passed as a parameter resulting in a ROWTYPE_MISMATCH exception at runtime. 

The cursor_rowtype_mismatch.sql script demonstrates this by opening a cursor variable with a query that only references a single table column.  This cursor variable is then passed as a parameter to a procedure that expects a two column return type.  The resulting exception is trapped and a message is displayed below.

cursor_rowtype_mismatch.sql

SET SERVEROUTPUT ON
DECLARE
  l_cursor  SYS_REFCURSOR;
 
  PROCEDURE process_cursor (p_cursor  IN  SYS_REFCURSOR) AS
    l_row  cursor_variable_test%ROWTYPE;
  BEGIN
    LOOP
      FETCH p_cursor
      INTO  l_row;
      EXIT WHEN p_cursor%NOTFOUND;
    END LOOP;
  EXCEPTION
    WHEN ROWTYPE_MISMATCH THEN
      DBMS_OUTPUT.put_line('A ROWTYPE_MISMATCH exception was trapped.');
  END process_cursor;
BEGIN
  OPEN l_cursor FOR
    SELECT id
    FROM   cursor_variable_test; 

  process_cursor (p_cursor => l_cursor); 

  CLOSE l_cursor;
END;
/

As expected, the results from this script show there are no compile time errors but rather the ROWTYPE_MISMATCH exception is produced at runtime.

SQL> @cursor_rowtype_mismatch.sql
A ROWTYPE_MISMATCH exception was trapped.

PL/SQL procedure successfully completed.

The next section introduces the subject of host variables as cursor variables.

Host Variables as Cursor Variables

Host variables can be defined as cursor variables in the normal fashion, as seen in the following example.

EXEC SQL BEGIN DECLARE SECTION;
   /* Declare host cursor variable. */
   SQL_CURSOR l_cursor;
   int        l_order;
EXEC SQL END DECLARE SECTION;

/* Initialize host cursor variable. */
EXEC SQL ALLOCATE :l_cursor;

/* Pass host cursor variable and selector to PL/SQL block. */
EXEC SQL EXECUTE
BEGIN
   IF :l_order = 1 THEN
      OPEN :l_cursor FOR SELECT * FROM cursor_variable_test ORDER BY id ASC;
   ELSE
      OPEN :l_cursor FOR SELECT * FROM cursor_variable_test ORDER BY id DESC;
   END IF;
END;
END-EXEC;

Notice the references to the host variables are prefixed with a ":" in the PL/SQL code.

Host variables defined as cursor variables are always weakly typed and function correctly with any return type.

The next section describes the use of cursor variables with dynamic SQL.

 

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