ORA-00904: \"TOLOWER\": invalid identifier

ORA-00904: \"TOLOWER\": invalid identifier

Postby jacksparrow » Tue 08 Apr 2014 01:14

Hello,

We have the following LINQ query for Oracle which generates the "ORA-00904: \"TOLOWER\": invalid identifier" exception. Please advice on the solution.

Code: Select all
var qry = from proddesc in entities.Productdescriptions.Include("Product")
                          from manuf in entities.Optionsmanufacturers
                          where proddesc.Languageid == languageID
                          && proddesc.Product.Optionsmanufacturerid == manuf.Optionsmanufacturerid
                          && proddesc.Product.Producttype.ToLower() == productType.ToLower()
                          && manuf.Languageid == DefaultLanguage
                          && (proddesc.Isdeleted == null || proddesc.Isdeleted == 0)
                          && (proddesc.Product.Isdeleted == null || proddesc.Product.Isdeleted == 0)
                          && (manuf.Isdeleted == null || manuf.Isdeleted == 0)
                          orderby proddesc.Publishdate descending
                          select new { proddesc, manuf, product=proddesc.Product };


The below SQL query trace is obtained using dbMonitor.

Code: Select all
SELECT
"Project1".C1 AS C1,
"Project1".SKU AS SKU,
"Project1".NAME AS NAME,
"Project1".DESCRIPTIONSHORT AS DESCRIPTIONSHORT,
"Project1".DESCRIPTIONLONG AS DESCRIPTIONLONG,
"Project1".MARKETINGTEXT AS MARKETINGTEXT,
"Project1".LANGUAGEID AS LANGUAGEID,
"Project1".TAGLINE AS TAGLINE,
"Project1".LEGALTERM AS LEGALTERM,
"Project1".ISTRANSLATIONNEEDED AS ISTRANSLATIONNEEDED,
"Project1".IMMUTABLE AS IMMUTABLE,
"Project1".UPDATEDDATE AS UPDATEDDATE,
"Project1".PUBLISHSTATUS AS PUBLISHSTATUS,
"Project1".PUBLISHDATE AS PUBLISHDATE,
"Project1".ISDELETED AS ISDELETED,
"Project1".CREATEDBY AS CREATEDBY,
"Project1".UPDATEDBY AS UPDATEDBY,
"Project1".VIDEOID AS VIDEOID,
"Project1".CROSSSELLIMAGEID AS CROSSSELLIMAGEID,
"Project1".UPSELLIMAGEID AS UPSELLIMAGEID,
"Project1".OPTIONSMANUFACTURERID AS OPTIONSMANUFACTURERID,
"Project1".NAME1 AS NAME1,
"Project1".SORTORDER AS SORTORDER,
"Project1".LANGUAGEID1 AS LANGUAGEID1,
"Project1".IMMUTABLE1 AS IMMUTABLE1,
"Project1".OPUSMANUFACTURERID AS OPUSMANUFACTURERID,
"Project1".OPUSLOADDATE AS OPUSLOADDATE,
"Project1".LASTMODIFIEDDATE AS LASTMODIFIEDDATE,
"Project1".ISDELETED1 AS ISDELETED1,
"Project1".UPDATEDBY1 AS UPDATEDBY1,
"Project1".PUBLISHSTATUS1 AS PUBLISHSTATUS1,
"Project1".PUBLISHDATE1 AS PUBLISHDATE1,
"Project1".UPDATEDDATE1 AS UPDATEDDATE1,
"Project1".ISTRANSLATIONNEEDED1 AS ISTRANSLATIONNEEDED1,
"Project1".MANUFACTURERIMAGE AS MANUFACTURERIMAGE,
"Project1".SKU1 AS SKU1,
"Project1".PRODUCTTYPE AS PRODUCTTYPE,
"Project1".MODELNUMBER AS MODELNUMBER,
"Project1".PRODUCTSIZE AS PRODUCTSIZE,
"Project1".WEIGHT AS WEIGHT,
"Project1".FULLSIZEIMAGEURL AS FULLSIZEIMAGEURL,
"Project1".THUMBNAILIMAGEURL AS THUMBNAILIMAGEURL,
"Project1".STATUS AS STATUS,
"Project1".SORTORDER1 AS SORTORDER1,
"Project1".IMMUTABLE2 AS IMMUTABLE2,
"Project1".OPTIONSMANUFACTURERID1 AS OPTIONSMANUFACTURERID1,
"Project1".OPUSLOADDATE1 AS OPUSLOADDATE1,
"Project1".COLOR AS COLOR,
"Project1".PUBLISHSTATUS2 AS PUBLISHSTATUS2,
"Project1".PUBLISHDATE2 AS PUBLISHDATE2,
"Project1".LASTMODIFIEDDATE1 AS LASTMODIFIEDDATE1,
"Project1".ISDELETED2 AS ISDELETED2,
"Project1".RELEASEDATE AS RELEASEDATE,
"Project1".OPTIONSMODULEID AS OPTIONSMODULEID,
"Project1".REBATEPRICE AS REBATEPRICE,
"Project1".REBATELEGALTERM AS REBATELEGALTERM,
"Project1".ISSMALLBUSINESS AS ISSMALLBUSINESS,
"Project1".OPTIONSSTYLEID AS OPTIONSSTYLEID,
"Project1".CREATEDBY1 AS CREATEDBY1,
"Project1".CREATEDON AS CREATEDON,
"Project1".UPDATEDBY2 AS UPDATEDBY2,
"Project1".UPDATEDDATE2 AS UPDATEDDATE2,
"Project1".DISPLAYBARCODE AS DISPLAYBARCODE,
"Project1".SKU_CATEGORY AS SKU_CATEGORY,
"Project1".IMEI_TYPE AS IMEI_TYPE,
"Project1".TECHNOLOGY_TYPE AS TECHNOLOGY_TYPE,
"Project1".DEALER_COST AS DEALER_COST,
"Project1".CHANNEL AS CHANNEL,
"Project1".COMMISSIONABLE AS COMMISSIONABLE,
"Project1".MSRP AS MSRP
FROM ( SELECT
   "Filter1".SKU AS SKU,
   "Filter1".NAME1 AS NAME,
   "Filter1".DESCRIPTIONSHORT AS DESCRIPTIONSHORT,
   "Filter1".DESCRIPTIONLONG AS DESCRIPTIONLONG,
   "Filter1".MARKETINGTEXT AS MARKETINGTEXT,
   "Filter1".LANGUAGEID1 AS LANGUAGEID,
   "Filter1".TAGLINE AS TAGLINE,
   "Filter1".LEGALTERM AS LEGALTERM,
   "Filter1".ISTRANSLATIONNEEDED1 AS ISTRANSLATIONNEEDED,
   "Filter1".IMMUTABLE1 AS IMMUTABLE,
   "Filter1".UPDATEDDATE1 AS UPDATEDDATE,
   "Filter1".PUBLISHSTATUS1 AS PUBLISHSTATUS,
   "Filter1".PUBLISHDATE1 AS PUBLISHDATE,
   "Filter1".ISDELETED1 AS ISDELETED,
   "Filter1".CREATEDBY AS CREATEDBY,
   "Filter1".UPDATEDBY1 AS UPDATEDBY,
   "Filter1".VIDEOID AS VIDEOID,
   "Filter1".CROSSSELLIMAGEID AS CROSSSELLIMAGEID,
   "Filter1".UPSELLIMAGEID AS UPSELLIMAGEID,
   "Filter1".OPTIONSMANUFACTURERID AS OPTIONSMANUFACTURERID,
   "Filter1".NAME2 AS NAME1,
   "Filter1".SORTORDER AS SORTORDER,
   "Filter1".LANGUAGEID2 AS LANGUAGEID1,
   "Filter1".IMMUTABLE2 AS IMMUTABLE1,
   "Filter1".OPUSMANUFACTURERID AS OPUSMANUFACTURERID,
   "Filter1".OPUSLOADDATE AS OPUSLOADDATE,
   "Filter1".LASTMODIFIEDDATE AS LASTMODIFIEDDATE,
   "Filter1".ISDELETED2 AS ISDELETED1,
   "Filter1".UPDATEDBY2 AS UPDATEDBY1,
   "Filter1".PUBLISHSTATUS2 AS PUBLISHSTATUS1,
   "Filter1".PUBLISHDATE2 AS PUBLISHDATE1,
   "Filter1".UPDATEDDATE2 AS UPDATEDDATE1,
   "Filter1".ISTRANSLATIONNEEDED2 AS ISTRANSLATIONNEEDED1,
   "Filter1".MANUFACTURERIMAGE AS MANUFACTURERIMAGE,
   "Extent3".SKU AS SKU1,
   "Extent3".PRODUCTTYPE AS PRODUCTTYPE,
   "Extent3".MODELNUMBER AS MODELNUMBER,
   "Extent3".PRODUCTSIZE AS PRODUCTSIZE,
   "Extent3".WEIGHT AS WEIGHT,
   "Extent3".FULLSIZEIMAGEURL AS FULLSIZEIMAGEURL,
   "Extent3".THUMBNAILIMAGEURL AS THUMBNAILIMAGEURL,
   "Extent3".STATUS AS STATUS,
   "Extent3".SORTORDER AS SORTORDER1,
   "Extent3".IMMUTABLE AS IMMUTABLE2,
   "Extent3".OPTIONSMANUFACTURERID AS OPTIONSMANUFACTURERID1,
   "Extent3".OPUSLOADDATE AS OPUSLOADDATE1,
   "Extent3".COLOR AS COLOR,
   "Extent3".PUBLISHSTATUS AS PUBLISHSTATUS2,
   "Extent3".PUBLISHDATE AS PUBLISHDATE2,
   "Extent3".LASTMODIFIEDDATE AS LASTMODIFIEDDATE1,
   "Extent3".ISDELETED AS ISDELETED2,
   "Extent3".RELEASEDATE AS RELEASEDATE,
   "Extent3".OPTIONSMODULEID AS OPTIONSMODULEID,
   "Extent3".REBATEPRICE AS REBATEPRICE,
   "Extent3".REBATELEGALTERM AS REBATELEGALTERM,
   "Extent3".ISSMALLBUSINESS AS ISSMALLBUSINESS,
   "Extent3".OPTIONSSTYLEID AS OPTIONSSTYLEID,
   "Extent3".CREATEDBY AS CREATEDBY1,
   "Extent3".CREATEDON AS CREATEDON,
   "Extent3".UPDATEDBY AS UPDATEDBY2,
   "Extent3".UPDATEDDATE AS UPDATEDDATE2,
   "Extent3".DISPLAYBARCODE AS DISPLAYBARCODE,
   "Extent3".SKU_CATEGORY AS SKU_CATEGORY,
   "Extent3".IMEI_TYPE AS IMEI_TYPE,
   "Extent3".TECHNOLOGY_TYPE AS TECHNOLOGY_TYPE,
   "Extent3".DEALER_COST AS DEALER_COST,
   "Extent3".CHANNEL AS CHANNEL,
   "Extent3".COMMISSIONABLE AS COMMISSIONABLE,
   "Extent3".MSRP AS MSRP,
   1 AS C1
   FROM   (SELECT "Extent1".SKU AS SKU, "Extent1".NAME AS NAME1, "Extent1".DESCRIPTIONSHORT AS DESCRIPTIONSHORT, "Extent1".DESCRIPTIONLONG AS DESCRIPTIONLONG, "Extent1".MARKETINGTEXT AS MARKETINGTEXT, "Extent1".LANGUAGEID AS LANGUAGEID1, "Extent1".TAGLINE AS TAGLINE, "Extent1".LEGALTERM AS LEGALTERM, "Extent1".ISTRANSLATIONNEEDED AS ISTRANSLATIONNEEDED1, "Extent1".IMMUTABLE AS IMMUTABLE1, "Extent1".UPDATEDDATE AS UPDATEDDATE1, "Extent1".PUBLISHSTATUS AS PUBLISHSTATUS1, "Extent1".PUBLISHDATE AS PUBLISHDATE1, "Extent1".ISDELETED AS ISDELETED1, "Extent1".CREATEDBY AS CREATEDBY, "Extent1".UPDATEDBY AS UPDATEDBY1, "Extent1".VIDEOID AS VIDEOID, "Extent1".CROSSSELLIMAGEID AS CROSSSELLIMAGEID, "Extent1".UPSELLIMAGEID AS UPSELLIMAGEID, "Extent2".OPTIONSMANUFACTURERID AS OPTIONSMANUFACTURERID, "Extent2".NAME AS NAME2, "Extent2".SORTORDER AS SORTORDER, "Extent2".LANGUAGEID AS LANGUAGEID2, "Extent2".IMMUTABLE AS IMMUTABLE2, "Extent2".OPUSMANUFACTURERID AS OPUSMANUFACTURERID, "Extent2".OPUSLOADDATE AS OPUSLOADDATE, "Extent2".LASTMODIFIEDDATE AS LASTMODIFIEDDATE, "Extent2".ISDELETED AS ISDELETED2, "Extent2".UPDATEDBY AS UPDATEDBY2, "Extent2".PUBLISHSTATUS AS PUBLISHSTATUS2, "Extent2".PUBLISHDATE AS PUBLISHDATE2, "Extent2".UPDATEDDATE AS UPDATEDDATE2, "Extent2".ISTRANSLATIONNEEDED AS ISTRANSLATIONNEEDED2, "Extent2".MANUFACTURERIMAGE AS MANUFACTURERIMAGE
      FROM  JANUSSTG.PRODUCTDESCRIPTION "Extent1"
      CROSS JOIN JANUSSTG.OPTIONSMANUFACTURER "Extent2"
      WHERE ((1 = "Extent2".LANGUAGEID) AND (("Extent1".ISDELETED IS NULL) OR (0 = "Extent1".ISDELETED))) AND (("Extent2".ISDELETED IS NULL) OR (0 = "Extent2".ISDELETED)) ) "Filter1"
   INNER JOIN JANUSSTG.PRODUCT "Extent3" ON ("Filter1".OPTIONSMANUFACTURERID = "Extent3".OPTIONSMANUFACTURERID) AND ("Filter1".SKU = "Extent3".SKU)
   WHERE ((("Extent3".ISDELETED IS NULL) OR (0 = "Extent3".ISDELETED)) AND ("Filter1".LANGUAGEID1 = :p__linq__0)) AND ((ToLower("Extent3".PRODUCTTYPE)) = (ToLower(:p__linq__1)))
)  "Project1"
ORDER BY "Project1".PUBLISHDATE DESC
jacksparrow
 
