BUG: Bug 29593228 does not seem to be fixed in Oracle.EntityFrameworkCore beta 4

Plankman2k

Hello.

According to the documentation, the above bug is supposed to be fixed: Bug 29593228 – “ORA-00904: "<table alias>"."<column name>": INVALID IDENTIFIER” occurs when performing a Scaffolding operation against an 11.2 DB.

At our company, we are running Oracle 11.2. I am trying to make a .net core WebApi project and am trying to scaffold a table and I get this error:

2019-07-18 09:43:46.204204 ThreadID:1   (ERROR)   OracleDatabaseModelFactory.GetColumns() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "C"."IDENTITY_COLUMN": invalid identifier

   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.ExecuteReader()

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.GetColumns(DbConnection connection, String tableFilter, DatabaseModel databaseModel)

2019-07-18 09:43:46.209581 ThreadID:1   (ERROR)   OracleDatabaseModelFactory.GetTables() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "C"."IDENTITY_COLUMN": invalid identifier   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.ExecuteReader()

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.GetColumns(DbConnection connection, String tableFilter, DatabaseModel databaseModel)

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.GetTables(DbConnection connection, Func`3 tableFilter, DatabaseModel databaseModel)

2019-07-18 09:43:46.266888 ThreadID:1   (ERROR)   OracleDatabaseModelFactory.Create() :  Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00904: "C"."IDENTITY_COLUMN": invalid identifier

   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.ExecuteReader()

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.GetColumns(DbConnection connection, String tableFilter, DatabaseModel databaseModel)

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.GetTables(DbConnection connection, Func`3 tableFilter, DatabaseModel databaseModel)

   at Oracle.EntityFrameworkCore.Scaffolding.Internal.OracleDatabaseModelFactory.Create(DbConnection connection, IEnumerable`1 tables, IEnumerable`1 schemas)

I see in the beta 4 documentation there is that UseOracleSQLCompatibility(string version) for when you set the context connection to use. Is there any way to specify this in the scaffold command?:

dotnet ef dbcontext scaffold "User Id = <user>;Password=<pass>;Data Source=<connectionstring>;Connection Timeout=600;min pool size = 0;connection lifetime=18000;PERSIST SECURITY INFO=True;" Oracle.EntityFrameworkCore --table TRAILER_DTL  -o Models -f

I did find a dirty workaround to add a view called your_schema_name.user_tab_cols and "add" the IDENTITY_COLUMN. If I do that, I then get a null reference exception about a trigger column.

Any assistance will be greatly appreciated. I've spent about two days trying to find a way to do this.

Plankman2k

Hi Alex

Thanks, I ran the command and get the following version:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Sorry, I was under the impression it was 11.2. I will have to check with the DBA when they will be upgrading.

Alex Keh-Oracle

Your error is more similar to Bug 29173924, which hasn't been published. That bug has been fixed and should not appear in Beta 4.

Please turn on ODP.NET tracing, In the trace output, please post what the text for the line that begins with "Server Version" says. Instructions to turn on tracing are in the beta doc PDF included with the NuGet package.

Also, can you add the following line of code that outputs the server version (or the equivalent if it's not a console app)?

• Console.WriteLine(conn.ServerVersion);

This will definitively identify the DB server version you are using.

Plankman2k

Hi Alex

I've tried to enable the ODP.Net tracing, but it doesn't seem to be logging anything. I am getting this error from the CLI command when I try to scaffold the table to generate the model. We are definitely running on Oracle 11.2. There are plans to upgrade to Oracle 12, but we are waiting to acquire additional hardware. Is there a way to specify the Oracle version in the CLI command?

Plankman2k

In the error message I get, there is this line that has Server Version:

2019-07-22 07:59:33.838436 ThreadID:1   (MAP)     OracleDatabaseModelFactory.GetColumns() : Server Version: NOT 11.2

But it definitely is not 12

Alex Keh-Oracle

Please connect to the DB with EF Core and run the following query: SELECT * FROM V$VERSION

That will tell us which DB version you are using. If it's not 11.2 and it's 12c or later, then the DB version is likely some earlier Oracle version.

Plankman2k

Hi Alex

Thanks, I ran the command and get the following version:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

Sorry, I was under the impression it was 11.2. I will have to check with the DBA when they will be upgrading.