Here is a way to capture session errors except following errors (modified from
here):
ORA-01034 ORACLE not available
ORA-01403 no data found
ORA-01422 exact fetch returns more than requested number of rows,
ORA-01423 error encountered while checking for extra rows in exact fetch
ORA-04030 out of process memory when trying to allocate string bytes.
create table myschema.caught_errors (
dt date,
username varchar2( 30),
msg varchar2(4000),
stmt varchar2(4000)
);
create or replace trigger myschema.catch_errors
after servererror on database
declare
sql_text ora_name_list_t;
msg_ varchar2(4000) := null;
stmt_ varchar2(4000) := null;
i int;
begin
for depth in 1 .. ora_server_error_depth loop
msg_ := msg_ || ora_server_error_msg(depth);
end loop;
for i in 1 .. ora_sql_txt(sql_text) loop
if (nvl(length(stmt_),0) + length(sql_text(i))) < 4000 then
stmt_ := stmt_ || sql_text(i);
else
insert into caught_errors (dt , username ,msg ,stmt )
values (sysdate, ora_login_user,msg_,stmt_);
stmt_ :=sql_text(i);
end if;
end loop;
i:= ora_sql_txt(sql_text);
insert into
caught_errors (dt , username ,msg ,stmt )
values (sysdate, ora_login_user,msg_,stmt_);
end;
/
alter trigger myschema.catch_errors disable;
truncate table myschema.caught_errors;
