Simple date from Seq file not uploading to Oracle

dprasanth · Post by **dprasanth** » Thu Oct 13, 2005 8:26 am

Hi,
I have a customer record in a PIPE delimited Customer File. The record format is as follows

750007|DET|514028073|514028073|01|75|1|M|Mr|M|David |Rewwick|D|Dear Mr Rewwick|Mr D Rewwick|rywyk|dyvyt|RewwickD|22-12-1
962|22|12|NULL|NULL|27 Fernwood Avenue|STREATHAM||London||SW16 1RD|SW161RD|27 Fernwood AvenueSW16 1RD|F65320000000000000
00|52 |NULL|NULL|NULL|NULL|N|Y|N|NULL|NULL|N|NULL|A|N|N|N|N|N|N|NULL|1RD|SW16|30-05-1995|125|NULL|NULL|NULL|NULL|08-06-
2002|System|125|09-03-2002|System|125| |N|END

I am using a sequential Input stage and OCI Oracle stage. None of the date fields are getting uploaded to the database. From what I see, the file looks fine. I tried uploading all the fields expect the date field and it worked. I tried each and every type of date conversion in the transformer stage and each one is throwing the same error
RNK_SEQOCI_V4_cussm..Transformer_29: ORA-01861: literal does not match format string Really puzzled about this situation. All the date fields have been declared as TIMESTAMP on oracle. So I even tried converting the date field to timestamp and still it is throwing me the same error. Has anyone of you encountered this sitation?The input columns and output date columns have the same data type and other attributes.

Any Help is welcome.Thanks
Dprasanth

chulett · Post by **chulett** » Thu Oct 13, 2005 8:28 am

Look at the generated SQL in the Oracle stage. Note the format it expects the DATE fields declared as Timestamps to come in as. Transform your fields to match that format. Problem goes away.

kcbland · Post by **kcbland** » Thu Oct 13, 2005 8:30 am

The ORAOCI stage adds a To_DATE() to all columns where the datatype is DATE when using generated SQL. You need to make sure your datatypes say DATE and get all your dates into YYYY-MM-DD HH24:MI:SS format, or atleast YYYY-MM-DD in order to take advantage of generated SQL.

If you wrote user-sql, it's up to you to put the TO_DATE() on all your date columns. This is a long documented requirement for using the ORAOCI stages.

dprasanth · Post by **dprasanth** » Thu Oct 13, 2005 8:42 am

kcbland wrote:The ORAOCI stage adds a To_DATE() to all columns where the datatype is DATE when using generated SQL. You need to make sure your datatypes say DATE and get all your dates into YYYY-MM-DD HH24:MI:SS format, or atleast YYYY-MM-DD in order to take advantage of generated SQL.

If you wrote user-sql, it's up to you to put the TO_DATE() on all your date columns. This is a long documented requirement for using the ORAOCI stages.

I had changed all the date to YYYY-MM-DD using OCONV. But still I am facing the same problem. I did see in the generated sql that all the dates are getting converted to YYYY-MM-DD HH24:MI:SS. So I did change the date format. But still no use

dprasanth · Post by **dprasanth** » Thu Oct 13, 2005 8:45 am

chulett wrote:Look at the generated SQL in the Oracle stage. Note the format it expects the DATE fields declared as Timestamps to come in as. Transform your fields to match that format. Problem goes away.

My input date fields just have a date in them, they don't have timestamp. I tried changing the date to timestamp. But still I am facing the same problem. I used various functions like DategenerictoOCIwithtimestamp etc. But still no results

chulett · Post by **chulett** » Thu Oct 13, 2005 8:48 am

Simple - concatenate a zero time onto the end of your date:

Code: Select all

YourField : " 00:00:00"

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 1:52 am

chulett wrote:Simple - concatenate a zero time onto the end of your date:
Code: Select all
YourField : " 00:00:00"

I Tried concatenating zero to the date field. I tried even converting String to Date format. I don't know why it is still giving me the same error

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 2:02 am

chulett wrote:Simple - concatenate a zero time onto the end of your date:
Code: Select all
YourField : " 00:00:00"

I was just searching for a function to convert String to Date. But couldn't find it. Are there any functions that will convert string to date?

dprasanth · Post by **dprasanth** » Fri Oct 14, 2005 2:33 am

dprasanth wrote:
chulett wrote:Simple - concatenate a zero time onto the end of your date:
Code: Select all
YourField : " 00:00:00"
I was just searching for a function to convert String to Date. But couldn't find it. Are there any functions that will convert string to date?

I have cracked the problem. The date fields are declared as DATE in the database. But for some reason when they are imported to the table definition they are getting changed to TIMESTAMP. So what I did was changed the TIMESTAMP to date and it worked. Thanks a lot for everyone who helped. But really couldn't figure out how the datatype changed while importing

chulett · Post by **chulett** » Fri Oct 14, 2005 7:57 am

dprasanth wrote:But really couldn't figure out how the datatype changed while importing

That's because a DATE field in Oracle is really a Timestamp in disguise and always carries a time portion. When it looks like it doesn't, that's because the time portion has been set to zeroes as we've discussed earlier in the thread. But there is always one there.

I prefer to always treat Oracle dates as Timestamps, again as part of the standardization we've also discussed. If you always treat them as Timestamps with explicit control over what goes into the time portion (including zeroes when needed) enforced by standard routines leveraged by all developers - then you will never have a problem with Oracle date fields.

This is much akin to what Ken said in other of your posts.