.NET ORA-01858: a non-numeric character was found where a numeric was expected

Question

I am trying to construct named parameters but receive an error.

Couldn't get data from Database Oracle.DataAccess.Client.OracleException ORA-01858: a non-numeric character was found where a numeric was expected at Oracle.DataAccess.Client.OracleException.HandleErrorHelper...

private static void AddCriteria(IDbCommand command, string column, object value, string sqlOperator = "=")
{
    var parameter = command.CreateParameter();

    if (value is DateTime)
    {
        value = FormatSqlDate((DateTime)value);
    }

    parameter.ParameterName = DbHelper.GetParameterSql(parameter, "P" + (command.Parameters.Count + 1));
    parameter.Value = value;
    command.Parameters.Add(parameter);

    command.CommandText += string.Format(" {0} {1} {2} {3}", (command.Parameters.Count > 1 ? "AND" : "WHERE"), column, sqlOperator, parameter.ParameterName);
}

Following query is constructed:

SELECT *
FROM trade LEFT JOIN 
     findetail
     ON trade.trade = findetail.trade LEFT JOIN
     fintransact 
     ON findetail.fintransact = fintransact.fintransact
WHERE trade.trade = :P1 AND acctdate = :P2

While parameters are

:P1 - 2298056
:P2 - TO_DATE('2014-12-31T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')

P2 should not contain the TO_DATE function and its parameter but only the DateTime value. The remainder should go in the query text — Steve, Mar 22 '16 at 11:07

score 0 · Answer 1 · answered Mar 22 '16 at 11:08

Variables can only be values. You cannot have a function call like TO_DATE as text in a variable.

You need to put all SQL in the query text and only extract the real variables (like the actual time) into bound variables.

In this case, why don't you pass a properly parsed .NET DateTime as your value?

.NET ORA-01858: a non-numeric character was found where a numeric was expected

1 Answers1