DSXchange

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.

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

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 ?

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.

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

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