5 Replies Latest reply on Aug 12, 2011 2:05 PM by Srini Chavali-Oracle

    SQL language change in 11g?

    876267
    876267 Aug 9, 2011 4:35 PM
      Is anyone aware of any changes to SQL language with Oracle 11g, from 10g? We have recently upgraded a database from 10g to 11g. A Microsoft Access database we are using has a large amount of SQL pass-thru queries, and we are having trouble with it now, since the Oracle database has been upgraded to 11g, particularly with "Invalid Use of Null" error message, though no code has been changed. We have also upgraded our Oracle client to the 11g Full-client, and changed the driver name in the scripts to reflect this. We've had luck connecting to the 11g database in other manners (i.e., import external data > ODBC sources).

      Edited by: Barry on Aug 9, 2011 7:16 AM

      Edited by: Barry on Aug 9, 2011 9:34 AM
      • 593 Views
      • Tags:
        • 1. Re: SQL language change in 11g?
          user130038
          user130038 Aug 9, 2011 5:52 PM (in response to 876267)
          Could you please provide some example of the issue you are facing?

          http://www.asktheoracle.net/oracle-11g-sql-new-features.html
          http://www.oracle.com/technetwork/database/features/plsql/overview/plsql-new-in-11gr1-128133.pdf
          • 2. Re: SQL language change in 11g?
            876267
            876267 Aug 9, 2011 6:50 PM (in response to user130038)
            The VBA script, within Microsoft Access 2007, runs up to this SQL script, though strangely even if I comment out the "Is Null" statements:

            strSQL = "SELECT DISTINCT IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"") AS AQ_CODE, qsptLOCATION.AQUIFER_CODE, qsptLOCATION.LOC_CODE, qsptLOCATION.CLASS, IIf([tblWellInfo]![CP_TBL_V]=""POC"",""Compliance"",IIf([tblWellInfo]![CP_TBL_V]=""POE"",""Exposure"","""")) AS DESIGNATION, IIf(clng(tblWellInfo.SEG_SCR) > 1,""M"","""") AS MULTI_LEVEL, tblWellInfo.PROPERTY INTO tbl_LOCATION " & _
            "FROM qsptLOCATION INNER JOIN tblWellInfo ON qsptLOCATION.LOC_CODE = tblWellInfo.WELL_ID " & _
            "WHERE (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
            "AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
            "AND ((qsptLOCATION.CLASS) Is Null) AND ((qsptLOCATION.DESCRIPT) Is Null)) OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' " & _
            "Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
            "AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
            "AND ((qsptLOCATION.CLASS) Is Null) AND ((qsptLOCATION.DESCRIPT) Not Like '*RENAMED*')) " & _
            "OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or (IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
            "AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
            "AND ((qsptLOCATION.CLASS) Not In ('PR','AW','IB','PV','IJ')) AND ((qsptLOCATION.DESCRIPT) Is Null)) OR (((IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'PER*' Or " & _
            "(IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala"")) Like 'OGA*') " & _
            "AND ((qsptLOCATION.LOC_CODE) Not Like '*-F' And (qsptLOCATION.LOC_CODE) Not Like 'FPOP*' And (qsptLOCATION.LOC_CODE) Not Like 'OSO*' And (qsptLOCATION.LOC_CODE) Not Like '*GAL') " & _
            "AND ((qsptLOCATION.CLASS) Not In ('PR','AW','IB','PV','IJ')) AND ((qsptLOCATION.DESCRIPT) Not Like '*RENAMED*')) " & _
            "ORDER BY IIf(qsptLOCATION.AQUIFER_CODE Like 'PER*',""Perched"",""Ogallala""), qsptLOCATION.LOC_CODE;"
            CurrentDb.Execute strSQL

            Edited by: Barry on Aug 9, 2011 11:50 AM
            • 3. Re: SQL language change in 11g?
              Srini Chavali-Oracle
              Srini Chavali-Oracle Aug 11, 2011 2:13 AM (in response to 876267)
              Pl post the complete error message and text

              HTH
              Srini
              • 4. Re: SQL language change in 11g?
                876267
                876267 Aug 12, 2011 1:45 PM (in response to Srini Chavali-Oracle)
                We removed "DISTINCT" from the beginning of the script, which solved the problem. But now we are having other problems with the Access database / SQL pass-through queries. Is there much that has changed with Oracle 11g that the SQL queries must be written differently?
                • 5. Re: SQL language change in 11g?
                  Srini Chavali-Oracle
                  Srini Chavali-Oracle Aug 12, 2011 2:05 PM (in response to 876267)
                  Details are in the documentation - http://download.oracle.com/docs/cd/E11882_01/server.112/e17128/toc.htm

                  HTH
                  Srini