ORA-32036

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
mmatveev
Posts: 17
Joined: Mon 19 Apr 2010 13:18

ORA-32036

Post by mmatveev » Fri 30 Apr 2010 13:43

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/2 ... 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
Posts: 17
Joined: Mon 19 Apr 2010 13:18

Post by mmatveev » Wed 05 May 2010 13:01

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
Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Thu 06 May 2010 13:17

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

gala_l
Posts: 13
Joined: Tue 31 Jul 2007 04:05

The same problem

Post by gala_l » Tue 17 Apr 2012 23:18

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
Site Admin
Posts: 8247
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 20 Apr 2012 11:42

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/2 ... 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!"

Post Reply