How-to get client errors by trigger

Sometimes applications don’t have enough log or they don’t know the details when report an error/exception to DBA. Fortunately, Oracle has a type of trigger on “SERVERERROR”. So we can create a table and a trigger as follows to log any errors:

--Purpose: gather client ORA- errors
create table TMP_SCANALERT_ERROR_LOG (
          error_date date,
          error_no number,
          machine varchar2(64),
          sqltext varchar2(4000)
          );

create or replace trigger ERROR_LOG_TRIGGER AFTER SERVERERROR ON DATABASE
            declare
            n number;
            stmt varchar2(4000);
            sql_text ora_name_list_t;
            version number := 2;
            BEGIN
            n := ora_sql_txt(sql_text);
            FOR i IN 1..n LOOP
              if(length(stmt||sql_text(i)) > 4000) then
                stmt := substr(stmt||sql_text(i), 1, 4000);
                exit;
              end if;
              stmt := stmt || sql_text(i);
            END LOOP;
            if (server_error(1) 1 and server_error(1) <= 20000 ) then
               insert into sys.TMP_SCANALERT_ERROR_LOG (error_date,error_no,machine,sqltext)
               values (sysdate,server_error(1),user||'@'||sys_context('userenv','host'),stmt);
            end if;
            END;
/

--check log
 SQL> select error_no,count(*) cnt, machine,sqltext from TMP_SCANALERT_ERROR_LOG group by error_no,machine,sqltext order by cnt desc;

You may need to purge the log table regularly to avoid it becomes too large. Also you can use it to monitor if there is any errors after application code upgrade, etc. I think it’s quiet useful. What do you think? Leave me a message 🙂

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

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

%d bloggers like this: