Oracle-L: Re: Capturing ORA errors and inserting into a table.

From: <G.Plivna_at_itsystems.lv>
Date: Wed, 08 May 2002 06:23:26 -0800
Message-ID: <F001.0045B668.20020508062326@fatcity.com>

OK here is an example that is used in one of our web applications It isn't very simple :-)

I modified (deleted) code a little bit. There were also show error procedures that get some info from error messages table and printed that in user friendly format in browser window.

There may be some comments in Latvian, if You don't understand them :-)

Enjoy !

Firstly table specs
CREATE TABLE MCM_ERROR_SETS
(ERS_ID NUMBER(38,0) NOT NULL
,ERS_TIMESTAMP DATE NOT NULL

)
/

CREATE TABLE MCM_ERRORS

 (ERR_ID  NUMBER(38,0) NOT NULL


 ,ERR_ERS_ID  NUMBER(38,0) NOT NULL

 ,ERR_TIMESTAMP  DATE NOT NULL

 ,ERR_USER  VARCHAR2(30)

 ,ERR_SEQUENCE  NUMBER(4)

 ,ERR_ALLOCATION  VARCHAR2(60)

 ,ERR_CODE  VARCHAR2(60)

 ,ERR_MESSAGE  VARCHAR2(4000)

 ,ERR_CALL_STACK  VARCHAR2(4000)

 ,ERR_ERROR_STACK  VARCHAR2(4000)

 ,ERR_IP_ADDRESS  VARCHAR2(15)

 ,ERR_REFERRER  VARCHAR2(30)

 ,ERR_DESCRIPTION  VARCHAR2(4000)

,ERR_MESSAGE_CODE VARCHAR2(20)
,ERR_ACTION_CODE VARCHAR2(20)

)
/

Main procedure that logs errors is handle, it is called something like that

      EXCEPTION WHEN OTHERS
      THEN

log$errors.handle(v_ref,'log_journal.log.find_slaes_person_id_by_offer_id',
                            'in_ofr_id=' || in_ofr_id || '&v_customer_id='
|| v_customer_id || '&v_sales_id=' || v_sales_id,
                            v_error_rec.success, v_error_rec.error_code,
v_error_rec.error_text);
      END;

CREATE OR REPLACE PACKAGE LOG$ERRORS IS
-- Datastructure Definitions
TYPE ERROR_REC IS RECORD
(SUCCESS INTEGER(1) := 1
,ERROR_CODE INTEGER(9) := 0
,ERROR_TEXT VARCHAR2(2000) := 'Operation Successful'
);

PROCEDURE INIT;
PROCEDURE FLUSH_QUEUE
(IN_EMPTY_QUEUE IN BOOLEAN := TRUE
);

PROCEDURE HANDLE
(
p_ref IN VARCHAR2
,IN_MODULE_NAME IN VARCHAR2
,OUT_SUCCESS_IND OUT INTEGER
,OUT_ERROR_CODE OUT INTEGER
,OUT_ERROR_MESSAGE OUT VARCHAR2
,IN_SHOW_MESSAGE IN BOOLEAN DEFAULT FALSE
,IN_MESSAGE IN VARCHAR2 DEFAULT NULL
,IN_ACTION IN VARCHAR2 DEFAULT NULL
,IN_LANGUAGE IN VARCHAR2 DEFAULT NULL

);

PROCEDURE HANDLE
(
p_ref IN VARCHAR2
,IN_MODULE_NAME IN VARCHAR2
,in_description IN VARCHAR2
,OUT_SUCCESS_IND OUT INTEGER
,OUT_ERROR_CODE OUT INTEGER
,OUT_ERROR_MESSAGE OUT VARCHAR2
,IN_SHOW_MESSAGE IN BOOLEAN DEFAULT FALSE
,IN_MESSAGE IN VARCHAR2 DEFAULT NULL
,IN_ACTION IN VARCHAR2 DEFAULT NULL
,IN_LANGUAGE IN VARCHAR2 DEFAULT NULL

);

type definitions TYPE t_error_rec IS RECORD ( timestamp DATE ,module_name VARCHAR2(1000) ,sql_code INTEGER ,sql_messg VARCHAR2(2000) ); TYPE t_error_queue IS TABLE OF t_error_rec INDEX BY BINARY_INTEGER;
- array definitions a_error_queue t_error_queue;
- simple variable definitions v_error_rec t_error_rec; v_is_init BOOLEAN := FALSE; -- Init flag v_sequence INTEGER; -- error sequence number in the queue v_ers_id mcm_error_sets.ers_id%TYPE; END LOG$ERRORS; /

CREATE OR REPLACE PACKAGE BODY LOG$ERRORS IS

