Cursor Basics: Cursor Iteration Controls

declare
    l_task varchar2(100);
    type t_emp_rec is record(
        name varchar2(50), 
        job varchar2(50));
    type t_emp_cur is ref cursor return t_emp_rec;
    c t_emp_cursor;
    rc sys_refcursor;
begin
    l_task := 'use a for loop to iterate the rows of a ref cursor';
    dbms_output.put_line(l_task);
end;

In the previous post we discussed the use of bulk collect and limit to optimize cursor iteration. This gave us a way to see the undocumented bulk prefetch optimization that has been applied behind the scenes to the cursor for loop.

Unless there are gains to be made from tuning a bulk collect limit, the cursor for loop offers simpler syntax compared to the open, fetch, close method and equivalent performance to a bulk collect in any use case that requires row by row iteration. Performance and simpler syntax give the cursor for loop an edge over the cumbersome open, fetch, close approach when working with explicity cursors.

The ability to inline the cursor definition is also an advantage for the cursor for loop. The cursor for loop cannot be used with cursor variables and dynamic sql, these limitations keep it from being an approach for all use cases.

In Oracle version 21c, along with other for loop iterator enhancements, the cursor iteration control was introduced as a new way to iterate cursor resultsets. These features allow a for loop to be combined with cursor variables and dynamic sql, removing the classic limitations of the cursor for loop. This new approach combines the elegance of the cursor for loop syntax with the versatility of the fetch method and the performance of the bulk collect method. Cursor iteration controls also introduce completely new functionality that was not possible with earlier approaches to cursor iteration.

Getting Started

The source code for all articles here on practicalplsql.org is available in the practicalplsql public repository on github in the scripts directory. You can use scripts/create.user.practicalplsql.sql to create the practicalplsql schema. Sample data for articles can be found in the scripts/examples directory organized by example name.

Scripts for this post are located in scripts/articles/cursor-basics/part-3-cursor-iteration-controls. For many of the examples I will be using a simple table populated with employees, storing the name and job of each employee. This example is located at scripts/examples/simple-employees. The example includes a few standalone procedures for commonly repeated code to keep the code more readable. These include the following: print_boolean_attribute, print_employee, print_timing and print_timing_and_fetches.

The performance tests in this article use the standalone function get_sql_engine_fetches to estimate the number of fetches for a given sql statement. This is done by using a text literal to track the sql statement fetches using v$sqlarea.

This article focuses on a feature which was introduced in Oracle version 21, any examples using this new feature will not run on prior versions of the database. Additional syntax enhancements from version 21 are used freely which may require additional changes to convert examples to their equivalence in prior versions. Iterating a collection using indices of, values of or pairs of is an example of this. Note: the performance compare scripts use indices of to iterate collections, this works in anonymous blocks for version 21, but won’t compile in a procedure until version 23. The performance tests use procedures with conditional compilation as a workaround to this problem.

All examples have been tested in sql developer. All scripts should be executed with serveroutput on, so this repetition is omitted for clarity. Finally, when several scripts produce the same correct output, the output will only be shown once to avoid repetition. Because each example is meant to focus on different aspects of the code, some parts are ommitted when they are not necessary to make the code compile and execute. This also helps to show that code can be written which compiles and works in many cases even when it is lacking in part of the implementation.

Cursor For Loop

Cursor For Loops can be used with explicit cursors. The activities of opening the cursor before the loop, fetching each record inside the loop, exiting the loop when cursor%notfound is true and closing the cursor are handled behind the scenes. As we saw in the previous article on bulk collect, the cursor for loop also has an undocumented bulk prefetch optimization, making its performance equivalent to a bulk collect with a limit of 100 rows.

Prompt Method: Cursor For Loop with explicit cursor
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        fetch first 3 rows only;    
begin

    for r_emp in c_emps loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
    
end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR

An implicit cursor can also be defined inline. This approach eliminates the declaration of the cursor, further simplifying the necessary code.

Prompt Method: Cursor For Loop with inline cursor
begin

    for r_emp in (
        select e.name, e.job
        from employees e
        fetch first 3 rows only
    ) loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
    
end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR

Cursor For Loop Limitations

The cursor for loop cannot be used with any type of ref cursor. The syntax is obviously incompatible, how could the for loop open the cursor when the ref cursor needs to be opened with an sql statement explicitly? Is it still a cursor for loop if we have to explicitly open the ref cursor?

It would be nice to loop through the records of a ref cursor and not have to worry about fetching each record and exiting the loop at the correct time. The following examples open ref cursors and attempt to iterate the records with the cursor for loop syntax. Try running them on Oracle 19c to confirm this limitation.

The cursor for loop cannot be used with strongly typed ref cursors. If we try to run this block on any version of the database less than 21 we get a compiler error. The compiler is simply not sure about the reference to c_emps in the for loop…even though c_emps has been declared as a ref cursor.

Prompt Method: Cursor For Loop with strongly typed ref cursor fails
declare
    type t_emp_rec is record(
        name varchar2(50), 
        job varchar2(20));
    type t_emp_cur is ref cursor return t_emp_rec;
    c_emps t_emp_cur;
begin

    open c_emps for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_emp in c_emps loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
    
end;

Error report -
ORA-06550: line 14, column 18:
PLS-00221: 'C_EMPS' is not a procedure or is undefined

This is also true with weakly typed ref cursors. If we run this block against an Oracle version 19 database we get the same error.

Prompt Method: Cursor For Loop with weakly typed ref cursor fails
declare
    type t_emp_rec is record(
        name varchar2(50), 
        job varchar2(20));
    c_emps sys_refcursor;
begin

    open c_emps for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_emp in c_emps loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
    
end;

Fails on any version less than 21 with compiler error
Error report -
ORA-06550: line 14, column 18:
PLS-00221: 'C_EMPS' is not a procedure or is undefined

What about dynamic sql? Let’s try a for loop with inline dynamic sql in Oracle 19.

Prompt Method: Cursor For Loop with inline dynamic sql
declare
    l_sql varchar2(1000);
