declare
    l_task varchar2(100);
    cursor c is
    select level as n
    from dual
    connect by level <= 10;
begin
    l_task := 'print all the rows of explicit cursor c to output';
    dbms_output.put_line(l_task);
end;

1. Getting Started
2. Open, Fetch, Close
3. Parameterized Cursors
4. Fetching Single Records
5. Fetching Multiple Records
6. Ref Cursors
7. Dynamic SQL
8. The Cursor For Loop
9. Cursor For Loop Limitations
10. Performance Comparison
11. Puzzle

There are some techniques that are really basic to what we do everyday as PL/SQL developers. There is a tendency to automatically do these basic things in the same way without considering the alternatives that the particular situation might merit. In this post and the next we will explore one of these basic techniques in order to develop a deeper understanding.

One of the most common tasks is to get a set of records and look through them in order to do something with each row. This is accomplished by opening a cursor and iterating through its rows. Even though the code for doing this is executing primarily in PL/SQL, the actual process involves an interactive dialog between the SQL engine and the PL/SQL engine.

A cursor is a pointer to a private area of the SQL engine that stores instructions about how to process a select statement or a DML statement (Insert, Update, Merge and Delete). When we select a value into a variable or run a DML statement, the PL/SQL engine informs the SQL engine of the intentions for the cursor using an SQL statement, and obtains a pointer to an implicit cursor that will be managed by the SQL engine. This implicit cursor is opened, accessed and closed by the SQL engine and then the results are passed back to PL/SQL.

We can also create explicit cursors and work with their records individually or as a group. We first instruct the SQL engine to open the cursor, and then ask for each row to be returned to PL/SQL in order to access its attributes and do something. After we are done with the cursor we tell the SQL engine to close the cursor. There are many ways to do this and it is important to know the details of each method.

Examining a variety of approaches to this in detail will help when deciding which approach is warranted in a particular situation. Let’s start by looking at a way to iterate through cursors which has remained unchanged since the days of Oracle 8i and is still used by many two decades later: the open, fetch, close method.

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-1-fetch-vs-cursor-for-loop. 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 and print_timing.

At times I may use syntax specific to a more recent version of Oracle for clarity of presentation. In these cases, I will include the more recent syntax in the article and use conditional compilation in the actual scripts to make the examples backward compatible when possible. 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.

Open, Fetch, Close

Let’s look at using an explicit cursor to access all of the the records in the employees table and print the name and job for each employee. The classic approach to this is called the Open, Fetch, Close method. Because this requires manual management of the cursor there are several specific elements for a correct implementation. Not all of these elements are required, but some omissions can lead to errors. It is a good practice to always include the correct elements.

Prompt Fetching into separate variables
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    l_name employees.name%type;
    l_job employees.job%type;
begin
    open c_emps;
    loop
        fetch c_emps into l_name, l_job;
        exit when c_emps%notfound;
        print_employee(l_name, l_job);
    end loop;
end;

We can use a loop to iterate through the records and fetch the columns of the cursor into individual variables. To do this requires declaring all of the necessary variables and then selecting individual columns from the cursor as each row is fetched from the SQL engine. If we add columns to the select statement, we will have to declare more variables to fetch them into and modify the fetch statement. If we are using a lot of columns in the select statement, this code becomes cumbersome.

Prompt Method: Open, Fetch, Close
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    type t_emp_rec is record(
        name varchar2(50), job varchar2(50));
    r_emp t_emp_rec;  
begin
    open c_emps;
    loop
        fetch c_emps into r_emp;
        exit when c_emps%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
end;

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

We can simplify the process of selecting multiple columns by creating a record type variable to use as the iterator which is of the same type as the columns which have been defined in the cursor definition. Then we can just fetch into this record type variable. However, any changes in the select clause of the cursor will have to be reflected in the record type.

Prompt Using a rowtype anchor for iteration variable
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    r_emp c_emps%rowtype;  
begin
    open c_emps;
    loop
        fetch c_emps into r_emp;
        exit when c_emps%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
