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@xxxxxxx wrote:
epipko@xxxxxxxxx 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

.

Relevant Pages

  • Re: Trigger for update
    ... > which 2 logins has access to modify this DB and specially EMP, ... > the trigger works successfully when I make new changes in empname ... Since you are dealing with a cross database query, ... A better way might be to use dynamic SQL to determine if the user really ...
    (microsoft.public.sqlserver.programming)
  • RE: Execute a DTS package from a trigger?
    ... I'll assume you are refering literally to "Database Transformation Services" ... Yet you allude to SQL 2005. ... *Using a trigger to *directly* start another process may lead to trouble. ... The message gets sent from the outbound queue to an inbound queue (within the ...
    (microsoft.public.sqlserver.dts)
  • Re: Active directory update
    ... I would look into WMI and sp_OA* if you really want to do it from Sql. ... Modifying AD is quite serious. ... > Directory when Employee details are changed in our SQL Server database. ... > especially as there appears to be no trigger type event in AD. ...
    (microsoft.public.sqlserver.programming)
  • Re: application roles
    ... I 've made a trigger on a SQL2000 database to test your solution with the ... But my login-account and database user appear in my logtable instead of the ... if you are on SQL 2005. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.security)
  • Re: after servererror trigger question
    ... CREATE OR REPLACE TRIGGER UB_OBJECTS.log_server_errors ... AFTER SERVERERROR ON DATABASE ... vProgram VARCHAR2; ... Even for an ora-3113 (dropped connection), ...
    (comp.databases.oracle.server)