begin

    l_sql := 
        'select e.name, e.job
        from employees e
        fetch first 3 rows only';

    for r_emp in l_sql loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
    
end;

Error report -
ORA-06550: line 10, column 18:
PLS-00456: item 'L_SQL' is not a cursor

The code won’t compile, but the error message tells us that l_sql is not a cursor. That much is obvious, l_sql is a string, not a cursor. In this case the compiler decided that l_sql needs to be a cursor based on the ‘for r_emp in l_sql loop’ syntax. In the previous examples the error complained that c_emps was not a procedure or was undefined…and c_emps was a cursor variable! It is true that a cursor variable (ref cursor) is not the same thing as a cursor, but the error message for the string variable could have been used to make this point. Obviously we are running into a construct that is not supported in this version. In the next section we try these concepts in Oracle version 21, with very different results.

Enhanced Iteration Controls

With Oracle version 21, the iteration controls were introduced with other enhancements to the for loop. The vocabulary used to describe for loops has also been expanded. For loops are used to run one or more statements for each value of the loop index in a sequence of values. The simplest case is the numeric for loop:

for idx in 1..3 loop
    ...run statements with current value of idx
end loop;

In this example, idx is the loop index, and 1..3 defines the sequence of values (1, 2, 3). The statements inside the loop are executed for each iteration of the loop using the current value of the loop index in the sequence of values. The loop index is the iterand, and the sequence of values is the iteration control sequence.

for [iterand] in [iteration control sequence] loop
    ...run statements with current value of iterand
end loop;

There are two other important concepts related to iteration controls that need mentioning before we get to an explanation of how they can be used to iterate ref cursors with simple for loop syntax. The datatype of the iterator can now be specified, and multiple iteration controls can be used in the for loop.

With a stepped iteration control that uses a non integer step, the datatype of the iterand must be specified because the default for numeric iterands is integer.

prompt Stepped iteration control specifies datatype for iterator
begin
    for i number in 1..3 by 0.5 loop
        dbms_output.put_line(i);
    end loop;
end;

1
1.5
2
2.5
3

Multiple iteration controls can also be combined in the same loop as long as each iteration control is a sequence of values with the same data type as the iterand.

This block uses three iteration controls: [1..3], [4], and [REVERSE 1..3].

prompt Using Multiple Iteration Controls
begin
    for i in 1..3, 4, REVERSE 1..3  loop
        dbms_output.put_line(i);
    end loop;
end;

1
2
3
4
3
2
1

Cursor Iteration Control Types

As of version 21, the cursor for loop can be generalized to the iteration control syntax instead of being seen as a special type of for loop.

for [record] in [cursor] loop
    ...
end loop;

In this case, the implicitly declared loop index record is the iterand and the reference to cursor is the iteration control sequence (the sequence of records defined by the cursor). An iteration control is used to generate a sequence of values, and cursors are a type of iteration control used to generate a sequence of records using a cursor.

The classic cursor for loop does more than just iterate through the records of a cursor, it opens the cursor before iteration and closes it when the sequence of records is exhausted. According to the documentation, this machinery has not been implemented for cursor iteration controls, and the cursor needs to be opened before it can be used as an iteration control and it will remain open after the loop is completed.

There are four types of cursor iteration controls: a cursor object, sql statement, cursor variable or a dynamic sql statement. The values of the records generated by any of these types of cursor iteration controls is the sequence of iterands to be iterated. The datatype of these iterands is the rowtype of the cursor.

As we examine the use of each of these iteration controls we will see that the documented requirements to open the cursor prior to iteration and close the cursor after iteration are only applicable to cursor variable iteration controls.

Iteration Control: Cursor Objects

A cursor object is an explicit cursor defined by PL/SQL. This sounds a lot like a classic for loop, but the documentation says that the cursor for a cursor iteration control needs to be opened before we can generate its values and it remains open after the values are exhausted.

Prompt Cursor Iteration Control: Cursor Object is an explicit cursor
declare
    cursor c_cursor_object is
        select e.name, e.job
        from employees e
        fetch first 3 rows only;    
begin
    open c_cursor_object;
    
    for r_iterand in c_cursor_object loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    close c_cursor_object;    
end;

Error report -
ORA-06511: PL/SQL: cursor already open
...
*Cause:    An attempt was made to open a cursor that was already open.
*Action:   Close cursor first before reopening.

The loop header opened the explicit cursor again! Maybe this is just being interpreted as a classic cursor for loop by the compiler?

Since the cursor for loop has never supported iterating multiple cursors, we can try this approach to be sure that we are testing a cursor iteration control. In the example we will skip opening the cursors first because we know that causes an exception. The script will check the cursor%isopen attribute to see if the iteration control is opening and closing the explicit cursors.

Prompt Cursor Iteration Control: Multiple Cursor Objects
declare
    cursor c_rep is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_REP';

    cursor c_mgr is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_MGR';
        
begin
    print_boolean_attribute(c_rep%isopen,'c_rep%isopen');
    print_boolean_attribute(c_mgr%isopen,'c_mgr%isopen');
    
    for r_iterand in c_rep, c_mgr loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    print_boolean_attribute(c_rep%isopen,'c_rep%isopen');
    print_boolean_attribute(c_mgr%isopen,'c_mgr%isopen');
end;

(c_rep%isopen is false)
(c_mgr%isopen is false)
John SALES_REP
Jane SALES_REP
Julie SALES_REP
Alex SALES_REP
Sarah SALES_REP
Ann SALES_MGR
Tobias SALES_MGR
(c_rep%isopen is false)
(c_mgr%isopen is false)

It worked, we iterated two different cursors with a single for loop! The rowtypes of each cursor were identical, so there were no issues for the implicitly declared iterand. It is clear that using a cursor object iteration control takes care of opening and closing the cursor behind the scenes.

Running this script fails on any version before Oracle 21 because of the comma after the first cursor referenced in the for loop. We can conclude that using cursor objects as cursor iteration controls relaxes the requirement to open and close the cursors. This is probably done for backwards compatibility with existing cursor for loops.

