Posted in 2016

Catching errors

Sometimes happened that some rows could not be loaded and the application got an ORA-1, how can you find out what value it was, I know, it can be defined other way too, but I wanted it this way… I don’t remember even why I did it this way, this was in a time like my first year as a DBA, but it resolved the problem. And this is important… and I have to check it if it’s really working, but it should.. it’s enough to create a unique column and insert value 1 two times..

create table caught_errors (
  dt        date,               
  sid 		number
);

create or replace trigger catch_errors
   before insert on catchtab
   for each row
declare
spid_val number ;   
PRAGMA AUTONOMOUS_TRANSACTION;
begin

IF (IS_SERVERERROR(1)) THEN
execute immediate 'alter session set events ''10046 trace name context level 12, lifetime 1''';
select spid into spid_val from v$session s,v$process p where s.paddr=p.addr and audsid=userenv('sessionid') ;
insert into caught_errors values (sysdate,spid_val) ;
END IF;

end;
/
Advertisements

Author:

Database administrator who loves to work with Oracle software. (Sometimes not)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s