ODP.NET: Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01841 on .NET 5 (Core) application

Question

trying to execute a select with Oracle.ManagedDataAcces.Client (ODP.NET) with a C# .NET 5 web app.

Oracle.ManagedDataAcces.Client version is latest 3.21.1 as of 02/06/2021.

The error:

Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware1 An unhandled exception has occurred while executing the request. Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-01841: (full) year must be between -4713 and +9999, and not be 0 at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)

This is the query code

   public async Task<int> GetMyCount(string userName, int THE_YEAR)
    {
        try
        {
            string TEST = "STACK_OVERFLOW_TEST";
            builder.Clear();

            builder.Append($@" SELECT COUNT(*) 
                FROM {configuration.SCHEMA}.SOME_TABLE CA 
                INNER JOIN {configuration.SCHEMA}.SOME_OTHER_TABLE CS 
                ON CS.ID=CA.ID ");

            if (viewNotAll)
            {
                builder.Append($" INNER JOIN {unitOfWork.oracleDbOptions.DBSchemaQP}.ANOTHER_TABLE UT ON CA.SOME_FIELD = UT.SOME_FIELD ");
            }

            builder.Append(@$" WHERE CA.DATE_TO_FILET BETWEEN TO_DATE(CONCAT('0101', :THE_YEAR),'DDMMYYYY') 
            AND TO_DATE(CONCAT('3112', :THE_YEAR),'DDMMYYYY') ");


            if (TEST == "NO")
                builder.Append(" AND CS.TEST_FIELD=0 ");
            else
                builder.Append(" AND CS.TEST_FIELD=:THE_TEST_FIELD ");


            int result = 0;

            using (var cmd = unitOfWork.connection.CreateCommand())
            {

                cmd.Parameters.Add("THE_YEAR", OracleDbType.Int16, 4, THE_YEAR, ParameterDirection.Input);
                cmd.Parameters.Add("THE_TEST_FIELD", OracleDbType.Varchar2, 20, userName, ParameterDirection.Input);

                cmd.CommandText = builder.ToString();
                using (var reader = await cmd.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        result = reader.IsDBNull(0) ? 0 : reader.GetInt32(0);
                    }
                }

                return result;
            }
        }
        catch (Exception)
        {
            throw;
        }
    }

Executing the query in Oracle SQL Developer or PL/SQL works. Also from visual studio server exporer works. A window pops up asking for the parameter, typed as VARCHAR2 and it executes the query fine.

I Tried everything. Changing type from Int16, Int32, VARCHAR2 with various length for testing purposes, based also on the Oracle .NET type guide, which states that Int16 is for number of lenght 4, which is the year in my case. Nothing.

I've also tried to retrieve SQL query history by using what is shown in this stackoverflow question, to discover that apparently, queries fired from outside don't get logged. I can't find them with this query.

The only way it works is by directly concatenating the value in the query:

builder.Append(@$" WHERE CA.SOME_DATE BETWEEN TO_DATE(CONCAT('0101', '{THE_YEAR}'),'DDMMYYYY') 
            AND TO_DATE(CONCAT('3112', '{THE_YEAR}'),'DDMMYYYY') ");

But I would like to use parameters to avoid SQL injection.

What am I doing wrong?

Answer 1

Better use

builder.Append(@$" WHERE CA.DATE_TO_FILET BETWEEN :aDate AND :bDate");

cmd.Parameters.Add("aDate", OracleDbType.Date, ParameterDirection.Input).Value = new DateTime(THE_YEAR, 1, 1);
cmd.Parameters.Add("bDate", OracleDbType.Date, ParameterDirection.Input).Value = new DateTime(THE_YEAR, 12, 31);