Iteration Control: SQL Statements

The second type of cursor iteration control is an implicit cursor specified by an SQL statement directly in the iteration control. This sounds a lot like a cursor for loop with an inline sql statement. Because the implicit cursor is defined inline there is nothing to reference before the loop that we can open, and nothing to reference after the loop that we can close.

Lets try this with multiple inline sql statements to be sure that we are testing a cursor iteration control.

Prompt Cursor Iteration Control: Multiple Sql Statements
begin

    for r_iterand in (
        select e.name, e.job
        from employees e
        where e.job = 'SALES_REP'    
        ), (
        select e.name, e.job
        from employees e
        where e.job = 'SALES_MGR'
        ) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

John SALES_REP
Jane SALES_REP
Julie SALES_REP
Alex SALES_REP
Sarah SALES_REP
Ann SALES_MGR
Tobias SALES_MGR

That works nicely! Because the implicit cursors were defined inline with the for loop header, there is really no way to open or close these kinds of cursor iteration controls. If only one implicit cursor was defined, this would run on previous versions of Oracle. Because there are two implicit cursors, we can tell that this is a cursor iteration control in action.

Iteration Control: Strong Cursor Variable

The third type of cursor iteration control uses a cursor variable to generate the set of iterand values. Cursor variables can be strongly typed or weakly typed. With a strongly typed cursor variable, the rowtype is explicitly defined. There is no way for the for loop to open this cursor, so we will have to open it before using it as an iteration control.

This is definitely a new method for cursor variables. In previous versions, a for loop could not be used to iterate the records of an open strong cursor variable. Each record had to be fetched and the loop had to be exited when an empty fetch set the cursor%notfound attribute to true.

Prompt Cursor Iteration Control: Cursor Variable strongly typed
declare
    type t_emp_rec is record(
        name varchar2(50), job varchar2(20));
    type t_strong_cursor_variable is ref cursor return t_emp_rec;
    cv_strong t_strong_cursor_variable;
begin

    open cv_strong for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_iterand in cv_strong loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    print_boolean_attribute(cv_strong%isopen, 'cv_strong%isopen');    
    close cv_strong;
end;

Cursor Iteration Control: Cursor Variable strongly typed
Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR
(cv_strong%isopen is true)

Because the cursor variable is strongly typed, the iterand is implictly declared using the cursor rowtype just like the cursor object and SQL statement cursor iteration controls.

After the loop is finished, the cursor variable is still open and needs to be closed. This is in accordance with the documentation. Cursor object iteration controls and SQL iteration controls close their cursors automatically when the iteration control is exhausted, why not do the same with cursor variables? Obviously this is not a technical restriction if other kinds of cursor iteration controls can manage their own cursors.

What happens if we try to reiterate the open cursor variable?

Prompt Cursor Iteration Control: Cursor Variable Reiteration
declare
    type t_emp_rec is record(
        name varchar2(50), job varchar2(20));
    type t_strong_cursor_variable is ref cursor return t_emp_rec;
    cv_strong t_strong_cursor_variable;
begin

    open cv_strong for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_iterand in cv_strong loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    print_boolean_attribute(cv_strong%isopen, 'cv_strong%isopen');  
    dbms_output.put_line('Cursor is still open, try to iterate again');
    
    for r_iterand in cv_strong loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    close cv_strong;

end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR
(cv_strong%isopen is true)
Cursor is still open, try to iterate again
[nothing prints]

Even though the cursor variable is still open, all the rows of the cursor have already been fetched during the first iteration. Since there is no way to reset the open cursor to the first row there is no apparent reason to leave the cursor variable open after the iteration control has been exhausted. In my opinion, the requirement to close a cursor variable after using it as an iteration control seems arbitrary and unnecessary.

The cursor iteration control has simplified iteration for cursor variables! This enhancement results in streamlined code to iterate cursor variables. When we examine the performance of cursor iteration controls later, we will be able to see if the bulk prefetch optimization is also present.

Iteration Control: Weak Cursor Variable

Iterating a weakly typed cursor variable iteration control in the same way generates a very specific compilation error. The rowtype of the ref cursor may seem obvious because of the sql statement that is used to open it, but this rowtype has not been explicitly defined and the cursor variable can be opened for any sql statement. Without an explicit guarantee of the cursor rowtype, the compiler will not be able to implicitly declare the loop iterand type.

Prompt Cursor Iteration Control: Cursor Variable weakly typed Needs Iterand Type
declare
    cv_weak sys_refcursor;
begin

    open cv_weak for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_iterand in cv_weak loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    close cv_weak;

end;

Error report -
ORA-06550: line 10, column 18:
PLS-00859: No iterand type was specified for a weak Cursor Variable iteration control.

Specifying the iterand type in the header of the for loop allows it to be declared and the cursor variable iteration control is able to generate the rows.

Prompt Cursor Iteration Control: Cursor Variable weakly typed
declare
    type t_iterand_type is record(
        name varchar2(50), job varchar2(20));
    cv_weak sys_refcursor;
begin

    open cv_weak for
        select e.name, e.job
        from employees e
        fetch first 3 rows only;

    for r_iterand t_iterand_type in cv_weak loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    print_boolean_attribute(cv_weak%isopen, 'cv_weak%isopen');
    close cv_weak;

end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR
(cv_weak%isopen is true)

The weak cursor variable has to be closed after the iteration is finished just like the strong cursor variable.

Strong cursor variables cannot be opened using dynamic sql statements at runtime, but weak cursor variables can be. This gives the simplified for loop iteration syntax to cursors defined with dynamic sql.

Considering the case of a weak cursor variable opened with dynamic sql also makes it clear that the rowtype of the iterand must be explicitly specified. Until the cursor variable is opened at runtime, the rowtype is undefined and the iterand type cannot be determined implicitly.

