Month: April 2011

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

EXCEPTION WHEN NO_DATA_FOUND THEN

<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

SQL>

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
------------------------------ -------------------- --------------------
shared_pool_reserved_min_alloc

SQL>

This is easier to process.

Hope this helps !!