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.

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;

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.

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.