Timestamp issue inserting into Oracle table
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 9
- Joined: Tue May 31, 2005 3:44 pm
Timestamp issue inserting into Oracle table
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.
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.
-
- Participant
- Posts: 247
- Joined: Thu Apr 27, 2006 6:38 am
- Location: Hyderabad
-
- Participant
- Posts: 9
- Joined: Tue May 31, 2005 3:44 pm
-
- Participant
- Posts: 9
- Joined: Tue May 31, 2005 3:44 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
date & time in transformer
You can try this format in transformer for the columnssidharthasaha wrote:Can somebody please help on this issue. We are not able to move forward with the development without resolving this problem.
Oconv(Iconv(Trim(<linkname.fieldname>),"D/DMY[2,2,2]"),"D4-YMD"): " " : Oconv(Time(), "MTS")
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 135
- Joined: Tue Aug 14, 2007 4:27 am
- Location: Mumbai
Re: Timestamp issue inserting into Oracle table
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
Thanks
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
Swapnil
"Whenever you find whole world against you just turn around and Lead the world"
-
- Participant
- Posts: 9
- Joined: Tue May 31, 2005 3:44 pm
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.
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 :
Code: Select all
StringToTimestamp(substr(left(In_Trns_t1clod.ORIGINAL_OFF_START_DATE,19) , "%yyyy-%mm-%dd %hh:%nn:%ss")
It took me fifteen years to discover I had no talent for ETL, but I couldn't give it up because by that time I was too famous.