How to trap errors in pl/sql script?

[Randy Hagedorn]

Hi,

We are running pl/sql on an hp/ux system and would like to find a way to trap any errors that occur within the script. If an oracle error would occur, in the script execution, we would like to know of its success or failure when the script is finished.

Can anyone offer any suggestions?

Thanks in advance,
Randy

[Yogeeraj_1 · Accepted solution]

hi,

You have to put your exception blocks around the statements that can cause exceptions, for example:

begin
insert into t values ( ..... );
exception
when others LOG_ERROR;
end;

begin
update y set ....;
exception
when others LOG_ERROR;
end;

where LOG_ERROR and supporting routines would look like:
================================================================
create or replace function whence return varchar2
as
call_stack varchar2(4096) default dbms_utility.format_call_stack || chr(10);
n number;
found_stack BOOLEAN default FALSE;
line varchar2(255);
cnt number := 0;
begin
loop
n := instr( call_stack, chr(10) );
exit when ( cnt = 4 or n is NULL or n = 0 );

line := substr( call_stack, 1, n-1 );
call_stack := substr( call_stack, n+1 );
if ( NOT found_stack ) then
if ( line like '%handle%number%name%' ) then
found_stack := TRUE;
end if;
else
cnt := cnt + 1;
end if;
end loop;
return line;
end;
/

set define on
create table error_table
( timestamp date,
whence varchar2(1024),
msg varchar2(1024),
code number
)
/


create or replace procedure log_error
as
pragma autonomous_transaction;

l_whence varchar2(1024);
l_msg varchar2(1020) default sqlerrm;
l_code number default sqlcode;
begin
l_whence := whence;
insert into error_table
( timestamp, whence, msg, code )
values
( sysdate, whence, l_msg, l_code );

commit;
exception
when others then
rollback;
raise;
end;
/
================================================================

Now let us test:

yd@MYDB.MU> create table t ( x int primary key );

Table created.

Elapsed: 00:00:00.12
yd@MYDB.MU> begin
begin
insert into t values ( 1 );
exception
when others then log_error;
raise;
end;
begin
insert into t values ( 1 );
exception
when others then log_error;
raise;
end;
end;
/
begin
*
ERROR at line 1:
ORA-00001: unique constraint (YD.SYS_C0010813) violated
ORA-06512: at line 12


Elapsed: 00:00:00.53

Now, display the results of the information that has been logged:
=====================================================================
yd@MYDB.MU> exec print_table('select * from error_table');
TIMESTAMP : 03-may-2003 09:14:43
WHENCE : c0000000249a5988 11 anonymous block
MSG : ORA-00001: unique constraint (YD.SYS_C0010813) violated
CODE : -1
-----------------

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.49
yd@MYDB.MU>


Now if you want, you can write a piece of code that will send this error message to a Mail recipient using SMTP_MAIL package. Instead of the user having to come to verify this table for possibilities of errors.

For showing success, you may wish to modify this piece of code to include you own comments for success, and again define your own notification mechanism.

Hope this helps!

Best regards
Yogeeraj