Sokrates on Oracle

“ORA-32035: unreferenced query name defined in WITH” clause no longer existent in 11.2 ?

Posted by Matthias Rogel on 14. June 2011

when I issued a select in 10.2 with a query defined in the WITH-clause but not referenced later on, I received an ORA-32035 exception:


10.2 > with a as (select 1 a from dual)
2 select * from dual;
with a as (select 1 a from dual)
*
ERROR at line 1:
ORA-32035: unreferenced query name defined in WITH clause

I was a bit surprised today, when I saw the following running against 11.2:


11.2 > with a as (select 1 a from dual)
2 select * from dual;
D
-
X

I don’t manage to get a ORA-32035 on 11.2 ….
It’s documented though on
ORA-32035
that I should get one.

So, is it a bug or a feature ?

Advertisements

5 Responses to ““ORA-32035: unreferenced query name defined in WITH” clause no longer existent in 11.2 ?”

  1. Alex said

    The same is true for 11.1 (http://technology.amis.nl/blog/4252/subquery-factoring-in-oracle-11g) As far as I’m concerned it’s a feature, the part which is not required for the execution of the query is eliminated.

  2. Dom Brooks said

    I’ve looked into this before via a 10053 and it’s clear that the unreferenced query is eliminated but not which query transformation is the culprit – they all say bypassed or not valid

    From 10053:
    ..
    Final query after transformations:******* UNPARSED QUERY IS *******
    SELECT “DUAL”.”DUMMY” “DUMMY” FROM “SYS”.”DUAL” “DUAL”

    So bug or feature? Interesting.

    From a practical, development perspective, I like that it’s disappeared.

    • Dom Brooks said

      > I like that it’s disappeared.
      Just to qualify that, I mean that when I’m messing around queries I quite often comment out bits, comment back in, etc. So, this means I just have to comment out the references in the FROM and WHERE not the actual subquery above.

  3. John Kelly said

    I would rather see the error message as this would alert you to either dead code or a mistake, and force you to resolve it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: