ORA-00932 Error When Switching From 6.30 to 6.70

[yensid21]

I recently switched from dotConnect for Oracle version 6.30 to 6.70. When I changed, I started receiving the error:

Code: Select all

ORA-00932: inconsistent datatypes: expected NCLOB got CHAR

I am using EF 4.2 and the ODP Provider Version 4.112.2.0. I am doing a code first project in which I have the following inheritance LegalEntity -> Person -> User. When I try to query the Person object, the following SQL is generated:

Code: Select all

SELECT 
"Limit1".C1,
"Limit1".ID,
"Limit1".ENTITY_TYPE,
"Limit1".STATUS,
"Limit1".VERSION,
"Limit1".TITLE,
"Limit1".FIRST_NAME,
"Limit1".MIDDLE_NAME,
"Limit1".LAST_NAME,
"Limit1".SECONDARY_LAST_NAME,
"Limit1".SUFFIX,
"Limit1".DOB,
"Limit1".DOD,
"Limit1".RACE_TYPE_ID,
"Limit1".GENDER_TYPE_ID,
"Limit1".MARITAL_TYPE_ID,
"Limit1".WEIGHT,
"Limit1".HEIGHT_FEET,
"Limit1".HEIGHT_INCHES,
"Limit1".HAIR_TYPE_ID,
"Limit1".HAIR_COLOR_TYPE_ID,
"Limit1".TEETH_TYPE_ID,
"Limit1".COMPLEXION_TYPE_ID,
"Limit1".BUILD_TYPE_ID,
"Limit1".NOSE_TYPE_ID,
"Limit1".EYE_COLOR_TYPE_ID,
"Limit1".DRESS_TYPE_ID,
"Limit1".GAIT_TYPE_ID,
"Limit1".COMMENTS,
"Limit1".C2,
"Limit1".C3,
"Limit1".C4
FROM ( SELECT 
                "Extent1".ID,
                "Extent1".ENTITY_TYPE,
                "Extent1".STATUS,
                "Extent1".VERSION,
                "Extent2".TITLE,
                "Extent2".FIRST_NAME,
                "Extent2".MIDDLE_NAME,
                "Extent2".LAST_NAME,
                "Extent2".SECONDARY_LAST_NAME,
                "Extent2".SUFFIX,
                "Extent2".DOB,
                "Extent2".DOD,
                "Extent2".RACE_TYPE_ID,
                "Extent2".GENDER_TYPE_ID,
                "Extent2".MARITAL_TYPE_ID,
                "Extent2".WEIGHT,
                "Extent2".HEIGHT_FEET,
                "Extent2".HEIGHT_INCHES,
                "Extent2".HAIR_TYPE_ID,
                "Extent2".HAIR_COLOR_TYPE_ID,
                "Extent2".TEETH_TYPE_ID,
                "Extent2".COMPLEXION_TYPE_ID,
                "Extent2".BUILD_TYPE_ID,
                "Extent2".NOSE_TYPE_ID,
                "Extent2".EYE_COLOR_TYPE_ID,
                "Extent2".DRESS_TYPE_ID,
                "Extent2".GAIT_TYPE_ID,
                "Extent2".COMMENTS,
                CASE WHEN  NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN '0X0X' ELSE '0X0X0X' END AS C1,
                CASE WHEN  NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NCLOB(NULL) ELSE "Project1".USER_NAME END AS C2,
                CASE WHEN  NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NCLOB(NULL) ELSE "Project1".DOMAIN END AS C3,
                CASE WHEN  NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NCLOB(NULL) ELSE "Project1".PASSWORD END AS C4
                FROM   "SO$ENTITY".LEGAL_ENTITIES "Extent1"
                INNER JOIN "SO$ENTITY".PERSONS "Extent2" ON "Extent1".ID = "Extent2".ID
                LEFT OUTER JOIN  (SELECT 
                                "Extent3".ID,
                                "Extent3".USER_NAME,
                                "Extent3".DOMAIN,
                                "Extent3".PASSWORD,
                                1 AS C1
                                FROM "SO$SECURITY".USERS "Extent3" ) "Project1" ON "Extent1".ID = "Project1".ID
                WHERE (("Extent2".FIRST_NAME LIKE 'NU') AND ("Extent2".LAST_NAME LIKE 'GUY')) AND ROWNUM <= 1
)  "Limit1"

However, if I create a test project with the same inheritance the CASE statements with the TO_NCLOB commands are not generated. It simply selects the values and uses joins on the Id column. The TO_NCLOB commands are creating the ORA-00932 error since, I can remove them and run the query manually and it works fine.

Thanks.
Andrew

[Shalex]

As we understood, you are working with the Code-First approach when there is an existing (predefined) database structure, but mapping was made manually (!). If a database exists, we recommend you using the DbContext template which allows to generate classes automatically with the most complete mapping (exact data types, etc).

The type for NULL in CASE depends on the type of the corresponding column in ELSE of this CASE:

Code: Select all

CASE WHEN  NOT (("Project1".C1 = 1) AND ("Project1".C1 IS NOT NULL)) THEN TO_NCLOB(NULL) ELSE "Project1".USER_NAME END AS C2

TO_NCLOB(NULL) is used here because USER_NAME is considered as NCLOB. There would be TO_CHAR(NULL) if the column was treated as VARCHAR2.

Why so? By default, strings in EF Code-First are considered as unicode and without explicit length. That's why it is of NCLOB type. It would be VARCHAR2 if the [not unicode string] and [length < 4000 symbols] settings were specified in fluent (or attribute) mapping for the USER_NAME property.

Solution for the attribute mapping. If you indeed have the type in your database which differs from NCLOB, specify this explicitly (at least for the columns which take part in CASEs). E.g.: set the MaxLength(123) attribute if you have the VARCHAR2(123) column in your database. Also turn off PropertyMaxLengthConvention from the Conventions collection in DbModelBuilder (this convention is used for unicode strings).

Solution for the fluent mapping. Set HasMaxLength(123) and IsUnicode(false) for the corresponding property. In this case there is no need to turn off PropertyMaxLengthConvention.