private procedures
initialise table records in mcm_err_sets for current session PROCEDURE init_err_in_table IS PRAGMA autonomous_transaction; BEGIN
- creating error set INSERT INTO mcm_error_sets ( ers_id ,ers_timestamp ) VALUES ( ers_seq.nextval ,sysdate );
- select current foreign key value into global variable SELECT ers_seq.currval INTO v_ers_id FROM dual; COMMIT; END init_err_in_table;
writing real errors PROCEDURE err_in_table ( in_module_name IN VARCHAR2 ,in_error_code IN INTEGER ,in_error_text IN VARCHAR2 ,in_description IN VARCHAR2 ,out_err_id OUT NUMBER ,out_ers_id OUT NUMBER ,in_message_code IN VARCHAR2 DEFAULT NULL ,in_action_code IN VARCHAR2 DEFAULT NULL ) IS PRAGMA autonomous_transaction; v_ip_address mcm_errors.err_ip_address%TYPE; v_referrer mcm_errors.err_referrer%TYPE; v_call_stack mcm_errors.err_call_stack%TYPE; v_error_stack mcm_errors.err_call_stack%TYPE; --
- for tracking sessions not from webserver v_user_audsid NUMBER; CURSOR c_session (in_audsid IN NUMBER) IS SELECT * FROM v$session WHERE audsid = in_audsid; v_session c_session%ROWTYPE;
- refam v_error_rec log$errors.error_rec; v_ref VARCHAR2(200); v_err_id NUMBER; BEGIN
- get call stack info (calling procedure sequence) BEGIN v_call_stack := dbms_utility.format_call_stack; EXCEPTION WHEN OTHERS THEN NULL; END;
- get error stack sequence BEGIN v_error_stack := dbms_utility.format_error_stack; EXCEPTION WHEN OTHERS THEN NULL; END;
- liekas, ka ¡Ôeit SQL*Plus¦£ nomirst
- no¦µekojam environmentu vai saukt ¡Ôeit kot ko vai nea v_user_audsid := userenv('SESSIONID'); -- built in function
- ar ¡Ô¦Å kursora pal¦Ådz¦Åbu tiek noskaidrots vai sesija ir izveidota no webservera vai sql*plusa vai kaukaa tamliidziiga
- tas ir svariigi jo no sqlplus nevar ieg¡Ìt cgi variab¡Éus tipa remote address OPEN c_session (v_user_audsid); FETCH c_session INTO v_session; IF c_session%FOUND THEN IF upper(v_session.osuser) LIKE 'ORACLE%' -- piesl¦Ódzies no servera AND v_session.module IS NULL -- nav SQL*Plus vai kaukas taml¦Ådz¦Ågs AND v_session.terminal IS NULL -- nav servera mened¡öeris, vai SQL*Plus no pa¡Ôa servera THEN
  - ¡Ô¦Õiet ka sesija var¦Ótu b¡Ìt izveidota no webservera BEGIN v_ip_address := owa_util.get_cgi_env('REMOTE_ADDR'); EXCEPTION WHEN OTHERS THEN NULL; END; BEGIN IF v_ref IS NULL THEN v_ref := my_log.connect_to_user(v_error_rec); END IF; v_referrer := log_security.get_user_id(v_ref); EXCEPTION WHEN OTHERS THEN NULL; END; END IF; END IF; CLOSE c_session;
- atrodam id-u, jo to vajag ar¦Å atgriezt BEGIN SELECT err_seq.nextval INTO v_err_id FROM dual; EXCEPTION WHEN OTHERS THEN v_err_id := NULL; END;
- g¦£¡öam iek¡Ô¦£ info INSERT INTO mcm_errors ( err_id ,err_ers_id ,err_timestamp ,err_user ,err_sequence ,err_allocation ,err_code ,err_message ,err_call_stack ,err_error_stack ,err_ip_address ,err_referrer ,err_description ,err_message_code ,err_action_code ) VALUES ( v_err_id ,v_ers_id ,sysdate ,user ,v_sequence ,in_module_name ,in_error_code ,in_error_text ,v_call_stack ,v_error_stack ,v_ip_address ,v_referrer ,in_description ,in_message_code ,in_action_code ); COMMIT; out_err_id := v_err_id; out_ers_id := v_ers_id; END err_in_table; -- function definitions
Sub-Program Units PROCEDURE INIT IS -- PL/SQL Specification -- error handling initialise
- PL/SQL Block BEGIN
  - If queue is not empty then flush it and empty it! flush_queue;
  - create new log file v_sequence := 1; -- Error table index reset init_err_in_table; v_is_init := TRUE; EXCEPTION WHEN OTHERS THEN v_is_init := FALSE; RAISE; END; PROCEDURE FLUSH_QUEUE (IN_EMPTY_QUEUE IN BOOLEAN := TRUE ) IS BEGIN IF in_empty_queue THEN a_error_queue.delete; END iF; v_is_init := FALSE; -- Not initialised any more END; PROCEDURE HANDLE ( p_ref IN VARCHAR2
    ,IN_MODULE_NAME IN VARCHAR2
    ,OUT_SUCCESS_IND OUT INTEGER
    ,OUT_ERROR_CODE OUT INTEGER
    ,OUT_ERROR_MESSAGE OUT VARCHAR2
    ,IN_SHOW_MESSAGE IN BOOLEAN DEFAULT FALSE
    ,IN_MESSAGE IN VARCHAR2 DEFAULT NULL
    ,IN_ACTION IN VARCHAR2 DEFAULT NULL
    ,IN_LANGUAGE IN VARCHAR2 DEFAULT NULL
    ) IS BEGIN handle(p_ref, IN_MODULE_NAME, NULL, OUT_SUCCESS_IND, OUT_ERROR_CODE, OUT_ERROR_MESSAGE, IN_SHOW_MESSAGE, IN_MESSAGE, IN_ACTION); END; -- PROCEDURE HANDLE ( p_ref IN VARCHAR2
    ,IN_MODULE_NAME IN VARCHAR2
    ,in_description IN VARCHAR2
    ,OUT_SUCCESS_IND OUT INTEGER
    ,OUT_ERROR_CODE OUT INTEGER
    ,OUT_ERROR_MESSAGE OUT VARCHAR2
    ,IN_SHOW_MESSAGE IN BOOLEAN DEFAULT FALSE
    ,IN_MESSAGE IN VARCHAR2 DEFAULT NULL
    ,IN_ACTION IN VARCHAR2 DEFAULT NULL
    ,IN_LANGUAGE IN VARCHAR2 DEFAULT NULL
    ) IS
  - PL/SQL Specification v_text_line VARCHAR2(32000); v_error_code INTEGER := SQLCODE; v_error_text VARCHAR2(2000) := SUBSTR(SQLERRM, 1, 2000); v_current_time DATE := SYSDATE; v_err_id NUMBER; v_ers_id NUMBER; v_id_message VARCHAR2(100); v_id_action VARCHAR2(100);
