DSXchange

I have a timestamp value in a flat file which I need to insert into oracle table. The I/P value looks like '2007-10-31 00:00:00.000'. In thae transformer I'm using the following function to insert the value.

StringToTimestamp(In_Trns_t1clod.ORIGINAL_OFF_START_DATE , "%yyyy-%mm-%dd %hh:%nn:%ss")

I've also tried with "%yyyy-%mm-%dd %hh:%nn:%ss.3" as the format

I'm getting a warning 'When checking operator: When validating export schema: At field "ORIGINAL_OFF_START_DATE": "null_field" length (19) must match field's fixed width (21)'.

If I check the Oracle Log file in unix I'm seeing the following error message

Record 1: Rejected - Error on table FACTDATA.T1CLOD1, column ORIGINAL_OFF_START_DATE.
ORA-01861: literal does not match format string

The timestamp field format looks like in the same log file

ORIGINAL_OFF_START_DATE 9:27 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if ORIGINAL_OFF_START_DATE = BLANKS

Please Help.

On the columns tab, select microseconds for timestamp column by edit cell option and try to insert records.

You maintain it as varchar/char in Datastage till you load it. While loading it, convert it into Date (not timestamp) using TO_DATE function in the query.

That depends on if the target Oracle column is a DATE or if it is indeed one of the TIMESTAMP flavors.

The target column is timestamp. So I need to insert the time information along with the date. How do I use TO_DATE function while inserting. I'm using Oracle Eterprise Stage with LOAD option.

Can somebody please help on this issue. We are not able to move forward with the development without resolving this problem.

Isn't your support provider providing support, which you are paying them to do?

sidharthasaha wrote:Can somebody please help on this issue. We are not able to move forward with the development without resolving this problem.

You can try this format in transformer for the columns

Oconv(Iconv(Trim(<linkname.fieldname>),"D/DMY[2,2,2]"),"D4-YMD"): " " : Oconv(Time(), "MTS")

Welcome aboard.

Oconv() and Iconv() functions are not available in parallel Transformer stages.

source field [1,19]
But then you need to be sure your source data corresponds with the mask you provided.
Works fine for us, also when tranporting timestamps from say SQL Server to Oracle

sidharthasaha wrote:I have a timestamp value in a flat file which I need to insert into oracle table. The I/P value looks like '2007-10-31 00:00:00.000'. In thae transformer I'm using the following function to insert the value.

StringToTimestamp(In_Trns_t1clod.ORIGINAL_OFF_START_DATE , "%yyyy-%mm-%dd %hh:%nn:%ss")

I've also tried with "%yyyy-%mm-%dd %hh:%nn:%ss.3" as the format

I'm getting a warning 'When checking operator: When validating export schema: At field "ORIGINAL_OFF_START_DATE": "null_field" length (19) must match field's fixed width (21)'.

If I check the Oracle Log file in unix I'm seeing the following error message

Record 1: Rejected - Error on table FACTDATA.T1CLOD1, column ORIGINAL_OFF_START_DATE.
ORA-01861: literal does not match format string

The timestamp field format looks like in the same log file

ORIGINAL_OFF_START_DATE 9:27 19 DATE YYYY/MM/DD HH24:MI:SS
NULL if ORIGINAL_OFF_START_DATE = BLANKS

Please Help.

As far as I understand you are converting string[21] to Timestamp[19]....

Try doing StringToTimestamp(trim(In_Trns_t1clod.ORIGINAL_OFF_START_DATE , "%yyyy-%mm-%dd %hh:%nn:%ss")


Hope this helps .... if not then provide some sample records

Please post some sample records, So that everyone will understand where the problem lies. Also describe the job description, what the job is doing and In which stage are you getting the Null field length warning?

Try using To_timestamp function in Database stage.

The input value is ''2007-10-31 00:00:00.000". I'm reading this in Char [23] . The job layout looks like

sequential file -> transformer -> Oracle Enterprise(with load option)

All the warnings and errors are in the oracle enterprise stage. All the fields are nullable but there are not any null values in the input. I even tried to hard code the date in the StringToTimestamp transformation. That also is not helping.

Use the derivation :