Prompt Cursor Iteration Control: Cursor Variable weakly typed opened with dynamic sql
declare
    type t_iterand_type is record(
        name varchar2(50), job varchar2(20));
    cv_weak sys_refcursor;
    l_sql varchar2(1000);
begin

    l_sql :=
        'select e.name, e.job
        from employees e
        fetch first 3 rows only';
    
    open cv_weak for l_sql;

    for r_iterand t_iterand_type in cv_weak loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
    close cv_weak;

end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR

Cursor iteration controls with cursor variables bring simplified for loop iteration to the cursor variable, even when it is opened with a dynamic sql statement. This is a significant enhancement to the cursor for loop, removing the limitations that we discussed in the beginning of this post.

Iteration Control: Dynamic SQL

The fourth type of cursor iteration control uses inline dynamic sql to open a cursor that has not been defined explicitly, implicitly or with a cursor variable. If we don’t supply the iterand type, the resulting error is self-evident.

Prompt Cursor Iteration Control: Dynamic Sql with implicitly typed iterand fails
declare
    l_sql varchar2(1000);
begin

    l_sql :=
        'select e.name, e.job
        from employees e
        fetch first 3 rows only';
    
    for r_iterand in (execute immediate l_sql) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

PLS-00858: No iterand type was specified for an EXECUTE IMMEDIATE iteration control.

The default iterand type is defined inline in the same way as the weak cursor variable iteration control.

Prompt Cursor Iteration Control: Dynamic Sql with typed iterand
declare
    type t_iterand_type is record(
        name varchar2(50), job varchar2(20));
    l_sql varchar2(1000);
begin

    l_sql :=
        'select e.name, e.job
        from employees e
        fetch first 3 rows only';
    
    for r_iterand t_iterand_type in (execute immediate l_sql) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR

As with any form of dynamic sql we can define bind variables in the sql statement and set them with the using clause.

Prompt Cursor Iteration Control: Dynamic Sql using bind variables
declare
    type t_iterand_type is record(
        name varchar2(50), job varchar2(20));
    l_sql varchar2(1000);
begin

    l_sql :=
        'select e.name, e.job
        from employees e
        where job = :bind_variable ';
    
    for r_iterand t_iterand_type in (
        execute immediate l_sql using 'SALES_MGR'
    ) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

Ann SALES_MGR
Tobias SALES_MGR

The cursor iteration controls that use dynamic sql completely manage the implicit cursors: passing bind variables, opening the cursor, fetching each record, exiting the loop when the last row has been fetched and closing the implicit cursor. All of the simplicity of the cursor for loop has been implemented with dynamic sql!

Using Multiple Cursor Iteration Controls

As we saw in the examples for cursor iteration controls with cursor objects and sql statements, the for loop now supports multiple iteration controls. Multiple iteration controls don’t have to be the same, iteration controls with cursor objects can be freely mixed with sql statements, cursor variables, dynamic sql, or even iteration controls that are not based upon cursors. The only restriction is that all iteration controls in a loop have the same iterand type. If the iterand type can be determined implicitly by the first iteration control, the iterand type does not need to be specified.

Prompt Multiple Cursor Iteration Controls with implicit iterand
declare
    cursor c_rep is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_REP';
    cv_weak sys_refcursor;
    l_sql varchar2(100);
begin

    open cv_weak for 
        select e.name, e.job
        from employees e
        where e.job = 'SALES_SUPPORT';

    l_sql :=  
        'select e.name, e.job
        from employees e
        where e.job = :bind_variable ';
    
    for r_iterand in 
        c_rep
        , (
        select e.name, e.job
        from employees e
        where e.job = 'SALES_MGR'
        )
        , cv_weak
        , (
            execute immediate l_sql using 'SALES_EXEC'
    ) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

John SALES_REP
Jane SALES_REP
Julie SALES_REP
Alex SALES_REP
Sarah SALES_REP
Ann SALES_MGR
Tobias SALES_MGR
Thomas SALES_SUPPORT
George SALES_SUPPORT
Martin SALES_SUPPORT
Gina SALES_EXEC

Because the explicit cursor c_rep was first in the list of iteration controls, the iterand type is determined implicitly. If the first iteration control cannot be used to determine the default iterator, the iterand type should always be specified.

Prompt Multiple Cursor Iteration Controls with explicit iterand
declare
    type t_iterand_type is record(
        name varchar2(50), job varchar2(20));

    cv_weak sys_refcursor;
    l_sql varchar2(100);
begin

    open cv_weak for 
        select e.name, e.job
        from employees e
        where e.job = 'SALES_SUPPORT';

    l_sql :=  
        'select e.name, e.job
        from employees e
        where e.job = :bind_variable ';
    
    for r_iterand t_iterand_type in 
        cv_weak
        , (
            execute immediate l_sql using 'SALES_EXEC'
    ) loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

Thomas SALES_SUPPORT
George SALES_SUPPORT
Martin SALES_SUPPORT
Gina SALES_EXEC

The rowtype of an explicit cursor can also be used to define the iterand.

Prompt Multiple Cursor Iteration Controls with rowtype iterand
declare
    cursor c_rep is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_REP';

    cursor c_mgr is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_MGR';
        
    l_sql varchar2(100);
begin

    l_sql :=  
        'select e.name, e.job
        from employees e
        where e.job = :bind_variable ';
    
    for r_iterand c_rep%rowtype in 
        c_rep
        , (
            execute immediate l_sql using 'SALES_EXEC'
        )
        , c_mgr
         loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

John SALES_REP
Jane SALES_REP
Julie SALES_REP
Alex SALES_REP
Sarah SALES_REP
Gina SALES_EXEC
Ann SALES_MGR
Tobias SALES_MGR

The exception to the above cases seems to be when the iterand is explicitly defined, and a cursor iteration control that requires an explicit iterand is used first, followed by cursor objects…this leads to a critical error that ends the session. This looks like a bug with the implementation.

Prompt Multiple Cursor Iteration Controls with rowtype iterand from cursor objects and no cursor object first
declare
    cursor c_rep is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_REP';

    cursor c_mgr is
        select e.name, e.job
        from employees e
        where e.job = 'SALES_MGR';
        
    l_sql varchar2(100);
