Going through my first attempt ever to connect to a Oracle database with a .Net application.
Using Visual Studio 2012, I have downloaded and installed the latest version of odp.net, actually both the 32 and 64bit versions.
Application is targeting the 4.5 Framework, Latest version of Entity Framework installed (but not important I think at this point)
Added a reference in my project to the Oracle Data Provider ( tried with both 2.0 and 4.0 )
Created a connection string with EZ Connect, stored in web.config file.
I have tried 2 different projects to use EF and one without. I've looked at several tutorials and can't see what I'm missing or doing wrong.
Trying to create a edmx or using a datasource adapter brings up the connection wizard or prompts to use the existing one, but no matter what I try, there are no tables, views or procedures listed.
From Server Explorer, I can open the Data Connections and verify that the connection to the Oracle database is Open. During the Connection Wizard, there were no errors, and all the databases available were listed. Yet, connecting to and opening the connection to this specific database or anyone for that matter no tables, views or procedures are listed.
I have little to no knowledge about Oracle, I've been thrown to the wolves. The Oracle dba for some reason is unable to help me solve this. He is able to login with my account info and view the data, but gets the same thing I get with JAVA. Clue?
Any help is appreciated.
did your user you use for login own any objects? Try to login with this user with sqldeveloper or similar and check if you have user objects. When not create a dummy object to verify.
I believe the answer is no.
I should be setup as a user that has access to read data, but not to create or modify etc.
As I understand, I have a user account and assigned Roles, but within VS, if I browse the Privileges for Roles, the Roles show up, but nothing granted. I do not show up in list when selecting the 'User' option vs 'Roles'. Again, not real knowledgeable about what I'm looking at here, but seems like something on the database side is not setup for me quite right.
normaly a user who get read access to data, the dba setup a second user and grant this rights. So in this case your user did not own objects. You must then reference this objects with <user>.<object> name.
But i belive you are not sure about this point, so maybe you should check this with your dba, or try select * from all_objects (in case you can excute a query). With this query you should see all objects you can access.
i think you miss understood me.
select * from all_objects shows also objects that you did not own but you can access.
select * from user_objects show all your objects and i expect here 0.
Ok, I will try this again later.
Currently downloaded and installing the client to see what I can access from my pc using it as I only have the odp.net installed.
Pretty much the end of the weekend and the day for me, I appreciate the help so far and will pick this up on Monday.
Ok, so I was able to do that pretty quickly and I hate leaving something unfinished on the weekend, or at least this last piece.
The query you wanted me to run had a result of 2
I was from SQLPlus able to login and execute a procedure and get 12 rows back.
So I'm still with nothing viewable from VS in the tables, views etc
From my dba:
Yes if you own objects you will see them. But we want to let you see objects that you don’t own.
Hoping they can figure this out, or someone can help turn on some light bulbs, but still seems to me to be some permission thing. I'm more adept with SQL and I can't provide much insight with Oracle.
Beginning to think the issue is on my end with Visual Studio 2010 or 2012.
As I work with this more, I'm discovering a few more things.
So I installed both the 32 and 64 bit versions of ODP.NET, I've gone in and added a reference to the Oracle Data Provider (Oracle.DataAccess v4)
When I attempt to create a Data Connection for say a SqlDataSource Object, I never get the Oracle Data Provider for .NET option, only the Microsoft versions. Where am I going wrong on this to get this to show up?
Oracle has this really annoying default where it only shows things you own, and not things you can "see". Here's how you change that.
In Server Explorer, right click on the connection and click on "Modify Connection". Click on the "Filters" tab.
There's a grid there, where the first option is Displayed Schemas. Select that, then click on the "..." button just above it. Now what you have to do is add the schemas where the objects you want to see are. Your DBA can tell you that. Click "Ok", when done, then click "Update" at the bottom of the grid. Click "Ok" to close the window.
Now if you open or refresh the connection, you should see things appearing. Entity Framework will also see them now if you try to build a model from the database.