How to register all exceptions in Oracle package?

Question

I am trying to register all exceptions in an Oracle package. Here is what I have at the end of the procedure:

EXCEPTION WHEN OTHERS THEN INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception'); 

This works great, however I also want to write down the error code and message. I tried:

 EXCEPTION WHEN OTHERS THEN INSERT INTO VSLogger (MESSAGE) VALUES ('Caught Exception: Error ' || SQLCODE || ', Msg: ' || SQLERRM); 

But this gives me an error:

 490/7 PL/SQL: SQL Statement ignored 490/100 PL/SQL: ORA-00984: column not allowed here 

What is the right way to do this? Thanks!

Answer 1

You cannot directly use SQLERRM - you need to assign it to an intermediate variable. Note that Oracle 9i will allow you to avoid this, but this has always been documented behavior . See here for sample code.

You may also consider moving this bit into an offline transaction, so it is logged even if your PL / SQL code transaction is canceled.

Answer 2

Never use SQLERRM or SQLCODE . Registering exceptions without saving the line number is cruel.

Always use dbms_utility.format_error_stack||dbms_utility.format_error_backtrace or something similar.

For example, the following block shows an error, but not where it occurred:

 declare v_number number; begin v_number := 1/0; exception when others then dbms_output.put_line(sqlerrm); end; / DBMS Output: ORA-01476: divisor is equal to zero 

This block shows both the error and where it occurred. This is important information for troubleshooting any non-trivial program.

 declare v_number number; begin v_number := 1/0; exception when others then dbms_output.put_line(dbms_utility.format_error_stack||dbms_utility.format_error_backtrace); end; / DBMS Output: ORA-01476: divisor is equal to zero ORA-06512: at line 4 

---

(The following is general advice on handling exceptions.)

For real code, you do not want to simply display error information. In fact, the best exception handling strategy usually is to do nothing, and use Oracle's default behavior to display all error messages and line numbers.

Handling custom exceptions in Oracle is usually only useful in one of the following three cases:

1. You are going to do something with a specific error, for example, call another procedure, simply ignoring the error.
2. If this is a database-only program, then you may need to catch and log exceptions at the top of the program, at a (limited) number of entry points. You do not need exception handling if the application already catches everything or for internal code. Exceptions propagate up the stack; it is best to catch them at the end.
3. If you need to register a specific value that exists only as a local variable, it must be registered immediately, since this value will not be propagated with an exception.

There is a lot of PL / SQL code that blindly catches and logs errors in each procedure. This is unnecessary and usually counterproductive.

Answer 3

Guy gave you a short answer. His comment on wrapping him in an autonomous transaction is very important. You will lose the day your transaction returns and you do not know why.

Something is shown here using an autonomous transaction with a few additional details that you have chosen so that you know the whole chain more about where your error occurred.

The exception block will look something like this: -

 exception when exception_pkg.assertion_failure_exception then rollback; raise; when others then rollback; v_code := SQLCODE; v_errm := SUBSTR(SQLERRM, 1, 255); exception_pkg.throw( exception_pkg.unhandled_except, v_code || ' - ' || v_errm || ' ($Header$)' ); 

