Oracle-L: Re:PL/Sql Error Handling Package

From: <dgoulet_at_vicr.com>
Date: Thu, 11 Jul 2002 13:33:37 -0800
Message-ID: <F001.00495BC6.20020711133337@fatcity.com>

Hanah,

This is one procedure out of an entire package, but it works for us.

procedure oracle_err(message in varchar2, status out number, mailing_list in varchar2 default 'NONE' ) is

   file_hndl utl_file.file_type;
   luser varchar2(40);
   sname varchar2(40);
   sn varchar2(7);
   fname varchar2(40);
   err_date varchar2(20);
   begin
   status := sqlcode;
   if(status < 0) then

select user, name, to_char(err_file_sqnc.nextval), to_char(sysdate,'DD-MON-YYYY HH24:MI')

      into luser, sname, sn, err_date
      from oracle_server;
      fname := 'oracle'||sn||'.err';
      file_hndl := utl_file.fopen(utl_home,fname,'a');
      utl_file.putf(file_hndl, 'Oracle Error Report from %s\n%s\n', sname,
err_date);
      utl_file.putf(file_hndl, 'Error: %s\nLocation: %s', sqlerrm, message);
      utl_file.fflush(file_hndl);
      utl_file.fclose(file_hndl);
      rollback;
      if(mailing_list != 'NONE') then
        cron_mail_list(mailing_list, fname, 'Oracle Procedure Error');
      end if;

end if;
end;

Dick Goulet

____________________Reply Separator____________________
Author: johanna.doran_at_sungard.com
Date:       7/11/2002 12:52 PM

Amy one have any decent stanard error handling packages or link to advice on creating such a package?

Thanks,

Hannah
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: johanna.doran_at_sungard.com

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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: dgoulet_at_vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------