end;

Instead of explicitly declaring a record type for the iterator variable, we can use a %rowtype anchor that ties it to the cursor definition. This ensures that the variable reflects the select clause of the defining query. We can add new columns to the query and the record variable will have these attributes without additional code changes.

prompt Fetch without open
declare
    cursor c_emp is
        select e.name, e.job
        from employees e;
    r_emp c_emp%rowtype;
begin
    fetch c_emp into r_emp;
exception
    when others then
        dbms_output.put(sqlerrm);
        print_boolean_attribute(c_emp%isopen, 'c_emp%isopen');
end;

ORA-01001: invalid cursor(c_emp%isopen is false)

The cursor has to be opened prior to fetching records. If we try to fetch a record without opening the cursor we encounter an exception. Recall that the cursor is just a pointer to instructions for how to construct the resultset. We can’t fetch rows from a set of instructions, the instructions have to be executed to form a resultset. Opening the cursor executes the cursor definition and the resultset is then in the SQL engine available for accessing its rows.

prompt cursor %isopen attribute shows state of cursor
declare
    cursor c_emp is
        select e.name, e.job
        from employees e;
    r_emp c_emp%rowtype;
begin
    dbms_output.put('before opening the cursor');
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    open c_emp;
    dbms_output.put('after opening the cursor');
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    close c_emp;
    dbms_output.put('after closing the cursor');
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    
    raise program_error;
exception
    when others then
        dbms_output.put_line(sqlerrm);
        print_boolean_attribute(c_emp%isopen, 'c_emp%isopen');
        if c_emp%isopen then
            close c_emp;
        end if;
end;

before opening the cursor(c_emp%isopen is false)
after opening the cursor(c_emp%isopen is true)
after closing the cursor(c_emp%isopen is false)
ORA-06501: PL/SQL: program error
(c_emp%isopen is false)

For an explicit cursor, there is an attribute we can access to see if the cursor is open or closed. The cursor attribute %isopen will be set to true when the cursor is opened. This attribute will be false before the cursor is opened, and false again when the cursor is closed. Opening a cursor which is already open or closing a cursor which is not open will both cause an exception which this attribute gives us important information to prevent.

We also use the %isopen attribute to avoid this problem in exception handlers when it is not clear what the current state of the cursor is.

A cursor declared within a block will be closed by Oracle when the block is exited and the cursor variable is out of scope. Closing the cursor is not required in this case. If the cursor is moved to a package to be reused by multiple procedure calls, each use of the cursor will have to close it properly before another procedure can open it.

By closing cursors even if they are locally declared, we will be in the habit of creating complete code and this switch to a reusable cursor will already have the close call in the code that iterates the cursor.

Parameterized Cursors

prompt fetch parameterized cursor, must close before reopening
declare
    cursor c_emp(p_job in varchar2) is
        select e.name, e.job
        from employees e
        where e.job = p_job;
    r_emp c_emp%rowtype;
begin
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    open c_emp('SALES_MGR');
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);
    
    dbms_output.put_line('Reopen cursor with a different parameter');
    print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
    open c_emp('SALES_EXEC');    
exception
    when others then
        dbms_output.put_line(sqlerrm);
        print_boolean_attribute(c_emp%isopen,'c_emp%isopen');
end;

(c_emp%isopen is false)
(c_emp%isopen is true)
Ann SALES_MGR
Reopen cursor with a different parameter
(c_emp%isopen is true)
ORA-06511: PL/SQL: cursor already open
(c_emp%isopen is true)

A typical use case for reusing cursors is the parameterized cursor. If we define formal parameters for the cursor, it can be opened again with a different actual parameter value. Using parameters avoids duplicating cursors where the only difference would be a value in the where clause.

Trying to reopen a cursor which is already open will generate an exception. In order to open the cursor again with different parameters, it must first be closed.

prompt fetch parameterized cursor, close before reopening
declare
    cursor c_emp(p_job in varchar2) is
        select e.name, e.job
        from employees e
        where e.job = p_job;
    r_emp c_emp%rowtype;
