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


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

  -- 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');

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.


  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,
             SELECT c.description
             FROM   child_tab c
             WHERE  c.parent_id =
           ) children
    FROM   parent_tab p;
  OPEN c_parent_child;
    -- Fetch each row from the parent query in turn.

    FETCH c_parent_child
    INTO  l_parent,
    EXIT WHEN c_parent_child%NOTFOUND;   

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

    -- Loop through the nested query returning each row.

      FETCH l_children
      INTO  l_child;
      EXIT WHEN l_children%NOTFOUND;

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

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.


  l_return  VARCHAR2(32767);
  l_temp    VARCHAR2(32767);
    FETCH p_cursor
    INTO  l_temp;
    EXIT WHEN p_cursor%NOTFOUND;
    l_return := l_return || ',' || l_temp;
  RETURN LTRIM(l_return, ',');

COLUMN children FORMAT A30

SELECT p.description AS parent,
           SELECT c.description
           FROM   child_tab c
           WHERE  c.parent_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 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