Posts: 9
Joined: Wed 26 May 2010 02:39

Re: ORA-00904: \"TOLOWER\": invalid identifier

Postby Shalex » Tue 08 Apr 2014 14:40

Seems like you are using an outdated version of dotConnect for Oracle. Please upgrade to the latest (8.3.125) build of dotConnect for Oracle and confirm that the issue is fixed.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00904: \"TOLOWER\": invalid identifier

Postby jacksparrow » Thu 10 Apr 2014 15:41

We are using dotConnect for Oracle version 5.70.190. This version is installed on our production server and the application runs fine with the same code. This problem appears in developer workstations when running the application under Visual Studo 2010. Hence an upgrade at this point is not warranted.

Seems like the problem is related to the version of Oracle client software installed on the machines. Please let us know how dotConnect for Oracle, interacts with the Oracle client software.
jacksparrow
 
Posts: 9
Joined: Wed 26 May 2010 02:39

Re: ORA-00904: \"TOLOWER\": invalid identifier

Postby Shalex » Fri 11 Apr 2014 14:27

The appearance of the problem with dotConnect for Oracle version 5.70.190 depends on the versions of:
a) Entity Framework
b) .NET Framework installed on the workstation

The issue is fixed in the latest version of dotConnect for Oracle. If you do not have access to the registered version, you can check the trial: http://www.devart.com/dotconnect/oracle/download.html.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44

Re: ORA-00904: \"TOLOWER\": invalid identifier

Postby jacksparrow » Tue 15 Apr 2014 20:06

We are able to fix the issue by uninstalling .Net Framework 4.5 on the affected workstation. dotConnect for Oracle version 5.70.190 works correctly with .Net Framework 4.0. We are also able to try dotConnect for Oracle version 8.3.125 (with .Net Framework 4.0) without any issues. Thanks for the support.
jacksparrow
 
Posts: 9
Joined: Wed 26 May 2010 02:39

Re: ORA-00904: \"TOLOWER\": invalid identifier

Postby Shalex » Wed 16 Apr 2014 07:43

jacksparrow wrote:We are also able to try dotConnect for Oracle version 8.3.125 (with .Net Framework 4.0) without any issues.

dotConnect for Oracle v8.3.125 should work also with .NET Framework 4.5.
Shalex
Devart Team
 
Posts: 7654
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle