Re: raise_application_error and when others

  • From: Adric Norris <landstander668@xxxxxxxxx>
  • To: Chuck Boddy <Chuck.Boddy@xxxxxxxxxxxxxxx>
  • Date: Fri, 2 Dec 2011 16:02:12 -0600

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@xxxxxxxxxxxxxxx>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@xxxxxxxxx]
> *Sent:* Wednesday, November 30, 2011 2:38 PM
> *To:* Chuck Boddy
> *Cc:* tim@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
>
> *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)?
>

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


Other related posts: