Read an Oracle TimeStamp using OCI stage

[ray.wurlod]

Timestamp(38) is assuming something else about the data - namely that it's Unicode encoded. What happens if you specify Timestamp(19)?

[I_Server_Whale]

Same story !

For 16th May 2000.

VarChar(19) reads it correctly as 16-MAY-00

Timestamp(19) reads it incorrectly as 3000-05-16 00:00:00

For 22nd May 2002.

VarChar(19) reads it correctly as 22-MAY-02

TimeStamp(19) reads it correctly as 2002-05-22 00:00:00

From what I observe, I think it is only the year 2000 is being read as 3000.

[ray.wurlod]

Check your epoch setting. It may be defaulting (or have been set) to 2001-3000. Change it as required.

[chulett]

From what I observe, I think it is only the year 2000 is being read as 3000.

Well... this is different than the problem as originally stated:

It is reading 26-APR-02 as 0002-04-26 00:00:00 instead of reading it as 2002-04-26 00:00:00.

This new set of data makes a little more sense... as Ray notes, something needs to be checked. There is a CENTURYPIVOT setting in the uvconfig file, but that's only in play when using IConv as far as I know. So I'm not really sure where this 'epoch setting' can be found. Can't seem to google up anything Oracle related.

[jdulaney]

I am pretty sure that the Oracle clients ARE backwards compatible. They are not, however, necessarily forwards compatible. Think about it - how can you write a client to access a database using features that weren't conceived at the time the client was written.

I do have one suggestion, however. Check the NLS settings on the client and the database. I haven't run into this in years (and never with DataStage), but this used to happen with 4-digit vs 2-digit year settings. I can't remember the full answer, but the client and the database need to be sync'd correctly. Usually they are by default, but someone could have switched a setting. And of course, you are looking at the client on the machine running DataStage.

[chulett]

I am pretty sure that the Oracle clients ARE backwards compatible. They are not, however, necessarily forwards compatible. Think about it - how can you write a client to access a database using features that weren't conceived at the time the client was written.

I know what you are saying and agree with it in principal. However, I've never had a problem accessing a higher version database with a lower version client that I recall, whereas I have had issues going the other way. By sticking with a 'lowest common denominator' approach to the client software, you ensure that any features you leverage are applicable to both database versions. Going the other way you really can't say the same thing.

I'll have to present this to my DBAs tomorrow, see if they have any words of wisdom on the subject.

Thanks for the reminder - it is controlled by the NLS_DATE_FORMATsetting in the database. It is typically set to DD-MON-RR to solve this 'Y2K' issue but may have been set to a 'YY' year mask in yours. Check with your DBAs, hopefully it's something simple like that.

Here is an article from DBMS Magazine in 1998 discussing the issue... or at least one very much like it.

[I_Server_Whale]

I do have one suggestion, however. Check the NLS settings on the client and the database.

How do I check the NLS settings?

Just to save some time

Thanks a lot !

Whale.

[chulett]

TOAD will let you do it if you've purchased the DBA Module. Better to just ask a DBA directly...