How to fix java.sql.SQLDataException: ORA-01858 error?

Question

I have a module in my system wherein the system ask a user to input two dates.

The system will use the two dates to get reports from the system.

My problem is with the query, the error is

java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

I don't know what the problem is

When I run this in navicat, it's working.

I've tried to remove the to_date, solutions here in stackoverflow said that I should use to_char but the input of the user is a String.

I'm out of options.

Here is the query

SELECT refer."DATE", 
        doc."DOCUMENTED_COMPLAINTS", 
        resolved."RESOLVED_COMPLAINTS", 
        dispatched."DISPATCHED_CREW", 
        intr."INTERRUPTION"
        FROM 
        (select 
           TO_CHAR((TO_DATE(#{dateFrom},'MM/DD/YYYY') + rownum -1),'MM/DD/YYYY') AS "DATE"
        from 
           all_objects
        where 
           rownum <= 
           TO_DATE(#{dateTo},'MM/DD/YYYY')-TO_DATE(#{dateFrom},'MM/DD/YYYY')+1) refer
        LEFT JOIN 
        (SELECT TO_CHAR(DATE_CREATED,'MM/DD/YYYY') as "CNR_DATE" , COUNT(TICKET_NO) as "DOCUMENTED_COMPLAINTS"
        FROM CNR_TICKET 
        WHERE CREATED_BY = #{name}
        GROUP BY TO_CHAR(DATE_CREATED,'MM/DD/YYYY')) doc
        ON refer."DATE" = doc."CNR_DATE"
        LEFT JOIN
        (SELECT TO_CHAR(DATE_CLOSED,'MM/DD/YYYY') as "CNR_DATE", COUNT(TICKET_NO) as "RESOLVED_COMPLAINTS"
        FROM CNR_TICKET
        WHERE CLOSED_BY = #{name}
        GROUP BY TO_CHAR(DATE_CLOSED,'MM/DD/YYYY')) resolved
        on refer."DATE" = resolved."CNR_DATE"
        LEFT JOIN
        (SELECT TO_CHAR(ACTION_DATE,'MM/DD/YYYY') as "DISPATCH_DATE", COUNT(TICKET_NO) as "DISPATCHED_CREW"
        FROM CNR_DIARY
        WHERE UPDATED_BY = #{name} AND CREW != null
        GROUP BY TO_CHAR(ACTION_DATE,'MM/DD/YYYY')) dispatched
        on refer."DATE" = dispatched."DISPATCH_DATE"
        LEFT JOIN 
        (SELECT TO_CHAR(INTERRUPTIONTS,'MM/DD/YYYY') as "INTR_DATE", COUNT(ID) as "INTERRUPTION"
        FROM INTERRUPTIONS
        WHERE OPERATOR = #{name} AND DELETED = '0'
        GROUP BY TO_CHAR(INTERRUPTIONTS,'MM/DD/YYYY')) intr
        ON refer."DATE" = intr."INTR_DATE"
        ORDER BY TO_DATE(refer."DATE",'MM/DD/YYYY') 

Sample input

Input 1 : 08/01/2019 : #{dateFrom}
Input 2 : 08/19/2019 : #{dateTo}

I can provide other codes you guys need. Just comment down. Thanks!

Answer 1

I think all of your variables which are string must be wrapped in single quotes.

I tried to reproduce your issue i.e. using string without single quotes in TO_DATE function.

select TO_DATE(11/11/2019,'MM/DD/YYYY') from dual;

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

-- Correct format:
select TO_DATE('11/11/2019','MM/DD/YYYY') from dual;

See the DEMO with error and corrected example.

So you will need to use single quotes as shown in the following example:

TO_DATE('#{dateTo}','MM/DD/YYYY') -- notice the single quotes around your variable name

Cheers!!