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