ORA-01843: not a valid month

[shailesh]

Hello,

Last few days we are observing ORA-01843: not a valid month, error continously.
On startup our Application always sets date format to dd.mm.yyyy format using
ALTER SESSION SET NLS_DATE_FORMAT = 'DD.MM.YYYY';

But error occurs on following statement.
SELECT Type, Module FROM TableTypeModule
WHERE NVL(TRUNC(BeginDate), '01.01.1800') <= TRUNC(SYSDATE)
AND TRUNC(EndDate) >= TRUNC(SYSDATE);

We have also done following to check, what was the date format when this error
occurs and to check in case someone else has set other date format, but even
following trace/debug trigger shows DD.MM.YYYY format. We can't change all
queries in our application to set date format explicitly.

CREATE OR REPLACE TRIGGER TrgDB_003
AFTER SERVERERROR ON DATABASE
DECLARE
sprogram VARCHAR2 (200);
smachine VARCHAR2 (200);
sosuser VARCHAR2 (200);
NLSHost VARCHAR2 (4000);
NLSDate VARCHAR2 (4000);
NLSLang VARCHAR2 (4000);
SPID VARCHAR2 (10);
sid number;
serial number;
BEGIN

IF (is_servererror (1843))
THEN
SELECT NVL (b.Program, b.Module), machine, OSUser, a.SPID,
SYS_CONTEXT ('USERENV', 'HOST'),
SYS_CONTEXT ('USERENV', 'NLS_DATE_FORMAT'),
SYS_CONTEXT ('USERENV', 'NLS_DATE_LANGUAGE'), b.SID, b.Serial#
INTO sProgram, smachine, sosuser, SPID, NLSHost,
NLSDate,
NLSLang, SId, serial
FROM SYS.v_$session b, v$process a
WHERE audsid = USERENV ('SESSIONID') AND A.Addr = B.Paddr;

INSERT INTO TempTraceInformation
(EventTime, Program, machine, osuser,
Information, TraceFileSPID,
NLSHost, NLSDate, NLSLang, sid, serial
)
VALUES (SYSDATE, sprogram, smachine, sosuser,
'Caught ORA-1843 exception with this program', SPID,
NLSHost, NLSDate, NLSLang, sid, serial
);

END IF;
END;
/

We are really in need of help to resolve this issue, please help. How can we
track that date format?

Thanks & Regards,

Shailesh

[LKBrwn_DBA]

Just change the default date to:

...NVL(TRUNC(BeginDate), TRUNC(SYSDATE-1))...

[slimdave]

I wouldn't rely on alter session. Just specify the date format:

NVL(TRUNC(BeginDate), to_date('01.01.1800','DD.MM.YYYY')) <= TRUNC(SYSDATE)

or

NVL(TRUNC(BeginDate), date '1800-01-01') <= TRUNC(SYSDATE)

[WilliamR]

We can't change all queries in our application to set date format explicitly.

Bet you wish it had been written properly in the first place. I guess you'll be having code reviews and testing from now on

[shailesh]

Thanks Guys for an answer.

Error appears on similar queries not the only query. I agree that it should be converted explicitly, but we have inherited this application and changing hundreds of queries should not be the only option.

The problem is that error doesn't come continuesly, When everyone starts working
after hour or so it starts coming on few sessions not at all sessions.

Is there any other way to track the same. Please let me know.

Thanks & Regards,

Shailesh

[davey23uk]

bite the bullett and fix things, save you time in the long run

[jmodic]

Is there any other way to track the same. Please let me know.

It seems that application login is not the only place when that "ALTER SESSION SET NLS_DATE_FORMAT" command takes place, and it looks that the other occurance sets the date format to something different. So try to find where else does it happen. It might be implemented somewhere else in the application, or maybe in some database procedure/function that is executed from the application.

Also, bare in mind that there is a possibility, that some other NLS setting which happens after your aplication logon, might be overwriting the NLS_DATE_FORMAT. For example, if after you have set your NLS_DATE_FORMAT, you execute "ALTER SESSION SET NLS_TERRITORY = xyz", your NLS_DATE_FORMAT, which you've explicitely set before, is reset to the format that is the default one for that territory xyz.

[LKBrwn_DBA]

Also, you could try setting NLS_DATE_FORMAT environment variable at the client side.

[tamilselvan]

Thanks Guys for an answer.

Is there any other way to track the same. Please let me know.

Shailesh

Yes. there is a way.
Hire good programmers and ask them to change the code.

NVL(TRUNC(BeginDate), '01.01.1800') <= TRUNC(SYSDATE)

To me, '01.01.1800' is a string, not a date.
nvl(begindate, to_date('01.01.1800','dd.mm.yyyy')) <= trunc(sysdate)

Tamil

[tamade]

Does this work?

SELECT Type, Module FROM TableTypeModule
WHERE NVL(TRUNC(BeginDate), to_date('01.01.1800', 'dd.mm.yyyy')) <= TRUNC(SYSDATE)
AND TRUNC(EndDate) >= TRUNC(SYSDATE);