Receiving ORA-0858 in Oracle DataReader but not in SQL Developer-CodePudding

I have a query that was built and works using SQL Developer. When I use the same query in an Oracle DataReader object, I receive ORA-01858: a non-numeric character was found where a numeric was expected

If I remove the clause to check the CRTDDATE column, the query works in the DataReader.

query:

SELECT count(distinct(H.id)) AS Completed, T.Cat, 'Task' as Type 
  FROM HISTORY H 
 INNER JOIN Tasks T ON H.id = T.id 
 WHERE H.Step In ('1.41', '1.61', '6.41', '6.61') 
   AND T.Cat = :cat 
   and H.CRTDDATE >= :sdate and H.CRTDDATE <= :edate 
 GROUP BY T.Cat, 'Task'

Code:

using (OracleConnection conn = new OracleConnection(ConnectionString))
{
    OracleCommand cmd = new OracleCommand(query, conn);
    cmd.Parameters.Add("sdate", startDate);
    cmd.Parameters.Add("edate", endDate);
    cmd.Parameters.Add("cat", cat);
    await conn.OpenAsync();

    using (var dr = await cmd.ExecuteReaderAsync())
    {
        if (dr.HasRows)
        {
            while (await dr.ReadAsync())
            {
                var report = new IPTCompletedReport();
                var count = dr.GetString(0);
                report.Completed = 0;
                report.IPT = dr.GetString(1);
                report.Type = dr.GetString(2);
                results.Add(report);
            }
        }
    }
}

Values:

startDate = {1/1/2021 12:00:00 AM}
endDate = {8/17/2022 12:00:00 AM}
cat = "DRV"

The error occurs at this line: using (var dr = await cmd.ExecuteReaderAsync())

How can I change the query to allow the DataReader to accept it? Should I use a DataAdapter instead?

I have several other queries and DataReaders in this file that are functioning properly. Most of them have where clauses featuring date checks.

CodePudding user response：

I don't know C# nor DataReader, but - error you got (and found line that causes it):

and H.CRTDDATE >= :sdate and H.CRTDDATE <= :edate

means that Oracle - in DataReader - can't implicitly convert values you provided as :sdate and :edate into a valid DATE datatype value. Oracle SQL Developer, on the other hand, did it and query worked.

Let me illustrate the problem.

Test table with one column whose datatype is DATE; as sysdate function returns value of that datatype, insert works OK:

SQL> create table test (datum date);

Table created.

SQL> insert into test values (sysdate);

1 row created.

SQL> select * From test;

DATUM
----------
17.08.2022

Really, today is 17th of August 2022.

Let's set date format and date language:

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> alter session set nls_date_language = 'croatian';

Session altered.

If I pass strings (note that '17.08.2022' is a string; to us, humans, it represents date, but Oracle has to implicitly try to convert it to date datatype:

SQL> select * from test where datum between '17.08.2022' and '20.08.2022';

DATUM
----------
17.08.2022

Oracle succeeded! Nice! OK, but - what if I pass a string that - instead of numeric month value - contains month abbreviation, written in English (remember that I previously set date language to Croatian!):

SQL> select * from test where datum between '17-aug-2022' and '20-aug-2022';
select * from test where datum between '17-aug-2022' and '20-aug-2022'
                                                         *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Aha ... here's the error, the same as you got. Oracle wasn't able to implicitly convert string '17-aug-2022' into date datatype value.

What if I pass Croatian month name (it is "kolovoz" for "August"):

SQL> select * from test where datum between '17-kol-2022' and '20-kol-2022';

DATUM
----------
17.08.2022

SQL>

Right, not it works again.

So, what should you do? Take control over it! One option is to use to_date function with appropriate format model. I'll again pass English month name, but this time it'll be OK:

SQL> select * from test where datum between to_date('17-aug-2022', 'dd-mon-yyyy', 'nls_date_language = english')
  2                                     and to_date('20-aug-2022', 'dd-mon-yyyy', 'nls_date_language = english');

DATUM
----------
17.08.2022

SQL>

Or, if you don't want to use that, use date literal (which always consists of the date keyword followed by date value in yyyy-mm-dd format enclosed into single quotes):

SQL> select * from test where datum between date '2022-08-17' and date '2022-08-20';

DATUM
----------
17.08.2022

SQL>

If you can't do any of these, then make sure that you passed values which can be implicitly converted to date datatype value.

Unfortunately, as I said, I don't know C# so I can't suggest anything smart (related to C#), but I hope that now - that you know what caused the error - you'll be able to fix it.

CodePudding user response：

Using the comment from @madreflection, I added cmd.BindByName = true and that fixed my problem.

Thanks to everyone who provided suggestions.