Error Triggers

Once every few weeks this customer encountered an ORA-1031 error – insufficient privileges. We saw the error in the alert log, and the customer complained about getting the error, but for some reason we never had enough information on what the customer was actually doing when the error occurred. We needed to know what he was doing to figure out how we can fix the issue.

We tried tracing, but the issue occurred so rarely that we were never able to actually capture it on time.

I thought we can solve the issue with error triggers. My suggestion was rejected by my team lead who prefers not to use triggers on production, but I still think it could be useful.

create table caught_errors (
dt        date,
username  varchar2( 30), -- value from ora_login_user
msg       varchar2(512),
stmt      varchar2(512)
);

CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE
declare
sql_text ora_name_list_t;
msg_     varchar2(2000) := null;
stmt_    varchar2(2000) := null;
BEGIN
IF (IS_SERVERERROR (1031)) THEN
    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
         stmt_ := stmt_ || sql_text(i);
    end loop;

    insert into
    caught_errors (dt     , username      ,msg ,stmt )
    values (sysdate, ora_login_user,msg_,stmt_);
END IF;
END;

---