PeopleTools and Lifecycle Management - PSFT (MOSC)

MOSC Banner

ORA-1858 Error issued for prompt table in 8.53, worked in 8.49

edited Jun 3, 2014 9:12AM in PeopleTools and Lifecycle Management - PSFT (MOSC) 4 commentsAnswered

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.

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center