OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for Oracle
Post Reply
df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Wed 27 Jun 2018 00:41

We have two databases, one 11g and one 12c. I have an ODATA controller that has paging enabled (20 max rows). The 11g works fine and it is using the older paging style (..ROWNUM <= :p).

12c on the other hand is using the newer OFFSET..FETCH FIRST and is failing on the second page and any succeeding pages. By the way I cannot log the generated SQL on the 12c for the second page, it doesn't even make it to the logging.

I thought I have a solution with this EF core version but I hit a snag, please let me know what you think.

Thanks.

The error is:

OracleException: ORA-00904: "c": invalid identifier
  .(int )
  .      (int ,    )
Devart.Data.Oracle.OracleCommand.InternalExecute(CommandBehavior behavior, IDisposable disposable, int startRecord, int maxRecords, bool nonQuery)
Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, bool nonQuery)
  .  ​                 ​  ​  (CommandBehavior )
 .ExecuteDbDataReader(CommandBehavior )
  .ExecuteDbDataReader(CommandBehavior )
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary<string, object> parameterValues)
Microsoft.EntityFrameworkCore.Query.QueryMethodProvider+<_FastQuery>d__3.MoveNext()
System.Collections.Generic.List.AddEnumerable(IEnumerable<T> enumerable)
Microsoft.AspNet.OData.Query.TruncatedCollection..ctor(IQueryable<T> source, int pageSize)
Microsoft.AspNet.OData.Query.ODataQueryOptions.LimitResults<T>(IQueryable<T> queryable, int limit, out bool resultsLimited)

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Wed 27 Jun 2018 19:54

It is definitely a bug.

It is in the the ORDER BY clause of the inner-most generated SQL:

select * from
(select .... from (
select .... from
ORDER BY "c", "c", "c", "c", "
)
)

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Wed 27 Jun 2018 23:08

The error happens when there is an orderby clause follwed by a skip:

var query = db.EntitySet.Where(s => s.OPR_DATE == System.DateTime.Now.Date)
.OrderBy(s => s.OPR_DATE).Skip(20).ToList();



Devart.Data.Oracle.OracleException: 'ORA-00904: "c": invalid identifier'

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Thu 28 Jun 2018 19:28

Thank you for your report. We have reproduced the bug and are working on it. We will provide the fix as soon as possible.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Fri 13 Jul 2018 20:06

The bug with paging in EF Core 2.1 is fixed. The internal build with the fix: https://www.devart.com/pub/nuget_oracle_9_6_554.zip.

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Mon 16 Jul 2018 23:58

It fixed the paging in oracle 12 but broke oracle 11g.

OracleException: ORA-30484: missing window specification for this function. I traced the generated query and it looks like this internal build is now using a ROWNUM() function which does not exist. Use ROWNUM without the parenthesis and it should work.

Error in the generated SQL

WHERE ROWNUM() <= :p__p_5

Should be:

WHERE ROWNUM <= :p__p_5

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Tue 17 Jul 2018 12:49

Thank you for your report. We have reproduced the issue and are working on it.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Wed 18 Jul 2018 11:52

The bug with paging in Oracle 11.2 is fixed. The internal build with the fix: https://www.devart.com/pub/nuget_oracle_9_6_557.zip.

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Wed 18 Jul 2018 18:04

Version 9.6.557 fixed the ROWNUM on 11g.

BUT, it now broke the stored procedure/function mapping on the EF by raising an error of Identifier too long error.

I define the stored procedure to EF mapping like this:

[DbFunction(FunctionName = "DATA_PORTAL.pkgSecurity.Is_Authorized")]
public decimal IsAuthorized(string dataPortalRole, string forCompanySN, string withCompanySN, string senderCompanySN, string receiverCompanySN, DateTime oprDate)
{
throw new NotImplementedException();
}

This used to work in version 9.6.540 and generated a SQL statement of .... select...where... AND (DATA_PORTAL.pkgSecurity.Is_Authorized(:p___userRole_6,,....). Please note there is not quote on the generated SQL statement.


Version 9.6.557 on the other hand is generating a SQL of AND (\"DATA_PORTAL.pkgSecurity.Is_Authorized\"(:p___userRole_6,.

Notice that it is now inside a quote which raises the ORA-00972-identifier too long error.

You guys keep changing one thing after a new version release and I can't get to a stable version because it breaks things that worked with a previous version. Let me now if you are going to stick to the quoted version of the generated EF DbFunction.

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Thu 19 Jul 2018 15:34

df5 wrote: Wed 18 Jul 2018 18:04Version 9.6.557 on the other hand is generating a SQL of AND (\"DATA_PORTAL.pkgSecurity.Is_Authorized\"(:p___userRole_6,.

Notice that it is now inside a quote which raises the ORA-00972-identifier too long error.
Thank you for your report. We will provide the fix as soon as possible.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Wed 25 Jul 2018 17:04

The bug with quoting a stored function name, when the FunctionName argument of the DbFunction attribute includes package and/or schema names, in EF Core 2 is fixed. The internal build with the fix is available at https://www.devart.com/pub/nuget_oracle_9_6_559.zip.

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Mon 06 Aug 2018 15:50

Hi Shalex,

I can't verify the update because I'm getting LicenseException: Feature is not supported.

I am still evaluating the devArt component and I can't justify the expense to management if this thing does not work correctly.

Could you extend the evaluation license time out?

Thanks.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Tue 07 Aug 2018 17:38

df5 wrote: Mon 06 Aug 2018 15:50Could you extend the evaluation license time out?
We have sent the new license file (30 days more) by email.

df5
Posts: 15
Joined: Wed 20 Jun 2018 23:22

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by df5 » Tue 07 Aug 2018 22:10

It is working on this version.


Generated SQL for the DbFunction is "DATA_PORTAL"."pkgSecurity"."Is_Authorized"(…...

My experience is that when using quote on the generated SQL it becomes case sensitive.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)

Post by Shalex » Fri 10 Aug 2018 13:02

New build of dotConnect for Oracle 9.6.570 is available for download: viewtopic.php?f=1&t=37559.

Post Reply