DSXchange

hai,
i am facing one problem i.e,my source is sequential file .one of my column is in 'date' type it is in "YYMMDD" format.i am inserting this values into oracle table using oracle(database) stage.oracle internal format is 'dd-mon-yyyy'.in oracle stage insert command like

insert into a1(date1) values('to_date(:1,'yymmdd'))

here the error is:

date001..Transformer_2: At row 1, link "DSLink4", while processing column "date1"
Value treated as NULL
Attempt to convert String value "61/08/13" to Date type unsuccessful


just i am giving example with one column.i laso tried with oconv functions in transformer to change the input format into dd/mm/yyyy
and also changed the insert command in output stage like

insert into a1(date1) values('to_date(:1,'dd/mm/yyyy'))
but it is also not working.

please help me.

Output the result from transformer to a sequential file and make sure that your date conversion is happening correct, if this is not right then here is where your problem is and you might have to correct this first.

If you got that right (ie. Your output in dd/mm/yyyy) then you don't have to use 'to_date' function in your insert statement because already the input column is converted into date.

reddy.vinod wrote: i also tried with oconv functions in transformer to change the input format into dd/mm/yyyy
and also changed the insert command in output stage like
insert into a1(date1) values('to_date(:1,'dd/mm/yyyy'))
but it is also not working.

reddy.vinod wrote:i am inserting this values into oracle table using oracle(database) stage. oracle internal format is 'dd-mon-yyyy'.

No, that is not the 'Oracle internal format'. That is an external format.

JoshGeorge wrote:you don't have to use 'to_date' function in your insert statement because already the input column is converted into date.

Not quite true. It's only 'converted to a date' if you use the TO_DATE function. Otherwise, you end up attempting to match the NLS_DATE setting of the current database, this allows Oracle to do the conversion 'behind the scenes' with default values. All in all, a Bad Idea even when it does work. Your solution should work on any instance, not just one where you've managed to match the NLS_DATE format.

I've posted over and over here how to handle Oracle DATE fields. It involves generated sql and proper conversion of the incoming field. It's not rocket science nor all that mysterious and a standard set of Oracle date handling routines will go a long way towards making your DATE issues go away forever.

One such example here, scroll down for a couple of Christmas time posts from me on the subject. Other examples abound.