Re: raise_application_error and when others

From: Adric Norris <landstander668_at_gmail.com>
Date: Fri, 2 Dec 2011 16:02:12 -0600
Message-ID: <CAJueESr_DxFo40-kPeJ5tkSz5=Sx1hoF1t=RPxSPQu-VOgR8ig_at_mail.gmail.com>

Actually, I think it demonstrates the opposite... again, unless I'm missing something. The *raise_application_error* invocation simply throws an exception (rather than exiting the program unit), which is then propagated just like any other.
If you need to capture and log arbitrary errors using WHEN OTHERS, then you'll have to re-raise the one you're don't want to suppress from within the exception handler. Something like this:

SQL> create table log (

  2     time   date         default sysdate not null,
  3     errmsg varchar2(50) not null

  4 );

Table created.

SQL> declare
  2

  3     procedure log_error (p_errno IN number, p_errmsg IN varchar2) is
  4        pragma autonomous_transaction;
  5     begin
  6        insert into log (errmsg) values (p_errmsg);
  7        commit;
  8     end log_error;

  9
 10 begin
 11 raise_application_error(-20400, 'This is bad... real bad.');  12
 13 exception

 14     when others then
 15        if SQLCODE != -20400 then
 16           log_error(SQLCODE, SQLERRM);
 17        else
 18           raise;
 19        end if;

 20 end;
 21 /
declare
*
ERROR at line 1:
ORA-20400: This is bad... real bad.
ORA-06512: at line 18

SQL> select * from log;

no rows selected

That's my best guess based upon the available information, at any rate.

On Wed, Nov 30, 2011 at 16:52, Chuck Boddy <Chuck.Boddy_at_gettyimages.com>wrote:

> Ahh…Adric…thank you…a very simple but precise test for this..i wish I
> would have thought of itJ****
>
> ** **
>
> Thank you…perfect…proves my point,****
>
> Chuck****
>
> ** **
>
> *From:* Adric Norris [mailto:landstander668_at_gmail.com]
> *Sent:* Wednesday, November 30, 2011 2:38 PM
> *To:* Chuck Boddy
> *Cc:* tim_at_evdbt.com; oracle-l_at_freelists.org
>
> *Subject:* Re: raise_application_error and when others****
>
> ** **
>
> I think that's normal behaviour... in essence, *raise_application_error*is just a slightly fancier version of
> *raise*.****
>
> SQL> begin
> 2 raise_application_error(-20400, 'This is bad... real bad.');
> 3 exception
> 4 when others then
> 5 dbms_output.put_line('Panic!');
> 6 end;
> 7 /
> Panic!
>
> PL/SQL procedure successfully completed.****
>
>
> Of course, it's always possible that I'm misinterpreting something. Any
> chance of sharing the relevant code snippet(s)?
>

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Dec 02 2011 - 16:02:12 CST