after servererror trigger question

From: <epipko_at_gmail.com>
Date: 25 Jul 2006 11:40:54 -0700
Message-ID: <1153852853.933095.177350@h48g2000cwc.googlegroups.com>

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 Received on Tue Jul 25 2006 - 13:40:54 CDT