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

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

Advertisements

An insight into Oracle Index rebuild

You must have gone through my previous article on Oracle Index rebuild – 10g Vs 11g – https://avdeo.com/2011/03/17/oracle-index-rebuild-online-10g-vs-11g/

Well, there is another nice article written by a friend of mine Sumit Bhatia.

He has talked about the actions and events happening while rebuilding an index online in oracle database 11g.

Different wait events that we encounter and to make it more practical he has also build a script which gives you the exact status for your index rebuild.

You can refer to the article at http://fordba.wordpress.com/2011/04/05/online-ndx-rebuild/

You can refer to the script at http://fordba.wordpress.com/2011/03/30/ndx-progress-sql/

Just to mention, this script is by no means a complete script and enhancements can be underway. Also this script should not be used in production. If you want to use in production, you can do it at your own risk.

Hope this helps !!