RE: New bug in 8.1.6 - Anyone seen this?

From: Larry G. Elkins <elkinsl_at_flash.net>
Date: Wed, 16 Aug 2000 19:05:30 -0500
Message-Id: <10591.114794@fatcity.com>

Lisa,

In the 8.1.6 and 8.1.5 docs, both the SQL Manual and the Application Developer's Guides, include sections on restrictions where sequence's currval and nextval *cannot* be referenced. One of those conditions is a SELECT with a DISTINCT operator. There are numerous others as well. Maybe this is a bug with 8.1.5? Well, it contradicts the 8.1.5 manual anyway. And on the surface, the 8.1.6 behavior is what I would expect, though I can understand people taking a different viewpoint. That's not much solace for you when code starts to fail. I think I would be a little irritated if SQL statements *I* had written started to fail after an upgrade.

You have probably come up with a workaround; but, just in case. Through the use of an in-line view, you get work around this problem by placing the sequence in the main select and the select distinct within an in-line view:

  1 Select x.deptno, foo_seq.nextval seq   2 from dual,

  3       (select distinct deptno
  4*       from emp) x

SQL> /     DEPTNO SEQ
---------- ----------

        10          1
        20          2
        30          3

The EMP table contains multiple occurrences of individual deptno values. The in-line view gets the distinct values. Since we are selecting from DUAL in the main query, we know (hope!) that only one row will be returned from the main query; so, there is no need to define a join condition between the main query and the in-line view.

I know that is of little help if all of the sudden you have code failing and you have to modify a lot of code. Would something like the following work (I didn't test; so, there may be typos or syntax problems; but, you get the idea).

Select qode$SEQ_PARTNER_CATEGORY_ID.NEXTVAL,

       x.category
FROM Dual,

      (SELECT DISTINCT category
       FROM   qode$load_data
       WHERE  load.job_id=&id
         AND  load.category IS NOT NULl) x

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Koivu, Lisa Sent: Wednesday, August 16, 2000 5:19 PM To: Multiple recipients of list ORACLE-L Subject: New bug in 8.1.6 - Anyone seen this?

Has anyone seen this particular behavior before? The statement works in 8.1.5 and fails in 8.1.6. Running HP/UX 11.0 I see nothing on Metalink that specifically describes this 'feature'. Thanks in advance.
Connected to:
Oracle8i Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production
SQL> SELECT DISTINCT

  2         qode$SEQ_PARTNER_CATEGORY_ID.NEXTVAL,
  3         category
  4     FROM qode$load_data load
  5     WHERE load.job_id=&id
  6     AND load.category IS NOT NULl;

Enter value for id: 12
old 5: WHERE load.job_id=&id
new 5: WHERE load.job_id=12
no rows selected
SQL>
Connected to:
Oracle8i Enterprise Edition Release 8.1.6.1.0 - Production With the Partitioning option
JServer Release 8.1.6.1.0 - Production
  1 SELECT DISTINCT

  2           qode$SEQ_PARTNER_CATEGORY_ID.NEXTVAL,
  3            category
  4         FROM qode$load_data load
  5         WHERE load.job_id=&id
  6*        AND load.category IS NOT NULL

  7 /
Enter value for id: 12

old   5:        WHERE load.job_id=&id
new   5:        WHERE load.job_id=12
         qode$SEQ_PARTNER_CATEGORY_ID.NEXTVAL,
                                      *

ERROR at line 2:
ORA-02287: sequence number not allowed here

SQL> Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL 33319

V: 954.484.3191, x174
F: 954.484.2933
C: 954.658.5849

http://www.qode.com
"The information contained herein does not express the opinion or position Received on Wed Aug 16 2000 - 19:05:30 CDT