Devart Forums

yensid21

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

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

yensid21

Yes, I did turn on the DeleteDatabaseBehaviour.Schema mode and I am running into the same problem. Using the dbMonitor tool, I found this is the command that failed:

Code: Select all

ALTER TABLE "A$LEGAL_ENTITIES".SMTS
  ADD CONSTRAINT "FK_SMT_Photo" FOREIGN KEY (PHOTO_ID) REFERENCES "A$PHOTOGRAPHS".PHOTOGRAPHS (ID)

I tried adding the constraint by hand using Toad and I received an ORA-00942: table or view does not exist error.

However, after I execute the command:

Code: Select all

GRANT REFERENCES ON A$PHOTOGRAPHS.PHOTOGRAPHS TO A$LEGAL_ENTITIES;

I can add the constraint.

This is the same behaviour that is happening when Entity Framework attempts to create the database from within code. The A$LEGAL_ENTITIES.SMTS table cannot add the constraint against the A$PHOTOGRAPHS.PHOTOGRAPHS table until the GRANT REFERENCES command is executed due to the fact that the tables are in different schemas. When I put all the tables into the same schema, then I don't have any problems dropping and re-creating the database.

yensid21

I am receiving an ORA-00942: table or view does not exist error when EF attempts to create the database using a code first model. The problem is due to the fact that tables are being created in multiple schemas with foreign key references between tables in different schemas. A "GRANT REFERENCES" ddl command must be executed for each table which needs to be accessed by another schema. I tried using the GRANTS property for the CreateDatabase() behavior, however it appears that command works for user level privileges, not object level privileges or I am using the property improperly.

Thanks.

yensid21

This fixed the problem. Thanks!

yensid21

I am getting an ORA-12704 error using the code-first model. I am using version 6.30.202.0 of your product for Oracle.

Here is the SQL generated by the framework:

SELECT
"Limit1".C2 AS C1,
"Limit1".C1 AS C2,
"Limit1".REFERRED_TO AS REFERRED_TO,
"Limit1".ENTITY_TYPE AS ENTITY_TYPE,
"Limit1".STATUS AS STATUS,
"Limit1".VERSION AS VERSION,
"Limit1".C3 AS C3,
"Limit1".C4 AS C4,
"Limit1".C5 AS C5,
"Limit1".C6 AS C6,
"Limit1".C7 AS C7,
"Limit1".C8 AS C8,
"Limit1".C9 AS C9,
"Limit1".C10 AS C10,
"Limit1".C11 AS C11,
"Limit1".C12 AS C12,
"Limit1".C13 AS C13,
"Limit1".C14 AS C14,
"Limit1".C15 AS C15,
"Limit1".C16 AS C16,
"Limit1".C17 AS C17,
"Limit1".C18 AS C18,
"Limit1".C19 AS C19,
"Limit1".C20 AS C20,
"Limit1".C21 AS C21
FROM ( SELECT
"UnionAll1".ID AS C1,
"Extent3".REFERRED_TO AS REFERRED_TO,
"Extent3".ENTITY_TYPE AS ENTITY_TYPE,
"Extent3".STATUS AS STATUS,
"Extent3".VERSION AS VERSION,
CASE WHEN "UnionAll1".C13 = 1 THEN '0X0X' ELSE '0X1X' END AS C2,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".BUSINESS_NAME END AS C3,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".EIN END AS C4,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".START_DATE END AS C5,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".END_DATE END AS C6,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".COMMENTS END AS C7,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".HOURS_OF_OPERATION END AS C8,
CASE WHEN "UnionAll1".C13 = 1 THEN "UnionAll1".PRIMARY_CONTACT_ID END AS C9,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C1 END AS C10,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C2 END AS C11,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C3 END AS C12,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C4 END AS C13,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C5 END AS C14,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C6 END AS C15,
CASE WHEN "UnionAll1".C13 = 1 THEN CAST(NULL AS TIMESTAMP(9)) ELSE "UnionAll1".C7 END AS C16,
CASE WHEN "UnionAll1".C13 = 1 THEN CAST(NULL AS TIMESTAMP(9)) ELSE "UnionAll1".C8 END AS C17,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NUMBER(NULL) ELSE "UnionAll1".C9 END AS C18,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C10 END AS C19,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NCHAR(NULL) ELSE "UnionAll1".C11 END AS C20,
CASE WHEN "UnionAll1".C13 = 1 THEN TO_NUMBER(NULL) ELSE "UnionAll1".C12 END AS C21
FROM (SELECT
"Extent1".ID AS ID,
"Extent1".BUSINESS_NAME AS BUSINESS_NAME,
"Extent1".EIN AS EIN,
"Extent1".START_DATE AS START_DATE,
"Extent1".END_DATE AS END_DATE,
"Extent1".COMMENTS AS COMMENTS,
"Extent1".HOURS_OF_OPERATION AS HOURS_OF_OPERATION,
"Extent1".PRIMARY_CONTACT_ID AS PRIMARY_CONTACT_ID,
TO_CHAR(NULL) AS C1,
TO_CHAR(NULL) AS C2,
TO_CHAR(NULL) AS C3,
TO_CHAR(NULL) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6,
CAST(NULL AS TIMESTAMP(9)) AS C7,
CAST(NULL AS TIMESTAMP(9)) AS C8,
TO_NUMBER(NULL) AS C9,
TO_CHAR(NULL) AS C10,
TO_CHAR(NULL) AS C11,
TO_NUMBER(NULL) AS C12,
1 AS C13
FROM "A$ENTITY".BUSINESSES "Extent1"
UNION ALL
SELECT
"Extent2".ID AS ID,
TO_CHAR(NULL) AS C1,
TO_CHAR(NULL) AS C2,
CAST(NULL AS TIMESTAMP(9)) AS C3,
CAST(NULL AS TIMESTAMP(9)) AS C4,
TO_CHAR(NULL) AS C5,
TO_CHAR(NULL) AS C6,
TO_NUMBER(NULL) AS C7,
"Extent2".TITLE AS TITLE,
"Extent2".FIRST_NAME AS FIRST_NAME,
"Extent2".MIDDLE_NAME AS MIDDLE_NAME,
"Extent2".LAST_NAME AS LAST_NAME,
"Extent2".SECONDARY_LAST_NAME AS SECONDARY_LAST_NAME,
"Extent2".SUFFIX AS SUFFIX,
"Extent2".DOB AS DOB,
"Extent2".DOD AS DOD,
"Extent2".RACE_TYPE AS RACE_TYPE,
"Extent2".GENDER AS GENDER,
"Extent2".MARITAL_STATUS AS MARITAL_STATUS,
"Extent2".PHYSICAL_CHARACTERISTICS_ID AS PHYSICAL_CHARACTERISTICS_ID,
0 AS C8
FROM "A$ENTITY".PERSONS "Extent2") "UnionAll1"
INNER JOIN "A$ENTITY".LEGAL_ENTITIES "Extent3" ON "UnionAll1".ID = "Extent3".ID
WHERE ("UnionAll1".ID = :p0) AND ROWNUM <= 2
) "Limit1"

