SQL Developer

Hi,

I develop using SQL Developer (3.2.20.09.87) against an Oracle 11gR2 db. I use Common Table Expressions (CTEs) often, when they can improve performance. (Note: These aren't usually invoking any recursive sub-querying, etc.; these are plain-jane CTE "WITH" clauses.) My institution has recently licensed an end-user reporting tool which is connecting to the same Oracle environment using "Microsoft OLE DB Provider for Oracle".

When I copy/paste my code with a CTE - I get no results in the tool using the MS OLE DB. I know that I need to find some forum, or other user that has encountered an issue with CTEs hitting Oracle with MS OLE DB to see if this is a known issue, or not. But searching google with "Microsoft OLE DB Provider for Oracle" "common table expression" isn't really getting me useful results.

I thought I'd take a shot in the dark here, and see if any other SQL Developer, and/or experienced Oracle folks could give me any better direction. Has anyone encountered issues with CTEs using the MS provider when it does not occur in SQL Developer? or, any thoughts on where else I can pose this question?

This is some info about our MS OLE DB Provider:
Name = Oracle Provider for OLE DB
FileName = OraOLEDB11.dll
Version = 11.2.0.1.0
DataSource = BI_DEVL

This is some info about our db from V$VERSION:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

If you want more details -- our transactional system is Ellucian's "Banner" product. We are a higher ed industry. The reporting tool using the MS OLE DB Provider for Oracle is Evision's "Argos" product. I have also submitted a help ticket with our vendor, Evisions -- just hoping for some additional (timely) help!
thanks//jacob