0

I'm attempting to connect to an Oracle 11g database from my ASP.NET MVC site. The format for the view and model work for both SQL Server and MySQL, so I shouldn't have problems there, as far as I can tell.

The application even attempts to query the Oracle database, but is throwing an Oracle.ManagedDataAccess.Client.OracleException in the EntityFramework.dll and returns an error

ORA-01918: user 'dbo' does not exist

The query that is constructed and sent to the database is:

 SELECT "Extent1"."Indext" AS "Indext", "Extent1"."Color" AS "Color" 
 FROM "dbo"."Tests" "Extent1"

This format seems to indicate its mimicking the SQL Server format.

How do I:

  1. Change the format of the query?
  2. If I can't change 1, then how should I be connecting to Oracle in this manner?

I have installed ODP.net. I am referencing System.Data.OracleClient.

Please let me know if I left off vital information.

Relevant database information:

SELECT * FROM ALL_TABLES WHERE OWNER = 'dbo' AND TABLE_NAME = 'Tests';

returns no results. (dbo is a SQL Server thing, near as I can tell.)

SELECT DBMS_METADATA.GET_DDL('TABLE', 'TESTS') FROM DUAL;

returns

CREATE TABLE "USERNAME"."TESTS" ( "INDEXT" NUMBER NOT NULL ENABLE, "COLO

which seems incomplete: "COLO" should be "COLOR"

SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'TESTS';

Returned nothing interesting, it would be a waste to paste it here, its 90% headers.

Relevant web.config information is as follows:

<add name="OracleConnectionString" 
     connectionString="Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xe))) Initial Catalog=Test; Persist Security Info=True; User ID=username; Password=password"  
     providerName="Oracle.ManagedDataAccess.Client" />

<provider invariantName="Oracle.ManagedDataAccess.Client" 
          type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342">
</provider>

<add name="ODP.NET, Managed Driver" 
     invariant="Oracle.ManagedDataAccess.Client" 
     description="Oracle Data Provider for .NET, Managed Driver" 
     type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
Kamurai
  • 143
  • 2
  • 4
  • 19
  • Please edit your question and include the results of the following query: `SELECT * FROM ALL_TABLES WHERE OWNER = 'dbo' AND TABLE_NAME = 'Tests'`. If that query doesn't find anything try `SELECT * FROM ALL_TABLES WHERE TABLE_NAME = 'TESTS'`. Also include the DDL of the table by executing `SELECT DBMS_METADATA.GET_DDL('TABLE', 'TESTS') FROM DUAL`. (May need to be 'Tests'). Thanks. – Bob Jarvis - Слава Україні Apr 11 '16 at 20:18
  • Just so you understand *why* the information requested above is needed: in Oracle all object names (e.g. tables, views, etc) are case-sensitive and by default are in UPPER CASE. The query in the question has used double-quotes, though, to look for "MixedCase" names. This is OK, but it's unusual enough that seeing the DDL is necessary to know what's really there. HOWEVER, the ORA-00942 error can also be caused if the user who's trying to access the table hasn't been granted the needed permission - so there could be a couple different things going on. – Bob Jarvis - Слава Україні Apr 11 '16 at 20:29
  • I appreciate the clarification Bob, but I don't have any control, that I can see, over how that select statement is generated. In order to get that statement to work on my db, I had to remove dbo and the double quotes from the table name. I did come across something about UPPER CASEing the statement, but I couldn't tell how to actually apply it to my situation here. I'll add your inqueries to the information in the question. – Kamurai Apr 11 '16 at 20:42
  • Well, OK, it sounds like you've narrowed it down to "how the framework is generating the query". I'm not familiar with EF6, so hopefully someone who is knowledgeable in that area will spot this and offer some ideas. Best of luck. – Bob Jarvis - Слава Україні Apr 11 '16 at 20:47
  • Okay Thanks @BobJarvis for trying to help. – Kamurai Apr 11 '16 at 20:54
  • Possible duplicate of [Oracle.ManagedDataAccess.EntityFramework - ORA-01918: user 'dbo' does not exist](http://stackoverflow.com/questions/27250555/oracle-manageddataaccess-entityframework-ora-01918-user-dbo-does-not-exist) – mason Apr 20 '16 at 15:49
  • It is possible, but the noted solutions there don't work for my issue and I can't even comment on that post. – Kamurai Apr 21 '16 at 16:19

0 Answers0