raise_application_error() is a good feature … but go one step ahead:
how do you identify the location of an exception in a pl/sql object with
hundreds or thousands of lines of code and what was an eventual data constellation
when the exception occurred?
I collect as many informations as I can, for instance the content of the
oracle stack and extra infos – I call them secondary messages – in the code
which I normally log for later analysis.
I am aware that it means extra effort when coding but in my experience
it pays out: I was much quicker in identifying problems and defects in my code.
Here’s a simple test case to demonstrate the idea:
—
— [A] create a function which returns the informations
— from the oracle error stack. Actually, this function
— should be integrated into a utility or log packages
—
create or replace
function f_cre_err_infos(i_len in pls_integer default 4000)
return varchar2
is
c_err constant varchar2(11) := ‘[ERROR]: ‘;
v_len pls_integer := i_len; — limits output length of error output msg
/*
||——————————————————————-
|| dbms_utility.format_call_stack:
|| Format the current call stack. This can be used an any stored
|| procedure or trigger to access the call stack. This can be
|| useful for debugging.
||——————————————————————-
*/
v_call_stk varchar2(2000); — call stack var. / <= 2000 bytes.
/*
||——————————————————————-
|| dbms_utility.format_error_stack:
|| Format the current error stack. This can be used in exception
|| handlers to look at the full error stack.
||——————————————————————-
*/
v_err_stk varchar2(2000); — Returns the error stack. <= 2000 bytes.
/*
||——————————————————————-
|| dbms_utility.format_error_backtrace:
|| Format the backtrace from the point of the current error
|| to the exception handler where the error has been caught.
|| NULL string is returned if no error is currently being
|| handled.
||——————————————————————-
*/
v_bck_trc varchar2(2000);
begin
v_err_stk := dbms_utility.format_error_stack;
v_call_stk := dbms_utility.format_call_stack;
/*
||————————————————————————
|| We replace/shorten following oracle error code with just the string
|| ‘backtrace’ (otherwise, it might be misleading; see also
|| http://download.oracle.com/docs/cd/B19306_01/server.102/b14219/e4100.htm#sthref2006):
||
|| ORA-06512: at stringline string
|| Cause: Backtrace message as the stack is unwound
|| by unhandled exceptions.
|| Action: Fix the problem causing the exception or write an exception
|| handler for this condition. Or you may need to contact your
|| application administrator or DBA.
||————————————————————————
*/
v_bck_trc := regexp_replace(dbms_utility.format_error_backtrace, ‘ORA-06512: ‘, ‘backtrace: ‘);
/*
||————————————————————————
|| A minimum length of 256 characters for the error message is guaranteed
|| (and a theoretical maximum of 32768 as well)
||————————————————————————
*/
v_len := case when coalesce(v_len,1) < 256 then 256
when v_len > 32768 then 32768
else v_len
end;
return substr(
c_err || v_err_stk ||
c_err || v_bck_trc ||
c_err || v_call_stk, 1, v_len);
end f_cre_err_infos;
/
—
— [B] I create a simple test case with 2 tables; my test procedure
— will transfer data from T1 to T2, record 5 fires the exception
—
create table T1 (id number(5,0), txt varchar2(10));
create table T2 (id number(5,0) not null,
txt varchar2(10) not null,
constraint T2_PK primary key(id) using index)
;
truncate table T1;
truncate table T2;
insert into T1 values (1, ‘A text 1’);
insert into T1 values (2, ‘2nd text’);
insert into T1 values (3, ‘Text 2’);
insert into T1 values (4, ‘Text 3’);
insert into T1 values (4, ‘Text 4’);
insert into T1 values (5, null);
commit;
select t.*, rowid from T1 t order by id;
—
— [C] create the test procedure; see comments in exception handler
—
create or replace procedure p_tst_xfer
is
c_module constant varchar2(30) := ‘p_tst_xfer’;
v_msg varchar2(230);
cursor sel_cur is
select ID,
TXT
from T1;
rec sel_cur%rowtype;
begin
v_msg := ‘[1] opening sel_cur’;
open sel_cur;
loop
v_msg := ‘[2] fetching from sel_cur’;
fetch sel_cur into rec;
exit when sel_cur%notfound;

