在让.NET Core中的Oracle数据库运行时遇到麻烦。
我能够创建与数据库的连接。
我的问题似乎是当我尝试执行语句时。
我首先使用带有DbContext的EF Core从数据库中检索单个实体,在这种情况下,我在下一行得到了异常:
var item = _context.CSProf.SingleOrDefault(e => e.Id == id);
异常和跟踪信息不多。
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,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()
...
我知道我仍然需要更改Schema,因此我也尝试在DbContext构造函数中进行此操作:
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();
然后我还尝试使用Oracle.ManagedDataaccess.Client OracleConnection类而不是通过EFCore的DbContext来更改这种模式,得到相同的错误:
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();
这是我的环境:
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
关于什么可能导致此“外部组件引发异常”或尝试什么的任何想法?
-------------------------------------- >
编辑:
关于实体的查询,我注意到打印到调试控制台的跟踪中除了“抛出了外部组件”外,还有一些其他信息:
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
当我将打印到调试控制台的SQL粘贴到SQL Developer中时,它会返回结果。但是从我的应用程序执行时,数据库似乎找不到该表。听起来我可能不在正确的模式中,但是当我执行以下命令时:
SELECT sys_context('userenv','current_schema') FROM dual
@Sam在注释中建议,它返回正确的模式名称,我认为这意味着我已经在正确的模式中了?