Unidata to Oracle date conversion - random failures

[jacksamsula]

Looking for recommendations for the most reliable way to convert dates from Unidata to Oracle 8i.
Presently in the Unidata stage we define the type as "date" and in the Oracle stage as "Timestamp 19". The generated SQL then converts the timestamp to date type with a to_date function. This works 99% of the time but we get random failures with the indeterminate message "Abnormal Termination of Stage".
Our workaround, when this occurs, is to define the Oracle stage type as VarChar and then, in user defined SQL, convert the Varchar to date format. This works, but we would prefer not to universally have to define user SQL.
Does anyone have a better suggestion?
Note: this happens even more frequently in 8.1

[chulett]

There's no additional information logged when you Reset the aborted job? Specifically in a 'From previous run...' log entry that the reset would add.

I'd also be curious what exact Oracle client version you have installed on your DataStage server? Some are buggy and can cause these odd intermittent aborts and sometimes all it takes to fix it is to 'upgrade' the client version.

[jacksamsula]

Craig, I should have mentioned that the error appears to occur in the Transformer Stage. A specific error message looks like: "Abnormal Termination of Stage MyTableName.. DS Transformer1 detected".

The Oracle stage is identified as "Oracle OCI" as delivered with our version 8.1. Our Oracle Client is 9i.

If we type all dates in the transformer as VarChar, the error goes away - it only occurs when dates are typed, as generated, as Timestamp 19. When changing the type to VarChar, we then have to create User Defined SQL to convert to the Oracle date type.

[chulett]

It's always an active stage that aborts, so in a Server job that pretty much means it will always be reported by the Transformer regardless of what actually causes the error. Saying your Oracle client is "9i" doesn't really tell anyone much, you really need to know the exact version which will be 9.2.0.5 or something similar. It's important to know because some releases are notoriously buggy and the solution may be a simple as installing a newer version.

On the OCI side, I always make sure I declare them as a Timestamp of size 19 in the job and have been having success with that approach, well forever. The generated sql will wrap the field in a TO_DATE() and expect your data to look like YYYY-MM-DD HH24:MI:SS when it arrives in the OCI stage, even if that means you have to append a zero time if all you have is the date portion. Are you doing that? Making sure the data looks exactly like the OCI stage is expecting with a full four-digit year and all other fields always two digits? If you are doing that you should be fine unless you've got a buggy Oracle client installed.

And you didn't answer my question about the 'From previous run...' log message.