... aaaand, here is all the code you need to support this. Play with him, this is useful :-)

 -- Create a table to hold the error messages CREATE TABLE ERROR_MESSAGES ( ERROR_MESSAGE_ID NUMBER(10) NOT NULL, ERROR_DATE TIMESTAMP(6) DEFAULT SYSDATE NOT NULL, ERROR_USER VARCHAR2(30 BYTE) DEFAULT USER NOT NULL, MESSAGE_TYPE VARCHAR2(15 BYTE), PACKAGE_NAME VARCHAR2(250 BYTE), PROCEDURE_OR_LINE VARCHAR2(30 BYTE), ERROR_CODE VARCHAR2(10 BYTE), ERROR_MESSAGE1 VARCHAR2(4000 BYTE), ERROR_MESSAGE2 VARCHAR2(4000 BYTE), ERROR_MESSAGE3 VARCHAR2(4000 BYTE), ERROR_MESSAGE4 VARCHAR2(4000 BYTE) ); CREATE UNIQUE INDEX ERROR_MESSAGES_XPK ON ERROR_MESSAGES (ERROR_MESSAGE_ID); -- Create the sequence used for the ERROR_MESSAGES PK CREATE SEQUENCE ERROR_MESSAGE_SEQ START WITH 1; -- The package CREATE OR REPLACE PACKAGE EXCEPTION_PKG as /************************************************************************************ * $Header$ * * Package: exception_pkg * * Purpose: Exception handling functionality * * Authors: M.McAllister (via AskTom - http://tinyurl.com/c43jt) * * Revision History: * * $Log[10]$ ******************************************************************************************/ /*===================================================================== * Constants *=====================================================================*/ c_InfMsg constant error_messages.message_type%type := 'Informational'; c_WarnMsg constant error_messages.message_type%type := 'Warning'; c_ErrMsg constant error_messages.message_type%type := 'Fatal Error'; c_DbgMsg constant error_messages.message_type%type := 'Debug'; c_MaintMsg constant error_messages.message_type%type := 'Maintenance'; /*===================================================================== * Exception Definitions *=====================================================================*/ unhandled_except constant number := -20001; unhandled_except_exception exception; pragma exception_init(unhandled_except_exception, -20001); bad_parameter constant number := -20002; bad_parameter_exception exception; pragma exception_init(bad_parameter_exception, -20002); assertion_failure constant number := -20003; assertion_failure_exception exception; pragma exception_init(assertion_failure_exception, -20003); /*===================================================================== * Procedures *=====================================================================*/ procedure write_exception_info( p_msg_type error_messages.message_type%type , p_pkg_name error_messages.package_name%type , p_func_name error_messages.procedure_or_line%type , p_error_code error_messages.error_code%type , p_msg1 error_messages.error_message2%type , p_msg2 error_messages.error_message3%type , p_msg3 error_messages.error_message4%type ); procedure who_called_me( p_owner out varchar2, p_name out varchar2, p_lineno out number, p_caller_t out varchar2, p_my_depth in number default 3 ); procedure throw( p_exception in number , p_extra_msg in varchar2 default NULL ); end exception_pkg; / -- Package Body CREATE OR REPLACE PACKAGE BODY EXCEPTION_PKG as /************************************************************************************ * $Header$ * * Package: exception_pkg * * Purpose: Exception handling functionality * * Authors: M.McAllister (via AskTom - http://tinyurl.com/c43jt) * * Revision History: * * $Log[10]$ ******************************************************************************************/ /*===================================================================== * Types *=====================================================================*/ type myArray is table of varchar2(255) index by binary_integer; /*===================================================================== * Globals *=====================================================================*/ err_msgs myArray; /*===================================================================== * Procedures *=====================================================================*/ procedure who_called_me( p_owner out varchar2, p_name out varchar2, p_lineno out number, p_caller_t out varchar2, p_my_depth in number default 3 ) as call_stack varchar2(4096) default dbms_utility.format_call_stack; n number; found_stack BOOLEAN default FALSE; line varchar2(255); cnt number := 0; begin loop n := instr( call_stack, chr(10) ); exit when ( cnt = p_my_depth or n is NULL or n = 0 ); line := substr( call_stack, 1, n-1 ); call_stack := substr( call_stack, n+1 ); if ( NOT found_stack ) then if ( line like '%handle%number%name%' ) then found_stack := TRUE; end if; else cnt := cnt + 1; -- cnt = 1 is ME -- cnt = 2 is MY Caller -- cnt = 3 is Their Caller if ( cnt = p_my_depth ) then p_lineno := to_number(substr( line, 13, 6 )); line := substr( line, 21 ); if ( line like 'pr%' ) then n := length( 'procedure ' ); elsif ( line like 'fun%' ) then n := length( 'function ' ); elsif ( line like 'package body%' ) then n := length( 'package body ' ); elsif ( line like 'pack%' ) then n := length( 'package ' ); elsif ( line like 'anonymous%' ) then n := length( 'anonymous block ' ); else n := null; end if; if ( n is not null ) then p_caller_t := ltrim(rtrim(upper(substr( line, 1, n-1 )))); else p_caller_t := 'TRIGGER'; end if; line := substr( line, nvl(n,1) ); n := instr( line, '.' ); p_owner := ltrim(rtrim(substr( line, 1, n-1 ))); p_name := ltrim(rtrim(substr( line, n+1 ))); end if; end if; end loop; end who_called_me; /*===================================================================== * PRIVATE function: get_session_info * purpose: Returns a formatted string containing some information * about the current session *=====================================================================*/ function get_session_info return varchar2 is l_sessinfo varchar2(2000); begin select '[SID = ' || sid || '], ' || '[SERIAL# = ' || serial# ||'], ' || '[MACHINE = ' || replace(machine,chr(0),'') || '], ' || '[OSUSER = ' || osuser || '], ' || '[PROGRAM = ' || program || '], ' || '[LOGON_TIME = ' || to_char(logon_time,'mm/dd/yyyy hh:mi:ss') || ']' into l_sessinfo from v$session WHERE audsid = SYS_CONTEXT('userenv','sessionid'); return l_sessinfo; end get_session_info; /*===================================================================== * procedure: write_exception_info * purpose: Call the exception logging routine *=====================================================================*/ procedure write_exception_info( p_msg_type error_messages.message_type%type , p_pkg_name error_messages.package_name%type , p_func_name error_messages.procedure_or_line%type , p_error_code error_messages.error_code%type , p_msg1 error_messages.error_message2%type , p_msg2 error_messages.error_message3%type , p_msg3 error_messages.error_message4%type ) is -- This procedure is autonomous from the calling procedure. -- ie The calling procedure does not have to be complete -- for this procedure to commit its changes. pragma autonomous_transaction; l_sessinfo varchar2(2000); begin l_sessinfo := get_session_info; insert into error_messages ( error_message_id , error_date , error_user , message_type , package_name , procedure_or_line , error_code , error_message1 , error_message2 , error_message3 , error_message4 ) values ( error_message_seq.nextval , sysdate , USER , p_msg_type , p_pkg_name , p_func_name , p_error_code , l_sessinfo , p_msg1 , p_msg2 , p_msg3 ); commit; exception when others then -- We don't want an error logging a message to -- cause the application to crash return; end write_exception_info; procedure throw( p_exception in number , p_extra_msg in varchar2 default NULL ) is l_owner varchar2(30); l_name varchar2(30); l_type varchar2(30); l_line number; l_exception number; begin who_called_me( l_owner, l_name, l_line, l_type ); write_exception_info( c_ErrMsg , l_owner || '.' || l_name , 'Line ' || l_line , p_exception , p_extra_msg , NULL , err_msgs(p_exception) ); raise_application_error( p_exception , 'Exception at ' || l_type || ' ' || l_owner || '.' || l_name || '(' || l_line || '). ' || err_msgs(p_exception ) || '. ' || p_extra_msg , TRUE ); exception -- we will get this when we have an invalid exception code, one -- that was not set in the err_msgs array below. The plsql table -- access will raise the NO-DATA-FOUND exception. We'll catch it, -- verify the exception code is in the valid range for raise_application_error -- (if not, set to -20000) and then raise the exception with the message -- "unknown error" when NO_DATA_FOUND then if ( p_exception between -20000 and -20999 ) then l_exception := p_exception; else l_exception := -20000; end if; write_exception_info( c_ErrMsg , l_owner || '.' || l_name , 'Line ' || l_line , p_exception , p_extra_msg , NULL , '**UNKNOWN ERROR**' ); raise_application_error( l_exception , 'Exception at ' || l_type || ' ' || l_owner || '.' || l_name || '(' || l_line || '). ' || '**UNKNOWN ERROR**' || '. ' || p_extra_msg , TRUE ); end throw; begin -- This code is run once per session when this package is first touched err_msgs( unhandled_except ) := 'Unhandled exception'; err_msgs( bad_parameter ) := 'Invalid parameter passed into function or procedure'; err_msgs( assertion_failure ) := 'Program execution stopped due to assertion failure'; end exception_pkg; /