DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ORA-01843: not a valid month

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80

    ORA-01843: not a valid month

    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

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Just change the default date to:
    Code:
    ...NVL(TRUNC(BeginDate), TRUNC(SYSDATE-1))...

    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I wouldn't rely on alter session. Just specify the date format:
    Code:
    NVL(TRUNC(BeginDate), to_date('01.01.1800','DD.MM.YYYY')) <= TRUNC(SYSDATE)
    or
    Code:
    NVL(TRUNC(BeginDate), date '1800-01-01') <= TRUNC(SYSDATE)
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Jun 2005
    Location
    London, UK
    Posts
    159
    Quote Originally Posted by shailesh
    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

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    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

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    bite the bullett and fix things, save you time in the long run

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by shailesh
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Also, you could try setting NLS_DATE_FORMAT environment variable at the client side.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by shailesh
    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

  10. #10
    Join Date
    Dec 2005
    Location
    Hangzhou, China
    Posts
    7
    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);

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width