Avoiding “no data found” : Tips

Some time we face issue about no data found depending on selection criteria. And when this happens in PLSQL procedure we get annoying error

ORA-01403: No data found and execution stops.

One way to get around this error is to add exception block in our PLSQL procedure as shown below


<Take Action>

But disadvantage of using this approach is that it will not process rest of the code and control will jump to exception block and from there on continue till the end. If we get this error in the middle of FOR loop or WHILE loop, rest of the records will not get processed.

One of the way to deal with this is joining your SQL with dual.

In that case you are sure to get a null value even if the row does not exists.

Example in my case I want to find the memory value and spfile value of one of the parameter in database by querying v$parameter and v$spparameter view.

If I do a simple join as below I am going to get “no data found” if these view does not have parameter

SQL> select a.name, a.value, b.value from v$parameter b, v$spparameter a
  2  where a.name = b.name
  3  and a.name = 'shared_pool_reserved_min_alloc';

no rows selected


But if we join with dual we can get the name of the parameter and a null value in front of that

SQL> select c.col, a.value, b.value
  2  from v$parameter b, v$spparameter a, (select 'shared_pool_reserved_min_alloc' col from dual) c
  3  where c.col = a.name (+) and c.col = b.name (+);

COL                            VALUE                VALUE
------------------------------ -------------------- --------------------


This is easier to process.

Hope this helps !!

4 thoughts on “Avoiding “no data found” : Tips

  1. Shouldn’t this return me data that I don’t want?
    Isn’t better to wrap that SQL statement between a begin..exception..end nested block? that way it will trap the exception and continue executing the code.

  2. Hi, just an additional thought: if you are in a loop and you don’t want the exception to throw you out of the loop, then put BEGIN-END around the SELECT:

    for i in 1..10 loop
    select * from dual where i < 5;
    exception when no_data_found then
    — exception processing then continue the loop
    end loop;

  3. All that exception handling can get clunky If all you want to do is get back a value if one exists. Using MAX takes care of too_many_rows and nesting your query in a select from dual will always give you a result, taking care of no_data_found. Use an NVL to provide an alternative if needed.

    (SELECT MAX(mycolumn) FROM myTable)
    , 0)
    INTO myVar
    FROM dual;

    1. Sebastian, that’s just about the sanest response I’ve seen to this question anywhere and exactly what I was looking for, thank you. It irks me that Oracle should treat the possibility of there not being a result as an exception when it more often that not that is simply not the case.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s