DSXchange

I have built a job where i am using a oraoci as source.when i have columns whose datatypes are DATE or TIMESTAMP i am not able to view the data.If i change the datetype of the date field to char or varchar i can view the data.

ok,Thanks for all the help.

Thanks
sheema

The generated sql is

INSERT INTO XYZ (No,Date) VALUES (:1,TO_DATE(:2, 'YYYY-MM-DD HH24:MI:SS'))

my problem is i am not able to view the data in oraOCI stage.

whale i am sorry that was a typo error the data is coming in mm/dd/yyyy format.

chulett,

The sql is Generated SQL and it has To_Date().
I am getting the error when i click view data

ocixyz: ORA-01821: date format not recognized

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

The source data which i am getting is of datatype Varchar(10) and it is in the format mm/dd/yy. I am converting it in transformer using OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00" Then i have datatype of the date fields in the oraOCI set to Timestamp(19...

i have updated it with a space as below
OCONV(ICONV(Date,"D/MDY[2,2,4]"),"D-YMD[4,2,2]"):" 00:00:00".
But it not solve my problem,the data is getting loaded but i cannot view the data through oraOCI stage.

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 gett...

ok,Thanks for all the help.

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.

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

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

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

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]") an...