begin

    dbms_output.put('print a sales manager: ');
    open c_emp('SALES_MGR');
    fetch c_emp into r_emp;
    print_employee(r_emp.name);
    close c_emp;
    
    dbms_output.put('print a sales executive: ');
    open c_emp('SALES_EXEC');    
    fetch c_emp into r_emp;
    print_employee(r_emp.name);
    close c_emp;

end;

print a sales manager: Ann
print a sales executive: Gina

Here the cursor is properly closed before it is reopened, and the block executes without exceptions. The case of parameterized cursors that are used with several different parameter values makes it clear that the cursor should be closed every time before opening it again.

For cursors that are declared globally and used by many procedures it is very important to check the %isopen attribute before opening, and to close the cursor after using it.

Fetching Single Records

Prompt Use implicit cursor to fetch a single record
declare
    cursor c_rep_count is
        select count(*) as emp_count
        from employees e
        where e.job = 'SALES_REP';
    l_sales_reps number;
begin

    dbms_output.put_line('using explicit cursor for one row');
    open c_rep_count;
    fetch c_rep_count into l_sales_reps;
    dbms_output.put_line(l_sales_reps || ' sales representatives found');
    close c_rep_count;

    dbms_output.put_line('using implicit cursor for one row');
    select count(*) into l_sales_reps
    from employees e
    where e.job = 'SALES_REP';
    dbms_output.put_line(l_sales_reps || ' sales representatives found');

end;

An explicit cursor is not really needed if we are only fetching a single record. This can be done more effectively by selecting into a variable, which creates an implicit cursor in the background that we don’t have to manage.

There is more to this than simplified code, this is about minimizing trips back and forth between SQL and PL/SQL. These back and forth dialogs are called context switches, and every switch has overhead.

With an expicit cursor, opening the cursor is a call from the PL/SQL engine to the SQL engine that returns a pointer to the open cursor. Fetching the row is another call which returns the row information. Closing the cursor is another call. The explicit cursor requires a minimum of three context switches even if used to fetch a single row.

With an implicit cursor, there is only one call from PL/SQL passing the cursor instructions (the sql query). The SQL engine opens a cursor, fetches the result, closes the cursor and then returns the result to PL/SQL. This is the reason that you should always use implicit cursors to select single rows. We will look into this concept more in part two when we discuss bulk binding.

Fetching Multiple Records

prompt repeating fetch calls for multiple records
declare
    cursor c_emp is
        select e.name, e.job
        from employees e
        order by e.job, e.name
        fetch first 2 rows only;
    r_emp c_emp%rowtype;
begin
    open c_emp;

    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);

    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);
end;

Gina SALES_EXEC
Ann SALES_MGR

When we want to view the rows from a cursor which selects multiple rows, each row has to be fetched to read its values. This block defines a cursor that can only have two rows, so two fetch calls will get all of the results.

If we always knew how many records there would be for a given cursor we could write code like this….but what if we fetched too many times? Or what if we didn’t fetch enough times to process all the available rows? The %notfound cursor attribute can help with this.

prompt %notfound attribute
declare
    cursor c_emp is
        select e.name, e.job
        from employees e
        order by e.job, e.name
        fetch first 2 rows only;
    r_emp c_emp%rowtype;
begin
    open c_emp;

    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);
    print_boolean_attribute(c_emp%notfound, 'c_emp%notfound');
    
    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);
    print_boolean_attribute(c_emp%notfound, 'c_emp%notfound');
    
    dbms_output.put_line('fetch after all records have been fetched');
    fetch c_emp into r_emp;
    print_employee(r_emp.name, r_emp.job);
    print_boolean_attribute(c_emp%notfound, 'c_emp%notfound');
    
end;

Gina SALES_EXEC
(c_emp%notfound is false)
Ann SALES_MGR
(c_emp%notfound is false)
fetch after all records have been fetched
Ann SALES_MGR
(c_emp%notfound is true)

