PL/SQL (MOSC)

MOSC Banner

servererror trigger in oracle 9i

edited Apr 24, 2009 9:34AM in PL/SQL (MOSC) 2 comments
 i have a trigger like below to catch database errors and insert error informations to a table.

CREATE OR REPLACE TRIGGER sys.catch_errors
 AFTER
  SERVERERROR
 ON DATABASE
declare
   var_osuser    varchar2(30);
   var_machine   varchar2(64);
   var_program   varchar2(48);
   var_cli_info  varchar2(64);
   sql_text ora_name_list_t;
   msg_     varchar2(2000) := null;
   stmt_    varchar2(2000) := null;
   err_     varchar2(30) :=null;
begin

  for depth in 1 .. ora_server_error_depth loop
    msg_ := msg_ || ora_server_error_msg(depth);
    err_ := err_ || ora_server_error(depth);
  end loop;

  for i in 1 .. ora_sql_txt(sql_text) loop
     stmt_ := stmt_ || sql_text(i);
  end loop;

  select
      osuser,
      machine,

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center