dotConnect for Oracle can't see views and table

dotConnect for Oracle can't see views and table

Postby ckelley » Wed 24 Mar 2010 04:51

We're using dotConnect for Oracle 5.35.79.0 to access two views and a table via an Entity Framework model. We're connecting to Oracle 11g in test and 10g in production. Test is working great.

We copied the views and table to a production schema and changed the connectionstring for the entity model to the userID and password of the production schema owner. When we fired up the production application, we got "ORA-00942: table or view does not exist" errors when attempting to access any of the views or table. The production connection string credentials were good enough to login to Oracle, but not good enough to see the views and table.

We then changed the production Entity Model connection string in the web.config file to point back to the test database. The production instance was able to access the test db objects without a problem.

My Oracle guy insists that the production string credentials are that of the production schema owner and therefore should be able to see the views and table. He is pointing his figure at dotConnect. If we were using Sql Server, I'd turn on the profiler and find out what is really going on. My Oracle guy says that Oracle doesn't have a similar profiler.

Does DevArt provide any way to see the conversation between dotConnect and Oracle? Any ideas? Help!
ckelley
 
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

Postby AndreyR » Wed 24 Mar 2010 11:10

You can add DBMonitor to your application to watch the queries sent to database.
However, in your case the situation seems to be simple.
I recommend you to edit the model file and remove the "Schema="" attribute.
In case of .edmx use any XML Editor you like, .edml file has the Store model that can be edited in design time.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Removing the Schema property and Schema qualifiers worked

Postby ckelley » Wed 24 Mar 2010 15:06

Thank you for saving my bacon, Andrey. :D

In addition to inserting Schema="xxx" properties in the .edmx file, dotConnect for Oracle also qualified the view names with the schema name in the sql statements generated to read the views. After removing the schema="xxx" properties and the schema qualifiers from the view names in the .edmx sql, everything worked as expected.

I suppose that the schema needs to be included in cases where views from multiple schemas are represented in the same Entity Model.
ckelley
 
Posts: 5
Joined: Wed 24 Mar 2010 04:10
Location: Berkeley,CA USA

Postby AndreyR » Thu 25 Mar 2010 10:50

You are correct, the reason for schema name adding is cross-schema support.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16


Return to dotConnect for Oracle