Oracle WITH Clause causing problem in SSRS?

Question

I am using SSRS (2k5) to report data from oracle. The purpose of the report is to process about 100 checks (when they are completed) on the data to ensure that they were entered correctly (and our software acts as intended). To achieve this, I created a package and a pipeline function in which the WITH statement was executed. An approximate package created in Oracle is as follows:

WITH A as (select stuff from X), B as (select stuff from Y join X), C as (select stuff from Z join X)
Subquery1
Union
Subquery2
Union
...
Subquery100

I call this package function directly from SSRS using table (). Everything works fine for a while. However, if I run it later, I get an error message:

ORA-32036: unsupported case for inserting query name in WITH clauseORA-6512: in "[function name]"

However, if I open Oracle SQLDeveloper and run the function, go back to SSRS, everything works fine (for a while).

I understand that I am probably angry at my nested WITH clauses, but what can make it work for a while, and then not soon afterwards? Note that it always works in SQLDeveloper.

Thanks in advance for your help!

Answer 1

Jeff may have suggested this before, but I did it for work by specifying an oracle query using OPENQUERY() .
Curiously, I still have to run the request once after all the changes for it to work, but I no longer need to run it before I need to run the report. Thank you all for your help!

Answer 2

Your problem is that the version of the client (or possibly the driver inside your client) that you connect to Oracle with (inside SSRS) probably does not support function calls in the WITH clause.

In the 9i client, the WITH clause was not fully functional, and I suspect that any client connecting to Oracle (even if it is not a 9i client) is still not fully functional.

You need to either:

1) Update Oracle client (or driver) in SSRS (if possible)
2) Update your version of SSRS (if it is not the latest, but check if you should upgrade first)
3) Rewrite the query without WITH clauses and use inline views instead.

Another option would be to create the database views of the required data and their link in your select statement, not the ideal option, but perhaps an option.

Hope this helps ...

Answer 3

I had the same problem and solved it by adding pragma autonomous_transaction; to the function ...... g:

 create or replace function myfunction() return varchar2 is pragma autonomous_transaction; begin --Your code here commit;---> don't forget the commit or else won't work return 'result' end; 

Just use it if your function does not work with data.

Answer 4

I used a different BI tool but got the same error. Found a good solution that worked for me by adding DistribTx = 0 to the connection string. The loan goes to Sean commented on this post https://orastory.wordpress.com/2007/09/20/one-of-those-weird-ones-ora-32036/#comment-9164

The problem is still not fixed by Oracle with 11g r2

Answer 5

In SSRS, I am connected directly to my Oracle 10g database using the Oracle driver. I found that this driver has the following limitations:

• A subquery of a single WITH cannot be referenced more than once in a query, although SQL Developer allows you to "cheat" and avoid it.

• I will get ORA-32036 error if I try to execute a query in SSRS with more than two WITH subqueries, i.e. WITH A AS (...), B AS (...) SELECT ... FROM A, B ... although in SQL Developer I can use as much WITH as I like.

So, another suggestion is to try rewriting your query to use no more than two WITH subqueries.