Home » RDBMS Server » Server Administration » NLS_DATE_FORMAT (Oracle 9.2.0.8, SunOS)
NLS_DATE_FORMAT [message #325138] Thu, 05 June 2008 02:14 Go to next message
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 Go to previous messageGo to next message
flyboy
Messages: 1832
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

By the way, the value of 'MON' depends on NLS_DATE_LANGUAGE setting, but I was not successful in finding the one which would not fail.
Re: NLS_DATE_FORMAT [message #325218 is a reply to message #325150] Thu, 05 June 2008 05:36 Go to previous messageGo to next message
blazingrock4u
Messages: 30
Registered: March 2006
Location: India
Member

NLS_DATE_LANGUAGE is AMERICAN on all the servers...

As I said earlier,
SELECT * FROM NLS_INSTANCE_PARAMETERS;
and
SELECT * FROM NLS_SESSION_PARAMETERS;
are same in all the five servers.

The only difference that was found was NLS_DATE_FORMAT in
SELECT * FROM NLS_DATABASE_PARAMETERS;
Re: NLS_DATE_FORMAT [message #325258 is a reply to message #325138] Thu, 05 June 2008 07:32 Go to previous message
flyboy
Messages: 1832
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.
Previous Topic: Cannot able to login as OS authentication
Next Topic: cross plat form migration
Goto Forum:
  


Current Time: Thu Dec 08 16:30:39 CST 2016

Total time taken to generate the page: 0.28600 seconds