v_msg := ‘[3] inserting into T2: ID=’|| rec.ID || ‘, TXT=’ || nvl(rec.TXT, ‘NULL’);
insert into T2 (ID, TXT)
values (rec.ID, rec.TXT);
end loop;
v_msg := ‘[4] closing sel_cur cursor’;
close sel_cur;
exception
when OTHERS then
if sel_cur%isopen then
close sel_cur;
end if;
— ————————————————
— For demo purposes: display oracle error stack
— and the secondary infos. Better: log all infos
— and use ‘raise;’ as the last statement
— ————————————————
dbms_output.put_line(f_cre_err_infos);
dbms_output.put_line(‘[ERROR]: module ‘ || c_module);
dbms_output.put_line(‘[ERROR]: while ‘ || v_msg);
end p_tst_xfer;
/
show errors

— in sqlplus
set serverout on size unlimited format wrapped
set trimout on trimspool on
set linesize 500
begin
p_tst_xfer;
end;
/
—
— The output looks like this:
—
[ERROR]: ORA-00001: unique constraint (SCOTT.T2_PK) violated
[ERROR]: backtrace: at “SCOTT.P_TST_XFER”, line 20
[ERROR]: —– PL/SQL Call Stack —–
object line object
handle number name
000007FFB5B48C38 37 function SCOTT.F_CRE_ERR_INFOS
000007FFB5AA4328 35 procedure SCOTT.P_TST_XFER
000007FFB5A70230 2 anonymous block
[ERROR]: module p_tst_xfer
[ERROR]: while [3] inserting into T2: ID=4, TXT=Text 4
PL/SQL procedure successfully completed.
What we can see:
* The module/program name and the location/line where the exception was fired
* The secondary message shows the ‘data constellation’ ([3] inserting into T2: ID=4, TXT=Text 4)
Now, I can debug my code with following sql (I put these lines in a script, e.g. srcproc.sql):
— SQL: begin ———–
set pagesize 40
set linesize 120
set trimout on trimspool on
set verify off
col LINE for 99999 hea ‘Line|Nr’
col TEXT for a80 hea ‘Text’ wrap
prompt
prompt *** Listing source of PL/SQL object *****
prompt
accept plsql_obj char prompt “*** Define name of PL/SQL object [%]: ” default “%”
accept plsql_typ char prompt “*** Type of object ….. [PROCEDURE]: ” default “PROCEDURE”
accept start_line number prompt “*** Starting line number ……..[1]: ” default 1
accept end_line number prompt “*** Ending line number ……….[9]: ” default 9
select LINE,
TEXT
from USER_SOURCE
where NAME like upper(‘&&plsql_obj.%’)
and TYPE = upper(‘&&plsql_typ.’)
and line between &&start_line and &&end_line
order by NAME, LINE
/
undefine plsql_obj
undefine plsql_typ
undefine start_line
undefine end_line
— SQL: end ———–
I run this script in SQL*Plus using e.g. starting line 15 and ending line 25 in order to
narrow the ‘problem area’ in the first run … you might ask ‘why SQL*Plus?’.
Reason: normally, SQL and PL/SQL source files are deployed by means of sqlplus.
Therefore I develop ‘production-close’ uploading my developed PL/SQL code via sqlplus
(my first basic unit test: do the PL/SQL objects get compiled at all?).
As for me, the sql script based debugging methodology payed out in most cases.
sometimes, I also use debugging functionality in PL/SQL Developer or Toad …
But this is a completely different discussion 😉
—
— [D] test case cleanup
—
drop procedure p_tst_xfer;
drop function f_cre_err_infos;
drop table T1 cascade constraints;
drop table T2 cascade constraints;