Handle select exceptions efficiently

Your procedures have to handle situations where no data is returned, but which way uses least resources?

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.

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;

Here's a script that can be run to time and profile executing each function one million times. This can be seen in Listing A.

Listing A

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.

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.

Thank You

By registering you become a member of the CBS Interactive family of sites and you have read and agree to the Terms of Use, Privacy Policy and Video Services Policy. You agree to receive updates, alerts and promotions from CBS and that CBS may share information about you with our marketing partners so that they may contact you by email or otherwise about their products or services.
You will also receive a complimentary subscription to the ZDNet's Tech Update Today and ZDNet Announcement newsletters. You may unsubscribe from these newsletters at any time.