2 Replies Latest reply: May 8, 2013 9:18 AM by JacobatTheNewSchool RSS

    CTE issue: Any SQL Developers also using a tool using MS OLEDB ... Oracle?

    JacobatTheNewSchool
      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