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 Expressions


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 expressions allow nested cursors to be returned as part of normal result sets, allowing complex parent-child data to be returned in a single query.  Cursor expressions are supported by PL/SQL in cursor definitions, REF CURSOR declarations, cursor variables and dynamic SQL.

The nested cursor is controlled implicitly while the parent cursor is being processed.  It is opened when the containing row is fetched from the parent cursor, and it is closed either explicitly by the user or when the parent cursor is re-executed, closed, cancelled or returns an error during a fetch operation.

The cursor_expression_tabs.sql script creates several schema objects which are used in the following examples.

cursor_expression_tabs.sql

-- Define the tables and data required by the sample code.

CREATE TABLE parent_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT parent_tab_pk PRIMARY KEY (id)
);

CREATE TABLE child_tab (
  id           NUMBER,
  parent_id    NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT child_tab_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_fk
    FOREIGN KEY (parent_id)
    REFERENCES parent_tab(id)
);

BEGIN
  -- Parent data.
  INSERT INTO parent_tab (id, description) VALUES (1, 'Parent 1');
  INSERT INTO parent_tab (id, description) VALUES (2, 'Parent 2');
  INSERT INTO parent_tab (id, description) VALUES (3, 'Parent 3');

  -- Child Data.

  INSERT INTO child_tab (id, parent_id, description) VALUES (1, 1, 'Child 1');
  INSERT INTO child_tab (id, parent_id, description) VALUES (2, 1, 'Child 2');
  INSERT INTO child_tab (id, parent_id, description) VALUES (3, 2, 'Child 3');
  INSERT INTO child_tab (id, parent_id, description) VALUES (4, 2, 'Child 4');
  INSERT INTO child_tab (id, parent_id, description) VALUES (5, 2, 'Child 5');
  INSERT INTO child_tab (id, parent_id, description) VALUES (6, 3, 'Child 6');
COMMIT;
END;
/

With these schema objects in place, the cursor_expression_parent_child.sql script can be run to illustrate how a cursor expression can be used.

cursor_expression_parent_child.sql

SET SERVEROUTPUT ON
DECLARE
  l_parent    parent_tab.description%TYPE;
  l_children  SYS_REFCURSOR;
  l_child     child_tab.description%TYPE;

  -- Define a cursor containing a cursor expression.

  CURSOR c_parent_child IS
    SELECT p.description,
           CURSOR(
             SELECT c.description
             FROM   child_tab c
             WHERE  c.parent_id = p.id
           ) children
    FROM   parent_tab p;
BEGIN
  OPEN c_parent_child;
  LOOP
    -- Fetch each row from the parent query in turn.

    FETCH c_parent_child
    INTO  l_parent,
          l_children;
    EXIT WHEN c_parent_child%NOTFOUND;   

    DBMS_OUTPUT.put_line('Parent: ' || l_parent);

    -- Loop through the nested query returning each row.

    LOOP
      FETCH l_children
      INTO  l_child;
      EXIT WHEN l_children%NOTFOUND;

            DBMS_OUTPUT.put_line('...Child: ' || l_child);
    END LOOP;
  END LOOP;
  CLOSE c_parent_child;
END;
/

The declaration section of the anonymous block contains several variable definitions, including a cursor variable to hold the nested cursor data and a cursor definition that contains a cursor expression.  The cursor returns all parent records, while the cursor expression returns the child records associated with each parent row.

The body of the block then loops through all the parent records returning the parent data and a cursor variable representing its associated child rows, which are in turn processed by a nested loop.  The output from the script clearly demonstrates how the parent-child relationship is modeled by the cursor expression.

SQL> @cursor_expression_parent_child.sql
Parent: Parent 1
...Child: Child 1
...Child: Child 2
Parent: Parent 2
...Child: Child 3
...Child: Child 4
...Child: Child 5
Parent: Parent 3
...Child: Child 6

PL/SQL procedure successfully completed.

In addition, cursor expressions can be used to supply rows to functions called in a queries select list.  The cursor_expression_function_parameter.sql script demonstrates this feature.

cursor_expression_function_parameter.sql

CREATE OR REPLACE FUNCTION concatenate_list (p_cursor IN  SYS_REFCURSOR)
  RETURN  VARCHAR2
IS
  l_return  VARCHAR2(32767);
  l_temp    VARCHAR2(32767);
BEGIN
  LOOP
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  END LOOP;
  RETURN LTRIM(l_return, ',');
END;
/
SHOW ERRORS

COLUMN parent FORMAT A10
COLUMN children FORMAT A30

SELECT p.description AS parent,
       concatenate_list(
         CURSOR(
           SELECT c.description
           FROM   child_tab c
           WHERE  c.parent_id = p.id
         )
       ) AS children
FROM   parent_tab p;

DROP FUNCTION concatenate_list;

The cursor_expression_function_parameter.sql script defines a function that accepts a single column cursor variable as an input parameter and returns a string, representing all the values within the cursor variable concatenated together into a comma delimited string.  This function is then used in a query and finally dropped.  The results from this script are displayed below.

SQL> @chapter6\cursor_expression_function_parameter.sql

Function created.

No errors.

PARENT     CHILDREN
---------- ------------------------------
Parent 1   Child 1,Child 2
Parent 2   Child 3,Child 4,Child 5
Parent 3   Child 6

Function dropped.

Using the concatenate_list function, the child rows associated with each parent have been displayed in a multi-value column.

There are some restrictions associated with cursor expressions including:

  • Cursor expressions cannot be used with an implicit cursor.

  • Cursor expressions are only allowed in SELECT statements not nested in subqueries, unless the subquery is part of the cursor expression itself.  This means they are only allowed in the outermost SELECT list of a query.

  • Cursor expressions are allowed as arguments to table functions, but only in the FROM clause of a SELECT statement.

  • Cursor expressions cannot appear in view definitions.

  • BIND and EXECUTE operations cannot be performed on cursor expressions.

 

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