oci can't handle miliseconds

[randy]

Hi,

I am getting the following error when I try to load a timestamp with miliseconds:

ORA-01830: date format picture ends before converting entire input string

I manipulate the date to look like this: yyyymmdd hh24:mi:ss.MMM

I am using OCI9 and have the field defined as timestamp.

Thanks for any suggestions
Randy

[kris007]

I tried doing that and got the same error message. Then I trimmed off the milliseconds as it was not of importance to the user. Before you want to do anything, I would suggest you to findout if milliseconds data is really important for the client or would it be ok if you skipped it.

If you want to trim the milliseconds then use the Field function in the Transformer Derivation. Hope that helps.

Kris

[randy]

Thanks, but the miliseconds if very important to the project.

[I_Server_Whale]

Will changing the default NLS_DATE_PARAMETERS in Oracle help? Try it.

[randy]

I guess I could, but was really hoping to do it in datastage.
Isn't there someway of doing this?

[DSguru2B]

what is the length and the scale of the timestamp field you have specified in the OCI stage

[randy]

The length is 26
The scale is 6

[gateleys]

Did you try setting the scale to 3?

gateleys

[randy]

The same:

xfmLoad: ORA-01830: date format picture ends before converting entire input string

[randy]

got it.
user defined sql

INSERT INTO tbl
(D_TRANSACTION_DATE_TIME)
VALUES
(TO_TIMESTAMP(:1, 'YYYY-MM-DD HH24:MI:SS.FF'))

not real elegant, but it works.

Thanks all

[ray.wurlod]

Yay! Does the FF part of the timestamp picture handle any fractional seconds (say microseconds) or is it limited to milliseconds?

[chulett]

not real elegant, but it works.

Yes, that's what you have to do. The OCI stage doesn't natively support the Oracle TIMESTAMP datatype, only DATE, so you need to wrap it up yourself with an appropriate 'TO_TIMESTAMP' function call that matches the way the TIMESTAMP was declared in the target table.

You may also be able to get away with treating it as a Varchar and making sure your data is in the correct format but to me the TO_TIMESTAMP is more 'appropriate' in spite of the custom sql that is (by necessity right now) involved.

Ray, the FF part is not limited to milliseconds but to the fractional precision declared when the TIMESTAMP was created - and the fractional seconds precision can range from 0 to 9 digits, from what I recall.