Problem with routine result and format insert
Posted: Fri Feb 27, 2009 9:48 am
Hi Forum,
I have a problem when insert record into oracle database, My job consist in a oracle source, a transformer, and a oracle target
oracle --> transformer --> oracle.
the target has 2 field (field1 varchar2, field2 date), when import the metadata that fields appear in the table definition with this type
field1 varchar
field2 timestamp
in the target field1 derivation assing the field1 from the source, and the target field2 assign a routine that return the system date in the format 'YMD', the routine syntax is DateSystem('','YMD'). In the manager test returns --> 20090227 and its ok.
when run the job, in the director log appear the next warnings:
s1..Oracle_OCI_8: INSERT INTO USR_ADMIN.O_CALL_TD_PROBE(FECHA1, FECHA2) VALUES(:1, TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'))
s1..Transformer_9: The value of the row is: FECHA1 = XXXXXX FECHA2 = 20092702
s1..Transformer_9: ORA-01843: not a valid month
why in the log appear the field2 value with the format different to the routine result???
routine result = 20000227 (YYYYMMDD)
log = 20092702 (YYYYDDMM)
the format is different in both cases.
i'm working with DataStage Enterprise 7.5.3 in a Itanium box, the oracle client is 10g, and the database target is oracle 9i.
the insert is not the problem, because i exec the query in the unix prompt with sqlplus and working fine, the problem is in the processing when DS change the date format order before insert.
i appreciate your help.
I have a problem when insert record into oracle database, My job consist in a oracle source, a transformer, and a oracle target
oracle --> transformer --> oracle.
the target has 2 field (field1 varchar2, field2 date), when import the metadata that fields appear in the table definition with this type
field1 varchar
field2 timestamp
in the target field1 derivation assing the field1 from the source, and the target field2 assign a routine that return the system date in the format 'YMD', the routine syntax is DateSystem('','YMD'). In the manager test returns --> 20090227 and its ok.
when run the job, in the director log appear the next warnings:
s1..Oracle_OCI_8: INSERT INTO USR_ADMIN.O_CALL_TD_PROBE(FECHA1, FECHA2) VALUES(:1, TO_TIMESTAMP(:2, 'YYYY-MM-DD HH24:MI:SS'))
s1..Transformer_9: The value of the row is: FECHA1 = XXXXXX FECHA2 = 20092702
s1..Transformer_9: ORA-01843: not a valid month
why in the log appear the field2 value with the format different to the routine result???
routine result = 20000227 (YYYYMMDD)
log = 20092702 (YYYYDDMM)
the format is different in both cases.
i'm working with DataStage Enterprise 7.5.3 in a Itanium box, the oracle client is 10g, and the database target is oracle 9i.
the insert is not the problem, because i exec the query in the unix prompt with sqlplus and working fine, the problem is in the processing when DS change the date format order before insert.
i appreciate your help.