“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 ?
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.
Matthias Rogel said
Thanks Alex, don’t have an 11.1 here to check it.
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.
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.