| NLS_DATE_FORMAT [message #325138] |
Thu, 05 June 2008 02:14  |
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
|
Member |
|
|
Intro:
I've got five servers built/configured by two different (contractors) teams. Server A,B,C by PartyX and Server D, E by PartyY.
Issue: I have a PLSQL code which appears something like this:
CREATE OR REPLACE PROCEDURE TTLCST
IS
V_EMPCODE VARCHAR2(100);
V_TASKCODE NUMBER:=0;
v_txndt date;
CURSOR CUR1 IS
SELECT DISTINCT RESCODE,TASKCODE
FROM SCOTT.EMP
WHERE DEPTCODE=10
AND EMPTYPE='X'
AND ENTRYDATE>TO_DATE(v_txndt,'DD/MON/YY HH12:MI:SS AM');
............
............
............
This code works okay on Server A,B and C but fails with "ORA-01861" on server D and E.
Analysis:
ORA-01861: Literal does not match format string
// *Cause: Literals in the input must be the same length as literals in
// the format string (with the exception of leading whitespace). If the
// "FX" modifier has been toggled on, the literal must match exactly,
// with no extra whitespace.
// *Action: Correct the format string to match the literal.
The reason for this is I can understand that the code is trying to use a date
type column in a to_date function. The variable v_txndt was declared as DATE and in the cursor we are converting the same variable to a date using TO_DATE function.
But what is confusing me is that, why is that the code is failing on server D and E, whereas it works okay on Server A, B and C. So, I thought it must be something to do with date_format.
I had checked the NLS_DATABASE_PARAMETERS, NLS_INSTANCE_PARAMTERS and NLS_SESSION_PARAMETERS for the afore-said five servers. The only difference that I had found is in NLS_DATE_FORMAT of the NLS_DATABASE_PARAMTERS.
NLS_DATABASE_PARAMETERS on ServerD and ServerE:
NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS"
Alert Log on ServerD and ServerE:
NLS_DATE_FORMAT = "YYYY-MM-DD HH24:MI:SS"
nls_comp=BINARY
nls_sort=BINARY
*********************************************************
NLS_DATABASE_PARAMETERS on ServerA, ServerB and ServerC:
NLS_DATE_FORMAT = "HH24:MI:SS'
Alert Log on ServerA, ServerB and ServerC:
nls_comp = BINARY
nls_date_format = "YYYY-MM-DD HH24:MI:SS"
nls_sort = BINARY
Note: Oracle version, NLS_INSTANCE_PARAMETERS and NLS_SESSION_PARAMETERS are same for all the five servers.
So, could that be the reason for the code to behave differently in different environments???
|
|
|
|
| Re: NLS_DATE_FORMAT [message #325150 is a reply to message #325138] |
Thu, 05 June 2008 02:56  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
v_txndt date;
...
AND ENTRYDATE>TO_DATE(v_txndt,'DD/MON/YY HH12:MI:SS AM');
As using TO_DATE function for DATE type parameter is useless, it is also not supported. So the parameter is implicitly converted to VARCHAR2 beforehand.
So I just wonder, how Oracle succeeded to convert string in 'YYYY-MM-DD HH24:MI:SS' format using 'DD/MON/YY HH12:MI:SS AM' format modifier.
SQL> alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
Session altered.
SQL> alter session set nls_date_language = english;
Session altered.
SQL> select to_date( sysdate, 'DD/MON/YY HH12:MI:SS AM' ) from dual;
select to_date( sysdate, 'DD/MON/YY HH12:MI:SS AM' ) from dual
*
ERROR at line 1:
ORA-01861: literal does not match format string
|
|
|
|
|
|
| Re: NLS_DATE_FORMAT [message #325258 is a reply to message #325138] |
Thu, 05 June 2008 07:32 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Sorry, I missed some parts of your first post. In fact, it was not very difficult, as it is not formatted as suggested in OraFAQ Forum Guide.
> So, could that be the reason for the code to behave differently in different environments???
Very probably, yes. As you did not post, how you connected when running the script (user, environment), I may only guess that NLS_DATE_FORMAT value was probably initialized differently than in the session where you query NLS_SESSION_PARAMETERS.
Anyhow, you shall classify it as a bug which shall be immediately fixed.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
