E-PC: TO_DATE Function in SQL Definition Causes ORA-01858 in 8.49.19 (Doc ID 789080.1)

Symptoms

Issue deals with Oracle database environments that have custom SQL definitions using the TO_DATE SQL function.

For example:

SELECT a.coverage_elect
FROM ps_leave_plan a
WHERE a.emplid = 'XXXXX'
AND a.empl_rcd = 0
AND a.effdt >= TO_DATE('15-jan-2007')
AND a.coverage_elect = 'E'

Then execute this SQL via PeopleCode, for example:
&ret = SQLExec(SQL.GCS_TEST, &result);

Starting in PeopleTools 8.49.19, the error "ORA-01858: a non-numeric character was found where a numeric was expected" will be returned for SQL definitions using the TO_DATE function. The same SQL does not cause errors in earlier PeopleTools releases.

Important: This document is specific to PeopleTools 8.49.19 and up, but not 8.50 or higher.  For 8.50 and higher, please see:

E-PC: Where is the OracleUseDescriptors Parameter to fix Date Format issues in 8.50 and up? (<Note  1116973.1>)

Changes

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.