An
ORA-06512 error message is thrown by the
PL/SQL execution engine if it encounters an SQL error or when
raise_application_error is called and when no
exception handler handles the error or exception.
The error message contains the actual error followed by a series of lines, starting with ORA-06512 … lines that contain the stack trace.
Find offending source code line in exception handler
The following code is an attempt to demonstrate how it can be dealt with ORA-06512 errors.
PL/SQL Package
First, we need a PL/SQL package. For demonstration purposes, it is rather simple and does nothing useful. In fact, it only raises an exception in proc_3:
The package's specification …
create or replace package tq84_err_test as
procedure go;
end tq84_err_test;
/
… and its body:
create or replace package body tq84_err_test as
procedure proc_3 is
begin
raise_application_error(-20888, 'Raising errors is easier than raising money');
end proc_3;
procedure proc_2 is
begin
proc_3;
end proc_2;
procedure proc_1 is
begin
proc_2;
end proc_1;
procedure go is
begin
proc_1;
end go;
end tq84_err_test;
/
show errors
Using the package without exception handler
The following anonymous block executes the package without exception handler. The returned error message conists of multiple lines, of which the lines starting with ORA-06512 walk the stack from the line of code where the exception was raised to the caller:
begin
tq84_err_test.go;
end;
/
--
-- ERROR at line 1:
-- ORA-20888: Raising errors is easier than raising money
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 5
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 10
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 15
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 20
-- ORA-06512: at line 2
Using the package with exception handler
Within an exception handler, the value of
sqlerrm is simply the raised error text. There is no stack trace:
begin
tq84_err_test.go;
exception when others then
dbms_output.put_line(sqlerrm);
end;
/
--
-- ORA-20888: Raising errors is easier than raising money
Using DBMS_UTIILITY.FORMAT_ERROR_BACKTRACE
begin
tq84_err_test.go;
exception when others then
dbms_output.put_line(dbms_utility.format_error_backtrace);
end;
/
--
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 5
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 10
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 15
-- ORA-06512: at "RENE.TQ84_ERR_TEST", line 20
-- ORA-06512: at line 2
Parse error stack using DBA_SOURCE
Parse the error stack and use
dba_source to display the source code line that cause the
exception:
begin
tq84_err_test.go;
exception when others then
declare
stack varchar2(4000); -- is 2000 sufficient?
err_line varchar2( 200);
fully_qualif_obj varchar2( 300);
obj_owner varchar2( 128);
plsql_obj varchar2( 128);
src_line number ( 6);
begin
stack := dbms_utility.format_error_backtrace;
err_line := regexp_substr(stack, 'ORA-06512: at [^' || chr(10) || ']+', 1, 1, 'n');
-- dbms_output.put_line('err_line: ' || err_line);
fully_qualif_obj := regexp_replace(err_line, 'ORA-06512: at "(.*)", line \d+', '\1');
-- dbms_output.put_line('fully_qualif_obj: ' || fully_qualif_obj);
obj_owner := regexp_replace(fully_qualif_obj, '([^.]+).*', '\1');
-- dbms_output.put_line('obj_owner : ' || obj_owner);
plsql_obj := regexp_replace(fully_qualif_obj, '[^.]+\.(.*)', '\1');
-- dbms_output.put_line('plsql_obj : ' || plsql_obj);
src_line := regexp_replace(err_line, 'ORA-06512: at ".*", line (\d+)', '\1');
-- dbms_output.put_line('src_line : ' || src_line);
dbms_output.new_line;
dbms_output.put_line('Exception was caused by the following line');
dbms_output.new_line;
for code in (select
text
from
dba_source
where
owner = obj_owner and
name = plsql_obj and
line = src_line and
type like '%BODY') loop
dbms_output.put_line(code.text);
end loop;
end;
end;
/