Oracle WITH Clause Causing Issue in SSRS?

Question

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!

Answer 1

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...