another bug with user-defined functions in sql

From: L Carl Pedersen <l.carl.pedersen_at_dartmouth.edu>
Date: 1995/04/24
Message-ID: <l.carl.pedersen-2404951202470001_at_kip-1-sn-423.dartmouth.edu>#1/1

this one is not a real impediment, but it's subtle and could be confusing.

if a function with no excpetion clause runs out of rows, i think it SHOULD give an unhandled exception error. instead it seems to cause the caller to think that *it* ran out of rows:

ADM> create or replace function bug2(flag in number) return number is   2
  2 junk number := 0;
  3
  3 begin
  4
  4 select 1 into junk from dual where flag < 4;   5
  5 return junk;
  6
  6 end bug2;
  7 /

Function created.

ADM> show errors
No errors.

ADM>
ADM>
ADM> drop table t;

Table dropped.

ADM> create table t(x number);

Table created.

ADM>
ADM> insert into t select rownum from all_tables where rownum < 11;

10 rows created.

ADM>
ADM> select x from t;

         X

---

         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

ADM>
ADM> select x, bug2(x) from t;

         X BUG2(X)
---------- ----------

         1          1
         2          1
         3          1

3 rows selected.

ADM>
ADM>
ADM> select * from v$version;

BANNER

---

Oracle7 Server Release 7.1.3.4.0 - Production Release PL/SQL Release 2.1.3.2.0 - Production
CORE Version 2.3.3.3.1 - Production (LSF Alpha) TNS for VMS: Version 2.1.3.0.0 - Production NLSRTL Version 2.3.4.2.1 - Production

5 rows selected.

ADM> we're on vms 6.1. i'd report this via oracle support, but they want us to pay extra for that privilege. Received on Mon Apr 24 1995 - 00:00:00 CEST