CODEWITHSUNDEEP
oracle-databaseoracle10greporting-servicesoracle-sqldeveloper
Paul
Paul

Reputation: 133

Oracle WITH Clause Causing Issue in SSRS?

I'm using SSRS (2k5) to report on data from oracle. The report's purpose is to process about 100 checks (when complete) on data to see if it was entered correctly (and our software is acting the way it is supposed to). To accomplish this I created a package and pipelined function implimenting the WITH Clause. Roughly the package built in Oracle looks like this:

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 in the day I get an error message:

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

However if I open Oracle SQLDeveloper, and run the function then come back to SSRS, everthing runs fine (for a while).

I realize it is probably getting angry at my nested WITH clauses, but what would cause it to work for a while, then fail soon after? Note it always works in SQLDeveloper.

Thanks in advance for your assistance!

Upvotes: 4

Views: 4501

Answers (5)

Baodad
Baodad

Reputation: 2501

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

  1. A single WITH subquery cannot be referenced more than once in a query, even though SQL Developer let you "cheat" and get away with this.

  2. I will get error ORA-32036 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 ..., even though in SQL Developer I can use as many WITHs as I like.

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

Upvotes: 0

mishkin
mishkin

Reputation: 6242

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

The issue is still not fixed by Oracle as of 11g r2

Upvotes: 0

Kamil
Kamil

Reputation: 31

I had the same problem and solve it by adding pragma autonomous_transaction; to the function..e.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 doesn't manipulate with data.

Upvotes: 3

Paul
Paul

Reputation: 133

Perhaps Jeff implied this earlier, however I did get this to work by containing the oracle query by using OPENQUERY().
Curiously though I still have to run the query once after all changes for it to work, but I no longer have to run it before I need to run the report. Thanks all again for your help!

Upvotes: 1

Ollie
Ollie

Reputation: 17568

Your issue is that the version of the client (or possibly the driver within your client) you are connecting to Oracle with (inside SSRS) probably doesn't support function calls within a WITH clause.

In the 9i client, the WITH clause was not fully functional and I suspectwhichever client is connecting to Oracle (even if it's not the 9i client) is still not fully functional.

You'll have to either:

1) Upgrade the Oracle client (or driver) within SSRS (if that's even possible)
2) Upgrade your version of SSRS (if it's not the latest, but check it is worthwhile doing the upgrade first)
3) Re-write your query without the WITH clauses and use inline views instead.

Another option would be to create DB views of the data needed and reference them in your select statement, not ideal but possibly an option.

Hope it helps...

Upvotes: 4

Related Questions