All of the rows in the cursor have already been fetched when the third fetch happens. An unsuccessful fetch doesn’t raise an exception, but it does set the %notfound attribute to true.

From the output we can also see that the record variable didn’t change after the last record was fetched. Subsequent fetches will leave the same values in the record variable.

We would not want to process the last record fetched twice, and we don’t want to have to customize our code with the exact number of fetches required for each cursor. The %notfound attribute can be used to determine if a fetch was successful and we can use it to make the code more robust and less repetitive.

prompt fetch using loop
declare
    cursor c_emp is
        select e.name, e.job
        from employees e
        order by e.job, e.name
        fetch first 3 rows only;
    r_emp c_emp%rowtype;
begin
    open c_emp;
    for i in 1..4 loop
        fetch c_emp into r_emp;
        print_employee(r_emp.name);
    end loop;
end;

Gina
Ann
Tobias
Tobias

A loop is appropriate to iterate through the cursor records without coding the precise number of fetch statements. Since an unsuccessful fetch will not raise an exception, the loop will never terminate unless we use a good exit condition.

If we know how many rows we are expecting, we can hard code the loop to that number of iterations. This can easily lead to mistakes if we change the cursor but forget the update the number of loop iterations. The example above has a cursor with only three rows, but the loop executes 4 times. The fourth execution of the loop processes the record for Tobias again because there were no more records to fetch.

We could get a count of records before opening the cursor and then loop that many times, but this is a lot of effort and involves executing an extra sql statement quite similar to our cursor definition. Every call to the SQL engine requires an implicit or explicit cursor and entails overhead.

Ideally we will fetch records, process them and exit the loop when all rows have been processed. The cursor %notfound attribute will accomplish this when used correctly.

prompt fetch using loop, wrong exit placement
declare
    cursor c_emp is
        select e.name, e.job
        from employees e
        order by e.job, e.name
        fetch first 3 rows only;
    r_emp c_emp%rowtype;
begin
    open c_emp;
    loop
        fetch c_emp into r_emp;
        print_employee(r_emp.name);
        exit when c_emp%notfound;
    end loop;
end;

Gina
Ann
Tobias
Tobias

A simple loop with the correct exit condition will allow us to process all of the records in the cursor even if we don’t know how many records there will be. The %notfound attribute is the correct exit condition, but it is important to put it in the correct place. As we see above, placing the exit condition after the processing code (our output statement), will process the last record fetched twice.

It may appear that the final record is being fetched twice, but this is not really what’s happening. The first loop iteration calls fetch and gets the record for Gina. The next iteration calls fetch and gets the record for Ann. The third iteration gets the record for Tobias. In the fourth iteration, the fetch call failed to get a record and the %notfound attribute was set to true. However, the values from the previous fetch are still present in the record variable.

Because each fetch does not clear the record variable before the fetch, it appears that the last record was fetched twice. You might encounter this problem and think that setting the record variable to null before the fetch is a good fix. This is really a hack that covers up the fact that the loop should be exited immediately when an unsuccessful fetch sets the cursor %notfound attribute to true.

prompt fetch using loop, correct exit placement
declare
    cursor c_emp is
        select e.name, e.job
        from employees e
        order by e.job, e.name
        fetch first 3 rows only;
    r_emp c_emp%rowtype;
begin
    open c_emp;
    loop
        fetch c_emp into r_emp;
        exit when c_emp%notfound;
        print_employee(r_emp.name);
    end loop;
    close c_emp;
end;

Gina
Ann
Tobias

This is worth reiterating: the placing of the exit condition immediately after the fetch is important to remember. We can only tell that the fetch failed by checking the %notfound attribute of the cursor. Always check the %notfound cursor attribute after every fetch call to avoid processing the same record twice.

Prompt Method: Open, Fetch, Close complete with all details
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    r_emp c_emps%rowtype;
begin
    open c_emps;
    loop
        fetch c_emps into r_emp;
        exit when c_emps%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
    close c_emps;
