ORA-1858 Error issued for prompt table in 8.53, worked in 8.49
We are in the process of upgrading from 9.0/PT 8.49 to 9.2/ PT 8.53. We have a custom page that contains a date field with a custom prompt view. When clicking on the search glass in 9.2/8.53, we get the following error:
ORA-01858: a non-numeric character was found where a numeric was expected
The error references the prompt view. The sql to the view is below:
SELECT START_DT
FROM PS_TL_CALENDAR
WHERE PERIOD_ID = 'PSWEEK'
AND START_DT BETWEEN (SYSDATE-90) AND (SYSDATE+90)
AND start_dt >= '01-nov-2009'
ORDER BY start_dt ASC
If I run the sql in sql developer, it does not abend and returns data. The page functions in 9.0/8.49. I traced both of them and the sql in the trace files is identical. Both 9.0 and 9.2 are on the same version of Oracle.