CODEWITHSUNDEEP
oracle-databaseplsql
Jeff Tilton
Jeff Tilton

Reputation: 1296

PL/SQL exception handling - log errors

How do I record the oracle error in a pl/sql script? I have been to the oracle error handling documentation and I see the built in exceptions, but what if I do not know what the exception is? How can I log this in an exception block?

I want to do something like the below.

exception
 when others then DBMS_OUTPUT.PUT_LINE(the error)

Upvotes: 1

Views: 788

Answers (1)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

It's easier to create error logging database trigger with conditional logging, for example my actual error_logging trigger: https://github.com/xtender/xt_scripts/blob/master/error_logging/on_database.sql

create table ERROR_LOG
(
  id       NUMBER,
  username VARCHAR2(30),
  errcode  INTEGER,
  seq      INTEGER,
  tmstmp   TIMESTAMP(6),
  msg      VARCHAR2(4000),
  sql_text CLOB
)
/
create sequence err_seq
/
create or replace trigger trg_error_logging
after servererror
on database
disable
declare
   v_id       number   := err_seq.nextval();
   v_tmstmp   timestamp:= systimestamp;
   n          int;
   sql_text   dbms_standard.ora_name_list_t;
   v_sql_text clob;
begin
   -- only if plsql_debug is set to TRUE:
   for r in (select * from v$parameter p where p.name='plsql_debug' and upper(p.value)='TRUE') loop
       v_sql_text:=null;
       n := ora_sql_txt(sql_text);
       for i in 1..n loop
          v_sql_text := v_sql_text || sql_text(i);
       end loop;

       for i in 1.. ora_server_error_depth
       loop
          if i=1 then
             insert into error_log(id,seq,tmstmp,username,errcode,msg,sql_text)
                values( v_id, i, v_tmstmp, user, ora_server_error(i), ora_server_error_msg(i), v_sql_text);
          else
             insert into error_log(id,seq,tmstmp,username,errcode,msg)
                values( v_id, i, v_tmstmp, user, ora_server_error(i), ora_server_error_msg(i) );
          end if;
       end loop;
       commit;
    end loop;
END;
/
select object_name,object_type,status from user_objects o where object_name='TRG_ERROR_LOGGING'
/
alter trigger trg_error_logging enable
/

As you can see it logs all errors into the table ERROR_LOG, but only if session parameter plsql_debug is set to true. Obviously, you can change it to own parameters or conditions.

Upvotes: 2

Related Questions