And this is the code I am using which is generating the problem:

using (RepositoryContext context = new RepositoryContext(con))
{
var updateValue = context.Persons.Find(value.Id);
var tmpValue = (Person)value;

updateValue.DOB = tmpValue.DOB;
updateValue.DOD = tmpValue.DOD;
updateValue.EntityType = tmpValue.EntityType;
updateValue.FirstName = tmpValue.FirstName;
updateValue.Gender = tmpValue.Gender;
updateValue.LastName = tmpValue.LastName;
updateValue.MaritalStatus = tmpValue.MaritalStatus;
updateValue.MiddleName = tmpValue.MiddleName;
updateValue.Race = tmpValue.Race;
updateValue.ReferredTo = tmpValue.ReferredTo;
updateValue.SecondaryLastName = tmpValue.SecondaryLastName;
updateValue.Status = tmpValue.Status;
updateValue.Suffix = tmpValue.Suffix;
updateValue.Title = tmpValue.Title;
updateValue.Version = tmpValue.Version;

return context.SaveChanges();
}

In the SQL statement generated, I noticed that there are TO_NCHAR statements. When I change those to TO_CHAR, it fixes the problem. I have also found that if I set the IsUnicode value to false using the fluent interface for each string property in the class that also fixes the problem. However, I have some abstract base classes and I haven't figured out how to set the IsUnicode value on them yet.

Thanks.

yensid21

I did not have the latest version. When I updated to version 6.30.202 it did fix the problem. Thanks.

yensid21

I have a self-tracking entity object (Phone) which has a nullable boolean property (PrimaryPhone) and which has the concurrency mode set to fixed. If I retrieve a record from the table tied to the entity object (SO$ENTITY.PHONES) and there is a value other than null in the field tied to the boolean property, I can update the row. However, if the field has a null value and I try to update the row, an OptimisticConcurrencyException is thrown. If, however, I set the concurrency mode to none, then the row can be updated. This is true when different object contexts are used when the data is retrieved then later updated. This is due to the fact that the data is being sent through a WCF service and so I don't maintain the object context between calls.

This is the SQL generated:

UPDATE "SO$ENTITY".PHONES
SET ENTITY_ID = :p0,
COUNTRY_CODE = NULL,
PHONE_NUMBER = :p1,
EXTENSION = NULL,
PRIMARY_PHONE = :p2,
BEST_TIME_TO_CALL = NULL,
COMMENTS = :p3,
PHONE_TYPE = :p4,
STATUS = :p5
WHERE ID = :p6
AND ENTITY_ID = :p7
AND (COUNTRY_CODE IS NULL)
AND PHONE_NUMBER = :p8
AND (EXTENSION IS NULL)
AND PRIMARY_PHONE = :p9
AND (BEST_TIME_TO_CALL IS NULL)
AND COMMENTS = :p10
AND PHONE_TYPE = :p11
AND STATUS = :p12

