sql - tipos - ¿Cómo registrar todas las excepciones en el paquete de Oracle?

sqlerrm oracle ejemplos

Question

Estoy intentando registrar todas las excepciones en un paquete de Oracle. Esto es lo que tengo al final del procedimiento:

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

Esto funciona bien, sin embargo, también quiero registrar el código de error y el mensaje. He intentado:

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

Pero esto me da el error:

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

¿Cuál es la forma correcta de hacer esto? ¡Gracias!

Answer 1

No puede usar SQLERRM directamente; debe asignarlo a una variable intermedia. Tenga en cuenta que Oracle 9i le permitiría salirse con la suya, pero ese ha sido siempre el comportamiento documentado . Vea aquí para obtener un código de muestra.

También podría considerar envolver este bit en una transacción autónoma, por lo que se registra incluso si la transacción de su código PL / SQL se retrotrae.

Answer 2

Gaius te dio la respuesta corta. Su comentario sobre envolverlo en una transacción autónoma es muy importante. Lamentarás el día en que tu transacción se retiró, y no sabes por qué.

Aquí hay algo que muestra el uso de una transacción autónoma con algunos detalles adicionales para conocer un montón más acerca de dónde se produjo el error.

Su bloque de excepción se convierte en algo como esto: -

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, aquí está todo el código que necesita para apoyar esto. Juega con él, es útil :-)

-- 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.
   -- i.e 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;
/

Answer 3

Nunca use SQLERRM o SQLCODE .

Siempre use dbms_utility.format_error_stack||dbms_utility.format_error_backtrace , o algo similar.

El registro de excepciones sin almacenar el número de línea es simplemente cruel.