Exception object

An Exception object you can pass around between procedures. It was a bit of an experiment and I haven't found a real use for it yet, but maybe that's just because I've been working on warehouse systems for a while. Let me know if you find a use for it!

-- EXCEPTION object type
-- William Robertson 2003, www.williamrobertson.net

create or replace type exception_ot as object
( message     varchar2(32767)
, error_code  integer
, module      varchar2(30)
, action      varchar2(100)
, os_logdir   varchar2(80)
, os_logfile  varchar2(70)
, raised      timestamp
, member procedure include_text
  ( p_word1              varchar2 default null
  , p_word2              varchar2 default null
  , p_word3              varchar2 default null )
, member procedure log
  ( p_log_to_database_yn varchar2 default 'Y'
  , p_log_to_file_yn     varchar2 default 'N'
  , p_send_mail_yn       varchar2 default 'N' )
, member procedure raise
  ( p_log_to_database_yn varchar2 default 'N'
  , p_log_to_file_yn     varchar2 default 'N'
  , p_send_mail_yn       varchar2 default 'N' )
, constructor function exception_ot
  ( p_message            varchar2
  , p_error_code         integer  default -20000
  , p_module             varchar2 default null
  , p_action             varchar2 default null
  , p_logdir             varchar2 default null
  , p_logfile            varchar2 default null )
  return self as result
, member procedure print )
/

show errors

create or replace type body exception_ot
as
   member procedure include_text
      ( p_word1              varchar2 default null
      , p_word2              varchar2 default null
      , p_word3              varchar2 default null )
   is
   begin
      message := replace(replace(replace(message,'[1]',p_word1),'[2]',p_word2),'[3]',p_word3);
   end;


   member procedure log
      ( p_log_to_database_yn varchar2 default 'Y'
      , p_log_to_file_yn     varchar2 default 'N'
      , p_send_mail_yn       varchar2 default 'N' )
   is
   begin
      if upper(substr(p_log_to_database_yn,1,1)) = 'Y' then
         logger.write(message, module, action);
      end if;

      if upper(substr(p_log_to_file_yn,1,1)) = 'Y' then
         logger.write(message, module, action);
      end if;

      if upper(substr(p_send_mail_yn,1,1)) = 'Y' then
         logger.write(message, module, action);
      end if;
   end;


   member procedure raise
      ( p_log_to_database_yn varchar2 default 'N'
      , p_log_to_file_yn     varchar2 default 'N'
      , p_send_mail_yn       varchar2 default 'N' )
   is
   begin
      raise_application_error
      ( nvl(error_code,-20000)
      , message
      , true );
   end;


   constructor function exception_ot
      ( p_message            varchar2
      , p_error_code         integer  default -20000
      , p_module             varchar2 default null
      , p_action             varchar2 default null
      , p_logdir             varchar2 default null
      , p_logfile            varchar2 default null )
      return self as result
   is
      v_module varchar2(30);
      v_action varchar2(100);
      v_dummy  pls_integer;
   begin
      message    := p_message;
      error_code := nvl(p_error_code,-20000);

      if p_logdir is not null then
         begin
            select directory_name into os_logdir
            from   all_directories
            where  p_logdir in (directory_name, directory_path)
            order by
                   decode(p_logdir, directory_name, 1,2);

            begin
               select 1 into v_dummy
               from   user_tab_privs_recd
               where  table_name = self.os_logdir
               and    privilege = 'WRITE';
            exception
               when no_data_found then
                  raise_application_error
                  ( -20999
                  , 'user ' || user || ' lacks write permission to directory "' || p_logdir || '".' );
            end;
         exception
            when no_data_found then
               raise_application_error
               ( -20999
               , 'No such directory "' || p_logdir ||
                 '" defined in database.' );
         end;
      end if;

      os_logfile := p_logfile;

      raised     := systimestamp;

      if p_module is null
      or p_action is null
      then
         dbms_application_info.read_module(v_module, v_action);
         module := nvl(p_module,v_module);
         action := coalesce(p_action,v_action,'Unknown');
      end if;
      return;
   end;


   member procedure print
   is
   begin
      echo(error_code || ': ' || message);
   end;
end;
/

show errors

create or replace package error
as
   e_general constant exception_ot := exception_ot('An error occurred', -20001);
   e_no_such constant exception_ot := exception_ot('No such [1]', -20002);
   e_job     constant exception_ot := exception_ot('Job submit error', -20003);
end error;
/

prompt demo exception_ot:

create or replace procedure test_exception
   ( p_test varchar2 )
as
   e_no_such_thing exception_ot := error.e_no_such;
   v_dummy varchar2(1);
begin
   select dummy into v_dummy from dual where dummy = p_test;
exception
   when no_data_found then
      -- e_no_such_thing.message := sqlerrm;
      e_no_such_thing.include_text('dummy "' || p_test || '"');
      e_no_such_thing.print();
      e_no_such_thing.raise();
end;
/

PROMPT Success:
exec test_exception('X')

PROMPT Failure:
exec test_exception('Y')

Source code: //www.williamrobertson.net/code/exception_ot.typ