SqlFunctionExpression for to_char inverts the arguments in the generated sql if the function name is uppercased

[iulianb]

I'm implementing a custom db expression and I've noticed the issue mentioned in the title. I've worked out the source of the issue in the OracleQuerySqlGenerator class of the Oracle.EntityFrameworkCore.Query.Sql.Internal namespace, the function that handles the translation of the TO_CHAR function inverts the indexes of the arguments. Not sure if it's intentional, although I can't understand why it would be.

new SqlFunctionExpression(
     // if this is uppercased then its arguments are inverted in the generated sql, it works fine when 
     // lowercased because the translation code tests for equality on the uppercased version
     "to_char", 
     new SqlExpression[]
     {
           // ...
     },
     nullable: false,
     argumentsPropagateNullability: new[] { false, false },
     type: args[0].Type,
     typeMapping: args[0].TypeMapping)

[alexkeh]

Which Oracle EF Core version are you using? Can you share a TO_CHAR code sample that has this error?

[iulianb]

Oracle.EntityFrameworkCore 7.21.13

I've added you to a private repository that demonstrates the issue.

[alexkeh]

@iulianb I ran your test case and lower cased "TO_CHAR", to "to_char",, I see the following query get generated:

SELECT "e"."ID", "e"."BIRTH_DATE"
FROM "EMPLOYEES" "e"

ORDER BY NVL2("e"."BIRTH_DATE", to_char("e"."BIRTH_DATE", 'hh24miss'), '0000')

I was able to execute the query without an error. I also ran the test app with "TO_CHAR",.

SELECT "e"."ID", "e"."BIRTH_DATE"
FROM "EMPLOYEES" "e"

ORDER BY NVL2("e"."BIRTH_DATE", TO_CHAR('hh24miss', "e"."BIRTH_DATE"), '0000')

Still no error. I'm running against a 23ai DB. Is there something else I need to do to reproduce the problem?

[iulianb]

This fails on 11g (11.2.0.3.0) with ORA-01722: invalid number. It really isn't an issue for us in the current state, so long it works with lowercased version. It could become one if the code that causes the issue is changed to test both lower and uppercased versions.

[alexkeh]

The source of the problem is likely the newest versions of ODP.NET and Oracle EF Core no longer support DB 11.2. The providers are undergoing fairly rapid changes in each new release as new versions of .NET and EF Core are released once a year.

Oracle DB 11.2 reached end of Extended Support in 2020. Oracle provided Market Driven Support for DB 11.2 for two more years. Since then, Oracle stopped support (and testing) newer Oracle DB client updates with DB 11.2. And for many years during the extended 11.2 support phase, ODP.NET testing certified with the DB 11.2.0.4 version, not 11.2.0.3.

Oracle.EntityFrameworkCore 7.21.13 was released in January, 2024.

The recommended resolution is to upgrade your DB to a supported version, 19c or higher.