exception
    when others then
        if c_emps%isopen then
            close c_emps;
        end if;
        dbms_output.put_line(sqlerrm);
        raise;
end;

This block shows the earlier incomplete example with all of the correct elements in the right place. Because this approach requires the developer to manage the cursor explicitly, there are a lot of elements that need to be present. However, this method is extremely versatile and it is an important tool for working with data in PL/SQL.

Ref Cursors

The open, close, fetch method is also used to iterate ref cursors that use dynamic or static sql statements. Ref cursors are very useful and can even be opened and passed to other procedures as actual parameters. These cursors can be strongly typed and bound to a record type for their output or weakly typed for use with any sql statement. The iteration process is the same for both and uses the open, close, fetch approach to iterate through the resultset.

Strongly Typed Ref Cursors

prompt fetch from strongly typed ref cursor
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    type t_emp_cur is ref cursor return t_emp_rec;
    c_emp t_emp_cur;    
    r_emp t_emp_rec;
begin
    open c_emp for 
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    loop
        fetch c_emp into r_emp;
        exit when c_emp%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
    close c_emp;
end;

For a strongly typed ref cursor, the record type is declared first. Then the cursor type is declared as a ref cursor with a return type bound to the record type. Finally, a cursor variable is declared with this cursor type. This ref cursor variable can be opened for multiple sql statements as long as the select columns match the record type. The defining query is not part of the declaration of the cursor.

In the executable section, we open the cursor variable for a select statement. This SQL statement can only select columns that are the same type (or implicitly convertible to the same type) as the columns defined in the record type variable. After closing, the ref cursor variable can be reopened for a different SQL statement as long as the select clause returns the correct rowtype.

Weakly Typed Ref Cursors

prompt fetch from weakly typed ref cursor
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    c_emp sys_refcursor;    
    r_emp t_emp_rec;
begin
    open c_emp for 
        select e.name, e.job
        from employees e
        order by e.job, e.name;
    loop
        fetch c_emp into r_emp;
        exit when c_emp%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
    close c_emp;
end;

If the ref cursor is not bound to a record type, it is a weakly typed cursor. The cursor variable is declared as a ref cursor without a specific return type. In earlier versions of Oracle, developer’s created a types package with a reusable ref cursor type to use in the codebase. The introduction of the sys_refcursor type eliminated the need to declare a type that is a ref cursor.

Because this cursor does not have a declared return type it can be opened for any SQL statement. The code will compile with a mismatch between the cursor definition and the variable being fetched into, so these errors are harder to spot. There will be a runtime exception if the selected columns do not match the record type of the variable that is being fetched into.

prompt fetch from weakly typed ref cursor, inconsistent datatypes
declare
    type t_emp_rec is record (
        name varchar2(50),
        id number,
        hire_date date);
    c_emp sys_refcursor;    
    r_emp t_emp_rec;
begin
    open c_emp for 
        select e.name, sysdate, e.id
        from employees e
        order by e.job, e.name;
    fetch c_emp into r_emp;

exception
    when others then
    dbms_output.put_line(sqlerrm);    
end;

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

If the SQL statement doesn’t match the record variable to be fetched into, having columns with datatypes that cannot be implicitly converted, an exception will be raised. In this case we selected a date in the column that is expecting a numeric value.

prompt fetch from weakly typed ref cursor, missing select columns
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    c_emp sys_refcursor;    
    r_emp t_emp_rec;
begin
    open c_emp for 
        select e.name
        from employees e
        order by e.job, e.name;
    fetch c_emp into r_emp;
exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

ORA-06504: PL/SQL: Return types of Result Set variables or query do not match

If the selected columns match in type but there are not enough columns or too many columns, an exception will be raised.

If the number and type of the selected columns do not match the record variable, the open, fetch, close method will not work to iterate the ref cursor’s results.

Dynamic SQL

There are many cases when static SQL cannot be used to define a cursor at compile time. The SQL query may depend on tables that will not be present until the code is executed, or the query may be constructed on the fly, etc. These cases must use ref cursor variables with dynamic SQL and the open, fetch, close method is used to work with their resultsets.

