Re: RAISE_APPLICATION_ERROR

From: Nandakumar <nanban_at_my-deja.com>
Date: Fri, 30 Jul 1999 14:28:55 GMT
Message-ID: <7nscqp$g64$1@nnrp1.deja.com>

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');

end;
/
select name, my_function(name,age) age from 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. Received on Fri Jul 30 1999 - 09:28:55 CDT