Problem with Oracle loading "ORA-01843: not a valid mon

sshettar · Post by **sshettar** » Tue Apr 24, 2007 1:39 pm

Hi All,

We have recoently moved to 7.5.2 from 7.0 version and we are in process of testing all jobs on 7.5.2.
i have this problem here in one of my job where in the final data gets loaded into oracle table .
when i run the job there are several warning throwing up which says like this
ETSeDocRetail..x_Retl2: The value of the row is: RTL_CTRL = 100000 REGION_CODE = 1 SECTION_CODE = 12 PHYSICAL_DISTRICT = 127300 UNIT_CODE = 1208 TERRITORY_NBR = 120802 CHAIN_AFFILIATION = NULL TRADE_CLASS = 03 STORE_NAME = 575 NORTH WINTON RD ADDR_LINE_1 = 575 WINTON RD NORTH CITY_NAME = ROCHESTER STATE_CODE = NY ZIP_CODE = 14610 PLAN_CODE = P PLAN_CATEGORY = 5 OPEN_FOR_BUSINESS = 1993-03-30 CLOSED_FOR_BUSINESS = 9999-12-31 CLOSED_INDICATOR = N SYSTEM_OPEN = 1993-03-30 SYSTEM_CLOSED = 9999-12-31 CONTRACT_EFF_DATE = 03/01/2007 CONTRACT_TERM_DATE = 12/31/9999 APPROVAL_STATUS = V IMPLEMENTATION_STATUS = Y TOTAL_CPW_VOL = 108 TOTAL_PM_CPW_VOL = 108 PAY_TO_MA =

ETSeDocRetail..x_Retl2: ORA-01843: not a valid month

similar kind of warning repeates for several times

where do you think i'm going wrong.
well the same job on 7.0 works fine . but here it is throwing in these many warnings.

Help needed .
any help would be highly appreciated

Thanks in advance

DSguru2B · Post by **DSguru2B** » Tue Apr 24, 2007 1:45 pm

For debugging purposes, change all date formats to YYYY-MM-DD. See what happens.

chulett · Post by **chulett** » Tue Apr 24, 2007 2:58 pm

Why are these two dates in a different format than the rest?

CONTRACT_EFF_DATE = 03/01/2007 CONTRACT_TERM_DATE = 12/31/9999

Consistent handling of dates is key to success.

ivannavi · Post by **ivannavi** » Wed Apr 25, 2007 6:20 am

I don't know which date format is default in DataStage, but putting date literals inside TO_DATE function with the format spec that matches your date, will most certainly help.