- PL/SQL Block BEGIN IF NOT v_is_init THEN init; END IF;
  - All errors are handled here!
  - Oracle errors go First (Those Oracle errors which require additional or different messaging) IF v_error_code IN (1, 100) THEN NULL; ELSIF v_error_code > -20000 THEN -- Other Oracle errors with no additional preprocessing NULL;
  - Then Follow User defined exceptions ELSIF v_error_code <= -20000 THEN -- User defined exceptions (package log$error_messages) IF log$error_messages.a_messages.exists(0 - (v_error_code + 21000)) THEN v_error_text := translations.system_translation_ref(log$error_messages.a_messages(0 - (v_error_code + 21000)),p_ref); ELSE v_error_text := 'Error Message <' || v_error_code || '> Not Found For User Defined Exception'; END IF; END IF; out_success_ind := 0; out_error_code := v_error_code; out_error_message := v_error_text;
  - Write error log to queue a_error_queue(v_sequence).timestamp := v_current_time; a_error_queue(v_sequence).module_name := in_module_name; a_error_queue(v_sequence).sql_code := v_error_code; a_error_queue(v_sequence).sql_messg := v_error_text;
  - Format error string
- v_text_line := USER || ';' || TO_CHAR(v_current_time, 'YYYYMMDDHH24MISS') || ';' || v_sequence || ';' || in_module_name || ';' ||
- v_error_code || ';' || v_error_text;
  - Write error log to file
- IF NOT utl_file.is_open(v_file) THEN
- v_file := utl_file.fopen(v_file_location, v_file_name, 'A');
- END IF;
- utl_file.put_line(v_file, v_text_line);
- utl_file.fflush(v_file); err_in_table (in_module_name, v_error_code, v_error_text, in_description, v_err_id, v_ers_id, in_message, in_action);
  - queue idx++ v_sequence := v_sequence + 1; END handle; --
print error to HTML -- -- -- -- PL/SQL Block BEGIN NULL; EXCEPTION WHEN OTHERS THEN
- Disaster errors are not handled! v_is_init := FALSE; RAISE; END LOG$ERRORS; / show err

Gints Plivna
IT Sist¨¥mas, Mer?e?a 13, LV1050 R¨©ga
http://www.itsystems.lv/gints/

                                                                                                                                        
                      Denham Eva                                                                                                        
                      <EvaD_at_TFMC.co.za>        To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>                    
                      Sent by:                 cc:                                                                                      
                      root_at_fatcity.com         Subject:  Capturing ORA errors and inserting into a table.                               
                                                                                                                                        
                                                                                                                                        
                      2002.05.08 13:33                                                                                                  
                      Please respond to                                                                                                 
                      ORACLE-L

Hello List

Hope someone can help.
Trying to write a procedure where an event, such as a insert or update is done.
However I would like to capture any oracle error (ORA-......) that is generated, into a table for instance..
i.e.

DECLARE
somevars VARCHAR2(10);
BEGIN

      SELECT stuff
             INTO somevars
      FROM testtbl;

     BEGIN
         INSERT INTO anothertbl
          (col1)
         VALUES(somevars);
     EXCEPTION
       WHEN OTHERS
             *take oracle ORA error and do something*
             *like insert it into a error table. Not just Output to screen.
*
     END;

END; Many Thanks
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.

#####################################################################################

This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal
For more information please visit www.marshalsoftware.com

#####################################################################################

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
  INET: EvaD_at_TFMC.co.za

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


ÿóåy«±ç…ê~'"jS‘
ü¸¬´k«»ÿÚØ§¶¦zËàN
âÉnuæ¥Šwœ¢{ZŠx§ƒõ
I_at_ND

Received on Wed May 08 2002 - 09:23:26 CDT