begin

    l_sql :=  
        'select e.name, e.job
        from employees e
        where e.job = :bind_variable ';
    
    for r_iterand c_rep%rowtype in 
        --c_rep, 
        (
            execute immediate l_sql using 'SALES_EXEC'
        )
        , c_mgr
         loop
        print_employee(r_iterand.name, r_iterand.job);
    end loop;
    
end;

Error report -
No more data to read from socket

If the c_rep iteration control is uncommented, the script works. If the c_mgr iteration control is commented out, the example is no longer using multiple iteration controls and it works.

Populating Collections Using Cursors

In addition to more compact syntax for iteration, cursor iteration controls provide simplified syntax for populating collections from cursor. To see just how much this syntax has been simplified we will briefly review populating different types of collections with a cursor for loop and using bulk collect.

The scripts for the following examples print out the collection contents and counts, I have omitted this so that we can focus on the code required to populate the collections.

Populating Collections Using Cursor For Loops

The cursor for loop can be used to iterate a cursor and assign each record to a collection. As we saw in the previous article about bulk collect, the cursor for loop has a bulk prefetch optimization that is equivalent to using bulk collect with a limit of 100.

Using the open, fetch, close method to iterate the records and assign each row to a collection variable is similar but lacks this bulk prefetch optimization. For cursor variables and dynamic sql, bulk collect is a better way to populate collections. We will just look at using a cursor for loop and bulk collect in the following examples.

With the cursor for loop, nested tables require the extend method before each row can be added to the collection. Because the collection datatype matches the cursor rowtype, we can just assign each row instead of assigning each attribute separately.

prompt Using A Cursor For Loop To Populate a Nested Table
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype;
    l_rows t_table := t_table();
begin
    for r in c loop
        l_rows.extend();
        l_rows(l_rows.last) := r;
    end loop;
end;

Associative arrays with a numeric index are a bit simpler.

prompt Using A Cursor For Loop To Populate an Associative Array
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by pls_integer;
    l_rows t_table;
begin
    for r in c loop
        l_rows(l_rows.count + 1) := r;
    end loop;
end;

Associative arrays with a string index have always been easy to populate using a cursor for loop.

prompt Using A Cursor For Loop To Populate an Associative Array with Varchar2 Index
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by employees.name%type;
    l_rows t_table;
begin
    for r in c loop
        l_rows(r.name) := r;
    end loop;
end;

Populating Collections Using Bulk Collect

Using bulk collect is generally a better way to populate a collection and the performance can be tuned using the limit clause if necessary.

Using bulk collect simplifies the population of nested tables, the call to extend the collection before adding each element is eliminated and the collection does not have to be initialized.

prompt Using Bulk Collect To Populate A Nested Table from a cursor
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype;
    l_rows t_table;
begin
    open c;
    fetch c bulk collect into l_rows;
    close c;
end;

With bulk collect, the syntax is the same for nested tables and associative arrays with a numeric index.

prompt Using Bulk Collect To Populate An Associative Array from a cursor
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by pls_integer;
    l_rows t_table;
begin
    open c;
    fetch c bulk collect into l_rows;
    close c;
end;

If an empty cursor is used to populate a collection with bulk collect, the resulting collection will always be initialized and empty. Because this case does not raise an exception, it is common practice to iterate the rows of the collection or use it in a forall statement immediately without checking the collection to see if it has any contents. The collection count of zero is also used as the valid exit condition for a loop using bulk collect with a limit.

prompt empty cursor bulk collect returns empty collection
declare
    cursor c is
        select *
        from dual
        where 1 = 0;

    type t_rows is table of c%rowtype;
    l_rows t_rows;
begin        
    open c;
    fetch c bulk collect into l_rows;
    close c;
    dbms_output.put_line('fetched ' || l_rows.count || ' rows');
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

fetched 0 rows

An associative array with a string index cannot be populated using bulk collect. In this case we have to bulk collect the cursor object into a numerically indexed array and then transfer the values to the string indexed array one at a time.

prompt Using Bulk Collect To Populate An Associative Array with a varchar index from a cursor
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by employees.name%type;
    l_rows t_table;
    type t_table_int is table of c%rowtype index by pls_integer;
    l_rows_int t_table_int;
begin
    open c;
    fetch c bulk collect into l_rows_int;
    close c;
    
    for i in 1..l_rows_int.count loop
        l_rows(l_rows_int(i).name) := l_rows_int(i);
    end loop;
end;

Populating Collections With Cursor Object Iteration Controls

The enhanced iteration controls can be used as qualified expressions to populate collections. The iteration control becomes the array constructor which can specify both the index and the array element value.

prompt Using an Iteration Control as an Array Constructor
declare
    type t_table is table of number index by pls_integer;
    l_rows t_table;
begin
    l_rows := t_table(for i in 0..4 index i => power(2, i));
    
    dbms_output.put_line('Powers of 2:');
    for i in indices of l_rows loop
        dbms_output.put_line(i || ' => ' || l_rows(i));
    end loop;    
end;

Powers of 2:
0 => 1
1 => 2
2 => 4
3 => 8
4 => 16

Using a cursor iteration control as an array constructor simplifies the population of arrays from cursors. Instead of iterating the cursor and assigning each row to the collection or bulk collecting rows into the collect, we simply construct the collection with the cursor rows directly.

Nested tables can be constructed directly from cursor iteration controls.

The iterand in this case is the record value and the collection index is defined as an ascending sequence.

prompt Using Cursor Object Iteration Control To Populate A Nested Table
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype;
    l_rows t_table;
begin
    l_rows := t_table(for r in c sequence => r);
end;

The syntax is identical for an associative array with a numeric index.

prompt Using Cursor Object Iteration Control To Populate An Associative Array
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by pls_integer;
    l_rows t_table;
begin
    l_rows := t_table(for r in c sequence => r);
end;

