TechRepublic’s Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

There are several ways of handling SELECT statement
exceptions. Programmers tend to get into a habit of using the same technique,
so it’s a good idea to pick a technique that is generally efficient and
readable.

Unfortunately, in the days of Oracle versions 7 and 8, the
recommended method was to use explicit cursors. Now, many experts advise
switching to implicit cursors using different methods. I decided to take the
four most commonly seen techniques for handling the exception no data found and compare their
performance.

The first method is implicit cursors with explicit exception
handling. Developers used to say that exception handling was much slower than
just checking a cursor’s NOTFOUND state. Exception handling is now much more
efficient.

create or replace function oneexc return char

is
    x char;

begin
    begin
        select 'X' into x from dual where 1 = 2;
        return x;
    exception
        when no_data_found then
            return null;
    end;

end oneexc;

/

show errors;

The second method is the “old” way: To explicitly
declare a cursor, open, fetch, and close the cursor. I rely on the fact that if
the fetch fails, the result will still be NULL to avoid checking completely.

create or replace function onecur return char

is
    x char;
    cursor l_cursor is select 'X' x from dual where 1 = 2;
begin
    open l_cursor;
    fetch l_cursor into x;
    close l_cursor;
    return x;

end onecur;

/

show errors;

The third method is to use an implicit cursor wrapped up in
a cursor FOR loop. Cursor state checking, opening, closing, and fetching are
all implicit and optimized.

create or replace function onefor return char

is

begin
    for row in (select 'X' x from dual
where 1 = 2) loop
        return row.x;
    end loop;
    return null;

end onefor;

/

show errors;

The fourth method also uses a cursor FOR loop but, just to
be complete, uses an implicit cursor.

create or replace function onefor2 return char

is
    cursor l_cursor is select 'X' x from dual where 1 = 2;

begin
    for row in l_cursor loop
        return row.x;
    end loop;
    return null;

end onefor2;

/

show errors;

The script in
Listing A can be run to time and profile executing each function one million times.
In the final results, “onecur”, the
old-fashioned, explicit cursor was still faster in this example, but only
marginally. It didn’t have the overhead of throwing and catching an exception,
and it skipped checking the cursor state, and branching and looping. The runner
up was “onefor”. The implicit cursor in a
FOR loop is slightly faster than the explicit cursor, probably due to some
internal optimization. The slowest was the example that raises and catches
exceptions. The act of raising and then catching takes multiple internal steps,
which is more than the single check of the FOR loops or the absence of a check
in the “onecur” example.

However, the difference between the functions was minimal.
In each case, a select from DUAL took 96 percent of the processing time. The
actual code logic was much less. Also, the cursor example takes advantage of a
technical detail in explicit cursors. If I change the code in onecur to check
for whether the cursor found anything and return null, it would be slower than
the equivalent implicit cursor. Also, 99 percent of the time, you need to use
the same open/fetch/close logic that the implicit cursor would use anyway, so
implicit cursors in for loops, but not exceptions are currently the more
efficient way to handle exceptions.