Re: after servererror trigger question

It makes sense.
I am trying to log an event when d/b is down or user can't connect to it.

fitzjarrell_at_cox.net wrote:
> epipko_at_gmail.com wrote:
> > Hi all,
> > I have a trigger to catch ddl errors. I am trying (unsuccessful so far)
> > to catch ORA-03113.
> > I don't see this error in the list of errors Oracle can't catch and
> > don't know why it doesnt' work.
> > ----------------------------------------------------------------------------------------------------------
> > sql> DROP TABLE SERVERERROR_LOG;
> >
> > Table dropped.
> >
> > sql> CREATE TABLE SERVERERROR_LOG (
> > 2 err_user VARCHAR2(30),
> > 3 err_host VARCHAR2(30),
> > 4 err_program varchar2(30),
> > 5 err_datetime TIMESTAMP,
> > 6 err_stack VARCHAR2(2000),
> > 7 err_sql VARCHAR2(2000));
> >
> > Table created.
> >
> > CREATE OR REPLACE TRIGGER UB_OBJECTS.log_server_errors
> > AFTER SERVERERROR ON DATABASE
> > DECLARE
> > sql_text ora_name_list_t;
> > sql_stmt VARCHAR2(2000);
> > vProgram VARCHAR2(30);
> >
> > BEGIN
> > SELECT
> > program
> > INTO
> > vProgram
> > FROM v$session
> > WHERE audsid=USERENV('sessionid');
> >
> > FOR i IN 1..ora_sql_txt(sql_text)
> > LOOP
> > sql_stmt := sql_stmt || sql_text(i);
> > END LOOP;
> >
> > INSERT INTO SERVERERROR_LOG
> > (err_user,
> > err_host,
> > err_program,
> > err_datetime,
> > err_stack,
> > err_sql)
> > VALUES
> > ((SELECT SYS_CONTEXT ('USERENV', 'OS_USER') FROM DUAL),
> > (SELECT SYS_CONTEXT ('USERENV', 'HOST') FROM DUAL),
> > vProgram,
> > SYSTIMESTAMP,
> > DBMS_UTILITY.FORMAT_ERROR_STACK,
> > sql_stmt);
> >
> > END log_server_errors;
> >
> > sql> select * from dualllll
> > *
> > ERROR at line 1:
> > ORA-00942: table or view does not exist
> >
> > sql> select err_stack from servererror_log;
> >
> > ERR_STACK
> > ----------------------------------------
> > ORA-00942: table or view does not exist
> >
> > sql> select dbms_metadata.get_ddl('TABLE','PD2_WORK_ORDER')from dual;
> > ERROR:
> > ORA-03113: end-of-file on communication channel
> >
> > sql> select err_stack from servererror_log;
> > ERR_STACK
> > ----------------------------------------
> > ORA-00942: table or view does not exist
> > ----------------------------------------------------------------------------------------------------------
> >
> > Why can't I catch 3113???
> >
> > Thanks,
> > Eugene

>