Re: RAISE_APPLICATION_ERROR

Here's the code:

create table T1 ( name varchar2(20), age integer) /
create or replace function my_function ( name1 varchar2, age integer) return integer is
 new_age integer;
 name_pl varchar2(20);
 nanda_ex EXCEPTION;
begin

        new_age := age*10;
        select name into name_pl from T1 ;
        return new_age;
exception
        when NO_DATA_FOUND then
            dbms_output.put_line('100 - No data found in T1');
            raise_application_error(-20001,'No data found in T1');
        when nanda_ex then
            dbms_output.put_line('100 - Nanda Exception on T1');
            raise_application_error(-20003,'Nanda exception on T1');
        when OTHERS then
            dbms_output.put_line('100 - Other Exception on T1');
            raise_application_error(-20002,'Other exception on T1');

As you see, there are no records in T1, so i expect the select to raise NO_DATA_FOUND exception and to print '100 - No data found in T1'. I am not sure what this raise_application_error would do in this code. But the code should at least print the dbms_output mesg. But that's not happenning. I only see 'No rows selected' which is server mesg.

--

In article <37aea8e5.116341209_at_newshost.us.oracle.com>,
  tkyte_at_us.oracle.com wrote:


> A copy of this was sent to Nandakumar <nanban_at_my-deja.com>


> (if that email address didn't require changing)

> On Thu, 29 Jul 1999 18:52:17 GMT, you wrote:

>

> > Anyone has an idea as to where this PROCEDURE

> > RAISE_APPLICATION_ERROR (number,'mesg') would print the message.

> > The serveroutpt flag is set.

> >

> > When this procedure is invoked from within a function, it does not

> > print the message anywhere.

> >

> > When invoked from SQL prompt this is what happens!

> >                    SQL> execute raise_application_error(-20001,'hi')

> >                    begin raise_application_error(-20001,'hi'); end;

> >

> >                    *

> >                    ERROR at line 1:

> >                    ORA-20001: hi

> >                    ORA-06512: at line 1

> >

> > Anything wrong with the above code?

> >

> > Thanks

>

> lets see the code for your function.  I'll bet it has an exception

 handler with


> a WHEN OTHERS clause and hence the raised error is being caught and


 ignored.


>

> --

> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to

 Oracle8i'...


> Current article is "Part I of V, Autonomous Transactions" updated June


 21'st


>

> Thomas Kyte                   tkyte_at_us.oracle.com


> Oracle Service Industries     Reston, VA   USA

>

> Opinions are mine and do not necessarily reflect those of Oracle

 Corporation


>


--
Nandakumar
Systems Analyst
New York
(N.Kumar_at_rocketmail.com)


Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.