ORA-32036

[mmatveev]

I get this error code when call a function having a query using WITH clause.

Code: Select all

{"ORA-32036: unsupported case for inlining of query name in WITH clause\nORA-06512: at \"RETAILSOLUTIONS2.GetVariablesForS\", line 14"}


The same function from work perfectly in SQL developer, thus the SQL syntax is correct. Then I found that it is a common problem for oracle drivers:
http://orastory.wordpress.com/2007/09/20/one-of-those-weird-ones-ora-32036/The suitable workaround is turn off distributed transactions (it was described here http://forums.asp.net/t/990623.aspx ). Unfortunately, setting DistribTx=0 or Enlist=false in connection string doesn’t fix a problem. What you can suggest?

[mmatveev]

The workaround is using temporary table to store WITH-query results. However I'd like to get rid of temp tables. Is this stange behavior correct or is it a bug?

[AndreyR]

Could you please send us the query and scripts of all DB objects script illustrating the problem?
We will investigate the situation.

[gala_l]

HI,
we moved from very old UniDirect DLL to a new one. Now we started to get this error:

ORA-32036: unsupported case for inlining of query name in WITH clause

The sql query is trying to select data from a very complex function and getting this. But not always. From time to time it works, then again starts to give this error. Any advise?

[Shalex]

We are aware of the ORA-32036 problem when working with Oracle 10g in the OCI mode (via Oracle client). There are two possible workarounds:
1. Upgrade your Oracle server: 10g -> 11g.
2. Rewrite your query as it is recommended at http://orastory.wordpress.com/2007/09/20/one-of-those-weird-ones-ora-32036/:
"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 ......;

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!"