Re: FW: any idea what are we missing ?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 20 Sep 2006 19:45:10 +0200
Message-ID: <45117E26.4010604@gmail.com>

Powell, Mark D schrieb:
> What version of Oracle and is the code contained within a Pro* language
> program. The error code returned was and probably still is different in
> Pro*C than from pl/sql executed within SQLPlus depending on the setting
> on a compiler parameter. See below:
>
> $ oerr ora 01403
> 01403, 00000, "no data found"
> // *Cause:
> // *Action:
> $ oerr ora 00100
> 00100, 00000, "no data found"
> // *Cause: An application made reference to unknown or inaccessible
> data.
> // *Action: Handle this condition within the application or make
> appropriate
> // modifications to the application code.
> // NOTE: If the application uses Oracle-mode SQL instead of
> // ANSI-mode SQL, ORA-01403 will be generated instead of
> ORA-00100.
>
> PL/SQL executed from SQLPLUS (9.2.0.6 on AIX 5.2L)
> UT1 > @test
> UT1 > set echo on
> UT1 > declare
> 2 v_receiver number;
> 3 begin
> 4 select 1 into v_receiver from sys.dual where 1 = 0;
> 5 end;
> 6 /
> declare
> *
> ERROR at line 1:
> ORA-01403: no data found
> ORA-06512: at line 4
>
> HTH -- Mark D Powell --
>

I tested with 8.1.7.4, 9.2.0.6,10.2.0.2 on Linux, results are identical. The same code from within anonymous block or stored procedure work as expected.
Not expected behaviour is only with function called from SQL. Here is the test:

scott_at_ORA102> create or replace function f1   2 return number
  3 is
  4 v_receiver number;
  5 begin
  6 select 1 into v_receiver from sys.dual where 1 = 0;   7 return v_receiver;
  8 end;
  9 /

Function created.

scott_at_ORA102> REM notice, exception is not raised
scott_at_ORA102> REM and this is not expected behaviour
scott_at_ORA102> select f1 from dual;

        F1

scott_at_ORA102> REM however if function is called from scott_at_ORA102> REM within anonymous block, then exception is raised as expected
scott_at_ORA102> declare
  2 v_num number;
  3 begin
  4 v_num := f1;
  5 end;
  6 /
declare
*
ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "SCOTT.F1", line 6
ORA-06512: at line 4


scott_at_ORA102> REM and if function is created with exception handler

scott_at_ORA102> REM then exception is handled as expected scott_at_ORA102> create or replace function f1   2 return number
  3 is
  4 v_receiver number;
  5 begin
  6 select 1 into v_receiver from sys.dual where 1 = 0;   7 return v_receiver;
  8 exception
  9 when no_data_found then
10 v_receiver := 999;
11 return v_receiver;
12 end;
13 /

Function created.

scott_at_ORA102> select f1 from dual;

999

Best regards

Maxim

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

Received on Wed Sep 20 2006 - 12:45:10 CDT