prompt strongly typed ref cursor with dynamic sql will not compile
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
      type  t_emp_cur is ref cursor return t_emp_rec;
    c_emp t_emp_cur;
    r_emp t_emp_rec;
    l_sql varchar2(100);
begin
    l_sql :=
        'select e.name, e.job
        from employees e
        order by e.job, e.name';

    open c_emp for l_sql;
end;

PLS-00455: cursor 'C_EMP' cannot be used in dynamic SQL OPEN statement

Dynamic SQL cannot be used to open a strongly typed ref cursor. The compiler knows that the cursor must conform to the record type, but the SQL statement won’t be executed until runtime. Because of this, the compiler can’t know if the cursor will be valid and will not let the block compile.

prompt use weakly typed ref cursor with dynamic sql
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    c_emp sys_refcursor;
    r_emp t_emp_rec;
    l_sql varchar2(100);
begin
    l_sql := 
        'select e.name, e.job
        from employees e
        order by e.job, e.name';
    open c_emp for l_sql;
    loop
        fetch c_emp into r_emp;
        exit when c_emp%notfound;
        print_employee(r_emp.name, r_emp.job);
    end loop;
    close c_emp;
end;

Dynamic SQL can be used with weakly typed ref cursors. This allows for great flexibility in programming code, but errors cannot be detected at compile time. Static SQL is evaluated at compile time, and errors are seen immediately. Only use dynamic SQL when it is absolutely necessary.

prompt ref cursor with dynamic sql, inconsistent datatypes
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    c_emp sys_refcursor;
    r_emp t_emp_rec;
    l_sql varchar2(200);
begin
    l_sql := 
        'select 
            e.name, e.name as ename1
            , e.job, e.job as ejob2
        from employees e
        order by e.job, e.name';
    
    open c_emp for l_sql;

    fetch c_emp into r_emp;

exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

ORA-00932: inconsistent datatypes: expected - got -

Mismatches in the dynamic sql and the record type being fetched into will show up as runtime exceptions.

prompt ref cursor with dynamic sql, column not in select list
declare
    type t_emp_rec is record (
        name employees.name%type, 
        job employees.job%type);
    c_emp sys_refcursor;
    r_emp t_emp_rec;
    l_sql varchar2(100);
begin
    l_sql :=
        'select e.name
        from employees e
        order by e.job, e.name';

    open c_emp for l_sql;

    fetch c_emp into r_emp;

exception
    when others then
        dbms_output.put_line(sqlerrm);
end;

ORA-01007: variable not in select list

The record type variable can’t have more columns than the dynamic sql statement. Because dynamic SQL statements are used, there is no way to anchor a record variable to a weakly typed ref cursor using %rowtype because the rowtype is not defined until runtime.

DBMS_SQL

The dbms_sql package can also be used to open a ref cursor for a dyamic SQL statement that is constructed at runtime and fetch the rows into variables that are not determined until runtime. These situations are more rare and need to be carefully evaluated to see if there is an approach that can work without involving this level of complexity.

Note: Not only is it good to know how to do this in case the need arises, the process used with the dbms_sql is a good illustration of how cursors are created and accessed. For completeness, this section should be expanded with a simple example in a future rewrite.

The Cursor For Loop

Prompt Method: Cursor For Loop
declare
    cursor c_emps is
        select e.name, e.job
        from employees e
        order by e.job, e.name;
begin
    for r in c_emps loop
        print_employee(r.name, r.job);
    end loop;
end;

The open, fetch, close method takes a bit of code to iterate through an explicit cursor. With the introduction of the cursor for loop, most of these actions are handled behind the scenes. The cursor is opened automatically. The iterator variable is implicitly declared with no need for a record type or iterator variable. The records are automatically fetched with each loop iteration. The loop exits automatically when there are no more records available. The cursor is automatically closed when the loop is exited or when an error causes the loop to terminate early.

