Kbase P19852: ORACLE error -1861, with ** literal does not match format string error
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/10/2008 |
|
Status: Verified
FACT(s) (Environment):
Oracle DataServer
SYMPTOM(s):
Error 1252 received when calling stored procedures
ORACLE error see "ORACLE Error Messages and Codes Manual". (1252)
ORACLE error -1861 see "ORACLE Error Messages and Codes Manual". (1252)
** literal does not match format string
When running following SQL statements from SQLplus, receive the same set of Oracle errors
SQL> ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,';
Session altered.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD';
Session altered.
SQL> exec xxproc_xxxx ; --- this is the stored procedure in question
BEGIN xxproc_xxxx ; END;
*
ERROR at line 1:
ORA-01861: literal does not match format string
The error line is using following function to set date format
to_date(sysdate + 1,'dd-mon-yy')
CAUSE:
The stored procedure in question is assuming default date format as
'dd-mon-yy'
But Oracle dataserver, prior to running send-sql-statement, sets numeric and date formats like following
ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '.,'
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'
The reason ALTER SESSION statements are issued is that the DataServer will know what format Oracle will use to return results. ORACLE will (given the alter session statement) return the date in a known format that dataserver can parse.
Formats 'dd-mon-yy' and 'YYYYMMDD' conflicts, thus error
** literal does not match format string
FIX:
Modify all code by explicitly specifying the format like :
to_date('01-Jan-2003','DD-MON-YYYY')