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 !!
Persistent Storage Solutions 
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.
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:
begin
for i in 1..10 loop
begin
select * from dual where i < 5;
exception when no_data_found then
— exception processing then continue the loop
end;
end loop;
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 NVL(
(SELECT MAX(mycolumn) FROM myTable)
, 0)
INTO myVar
FROM dual;
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.