It may seem obvious that the cursor needs to be properly opened before accessing the records. But it is common to miss the obvious, and tracking down the reason for this error depends on proper exception handling and messaging. The cursor for loop certainly puts an end to this problem.

Closing the cursor is a similar situation. Suppose that you want to reopen the cursor with a different parameter value to get a different set of records. The cursor must be closed before it can be opened again. Since the cursor for loop automatically closes the cursor when the loop exits, there is no need to check the state of the cursor before reusing it. And if an exception occurs inside the loop, the task of checking in the exception handler for an open cursor that must be closed is eliminated by the cursor for loop.

The two methods are functionally equivalent for all of the cases they have in common, and they can be used interchangeably to get the same results. This gives the appearance that there is only a cosmetic difference between the two methods that is just a matter of personal preference.

You may be thinking that this is just ‘syntax sugar’ designed to make the code to accomplish this basic task more compact. If you are used to using the fetch method, the cursor for loop seems to be lacking the needed clothing to operate in the chilly climate of functioning code. Or you may have been told that the cursor for loop is always better and should always be used in all cases.

Better how? That should be the question in our minds….we should not adopt a single approach for all situations unless we understand why it is always better. Let’s look at a few more aspects of the cursor for loop while we think about this question.

No Cursor Variable Required

Prompt Cursor For Loop without declared cursor variable
begin
    for r_emp in (
        select e.name, e.job
        from employees e
        order by e.job, e.name    
    ) loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
end;

With the cursor for loop, even the cursor variable does not need to be declared. When the SQL statement defining the cursor is large and complex, this approach may seem convenient… but it actually makes the code more difficult to read. Personally, I prefer to declare the cursor variables explicitly for readability and maintainability. As we will see in the next example, this approach makes the executable section of the code very easy to follow, especially when cursors might be nested.

Parameterized Cursor For Loops

Prompt Method: Parameterized Cursor For Loop
declare
    cursor c_jobs is
        select e.job, count(*) as emp_count
        from employees e
        group by e.job
        order by e.job;
        
    cursor c_emps(p_job in varchar2) is
        select e.name
        from employees e
        where e.job = p_job
        order by e.name;
begin
    <<jobs>>
    for r_job in c_jobs loop
        dbms_output.put_line(r_job.emp_count || ' employees with job = ' || r_job.job);
        
        <<employees>>
        for r_emp in c_emps(r_job.job) loop
            print_employee(r_emp.name);
        end loop employees;
        
    end loop jobs;
end;

1 employees with job = SALES_EXEC
Gina
2 employees with job = SALES_MGR
Ann
Tobias
5 employees with job = SALES_REP
Alex
Jane
...

The cursor for loop is amazingly simple, but can it be parameterized? Of course, and the code retains its concise nature. The implicit iterator variable means that only the cursor needs to be declared…or does it?

Prompt Method: Parameterized Cursor For Loop with no declaration
begin
    <<jobs>>
    for r_job in (
        select e.job, count(*) as emp_count
        from employees e
        group by e.job
        order by e.job    
    ) loop
        dbms_output.put_line(r_job.emp_count || ' employees with job = ' || r_job.job);
        
        <<employees>>
        for r_emp in (
            select e.name
            from employees e
            where e.job = r_job.job
            order by e.name        
        ) loop
            print_employee(r_emp.name);
        end loop employees;
        
    end loop jobs;
end;

A cursor for loop can use any variable in scope as a predicate! With large SQL statements and nested cursors it can be difficult to see what predicates from enclosing structures are being applied. The code here is a good contrast to the preceding example of the parameterized nested cursors using explicit formal parameters for filtering the cursors. In the prior example, it is very clear what parameters are being applied to the nested cursors….and the executable part of the code is easier to follow without the clutter of the SQL statements.

Cursor For Loop Limitations

The cursor for loop cannot be used to iterate a ref cursor, either strongly typed or weakly typed. In Oracle version 21c, enhancements to iteration controls introduced a hybrid that seems to be halfway between the cursor for loop and the open, fetch, close method: the cursor iteration control. After we look at bulk binding in part two, we will examine this new approach.

