Re: FW: any idea what are we missing ?

  • From: Maxim Demenko <mdemenko@xxxxxxxxx>
  • To: mark.powell@xxxxxxx
  • Date: Wed, 20 Sep 2006 19:45:10 +0200

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@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@ORA102> REM notice, exception is not raised
scott@ORA102> REM and this is not expected behaviour
scott@ORA102> select f1 from dual;

       F1
----------


scott@ORA102> REM however if function is called from
scott@ORA102> REM within anonymous block, then exception is raised as expected
scott@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@ORA102> REM and if function is created with exception handler
scott@ORA102> REM then exception is handled as expected
scott@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@ORA102> select f1 from dual;

       F1
----------
      999

Best regards

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


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 09-2006