A key difference between populating collections using bulk collect and using iteration controls as array constructors is what happens when the iteration controls were empty.

When bulk collect is used to fetch from a cursor that returns no rows, the resulting collection will be initialized (not null), and empty (collection count = 0). The behavior is the same when using limit with bulk collect, the loop iteration that returns no rows for the bulk collect fetch will leave the collection empty.

When we use cursor iteration controls as array constructors, an empty cursor will fail to initialize the collection.

prompt populating collections with empty cursors
declare

    cursor c is
        select *
        from dual
        where 1 = 0;

    type t_rows is table of c%rowtype;

    l_rows t_rows;
    i number := 0;
    
    procedure print_collection_state(p_rows in t_rows)
    is
    begin
        if p_rows is null then
            dbms_output.put_line('collection is null');
        else
            dbms_output.put_line('collection is initialized');
            if p_rows is empty then
                dbms_output.put_line('collection is empty');
            end if;
            dbms_output.put_line('collection count = ' || p_rows.count);
        end if;
    end print_collection_state;
begin
    
    dbms_output.put_line('fetch empty cursor using bulk collect');   
    l_rows := null;
    open c;
    fetch c bulk collect into l_rows;
    print_collection_state(l_rows);
    close c;
    
    dbms_output.put_line('fetch empty cursor using cursor object iteration control');   
    l_rows := null;
    l_rows := t_rows(for r in values of c sequence => r);
    print_collection_state(l_rows);
    
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

fetch empty cursor using bulk collect
collection is initialized
collection count = 0
fetch empty cursor using cursor object iteration control
collection is null

When converting existing code that uses bulk collect to populate a collection, the collection will have to be compared to null to see if nothing was fetched. If the code simply fetches into the collection and then references the collection count or collection elements, we get an exception.

prompt empty cursor with iteration control returns null collection
declare
    cursor c is
        select *
        from dual
        where 1 = 0;

    type t_rows is table of c%rowtype;
    l_rows t_rows;
begin        
    l_rows := t_rows(for r in values of c sequence => r);
    dbms_output.put_line('fetched ' || l_rows.count || ' rows');
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

ORA-06531: Reference to uninitialized collection

Just as multiple iteration controls can be combined in the same for loop, multiple iteration controls can also be combined in the same array constructor.

prompt Using Multiple Cursor Object Iteration Controls To Populate An Associative Array
declare
    cursor c_rep is
        select name, job
        from employees
        where job = 'SALES_REP';

    cursor c_mgr is
        select name, job
        from employees
        where job = 'SALES_MGR';

    type t_table is table of c_rep%rowtype index by pls_integer;
    l_rows t_table;
begin
    l_rows := t_table(for r in c_rep, c_mgr sequence => r);
end;

We can really see the syntax simplification when populating associative arrays with string indexes.

prompt Using Cursor Object Iteration Control To Populate An Associative Array with Varchar Index
declare
    cursor c is
        select name, job
        from employees;

    type t_table is table of c%rowtype index by employees.name%type;
    l_rows t_table;
begin
    l_rows := t_table(for r in c index r.name => r);
end;

Using multiple cursor iteration controls to construct a single array with a string index is dramatically simplified. This example would have required two for loops or two bulk collects without cursor iteration controls.

prompt Using Multiple Cursor Object Iteration Controls To Populate An Associative Array with Varchar Index
declare
    cursor c_rep is
        select name, job
        from employees
        where job = 'SALES_REP';

    cursor c_mgr is
        select name, job
        from employees
        where job = 'SALES_MGR';

    type t_table is table of c_rep%rowtype index by employees.name%type;
    l_rows t_table;
begin
    l_rows := t_table(for r in c_rep, c_mgr index r.name => r);
end;

Populating Collections With Other Cursor Iteration Controls

All of the cursor iteration controls can be used as array constructors: cursor objects, sql, cursor variables and dynamic sql.

Using inline static sql with an implicit cursor is the same as using a cursor object. The iterand is implicitly typed by the sql statement.

prompt Using SQL Iteration Control To Populate An Associative Array with Varchar Index
declare
    type t_rec is record (name employees.name%type, job employees.job%type);
    type t_table is table of t_rec index by employees.name%type;
    l_rows t_table;
begin
    l_rows := t_table(for r in (
        select name, job
        from employees
        ) index r.name => r);
end;

As we saw earlier, using a cursor variable requires the iterand type to be defined explictly.

The cursor variable has to be opened before constructing the array. After the array is constructed the cursor variable remains open and should be closed.

prompt Using Cursor Variable Iteration Control To Populate An Associative Array with Varchar Index
declare
    cv sys_refcursor;
    type t_rec is record (name employees.name%type, job employees.job%type);
    type t_table is table of t_rec index by employees.name%type;
    l_rows t_table;
begin
     open cv for 
        select name, job
        from employees;
    
    l_rows := t_table(for r t_rec in cv index r.name => r);
    
    dbms_output.put_line('After constructing array, cursor variable is still open');
    print_boolean_attribute(cv%isopen, 'cv%isopen');
    close cv;
end;

After constructing array, cursor variable is still open
(cv%isopen is true)

Using dynamic sql also requires the iterand type to be explicitly defined.

prompt Using Dynamic SQL Iteration Control To Populate An Associative Array with Varchar Index
declare
    l_sql varchar2(100);
    type t_rec is record (name employees.name%type, job employees.job%type);
    type t_table is table of t_rec index by employees.name%type;
    l_rows t_table;
begin
    l_sql := 'select name, job from employees';
    
    l_rows := t_table(for r t_rec in (execute immediate l_sql) index r.name => r);

end;

From these examples it is clear that cursor iteration controls can be used to simplify the code used to populate collections in a significant way. In the beginnning of this article we saw that iterations using cursor iteration controls are also quite simplified and traversing cursors constructed with dynamic SQL has never been easier.

The cursor iteration controls also allow the same iteration approach to be used with any type of cursor: explicit cursors, implicit inline sql, cursor variables and dynamic sql. This can make for more consistent that is easier for non specialists to follow.

