Re: after servererror trigger question

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

Does the fact that you're no longer CONNECTED to the database provide any clue? ORA-03113 is an informational message from SQL*Plus reported after the connection is lost. You can't capture that which isn't there.

David Fitzjarrell Received on Tue Jul 25 2006 - 14:29:20 CDT