DSXchange

Hi all,
I'm a beginner. I have troubles working with timestamp. I'm copying the content of the Employess table (only two rows), in another table, with the same structure. Just a data transfer, without any kind of transformation. The job is succeffully compiled but the running doesn't stop.
It happens every time there a timestamp field. I have tryed in two way:
- ORAOCI stage
- ODBC stage.
With ODBC it happens many time to have 00-000-00 as value for time stamp, even if I try to apply any kind of function to format it.

Ant kind of suggestion will be particularly appreciated. Thank you very much.
Best regards, Nat.

Hi Nat,

Timestamp is not easy. Oracle has to get the string like "2003-07-11 12:05:23" that is 'YYYY-MM-DD hh24:mi:ss". Use perhaps first Sequential as target for to be able that you did everything right and then use the table as target.

Wolfgang Huerter
=====================
Cologne, Germany

Hi Wolfgang,
first of all, thank you for your answer. The format you wrote me for Oracle is the same of the datastage project. If so, there should not be problems. But I noticed that when I look the Oracle table content I found DD-MMM-YY format. Is it simply a mask for visualization?
Bye the way, I also noticed that I have trouble with timestamp only if I use ODBC stage to write an Oracle table, instead of the Oracle stage.
Thank you for your suggestion about file target. It helped me much viewing what I were doing!
I'm looking for other exercise other than the tutorial, because I have to start a job with Datastage. I would be very glad to receive any kind of documentation. For the moment, the problems of other members are too difficult for me. I hope I'll be able soon to resolve them.

Bye, have anice day.

Nat

I think I understand your question. Your having problems with timestamp fields in ODBC when updating Oracle. They work fine in ORAOCI. DD-MMM-YY is the native format of Oracle. You will lose the time part if you do that. In the update or insert phrase you may want to call the Oracle function to_date() on the date column. It can convert almost any format into a date.

Kim.

Kim Duke
DwNav - ETL Navigator
www.Duke-Consulting.com

Hi Kim,
thank you for suggestions. I'm reading a file containing date, and I'm loading it into dimensional time table. The field of the date has timestamp format in the destination Oracle table.
Well, if I use the ORAOCI stage I have not trouble. If I use ODBC stage I'm not able to store this field. I have used the to_date funtion to format the date, but it seems to do not work. Summary of situaion follows:

Date format in the file: 1999-12-31

Date format after transformation: 1999-12-31 00:00:00 (I verified this writing it to a text file)

No rows is written in the destination table; the warning tell me that the month is not valid. Why?

Thank you again, bye.

Hi,
it sounds to me that your ODBC configuration is not set correctly
alas, I'm not that experianced with ORACLE, but I think that is what you need to check
I Hope this helps,

Roy R.