As you can see, it is trying to compare the nullable boolean field PRIMARY_PHONE to a value :p9 (which was NULL) when it should be using the IS NULL comparison if the value was NULL previously. However, if there was a value before, then the above statement would be correct.

I was wondering if there is a way to fix this problem without turning off concurrency checking.

Thanks.

yensid21

I ran into the same issue, however when I set the Unicode property to false, it fixed the problem.

yensid21

I am trying to install dotConnect for Oracle version 6.30.160 and I am getting an incompatible version found error. The message says:

Devart.Data assembly version 5.0.242.0 is found in Global Assembly Cache. This product is compatible with Devart.Data.dll version 5.0.260.0.

However, when I use the gacutil.exe tool, there are no references to any Devart product. In addition, I checked the assembly folder in the Windows directory and the GAC folders in the Microsoft.NET folder and uninstalled or removed anything related to Devart. I then scanned the entire c: drive and removed all Devart folders. I also scanned the registry and removed all references to Devart.

yensid21

Thanks for your quick reply! Do you have any idea when the fix will be released?

yensid21

When calling the SaveChanges() method of the entity context I am getting an "Object reference not set to an instance of an object" error. I am using self-tracking entities, dotConnect for Oracle ver 6.10 Professional Trial and VS 2010.

This is the stack trace:

Code: Select all

Devart.Common.Entity.au.a(cq A_0, EntitySetBase A_1)
Devart.Common.Entity.au.b(bp A_0, StringBuilder A_1, a3 A_2)
Devart.Common.Entity.au.a(bp A_0, DbCommand A_1)
Devart.Common.Entity.co.a(bp A_0, DbCommandBase A_1)
Devart.Common.Entity.co.c()
Devart.Data.Oracle.Entity.OracleEntityProviderServices.a(DbProviderManifest A_0, DbCommandTree A_1)
Devart.Data.Oracle.Entity.OracleEntityProviderServices.CreateDbCommandDefinition(DbProviderManifest providerManifest, DbCommandTree commandTree)
System.Data.Common.DbProviderServices.CreateCommandDefinition(DbCommandTree commandTree)
System.Data.Common.DbProviderServices.CreateCommand(DbCommandTree commandTree)
System.Data.Mapping.Update.Internal.UpdateTranslator.CreateCommand(DbModificationCommandTree commandTree)
System.Data.Mapping.Update.Internal.DynamicUpdateCommand.CreateCommand(UpdateTranslator translator, Dictionary`2 identifierValues)
System.Data.Mapping.Update.Internal.DynamicUpdateCommand.Execute(UpdateTranslator translator, EntityConnection connection, Dictionary`2 identifierValues, List`1 generatedValues)
System.Data.Mapping.Update.Internal.UpdateTranslator.Update(IEntityStateManager stateManager, IEntityAdapter adapter)
System.Data.EntityClient.EntityAdapter.Update(IEntityStateManager entityCache)
System.Data.Objects.ObjectContext.SaveChanges(SaveOptions options)
System.Data.Objects.ObjectContext.SaveChanges()
EntityModuleBL.EntityManager.SetDependentRecordsToInactive(LegalEntity value)
EntityModuleBL.EntityManager.UpdateEntity(LegalEntity value, IIdentity identity)
EntityModuleTests.EntityModelBLTests.EntityManagerTests.CanDeleteAnEntity()

And this is an example of the code causing the problem:

Code: Select all

using (EntitiesContext context = new EntitiesContext())
{
	entity = (from le in context.LegalEntities.OfType()		
		where le.EntityId == entityId
		select le).FirstOrDefault();
		entity.StartTracking();
		entity.ReferredTo = "Test";
		
		context.LegalEntities.ApplyChanges(entity);
		context.SaveChanges();
}

The Person object is inherited from LegalEntity in the entity model. The above code is in a procedure which is called by another method which updates data in its own using block and then, outside the using block, it calls this method passing in the EntityId value. I verified that a Person object is being returned by the query and when the ReferredTo value is changed, the change tracking class is showing the object as modified and has the original and new values. I also tried running the code in LINQPad and got the same results.

Thanks!

Devart Forums

Search found 11 matches

ORA-00932 Error When Switching From 6.30 to 6.70

ORA-00942 Table Does Not Exist Multiple Schemas Code First

Resolved

ORA-12704 Error with Code First

Resolved

STE Fixed Concurrency With Nullable Value Throwing Exception

Ran Into Same Issue

Installer Detected Incompatible Version

Any Idea When?

Object Reference Not Set to an Instance of an Object