With all these advantages of simplified code, simplified cursor management and ease of collection population, you may be asking the following question… Is it time to let go of the methods of the past and start using these iteration controls for all cursor related activities in PL/SQL?

As with any new method to be added to your developer’s toolbox, this decision will need be made on a case by case basis…and cannot be made without a clear understanding of the performance aspects of the method. Simplified syntax is great, as long as there are no performance issues to be concerned with.

Cursor Iteration Controls: Performance Testing

Each test will open two cursors of 500,001 rows and accumulate the rows into a nested table. The tests will report the overall timing and detail the number of fetches performed by the SQL engine so that we can see how many context switches occur. All tests will run with the plsql_optimize_level set to the default of 2.

To understand the baseline, let’s start with checking performance of the traditional methods for iterating cursors in order to populate a collection of records.

The open, fetch, close method of cursor iteration is the oldest and slowest way to iterate a cursor. Each record fetch will require a context switch, including the fetch that results in setting the cursor%notfound attribute to true. This will take over a million fetches and over a million context switches between the PL/SQL engine and the SQL engine.

prompt Cursor Iteration Performance: open, fetch, close
declare
    l_method varchar2(50) := 'open, fetch, close';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    l_rec t_rec;
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
                    
    cv_1 sys_refcursor; 
    cv_2 sys_refcursor;

    l_sql varchar2(1000) := q'[    
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= ]' || l_records;
begin

    open cv_1 for l_sql;
    loop
        fetch cv_1 into l_rec;
        exit when cv_1%notfound;
            l_rows.extend();
            l_rows(l_rows.last) := l_rec;
    end loop;
    close cv_1;
            
    open cv_2 for l_sql;
    loop
        fetch cv_2 into l_rec;
        exit when cv_2%notfound;
            l_rows.extend();
            l_rows(l_rows.last) := l_rec;
    end loop;
    close cv_2;

    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);

end;

open, fetch, close
1000004 fetches for 1000002 rows
2.832 seconds for 1000002 rows

Using bulk collect with a limit of 100 rows should be the fastest method. Each fetch of 100 rows will require a context switch, including the fetch that results in the out bind collection count being zero.

A million rows will only require about ten thousand fetches and the execution time is significantly faster.

prompt Cursor Iteration Performance: bulk collect limit 100
declare
    l_method varchar2(50) := 'bulk collect limit 100';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows_partial t_rows := t_rows();
    l_rows t_rows := t_rows();
                    
    cv_1 sys_refcursor; 
    cv_2 sys_refcursor;

    l_sql varchar2(1000) := q'[    
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= ]' || l_records;
begin

    open cv_1 for l_sql;
    loop
        fetch cv_1 bulk collect into l_rows_partial limit 100;
        exit when l_rows_partial.count = 0;
        for i in indices of l_rows_partial loop
            l_rows.extend();
            l_rows(l_rows.last) := l_rows_partial(i);
        end loop;        
    end loop;
    close cv_1;
            
    open cv_2 for l_sql;
    loop
        fetch cv_2 bulk collect into l_rows_partial limit 100;
        exit when l_rows_partial.count = 0;
        for i in indices of l_rows_partial loop
            l_rows.extend();
            l_rows(l_rows.last) := l_rows_partial(i);
        end loop;        
    end loop;
    close cv_2;

    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

bulk collect limit 100
10002 fetches for 1000002 rows
0.973 seconds for 1000002 rows

Using cursor for loops should have approximately the same performance as the bulk collect test because of the bulk prefetch optimization of cursor for loops. A million rows should be about ten thousand fetches.

prompt Cursor Iteration Performance: cursor for loops
declare
    l_method varchar2(50) := 'cursor for loops';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
        
    cursor c_1 return t_rec is
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;  
        
    cursor c_2 return t_rec is
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;  
            
begin

    for r in c_1 loop
        l_rows.extend();
        l_rows(l_rows.last) := r;
    end loop;    

    for r in c_2 loop
        l_rows.extend();
        l_rows(l_rows.last) := r;
    end loop;
        
    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

cursor for loops
10002 fetches for 1000002 rows
0.982 seconds for 1000002 rows

Now we will test the cursor object iteration control. It seems a lot like a cursor for loop, will it show the same performance characteristics?

prompt Cursor Iteration Performance: cursor object iteration controls
declare
    l_method varchar2(50) := 'cursor object iteration controls';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
        
    cursor c_1 return t_rec is
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;  
        
    cursor c_2 return t_rec is
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;  

begin

    l_rows := t_rows(for r t_rec in c_1, c_2 sequence => r);

    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);

end;

cursor object iteration controls
10002 fetches for 1000002 rows
1.082 seconds for 1000002 rows

The cursor object iteration control shows the same number of fetches as the cursor for loop and the bulk collect limit test! The timing is also close to both of those tests, the bulk fetch optimization has definitely been impemented with the cursor object iteration control!

What about the sql iteration control? This is exactly like a cursor for loop with an inline static sql statement defining an implicit cursor. This control should perform the same as the cursor object iteration control.

prompt Cursor Iteration Performance: sql iteration controls
declare
    l_method varchar2(50) := 'sql iteration controls';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();        
begin

    l_rows := t_rows(for r t_rec in (
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records
        ), (
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records          
        ) sequence => r);

    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

sql iteration controls
10002 fetches for 1000002 rows
1.529 seconds for 1000002 rows

The fetch counts match bulk collect, cursor for loops and the cursor object iteration controls. The timing is consistently a bit slower.

The cursor variable iteration controls bring the elegance and simplicity of the cursor for loop to cursor variables. Does the bulk prefetch optimization show up here?

prompt Cursor Iteration Performance: cursor variable iteration controls
declare
    l_method varchar2(50) := 'cursor variable iteration controls';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
                    
    cv_1 sys_refcursor; 
    cv_2 sys_refcursor;
    l_sql varchar2(1000) := q'[    
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= ]' || l_records;
begin

    open cv_1 for l_sql;
    open cv_2 for l_sql;
    l_rows := t_rows(for r t_rec in cv_1, cv_2 sequence => r);
    close cv_1;
    close cv_2;
    
    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

