use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger. : dbms_utility « System Packages « Oracle PL / SQL

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » System Packages » dbms_utility 




use DBMS_UTILITY.FORMAT_ERROR_STACK in a SERVERERROR trigger.
   
SQL>
SQL>
SQL> CREATE TABLE error_log (
  2    timestamp     DATE,
  3    username      VARCHAR2(30),
  4    instance      NUMBER,
  5    database_name VARCHAR2(50),
  6    error_stack   VARCHAR2(2000)
  7    );

Table created.

SQL>
SQL> CREATE OR REPLACE TRIGGER LogErrors
  2    AFTER SERVERERROR ON DATABASE
  3  BEGIN
  4    INSERT INTO error_log
  5      VALUES (SYSDATE, SYS.LOGIN_USER, SYS.INSTANCE_NUM, SYS.
  6              DATABASE_NAME, DBMS_UTILITY.FORMAT_ERROR_STACK);
  7  END LogErrors;
  8  /

Trigger created.

SQL>
SQL> SELECT FROM non_existent_table;
SELECT FROM non_existent_table
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>
SQL> BEGIN
  2    INSERT INTO non_existent_table VALUES ('Hello!');
  3  END;
  4  /
  INSERT INTO non_existent_table VALUES ('Hello!');
              *
ERROR at line 2:
ORA-06550: line 2, column 15:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored


SQL> COLUMN error_stack FORMAT a61 WRAPPED
SQL> SET LINE 80
SQL>
SQL> SELECT FROM error_log;

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
18-JUN-08 JAVA2S                                  1
XE
ORA-00942: table or view does not exist

18-JUN-08 JAVA2S                                  1
XE
ORA-06550: line 2, column 15:

TIMESTAMP USERNAME                         INSTANCE
--------- ------------------------------ ----------
DATABASE_NAME
--------------------------------------------------
ERROR_STACK
-------------------------------------------------------------
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored


SQL>
SQL> DROP TABLE error_log;

Table dropped.

SQL>
SQL> drop trigger LOGERRORS;

Trigger dropped.

SQL>

   
    
  














Related examples in the same category
1.This script demonstrates DBMS_UTILITY.NAME_TOKENIZE.
2.Use dbms_utility to time
3.time to commit
4.dbms_utility.get_parameter_value
5.Call dbms_utility.get_time twice to time a statement in PL SQL
6.round dbms_utility.get_time
7.Assign dbms_utility.get_time to integer variable
8.Use dbms_utility.get_time to do performace check
9.dbms_utility.format_error_stack
10.Timing Per Thousand Records Processed (in secs)
11.Timing Package function call
12.DBMS_UTILITY.analyze_schema
13.demonstrates the use of DBMS_UTILITY.TABLE_TO_COMMA and DBMS_UTILITY.COMMA_TO_TABLE.
14.Performace difference between simple parameter and collection parameter
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.