Oracle Ref Cursor Vs Select into with Exception handling

Question

I have a couple of scenarios:

1. Need to read the value of a column from three different tables in a predefined order and only 1 table will have the data

2. Read data from table1 if records are present for criteria given else read data from Table2 for given criteria

In Oracle Stored Procedures

The way these are being handled right now is to first get the count for a given query into a variable, and if the count > 0, then we execute the same query to read the actual data as in:

select count(*) from table1 into v_count
if v_count > 0
then
    select data into v_data from table1
end if;

Return v_data

This is being done to avoid the no_data_found exception, otherwise I would need three exception handler blocks to catch the no_data_found exception for each table access.

Currently I am reimplementing this with Cursors so that I have something like this:

cursor C1 is
    select data from table1;
Open C1
Fetch C1 into v_data
if C1%FOUND
then
    Close C1
    Return v_data
End If

I wanted to find out which one is better from a performance point of view--the one with Cursors, or the one which does a Select into a variable and has three no_data_found Exception blocks. I don't want to use the two stage query process which we have currently.

Answer 1

I don't know why you are so keen to avoid the exception? What is wrong with:

begin
    begin
        select data into v_data from table1;
    exception
        when no_data_found then
        begin
            select data into v_data from table2;
        exception
            when no_data_found then
            begin
               select data into v_data from table3;
            exception
                when no_data_found then
                    v_data := null;
            end;
        end;
    end;
    return v_data;
end;

I believe this will perform better than your other solution because it does the minimum possible work to achieve the desired result.

See How bad is ignoring Oracle DUP_VAL_ON_INDEX exception? where I demonstrate that using exceptions performs better than counting to see if there is any data.