ORAOCI8 Stage error on Date/Timestamp

luca · Post by **luca** » Fri May 02, 2003 11:48 pm

With DS 5.2.1, in a ORAOCI8 Stage, I have the following error message when I try to do a "view data":

DSBrowser..Repository: At row 2, link "DSLink1", while processing column "START_DATE"
Attempt to convert String value "16-FEB-03" to Timestamp type unsuccessful

The column I am trying to extrat data from is a DATE in ORACLE, and a Timestamp in my job, no transformation is made on this column.
It seems like a bug to me, can someone confirm or debunk ?

Thanks.

ariear · Post by **ariear** » Sat May 03, 2003 2:57 am

Did you let DataStage to generate the Query ? If so you'll observe that in order to convert to DataStage Timestamp datatype it generates a TO_CHAR function. It seems that the query is not doing so and DataStage tries to handle the default output from oracle "DD-MON-YY" which is a string

luca · Post by **luca** » Sat May 03, 2003 4:34 am

Yes, I let DS generate the query. The query was generated without a to_char function.
Moretheless, I manage to convert the date using to char, but then it cannot be stored in a timestamp field (or u get the error: ORA-01722: invalid number).
Then bug or not bug ?

hughsm · Post by **hughsm** » Sat May 03, 2003 11:23 pm

I don't know if this is related but: What I found moving date data from DB2 to Oracle was that I had to type cast the DB2 date field (field1) to char(10) and the Oracle to datestamp(19) (field1:' 00:00:00') in the left determinate? column in the grid window. I added the :' 00:00:00' to tell oracle it was a time stamp if I didn't have any time. I'm guessing here, but if your date field in DB2 was a timestamp, it would be char(19) to timestamp(19). In another example, I had a db2 date (as char(10) in one field and a time (char(8)) in another. I joined them both (field1:' ':field2) to get my result.

ray.wurlod · Post by **ray.wurlod** » Sun May 04, 2003 5:07 am

What did you use as the Data Element setting for this column? Experiment not only with the SQL data type but also with the data element - you will find that the combination is what affects the result returned (as does your default date picture for Oracle).
Just out of curiosity, does the ODBC stage exhibit the same behaviour?

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518

chulett · Post by **chulett** » Sun May 04, 2003 7:42 pm

You need to understand the formatting the OCI plugin uses/needs, it is documented in the .pdf file.

For example, a "timestamp" field needs to be formatted as "yyyy-mm-dd hh24:mi:ss" before it will insert properly via the plugin. A 'quirk' of Oracle is that a DATE field (not two separate types) is used to store both 'date' and 'timestamp' data types and always contains a time portion. As hugh mentions, you should append a zero time to dates that don't need a time portion before sending them to OCI via a timestamp.

When selecting an Oracle date, you can define it as a date in the OCI stage and then the plugin will control the formatting, or you can define it as a character string and it will follow your default date format.

As I said, it's all spelled out rather nicely in the OCI pdf document.

-craig