ORA-00942 Table Does Not Exist Multiple Schemas Code First

yensid21 · Post by **yensid21** » Mon 19 Sep 2011 14:35

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.

Shalex · Post by **Shalex** » Thu 22 Sep 2011 11:25

Have you turned on the DeleteDatabaseBehaviour.Schema mode?
http://www.devart.com/blogs/dotconnect/ ... eBehaviour

yensid21 · Post by **yensid21** » Thu 22 Sep 2011 12:35

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.

Shalex · Post by **Shalex** » Fri 23 Sep 2011 15:57

Thank you for the information. We are investigating the issue.

Shalex · Post by **Shalex** » Wed 28 Sep 2011 13:10

The bug with setting GRANT REFERENCE when creating FK between tables from different schemas for config.DatabaseScript.Schema.DeleteDatabaseBehaviour.Schema is fixed. We will post here when the corresponding build of dotConnect for Oracle is available for download.

Shalex · Post by **Shalex** » Mon 24 Oct 2011 16:21

New build of dotConnect for Oracle 6.50.237 is available for download now!
It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only).
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=22379 .