OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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)
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)
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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", "
)
)
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", "
)
)
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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'
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'
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
Thank you for your report. We have reproduced the bug and are working on it. We will provide the fix as soon as possible.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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
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
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
Thank you for your report. We have reproduced the issue and are working on it.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
Thank you for your report. We will provide the fix as soon as possible.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
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.
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.
Re: OracleException: ORA-00904: "c": invalid identifier on Oracle 12c using version 9.6.540 (Devart.Data.Oracle.EFCore)
New build of dotConnect for Oracle 9.6.570 is available for download: viewtopic.php?f=1&t=37559.