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.

SQLDeveloper, Subquery Refactoring & Toad

A couple of things to mention today.

For ages, I’ve been meaning to get to know SQL Developer a bit better.

I’ve not really played with it since it was known as Raptor back in beta or release 1.0.

I’m impressed. I like it a lot. And I think the plug-in third party modules have got real potential if it takes off (how about a Qute equivalent?). It’s come a long way.

I’ve had enough of Toad though. For me, it seems to crash frequently. And although more and more features get added, it does not seem to get any more robust.

The final straw that has pushed me over to SQL Developer is actually an injustice. I had thought that the ‘With’ clause, aka subquery refactoring, was unsupported. It was raising an error, not an ora- error, along the lines of “SQL statement doesn’t return any rows”. I had put this down to a Toad inadequacy, for which I must apologise.

If you get this error message, make sure that you are connecting using an Oracle client >8 (an option on the connection screen depending on what Oracle clients that you have installed).

The final point to make is that the ‘WITH’ clause rocks. I’m using subquery refactoring more and more, possibly with a tendency to overuse it at times. But it rules. And SQL Developer is getting there.

Follow

Get every new post delivered to your Inbox.

Join 72 other followers