FW: any idea what are we missing ?

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 20 Sep 2006 12:51:21 -0400
Message-ID: <5A14AF34CFF8AD44A44891F7C9FF4105087F0B90@usahm236.amer.corp.eds.com>

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

-----Original Message-----
From: Maxim Demenko [mailto:mdemenko_at_gmail.com] Sent: Wednesday, September 20, 2006 12:22 PM To: Powell, Mark D
Cc: oracle-l_at_freelists.org
Subject: Re: any idea what are we missing ?

Powell, Mark D schrieb:
> A select into query must return ONE and ONLY ONE row. Anything else
> is an error! Your query returns no rows. There are no_rows and
> too_many_rows (check correct spelling) exceptions provided. Look in
> the PL/SQL manual
>
> HTH -- Mark D Powell --
>

>
>

---

> *From:* oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] *On Behalf Of *Amihay Gonen
> *Sent:* Wednesday, September 20, 2006 1:23 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Q: any idea what are we missing ?

>


>     create or replace function t1 return number

>


>     is

>


>        v_exists number(5):=0;

>


>     begin

>


>        select 1

>


>        into   v_exists

>


>        from dual where 1 = 0;

>


>        return v_exists;

>


>     --exception

>


>     --    when others then

>


>     --        v_exists := '999';

>


>     --        return 999;

>


>     end t1;

>


>     /

>


>      

>


>     select t1 from dual;

>


>      

>


>     Expected: ORA-01403: no data found

>


>     Actual:

>


>     fraud_owner_at_E2E811> select t1 from dual

>


>       2  /

>


>      

>


>             T1

>


>     ----------

>


>      

>


>      

>


>     fraud_owner_at_E2E811>

>


>      

>


>     IMPORTANT: The contents of this email and any attachments are

>     confidential and proprietary to ECtel Ltd. They are intended for

>     the named recipient(s) only. If the reader of this message is not

>     the intended recipient, you are hereby notified that any

>     dissemination, use, distribution or copying of this communication

>     is strictly prohibited and may be unlawful.

>     If you have received this email in error, please notify us

>     immediately by replying to the message and deleting it from your

>     computer.

>
This looks like an oracle bug. SELECT INTO returns no rows and should
raise an ORA-01403.
If in the function body, exception clause is uncommented ( and even made
more specifically - WHEN NO_DATA_FOUND) - then the same query return
999, which indicate that exception handler is executed. However without
exception handler exception is not raised. Moreover, with identical
construct within stored procedure or anonymous block behaviour is
correct ( ORA-01403).  Only call of this function from SQL don't raise
exception, which IMO is not correct.

Best regards

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

Received on Wed Sep 20 2006 - 11:51:21 CDT