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.

About these ads

15 Responses to One of those weird ones – ora-32036

  1. Gary says:

    The clients need some way to work out what the result set will look like (how many columns, datatypes) etc.
    I suspect the problem is when it tries to get this metadata, rather than executing the query itself. If you use the view in an INSERT or UPDATE (where the client doesn’t get a result set), you may find it works

  2. Jeffrey Kemp says:

    “…This is currently unsupported yet.
    Action: remove such query name and retry”

    Not the best use of grammar I’ve seen in the Oracle documentation…

  3. dombrooks says:

    Gary – Sounds plausible.

    Jeff – sounds like an idea for an article – worst cases of grammar in Oracle documentation. Or perhaps not.

  4. Mary says:

    Is anything changed now? May be there is a solution (without rewriting query)?

  5. dombrooks says:

    No Mary. The issue will remain depending on the stack, drivers etc.

  6. Sean says:

    I have just come across this problem. Having been using subquery refactoring for quite some time now, and very much enjoying the benefits of it that you’ve mentioned, today I came across this error message. I have used this style of query many times, including within VB and VBA applications. Then suddenly I get stumped by this one, hence finding this web page.

    The problem appears to be from the reuse of a subquery, in the example you give you have subq_b referenced in subq_c and in the main query itself.

    I really didn’t want to have to rewrite my query in an awkward to read way, I wanted to keep my subqueries. So I repeated the lines for subq_b, so that now I have subq_b1 and subq_b2, two identical subqueries. I then referenced one in subq_c and the other in the main query. It worked!

    The odd thing is that both the versions of subq_b reference the same subq_a (or in my case, two previous subqueries) although these earlier subqueries are far less complicated and would return very few rows (only one in one case).

    I haven’t looked into this much to find out exactly why I can rereference subq_a, but not the more complicated subq_b, but I just wanted to say that there appears to be ways around it without habving to lose those lovely subqueries!

    If anyone is able to test the limits of this I’d be happy to read.

  7. Danilo Piazzalunga says:

    As far as I can understand from the metalink articles, this error should only be raised when the transaction isolation level is set to SERIALIZABLE.

    I have come across this problem in a Java/WebLogic application with the transaction isolation level set to READ COMMITTED. It appears that using XA transactions will trigger the problem:

    http://forums.oracle.com/forums/thread.jspa?threadID=858923

  8. dombrooks says:

    Thanks Danilo.
    Hmmm – had it just recently again with a pro*c process. It’s easy to work around once you’re familiar with it.

  9. David says:

    Thanks Sean. Creating two of the same queries in the with statement worked for me. Quick workaround. Easy to implement.

  10. Sean says:

    Hi David,

    At some point during 2010 I had reason to make changes to my code. The company I work for updated the Oracle client (I believe my work machine now has 10g installed). During my playing around and testing I found I could add a few more parameters to the connection string, and since implementing these I have found that this error no longer occurs and I have been able to resort to single instances of a subquery every time. I don’t recall my exact testing now, but I believe I found the parameter that made the big difference to be:

    DistribTx=0

    As I say, it was a while back now and I don’t recall the exact research and testing I did, but I did check with my DB admin guys that, as far as they were concerned, this wouldn’t cause issues with my queries etc.

    My full connection string is now

    “Provider=OraOLEDB.Oracle.1;” & _
    “Persist Security Info=False;” & _
    “UseSessionFormat=TRUE;” & _
    “DistribTx=0;” & _
    “Password=” & strPassword & “;” & _
    “User ID=” & strUserName & “;” & _
    “Data Source=” & strDatabaseName

    I can’t remember now why I decided to use it, what made me think it might help etc. Perhaps I just found that it worked and decided after to forget the problem ever existed. Anyway, I’ve been able to remove the duplicate subqueries from my SQL and haven’t had problems since. Seeing your message brought it all back, so I thought I’d share my jubilation. So, if you are able, have a play around and let me know.

    Good luck.

  11. Dom Brooks says:

    Just to be clear.

    If you’re in any doubt about how to best resolve this, I’d rewrite the query – e.g. repeat the block that’s currently reference more than once.

  12. Marcus says:

    I had a similar problem and it seemed that it was caused by the XA driver (oracle.jdbc.xa.client.OracleXADataSource). I changed the driver to oracle.jdbc.pool.OracleDataSource and it fixed the problem.

    • Dom Brooks says:

      Thanks Marcus.
      Judging by my search results, people continue to get this error regularly so information like this change of driver might prove very useful to someone.
      Your experience with the XA driver tallies with that of Danilo above.

      Thanks.

      Dominic.

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

Follow

Get every new post delivered to your Inbox.

Join 62 other followers

%d bloggers like this: