Problem with Date format in oracOCI

[sheema]

I have a simple job it extracts data from a flat file and loads into a oracle table.I have 3 date fields coming from source file which are of datatype varchar and length 10 and it is coming in the format mm/dd/yyyy.I am converting them in the transformer using
ICONV(Date,"D/MDY[2,2,4]") and inserting into oracle table ABCD,the corresponding data type for these fields is Date in my oraOCI.The job is running fine,it is inserting rows into table ABCD.
But when i try to click view data in the oraOCI stage i am getting below error

ocixyz: ORA-01821: date format not recognized

ocixyz.DSLink1: DSP.Open GCI $DSP.Open error -100.

when i view the data in TOAD against table ABCD i can see the data.

can anyone let me know what might be the problem.

Thanks
sheema

[DSguru2B]

Are you also doing OCONV along with your ICONV? I didnt know oracle could accept internal formats
Look into your log file, do you have warnings?

[sheema]

I did not use OCONV.
I did not get any warning in my log.

[DSguru2B]

Ok. When you check the data using TOAD. Do you see correct data? Is it the same one that you inserted?

[sheema]

yes,that is the data which i am inserting.the date appears in mm/dd/yyyy format in TOAD.

[narasimha]

Change the data type from date to Timestamp and check if you get the same problem.
I still doubt all the data got loaded into your target table from your previous run, check if everythinig got loaded.

[sheema]

I checked the data,all the rows got loaded.I will check by changing the datatype to timestamp.

[sheema]

I changed the datatype to TimeStamp in my oraOCI but still i am getting the same error when i click the View Data option.
I knew that DB2 accepts internal date format,so i was not sure that Oracle also accepts the internal date format.

[DSguru2B]

DB2 does. I dont think oracle does and hence my original question and doubt.
Try sending in your data as

Code: Select all

OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]")

[chulett]

yes,that is the data which i am inserting.the date appears in mm/dd/yyyy format in TOAD.

Means nothing. Toad uses specific date masks for display, set in the options - can be anything you want.

I knew that DB2 accepts internal date format,so i was not sure that Oracle also accepts the internal date format.

Nope. I'm getting tired of explaining this over and over, guess that FAQ on the subject is long overdue.

All that matters is the format coming in your Sequential file. After that it's all 'standard' transforms to get it into a format the OCI stage will love:

YYYY-MM-DD HH24:MI:SS

Set your target datatype to Timestamp, use generated SQL, get the date into the output format shown above and append a zero timestamp to the end of the date. Oracle will love you for it.

[sheema]

ok,Thanks for all the help.

[DSguru2B]

[ I'm getting tired of explaining this over and over, guess that FAQ on the subject is long overdue.

I say AYE AYE for that

[narasimha]

ok,Thanks for all the help.

Does that mean your problem is resolved
If yes mark it as the same.

[sheema]

chulett,
I have used the format OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):"00:00:00" in my transformer and changed the datatype to Timestamp(19) and loaded into the table.
The data is getting loaded into the table.
But when i select the view data option i am getting the same error as i posted .

[DSguru2B]

You forgot a space before the time part.

Code: Select all

OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00"

I am just amazed as to how your data gets loaded with messed up data format and no warning shows up.