Timestamp issue inserting into Oracle table

sidharthasaha · Post by **sidharthasaha** » Thu Apr 10, 2008 12:23 am

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.

devidotcom · Post by **devidotcom** » Thu Apr 10, 2008 12:40 am

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

kumar_s · Post by **kumar_s** » Thu Apr 10, 2008 12:43 am

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.

chulett · Post by **chulett** » Thu Apr 10, 2008 12:45 am

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

sidharthasaha · Post by **sidharthasaha** » Thu Apr 10, 2008 2:19 am

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.

sidharthasaha · Post by **sidharthasaha** » Thu Apr 10, 2008 10:38 pm

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

ray.wurlod · Post by **ray.wurlod** » Fri Apr 11, 2008 12:16 am

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

krisna · Post by **krisna** » Fri Apr 11, 2008 3:59 am

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")

ray.wurlod · Post by **ray.wurlod** » Fri Apr 11, 2008 4:10 am

Welcome aboard.

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

jinm · Post by **jinm** » Fri Apr 11, 2008 4:30 am

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

swapnilverma · Post by **swapnilverma** » Fri Apr 11, 2008 5:41 am

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

DSDexter · Post by **DSDexter** » Fri Apr 11, 2008 7:15 am

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?

kumar_s · Post by **kumar_s** » Fri Apr 11, 2008 8:48 am

Try using To_timestamp function in Database stage.

sidharthasaha · Post by **sidharthasaha** » Mon Apr 14, 2008 6:51 am

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.

sud · Post by **sud** » Mon Apr 14, 2008 7:42 am

Use the derivation :

Code: Select all

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

DSXchange

Timestamp issue inserting into Oracle table

Timestamp issue inserting into Oracle table

date & time in transformer

Re: Timestamp issue inserting into Oracle table