6 Replies Latest reply on Oct 17, 2014 8:13 AM by Billy~Verreynne

    Catching User Exception name in a variable

    user10566312

      We have a lot of user-exceptions defined in our code as below:

      e_err_20001 exception;

      e_err_20002 exception;

      .

      .

      .

      e_err_20017 exception;

       

      Whenever a business rule fails we call the corresponding exception. Say:

       

      If subscriber_id is null

      then

      raise e_err_20002 ;

      end if;

       

      In the exception block we are assigning the corresponding error message and error code, logging the exception and doing raise_application_error. However, the code is becoming too bulky and repetitive because for every user-exception we need to call the error logging proc and raise_application_error. Can this simplified:

       

      EXCEPTION

      WHEN e_err_20001 THEN

      l_errm := 'some message';

      l_errcode:= -20001;

      err_log(l_errm, l_errcode);

      raise_application_error(l_errm, l_errcode);

      .

      .

      .

      WHEN e_err_20017 THEN

      l_errm := 'some message';

      l_errcode := -20017;

      err_log(l_errm, l_errcode);

      raise_application_error(l_errm, l_errcode);

       

      WHEN OTHERS THEN

       

       

       


        • 1. Re: Catching User Exception name in a variable
          Moazzam

          May be this can help you:

           

          DECLARE

          e_err EXCEPTION;

          l_errm varchar2(100);

          l_errcode number;

           

          BEGIN

              IF subscriber_id IS NULL THEN

                  l_errm := 'subscriber_id is null';

                  l_errcode = -20001

                  RAISE e_err ;

              END IF;

             

              IF client_id IS NULL THEN

                  l_errm := 'client_id is null';

                  l_errcode = -20002

                  RAISE e_err ;

              END IF;

             

          EXCEPTION

          WHEN e_err THEN

              err_log(l_errm, l_errcode);

              raise_application_error(l_errm, l_errcode);   

          END;

          • 2. Re: Catching User Exception name in a variable
            user10566312

            I want to reduce the err_log and Raise_application_error calls in the exception block. It cant be achived through this approach.

            • 3. Re: Catching User Exception name in a variable
              Etbin

              a slight variation

               

                business_rule_exception exception;

              BEGIN

                ...

                IF business_rule_1_failure THEN

                  l_errcode := -20001;

                  raise business_rule_exception;

                END IF;

                ...

                IF business_rule_2_failure THEN

                  l_errcode := -20002;

                  raise business_rule_exception;

                END IF;

                ...

              EXCEPTION

                WHEN business_rule_exception

                THEN CASE

                       WHEN l_errcode = -20001

                       THEN l_errm := 'some message';

                       WHEN l_errcode = -20002

                       THEN l_errm := 'some message';

                       ...

                       ELSE 'message not specified';

                     END CASE;

                     err_log(l_errm, l_errcode);

                     raise_application_error(l_errm,l_errcode);

                WHEN others

                THEN err_log(sqlerrm,sqlcode);

                     raise_application_error(sqlerrm,sqlcode);

              END;

               

              Regards

               

              Etbin

              • 4. Re: Catching User Exception name in a variable
                Marwim

                Hello,

                 

                we have a lot of user defined exceptions and store the information in a table. From this table we generate a package that has definitions for each entry (too_many_rows is just used as an example)

                 

                e_too_many_rows EXCEPTION;
                c_too_many_rows CONSTANT INTEGER := -20997;
                PRAGMA EXCEPTION_INIT (e_too_many_rows, -20997);

                 

                FUNCTION getToo_many_rows

                    RETURN INTEGER;

                The function only returns the value of c_too_many_rows in cases where you cannot reference the package constant directly, e.g. in Forms.

                 

                Then there is a function to check whether an errorcode is registered in our table and one to return the message text

                FUNCTION isNamedException

                    (

                     p_sqlcode              IN named_exceptions.ne_sqlcode%TYPE

                    )

                    RETURN BOOLEAN;

                 

                FUNCTION getMessagetext

                    (

                     p_sqlcode              IN named_exceptions.ne_sqlcode%TYPE

                    ,p_arg                  IN VARCHAR2

                    )

                    RETURN named_exceptions.ne_message%TYPE;

                 

                The exception handler for a procedure could look like

                EXCEPTION

                  WHEN OTHERS THEN

                    IF err.isNamedException(SQLCODE) THEN

                      <insert into log table err.getMessagetext(SQLCODE,SQLERRM)>

                    ELSE

                      <insert into log table dbms_utility.format_error_stack()>

                    END IF;

                    RAISE;

                 

                Regards

                Marcus

                • 5. Re: Catching User Exception name in a variable
                  GregV

                  Hi,

                   

                  If want to raise user-defined exceptions, then you have no choice but use the RAISE <user_defined_excpetion_name> statement. Moazzam's approach simplifies your code as it reduces your exception handler.

                  • 6. Re: Catching User Exception name in a variable
                    Billy~Verreynne

                    It can easily be simplified using the object orientation features of SQL and PL/SQL - by defining an exception class. And have this class provide an abstraction interface to exceptions, and provide means for validating conditions that typically are used to raise exceptions (aka assertions).

                     

                    For example, instead of repeated IF conditions likes this to test validity of continuing with processing:

                    if some-condition is false then
                      raise_application_error( -hard_coded_error_no, 'hardcoded error message' );
                    end if;
                    
                    
                    
                    

                    This:

                    object.assert( some-condition1, error_constant1 );
                    object.assert( some-condition2, error_constant2 );
                    etc.
                    
                    
                    

                     

                     

                    Basic example:

                    SQL> create or replace type TString is table of varchar2(4000);
                      2  /
                    
                    Type created.
                    
                    SQL>
                    SQL> create or replace type TException authid current_user is object(
                      2          errorMessages  TString,
                      3
                      4          static procedure Assert( condition boolean, errCode integer, errMessage varchar2 ),
                      5          static procedure RaiseError( errCode integer, errMessage varchar2 ),
                      6
                      7          member function FormatMessage( errMessage varchar2, errParams TString ) return varchar2,
                      8          member procedure Assert( condition boolean, errCode integer, errParams TString default null ),
                      9          member procedure RaiseError( errCode integer, errParams TString default null )
                    10  );
                    11  /
                    
                    Type created.
                    
                    SQL>
                    SQL> create or replace type body TException as
                      2
                      3          static procedure Assert( condition boolean, errCode integer, errMessage varchar2 ) is
                      4          begin
                      5                  if not condition then
                      6                          TException.RaiseError(
                      7                                  errCode,
                      8                                  nvl(errMessage,'Unknown custom error occurred')
                      9                          );
                    10                  end if;
                    11          end;
                    12
                    13          static procedure RaiseError( errCode integer, errMessage varchar2 ) is
                    14          begin
                    15                  raise_application_error(
                    16                          -20000 - errCode,
                    17                          errMessage
                    18                  );
                    19          end;
                    20
                    21          member procedure RaiseError( errCode integer, errParams TString default null ) is
                    22          begin
                    23                  TException.RaiseError(
                    24                          errCode,
                    25                          self.FormatMessage( self.errorMessages(errCode), errParams )
                    26                  );
                    27          end;
                    28
                    29          member function FormatMessage( errMessage varchar2, errParams TString ) return varchar2 is
                    30                  formattedMessage        varchar2(32767);
                    31          begin
                    32                  formattedMessage := errMessage;
                    33                  if errParams is not null then
                    34                          for i in 1..errParams.Count() loop
                    35                                  formattedMessage := replace(
                    36                                                          formattedMessage,
                    37                                                          '%s'||trim(to_char(i)),
                    38                                                          errParams(i)
                    39                                                  );
                    40                          end loop;
                    41                  end if;
                    42
                    43                  return( formattedMessage );
                    44          end;
                    45
                    46
                    47          member procedure Assert( condition boolean, errCode integer, errParams TString default null ) is
                    48          begin
                    49                  if not condition then
                    50                          self.RaiseError(
                    51                                  errCode,
                    52                                  errParams
                    53                          );
                    54                  end if;
                    55          end;
                    56
                    57
                    58  end;
                    59  /
                    
                    Type body created.
                    
                    
                    
                    
                    SQL>
                    SQL> -- assertion that raises an exception if assertion fails,
                    SQL> -- using the class method of Type Exception
                    SQL> begin
                      2          TException.assert(
                      3                  1 > 1,                          -- assertion
                      4                  1,                              -- app error code, translates into -20001
                      5                  'Assertion 1 > 1 is false.'    -- error message
                      6          );
                      7
                      8          -- e.g. more assertions to ensure parameters/environment/etc is valid
                      9          -- for continued execution of process code
                    10  end;
                    11  /
                    begin
                    *
                    ERROR at line 1:
                    ORA-20001: Assertion 1 > 1 is false.
                    ORA-06512: at "BILLY.TEXCEPTION", line 15
                    ORA-06512: at "BILLY.TEXCEPTION", line 6
                    ORA-06512: at line 2
                    
                    
                    SQL>
                    SQL>
                    SQL> -- raising an exception using class method - e.g. turning a NO_DATA_FOUND
                    SQL> -- exception into a more meaningfull application exception
                    SQL> declare
                      2          id      integer;
                      3  begin
                      4          select object_id into id from user_objects where 1 = 2;
                      5  exception when NO_DATA_FOUND then
                      6          TException.RaiseError(
                      7                  1,                                                      -- app error code
                      8                  'Predicate for USER OBJECTS did not return any data.'  -- error message
                      9          );
                    10  end;
                    11  /
                    declare
                    *
                    ERROR at line 1:
                    ORA-20001: Predicate for USER OBJECTS did not return any data.
                    ORA-06512: at "BILLY.TEXCEPTION", line 15
                    ORA-06512: at line 6
                    
                    
                    SQL>
                    SQL>
                    SQL>
                    SQL> -- more formal use of class, instantiating a TException object and
                    SQL> -- defining your custom app error messages
                    SQL> declare
                      2          -- constants for use in code (e.g. ERROR_EMP_NOT_FOUND, etc)
                      3          ERROR_1        constant integer := 1;
                      4          ERROR_2        constant integer := 2;
                      5
                      6          -- instantiating exception object and define 2 custom app
                      7          -- error messages
                      8          local          TException :=
                      9          new TException(
                    10                  TString(
                    11                          'This is error 1.',
                    12                          'This is error 2. Foo: %s1. Sub-foo: %s2'
                    13                  )
                    14          );
                    15
                    16          procedure Example1 is
                    17          begin
                    18                  local.assert(
                    19                          1 > 1,          -- if assertion failes then:
                    20                          ERROR_1        -- app error 1 occurs
                    21                  );
                    22          exception when OTHERS then
                    23                  -- display error message that will result
                    24                  dbms_output.put_line( SQLERRM );
                    25          end;
                    26
                    27          procedure Example2 is
                    28          begin
                    29                  local.assert(
                    30                          1 > 1,
                    31                          ERROR_2,
                    32                          TString( 'Anonymous Block', 'Example2' )  -- variables for error message
                    33                  );
                    34          exception when OTHERS then
                    35                  dbms_output.put_line( SQLERRM );
                    36          end;
                    37
                    38  begin
                    39          Example1;
                    40          Example2;
                    41  end;
                    42  /
                    ORA-20001: This is error 1.
                    ORA-20002: This is error 2. Foo: Anonymous Block. Sub-foo: Example2
                    
                    PL/SQL procedure successfully completed.
                    
                    SQL>
                    
                    
                    

                     

                    Similar classes exist in most modern languages.