ORA-00942 with read only account

krwyth · Post by **krwyth** » Mon 10 Jan 2011 22:48

We are using dotconnect for oracle and had to move to a read only Oracle user for our application per company policy.

Even though the account has permissions to read all the tables and views (the only thing it needs to do for this app) we are getting ORA-00942: table or view does not exist.

Snippet from our edmx:

Snipped from web.config:

Any ideas on this?

krwyth · Post by **krwyth** » Mon 10 Jan 2011 23:30

I should note that we are moving into another environment also for testing.

Could this be a licensing issue?

AndreyR · Post by **AndreyR** » Tue 11 Jan 2011 11:10

The reason of the problem seems to be the Schema name
Remove the Schema element and everything should work correctly.

krwyth · Post by **krwyth** » Tue 11 Jan 2011 15:38

I still get an error after removeing the 'Schema=' property. Although this time it is a:

System.ServiceModel.DomainServices.Client.DomainServiceFault

Pardon my lack of oracle expertise but doesn't the application need to know the name of the schema that it is querying from?

Is there another part of the edmx or connect string that this is specified?

AndreyR · Post by **AndreyR** » Tue 11 Jan 2011 16:34

If Entity Set does not have the Schema attribute specified, the current schema is used.
As for the DomainServiceFault class, there should be more information concerning this error, this is a general class just wrapping the communication error.

krwyth · Post by **krwyth** » Tue 11 Jan 2011 17:02

It turns out there were some hardcoded non-EF queries which didn't specify the schema, so they failed when the user was different than the schema owner.

The Schema="SCHEMA_OWNER" is needed for our EF queries. Thanks

krwyth · Post by **krwyth** » Tue 11 Jan 2011 21:40

Well I thought this was resolved it works with the new account locally, but when I move to the test environment, I get this now:

error 0175: The specified store provider cannot be found in the configuration, or is not valid.. ---> System.Data.MetadataException: Schema specified is not valid. Errors: OracleEntityModel.ssdl(2,88) : error 0175: The specified store provider cannot be found in the configuration, or is not valid.

krwyth · Post by **krwyth** » Tue 11 Jan 2011 23:01

The app works after I installed dotconnect on the test server. However, we probably can't do this in production.

Pardon my ignorance but what's the best way to include our needed dotconnect binaries in our application deploy without installing the full package on the server?

AndreyR · Post by **AndreyR** » Wed 12 Jan 2011 10:03

You should put the Devart.Data, Devart.Data.Oracle, and Devart.Data.Oracle.Entity assemblies in the Bin folder of your application (or place these assemblies in Global Assembly Cache).
Make sure that your application is licensed correctly (take a look at the Licensing article for more information).
Register the dotConnect for Oracle provider as well:

Code: Select all

This information is available in the Deployment article.

bartdk · Post by **bartdk** » Fri 25 Feb 2011 10:14

Even after removing the Schema=[schema owner] we still get the error telling us the table does not exists :

Code: Select all

Devart.Data.Oracle.OracleException: ORA-00942: table or view does not exist

We are currently unable to re-use an entity framework project on multiple databases, because of the hardcoded schema.

AndreyR · Post by **AndreyR** » Fri 25 Feb 2011 11:38

Please check that your model does not contain Defining Queries, as schema can be hard-coded in these elements as well.