Prompt Method: Cursor For Loop Fails With Dynamic SQL
declare
    cursor c_emps is
        'select e.name, e.job
        from employees e';
begin
    for r_emp in c_emps loop
        print_employee(r_emp.name, r_emp.job);
    end loop;
end;

PLS-00103: Encountered the symbol "select e.name, e.job...

This code just won’t compile. The cursor for loop doesn’t work with dynamic SQL at all. The syntax to define a cursor variable with a string makes no sense. There is also no way for the compiler to know the datatype of the record iterator variable.

Performance Comparison

alter session set plsql_optimize_level = 2;
prompt performance challenge:  open, fetch, close vs. cursor for loop
declare
    c_records constant number := 500000;
    l_records number;
    l_start timestamp;

    cursor c_fetch is
        select 'item ' || level as info
        from dual 
        connect by level <= c_records;
    r_fetch c_fetch%rowtype;  

    cursor c_for is
        select 'item ' || level as info
        from dual 
        connect by level <= c_records;

begin 

    l_start := localtimestamp;
    l_records := 0;
    
    open c_fetch;
    loop
        fetch c_fetch into r_fetch;
        exit when c_fetch%notfound;
        l_records := l_records + 1;
    end loop;
    print_timing(l_start, l_records, 'Open, Fetch, Close');
    
    l_start := localtimestamp;
    l_records := 0;
    
    for r_for in c_for loop
        l_records := l_records + 1;
    end loop;
    print_timing(l_start, l_records, 'Cursor For Loop');

end;

1.185915 seconds for 500000 rows: Open, Fetch, Close
0.244358 seconds for 500000 rows: Cursor For Loop

Two identical cursors, two different approaches….and the cursor for loop is nearly five times as fast as the open, fetch close method in this a quick test with 500,000 rows….. All syntax differences aside, at the end of the day performance is what we are looking for.

Given all of the differences we have seen, you may be thinking that the fetch method is no longer useful unless a ref cursor with dynamic sql is required. Actually, a modification of the basic open, fetch, close approach is still in common use today because of a concept that was quite radical when it was introduced: bulk binding. Bulk binds are a completely different way to traverse the gap between the SQL engine and the PL/SQL engine, and they really improve performance.

The introduction of bulk binds allowed Oracle to implement an optimization behind the scenes in the cursor for loop which increased performance significantly in comparison to the open, fetch, close method. To understand this optimization better, we will start with a closer look at bulk binding in the next article. After that we are ready look into the curious enhancements that I mentioned earlier, the 21c cursor iteration controls.

–Anthony Harper

Puzzle

The following block of code is identical to the performance comparison above except for one character. This one character difference makes the two methods run with approximately the same performance…. This difference is really the key to the next post, can you explain what it is?

alter session set plsql_optimize_level = 0;
prompt performance challenge:  open, fetch, close vs. cursor for loop
declare
    c_records constant number := 500000;
    l_records number;
    l_start timestamp;

    cursor c_fetch is
        select 'item ' || level as info
        from dual 
        connect by level <= c_records;
    r_fetch c_fetch%rowtype;  

    cursor c_for is
        select 'item ' || level as info
        from dual 
        connect by level <= c_records;

begin 

    l_start := localtimestamp;
    l_records := 0;
    
    open c_fetch;
    loop
        fetch c_fetch into r_fetch;
        exit when c_fetch%notfound;
        l_records := l_records + 1;
    end loop;
    print_timing(l_start, l_records, 'Open, Fetch, Close');
    
    l_start := localtimestamp;
    l_records := 0;
    
    for r_for in c_for loop
        l_records := l_records + 1;
    end loop;
    print_timing(l_start, l_records, 'Cursor For Loop');

end;

1.185532 seconds for 500000 rows: Open, Fetch, Close
1.208598 seconds for 500000 rows: Cursor For Loop