Tag: plsql

Avoiding “no data found” : Tips

Published on by Advait4 Comments

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

Advertisement

Using TABLE_TO_COMMA in PLSQL

Published on by Advait3 Comments

Some times the situation arises where we want to output the result from the table as a comma separated string. This situation can be answered using many methods. The most efficient method, however, is using TABLE_TO_COMMA procedure of DBMS_UTILITY package. Below is the example that I encountered.

set echo on serveroutput on
declare
cursor c1 is select dept_no, dept_name, location
from dept12;

output_table dbms_utility.uncl_array;
cnt binary_integer;
output_string varchar2(80);
counter integer:= 0;
begin
for i in c1 loop
output_table(counter+1) := I.dept_no;
output_table(counter+2) := I.dept_name;
output_table(counter+3) := I.location;
counter := counter + 3;
end loop;
DBMS_UTILITY.TABLE_TO_COMMA(output_table, cnt, output_string);
dbms_output.put_line(output_string);
end;

Hope this helps !!