One of those weird ones – ora-32036

I’ve mentioned before that I really like using WITH subquery refactoring. Apart from anything else, I like the way it helps me identify the proper sets involved in a query, particularly when looking at other people’s complex SQL.

Interestingly enough, again notably when dealing with long and complex SQL with other people, I find that my finished rewritten versions sometimes end up without any WITHs and the usage of WITH is an interim measure to help my understanding – one main exception is materialized subqueries. But there is something nice and neat about how WITH helps organise.

But I digress.

I had a problem yesterday with a query using WITH subquery refactoring.
The basic structure was something along these lines:


WITH subq_a AS
(SELECT ....
FROM ....
WHERE ....)
, subq_b AS
(SELECT ...
FROM subq_a
, .....
WHERE .....)
, subq_c AS
(SELECT ....
FROM subq_b
, .....
WHERE .....)
SELECT .....
FROM subq_c
, subq_b
WHERE ......;


Take it as read that this blitzed the original query in terms of speed and efficiency whilst, obviously 😉 , returning the same data.

A view was created as this query and a straight select * from the view worked fine in SQL*Plus, Toad and SQLDeveloper. But via the application, C# / .Net, whallop – ora-32036.


ORA-32036: unsupported case for inlining of query name in WITH clause
Cause: There is at least one query name which is inlined more than once because it's definition query is too simple and references another query name. This is currently unsupported yet.
Action: remove such query name and retry


And I had the same problem in a similar but not identical situation calling a stored proc via the same application.

I’ve still not quite got my head fully around how two different clients can call a view or a stored procedure and get a different error message.

I wouldn’t have a comprehension problem if they were directly issuing the SQL with the WITH clause, but my trouble is that it’s a simple call to a view or stored proc that contains the WITH. Anyway.

According to a couple of metalink articles, the feature/bug/issue/problem is that SQL*Plus, Toad and SQLDeveloper are not reporting the error, rather than the correct behaviour as per ODBC or any other driver raising the error. Seems a strange one to me.

Anyway, I’ve rewritten query. It so happens, this time, without WITHs although they were fundamental to me getting where I needed to be in terms of understanding what someone was trying to so. But a frustrating error that is client/driver dependent and it’s not enough to test just via SQL*Plus, Toad or SQLDeveloper.