Question

Locked

Oracle 10g error reporting is wrong?

By f.gruman ·
Hello all,

I've got a weird issue with an Oracle trigger trying to catch servererrors and log them. I can get scripts to force errors, but the sql reported error and the logged output do not match. Any ideas??

Here is my trigger:
create or replace TRIGGER ERROR_LOG_TRG
AFTER SERVERERROR on SCHEMA
Declare
Pragma AUTONOMOUS_TRANSACTION;
logged_by VARCHAR2(30) DEFAULT USER;
error_num NUMBER;
error_msg VARCHAR2(1000);
host VARCHAR2(50);
svc_name VARCHAR2(1000);
v_ErrorStack VARCHAR2(3000);
v_CallStack VARCHAR2(3000);
BEGIN
error_num := SQLCODE;
error_msg := sqlerrm;
IF error_num > 0 THEN
v_ErrorStack := DBMS_UTILITY.FORMAT_ERROR_STACK;
v_CallStack := DBMS_UTILITY.FORMAT_CALL_STACK;
SELECT sys_context('USERENV', 'CURRENT_SCHEMA') INTO logged_by FROM dual;
SELECT sys_context('USERENV', 'HOST') INTO host FROM dual;
SELECT sys_context('USERENV', 'SERVICE_NAME') INTO svc_name FROM dual;
INSERT
INTO error_log
(
error_log_id, logged_datetime, error_num, error_msg,error_stack, call_stack, logged_by, host, svc_name
)
VALUES
(
ERROR_LOG_ID_SEQ.NEXTVAL,SYSTIMESTAMP, error_num, error_msg,v_errorstack, v_callstack, logged_by, host, svc_name
);
COMMIT;
END IF;
END;

Here is my output:
SQL> select * from bad_table;
select * from bad_table
*
ERROR at line 1:
ORA-00942: table or view does not exist

Error logging. 0-ORA-0000: normal, successful completion
SQL>

Any ideas why I might be getting two different sets of messages?

Regards,
FG

This conversation is currently closed to new comments.

4 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

This might give you some insight to your issue..

Triggers:

Triggers are named PL/SQL blocks that get executed implicitly when a triggering event occurs. Rather that being executed when called (as is the case with procedures and functions), triggers get fired automatically when certain events occur in the system. The action of executing a trigger is called ?firing? the trigger. A trigger fires when a triggering event occurs.
Triggering Events:

Triggering Events are events that occur due to the users? actions (or system events) that cause a trigger to be fired. Triggering events can be insertion, deletion, update etc. When any of these events occurs, it executes the triggers written on that event implicitly.
Types of Triggers:

Although, there may be many types and classifications of triggers, basically, there are three types of triggers:

DML Triggers:

DML triggers are fired by the execution of a DML statement. The DML triggers can be defined on insert, update or delete operations. Whenever a DML operation occurs on a table, the trigger will execute. Also, the triggers can be created in such a way that they get executed either before or after the DML operation occurs.

System Triggers:

System triggers fire when a system event such as a database startup or shutdown happens. System triggers can also be fired on DDL operations such as create table.

Instead-of Triggers:

Instead-of triggers can be defined on operations performed on views only. When you define a instead of trigger on an operation on a view, the trigger code will be executed instead of the operation that fired it. This type of triggers can only be row level.
Syntax for trigger creation:

The syntax for trigger creation is as below:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE/AFTER/INSTEAD OF}triggering_event

[WHEN trigger_condition]

[FOR EACH ROW]

trigger_body;

http://www.exforsys.com/tutorials/oracle-10g/oracle-10g-triggers-and-triggering-events.html

Please post back if you have any more problems or questions.

Collapse -

Sorry - no help there...

by f.gruman In reply to This might give you some ...

Unfortunately, I have already reviewed pretty much all the standard Oracle documentation I can lay my hands on, including the posting you mention as well as direct from Oracle.

Thanks for the help, though.

Regards,
Frank

Back to Hardware Forum
4 total posts (Page 1 of 1)  

Hardware Forums