Monthly Archives: May 2013

1. Don’t forget always add NO_DATA_FOUND exception handling, when you doing “select into” in code which can be called as from PL/SQL, as from SQL.

A little example:
Suppose we need to create a function, which would call some procedure:

create or replace procedure p_nested as
  a int;
begin
  select 1 into a from dual where 1=0;
end;
/
create or replace function f_no_data_found return varchar2 as
begin
  p_nested;
  return 'ok';
end;
/

When we call this function in PL/SQL, it will raise NO_DATA_FOUND and we will see it:

SQL> exec dbms_output.put_line(f_no_data_found);
BEGIN dbms_output.put_line(f_no_data_found); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "XTENDER.P_NESTED", line 4
ORA-06512: at "XTENDER.F_NO_DATA_FOUND", line 3
ORA-06512: at line 1

But it doesn’t when we call it in SQL, because it’s normal for SQL: it’s just like a result of scalar subquery that returns nothing – NULL:

SQL> set null "NUL"
SQL> col ndf format a10
SQL> select f_no_data_found ndf from dual;

NDF
----------
NUL

1 row selected.

So if you want the function to behave the same way in PL/SQL and SQL, just add exception handling with reraising another exception or just return null.
It must be at the level of reflexes – “select into” → “exception when no_data_found”
Otherwise, later, when code become a big and difficult, you can get unstable hidden error.

2. Exceptions raised in a declaration section or in default parameters assigning will never be handled in exception section of the same level

Let’s take a look at a very simple example:

SQL> create or replace function f_value_error return int is
  2  begin
  3    raise value_error;
  4    return 1;
  5  end;
  6  /

Function created.

SQL> create or replace function f(i int:=f_value_error) return varchar2 is
  2  begin
  3    return 'ok';
  4  exception when others then
  5    return dbms_utility.format_error_backtrace;
  6  end;
  7  /

Function created.

SQL> set serverout on;
SQL> begin
  2    dbms_output.put_line('From f: '||chr(10)||f);
  3    dbms_output.put_line('****************************');
  4  exception when others then
  5    dbms_output.put_line('****************************');
  6    dbms_output.put_line('From higher level:'||chr(10)||dbms_utility.format_error_backtrace);
  7    dbms_output.put_line('****************************');
  8  end;
  9  /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.

If the exception was caused in the declaration, we would see the “correct” backtrace, but exception would be still handled at higher level only:

SQL> create or replace function f(i int:=null) return varchar2 is
  2    l_i int:=nvl(i,f_value_error);
  3  begin
  4    return 'ok';
  5  exception when others then
  6    return dbms_utility.format_error_backtrace;
  7  end;
  8  /

Function created.

SQL> set serverout on;
SQL> begin
  2    dbms_output.put_line('From f: '||chr(10)||f);
  3    dbms_output.put_line('****************************');
  4  exception when others then
  5    dbms_output.put_line('****************************');
  6    dbms_output.put_line('From higher level:'||chr(10)||dbms_utility.format_error_backtrace);
  7    dbms_output.put_line('****************************');
  8  end;
  9  /
****************************
From higher level:
ORA-06512: at "XTENDER.F_VALUE_ERROR", line 3
ORA-06512: at "XTENDER.F", line 2
ORA-06512: at line 2

****************************

PL/SQL procedure successfully completed.

SQL> select PKG1.F(1,0,0,1275) from dual; 
select PKG1.F(1,0,0,1275) from dual 
       * 
ERROR at line 1: 
ORA-06553: PLS-801: internal error [1401]

And the function has many functions calls in default parameters initialization, so I couldn’t even find out which one contains a root problem.