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.
ORAOCI8 Stage error on Date/Timestamp
Moderators: chulett, rschirm, roy
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
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