Re: RAISE_APPLICATION_ERROR

A copy of this was sent to Nandakumar <nanban_at_my-deja.com> (if that email address didn't require changing) On Fri, 30 Jul 1999 14:28:55 GMT, you wrote:

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

you left lots of information out of your first post. the most important being that you were calling the function from SQL.

if you execute:

SQL> exec my_function( 'hi', 10 )

it'll show you the error message about NO_DATA_FOUND.

when you use "select my_function from t1", the function my_function *is never getting called*. As you said yourself -- "as you see, there are no records in T1" -- therefore "select ANYTHING from t1" will return "No rows selected" since there were *no rows selected*.

Since t1 is empty, my_function never gets called.

My_function will work if and only if there is 1 row in the table T1 the way you have it code by the way. If there are zero rows and you call my_function -- it'll get the NO_DATA_FOUND. If there are >1 rows, it'll get the OTHERS exception with an error for exact fetch returns too many rows. It'll never ever get the nanda_ex under any circumstances -- that exception will never be raised by anything, anywhere.

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

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