cursor variable iteration controls
1000004 fetches for 1000002 rows
2.787 seconds for 1000002 rows

Not the outcome I was hoping for. Cursor variable iteration controls have the same performance characteristics as the open, fetch, close method. Elegant syntax with poor performance, these controls can definitely be described as ‘syntactic sugar’. Hopefully future releases will bring the bulk prefetch optimization to the cursor variable iteration controls.

The dynamic sql iteration controls resemble the cursor for loop with inline static sql and implicit cursors. Is this more than just a surface similarity?

prompt Cursor Iteration Performance: dynamic sql iteration controls
declare
    l_method varchar2(50) := 'dynamic sql iteration controls';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
                    
    l_sql varchar2(1000) := q'[    
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= ]' || l_records;
begin

    l_rows := t_rows(for r t_rec in (
        execute immediate l_sql), (
        execute immediate l_sql) sequence => r);
        
    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

dynamic sql iteration controls
1000004 fetches for 1000002 rows
2.795 seconds for 1000002 rows

Again, not quite the outcome I was looking for… The dynamic sql iteration controls are just like the cursor variable iteration controls when it comes to performance. Both are equivalent to the open, fetch, close method for fetch counts and timing.

What happens when we mix cursor iteration controls with different performance characteristics? Will the mix cause all of the cursor iteration controls to emulate the same functional implementation?

prompt Cursor Iteration Performance: mixed cursor iteration controls
declare
    l_method varchar2(50) := 'mixed cursor iteration controls';
    l_start timestamp := localtimestamp;
    l_records number := 500001;
    l_fetches_previous number := get_sql_engine_fetches('cursor_iteration_performance');
    l_fetches number;
    type t_rec is record(
        seed varchar2(50),
        id   number,
        info varchar2(20));
    type t_rows is table of t_rec;
    l_rows t_rows := t_rows();
        
    cursor c_1 return t_rec is
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= l_records;  
                    
    cv_2 sys_refcursor;
    
    l_sql varchar2(1000) := q'[    
        select 
            'cursor_iteration_performance' as seed, 
            level as id, 
            'item ' || level as info
        from dual 
        connect by level <= ]' || l_records;
begin

    open cv_2 for l_sql;
    l_rows := t_rows(for r t_rec in c_1, cv_2 sequence => r);
    close cv_2;

    l_fetches := get_sql_engine_fetches('cursor_iteration_performance') - l_fetches_previous;
    print_timing_and_fetches(l_start, l_fetches, l_rows.count, l_method);
end;

mixed cursor iteration controls
505003 fetches for 1000002 rows
1.946 seconds for 1000002 rows

These results are interesting…mixing different types of cursor iteration controls shows that the cursor object iteration control is still benefiting from the bulk prefetch optimization when mixed with a cursor variable iteration control. The cursor variable control in the mix still used one fetch per row.

The performance_compare_optimizer_versions.sql and performance_compare_optimizer_level.sql scripts can be used to run the full set of performance tests against version 21 and version 23 for cursor iteration controls, and earlier versions for other methods. There are various differences in the environments which I ran the scripts, so it is hard to draw solid conclusions. The overall trend is definitely toward improved performance for all methods of cursor iteration.

These performance tests show that cursor object and sql iteration controls are equivalent to cursor for loops and bulk collect with a limit clause of 100. Since a limit of 100 offers optimal performance for most situations, these iteration controls can be leveraged to streamline the code in these cases.

The cursor variable and dynamic sql iteration controls showed performance equivalent to the slowest method for iterating a cursor: the open, fetch, close method. If performance is an issue, the cursor variable and dynamic sql iteration controls would not be a good option to choose.

All of the functionality offered by cursor iteration controls can also be implemented with the other cursor methods in cases where performance is critical with the minor cost of more code complexity. Because 21c was only an innovation release, there is more time for these methods to mature. As the cursor iteration controls gain more widespead use we will likely see more performance optimizations being applied to them.

The cursor iteration controls can definitely be used in many cases to simplify the code used for iterating cursor results and populating arrays. The ability to use this simplified syntax with cursor variables and dynamic sql as well as explicit cursors is a welcome addition to the Oracle developer’s toolbox.

–Anthony Harper

Puzzle

The following block of code shows two methods for populating collections from a cursor. The first method uses bulk collect with the limit clause to process the rows in batches. The second method uses a cursor iteration control as an array constructor to process all the rows in one batch.

Can the second method be modified to processes the rows in batches with the cursor object iteration control?

prompt Challenge:  Implementing limit with cursor iteration control
declare
    l_limit number := 25;
    cursor c is
        select level as id
        from dual 
        connect by level <= 111;

    type t_rows is table of c%rowtype;

    l_rows t_rows := t_rows();
    i number := 0;
begin

    dbms_output.put_line('Method 1:  process cursor with bulk collect and limit');
    open c;
	loop
	    fetch c bulk collect into l_rows limit l_limit;
	    exit when l_rows.count = 0;
            i := i + l_rows.count;
            dbms_output.put_line('processing batch of ' || l_rows.count || ' rows');
	end loop;
    close c;
    dbms_output.put_line('processed total of ' || i || ' rows');
	
    l_rows.delete;
    i := 0;

    dbms_output.put_line('Method 2:  process cursor rows with iteration control');
    l_rows := t_rows(for r in values of c sequence => r);
    dbms_output.put_line('processing batch of ' || l_rows.count || ' rows');
    i := i + l_rows.count;
    dbms_output.put_line('processed total of ' || i || ' rows');
    
end;

Method 1:  process cursor with bulk collect and limit
processing batch of 25 rows
processing batch of 25 rows
processing batch of 25 rows
processing batch of 25 rows
processing batch of 11 rows
processed total of 111 rows
Method 2:  process cursor rows with iteration control
processing batch of 111 rows
processed total of 111 rows