ORA-32036: unsupported case for inlining of query name in WITH clause
811419Sep 7 2012 — edited Mar 10 2013Using Oracle 10g, I have been developing a large query to generate XML, using a series of WITH queries (also known as subquery factoring, or Common Table Expressions, or CTEs). The query is over 1200 lines long, but it is a vast improvement over the technology used previously to implement this service. The worst problem that plagues development is recurrence of this error, "ORA-32036: unsupported case for inlining of query name in WITH clause". The query could be much shorter if this limitation didn't keep cropping up.
What are Oracle's plans to improve support for CTEs? I'd like to promote this technology within our [large] organization, but I'm about to give up on it; can't justify the hassle to other developers.
Can anyone offer general guidance for working around this limitation? Below is the general pattern, illustrating why and how CTEs are used.
Help is greatly appreciated; thanks in advance.
WITH CTE1 AS (
SELECT ...
), CTE2 AS (
SELECT ... FROM CTE1
)
SELECT ...
CASE WHEN Exists ( SELECT * FROM CTE2 ) THEN
( SELECT XMLAgg(
XMLElement("MyElementName", myColumnValue)
)--XMLAgg
FROM CTE2 )
END,
...
FROM DUAL;
Edited by: Kit.net on Sep 7, 2012 10:57 AM, Mar 10, 2013 5:44 PM