Please Help: ORA-01861: literal does not match format string
717294Aug 13 2009 — edited Aug 14 2009Hi All,
We are getting the ORA-01861 error but we are unable to reproduce the same error. It comes randomly..Here are the details..
Oracle version: 10.2.0.3
------------
NLS_DATE_FORMAT DD-MON-RRRR
NLS_DATE_LANGUAGE AMERICAN
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
--------------
The following sql is in a pl/sql procedure which is created using dynamic sql and executes as "execute immediate...".
TABLE: ECG
EGDN DATE
EGTN NUMBER
The values are:
EGDN - 22-JUN-2009
EGTN -
The query:
SELECT MAX ( to_date(( TO_CHAR(nvl( EGDN, '01-JAN-1900'), 'DD-MON-YYYY')||' '||decode(length(EGTN),3,('0'||to_char(EGTN)),2,('00'||to_char(EGTN)),1, ('000'||to_char(EGTN)),4,to_char(EGTN))),'DD-MON-YY HH24MI'))
FROM ECG
WHERE to_date(( TO_CHAR(nvl( EGDN, '01-JAN-1900'), 'DD-MON-YYYY')||' '||decode(length(EGTN),3,('0'||to_char(EGTN)),2,('00'||to_char(EGTN)),1, ('000'||to_char(EGTN)),4,to_char(EGTN))),'DD-MON-YY HH24MI') <= to_date ( '06/17/2009 07:22:00 AM ', 'MM/DD/YYYY HH:MI:SS AM')
The problem is, the SQL runs fine sometimes and raises the error sometime.
the SQl have been working fine for a long time and started failing recently, as per the DBAs no changes to the NLS variables. No other SQL in the procedure is altering the NLS_DATE_FORMAT at session level.
We created a PL/SQL block with this SQL and excuted like however it is executed in production, but couldn't reproduce the error.
Any help to resolve this issue would be greatly appreciated. Thanks,Bala