Oracle DB with .NET Core Using ODP.Net Provider - How to set the Schema

.net-core entity-framework-core odp.net oracle

Question

Having trouble getting things to work with an Oracle Database in .NET Core.

I am able to create a connection to the database.
My problem seems to be when I try to execute a statement. I first was using EF Core with a DbContext to retrieve a single entity from the DB, in which case I get the exception on the following line:

var item = _context.CSProf.SingleOrDefault(e => e.Id == id);

The exception and trace do not seem very informative.

    Exception has occurred: CLR/Oracle.ManagedDataAccess.Client.OracleException
    An exception of type 'Oracle.ManagedDataAccess.Client.OracleException' occurred in Microsoft.EntityFrameworkCore.dll but was not handled in user code: 'External component has thrown an exception.'
    at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader()
...

I know I will need to change the Schema anyway, so I also tried to do this in the DbContext constructor:

var conn = this.Database.GetDbConnection();
conn.Open();
Console.WriteLine("DB Server Version with open conn = " + conn.ServerVersion); //no problem here -version is "12.1.0.2.0"
var command = conn.CreateCommand();
command.CommandText = "ALTER SESSION SET CURRENT_SCHEMA = {SchemaName};";
int res = command.ExecuteNonQuery(); //Error on this line - 'External component has thrown an exception.' at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution( ...
command.Dispose();
conn.Close();

I then also tried to change the Schema like this, using Oracle.ManagedDataAccess.Client OracleConnection class instead of going through the EFCore's DbContext, getting the same error:

var oconn = new OracleConnection({ConnectionString});
oconn.Open();
Console.WriteLine("DB Server Version with open conn = " + oconn.ServerVersion); //no problem here -version is "12.1.0.2.0"
OracleCommand orclCmd = oconn.CreateCommand();
orclCmd.CommandText = "ALTER SESSION SET CURRENT_SCHEMA = {SchemaName};";
int res = orclCmd.ExecuteNonQuery(); // <---- Error on this line - 'External component has thrown an exception.' at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution( ...
Console.WriteLine("Result of Alter Session Schema = " + res);
orclCmd.Dispose();
oconn.Close();

Here's my env:

Windows Server 2016 
Developing / Debugging in VS Code 
.NETCoreApp,Version=v3.1 
Microsoft.EntityFrameworkCore/2.1.11 Microsoft.EntityFrameworkCore.Relational/2.1.11 Oracle.EntityFrameworkCore/2.19.60 
Oracle.ManagedDataAccess.Core/2.19.60

Any ideas on what might be causing this 'External component has thrown an exception' or what to try?

--------------------------------------

Edit:

On the query for the entity, I noticed that there is something a little more informative in the trace printed to the Debug Console other than just "External component has thrown":

Microsoft.EntityFrameworkCore.Database.Command: Error: 2020-03-30 10:18:31.153037 ThreadID:4   (ERROR)   OracleRelationalCommand.Execute() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00942: table or view does not exist

When I paste the SQL that is printed to the Debug Console into SQL Developer, it returns the results just fine. But when executed from my application, it seems that the database cannot find the table. Sounds like I might not be in the right Schema, but when I execute the following:

SELECT sys_context('userenv', 'current_schema') FROM dual

as suggested by @Sam in the comments, it returns with the correct Schema name, which I believe means I'm already in the correct Schema?

1
1
3/30/2020 2:53:51 PM

Accepted Answer

So, eventually I realized I was going about setting the Schema incorrectly. It seems that while the database connection had the correct Schema, Entity Framework may not have been aware of the correct Schema (or something like that).

I found this answer on another post that set me straight: [https://stackoverflow.com/a/35405309/3341533][1]

As shown in the linked answer, I just needed to add the following line to the OnModelCreating method for my Context with the corresponding Schema Name as argument:

modelBuilder.HasDefaultSchema(string schema);

This is not specific to Oracle, as I've actually had to do this before for MS SQL Server.
(Unfortunately, I didn't look at that other project and catch this mistake sooner.)

0
3/30/2020 3:05:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow