ORA-01805 and 11gR2

[flash-gordon]

Hi,

I have recently released Instant Client 64-bit 11.2.0.3 for Mac OS X on my Mountain Lion (10.8.2). It has futher timezone parameters:

gordon@gordon-pc /usr/local/oracle/instantclient_11_2 $ ./genezi -v
Client Shared Library 64-bit - 11.2.0.3.0

System name:    Darwin
Release:    12.2.0
Version:    Darwin Kernel Version 12.2.0: Sat Aug 25 00:48:52 PDT 2012; root:xnu-2050.18.24~1/RELEASE_X86_64
Machine:    x86_64

Operating in Instant Client mode.
Small timezone file = timezone_14.dat
Large timezone file = timezlrg_14.dat

I also have Oracle Database 11.2.0.2:

oracle@medvedev:~$ genezi -v
Client Shared Library 64-bit - 11.2.0.2.0

System name:    Linux
Release:    2.6.32-5-amd64
Version:    #1 SMP Mon Jan 16 16:22:28 UTC 2012
Machine:    x86_64

Operating in Instant Client mode.
Small timezone file = timezone_17.dat
Large timezone file = timezlrg_17.dat

As you can see server and my local library have different versions of timezone files. So I get "ORA-01805 possible error in date/time operation" raised from ocidatetime.c:119 in version 2.1.4. But Oracle documentation tells that I can work when client and server have different tz files: http://docs.oracle.com/cd/E14072_01/appdev.112/e10646/oci10new.htm#LNOCI16761. I tried to comment line with check and it seems to be working now.

Is it right that the check could be modified for work in 11gR2 environment? Main problem is that I can't build new Instant Client because there is no Oracle Database for Mac OS X.

[kubo]

Could you put the following code after "require 'oci8'" and try again?

OCI8::BindType::Mapping[Time] = OCI8::BindType::LocalTime
OCI8::BindType::Mapping[:date] = OCI8::BindType::LocalTime
OCI8::BindType::Mapping[:timestamp] = OCI8::BindType::LocalTime
OCI8::BindType::Mapping[:timestamp_ltz] = OCI8::BindType::LocalTime

or

OCI8::BindType::Mapping[Time] = OCI8::BindType::UTCTime
OCI8::BindType::Mapping[:date] = OCI8::BindType::UTCTime
OCI8::BindType::Mapping[:timestamp] = OCI8::BindType::UTCTime
OCI8::BindType::Mapping[:timestamp_ltz] = OCI8::BindType::UTCTime

I'm not sure which is better.

[yahonda]

Did you mean "require 'oci8'" ?

[kubo]

@yahonda Thanks for the pointing out. I fixed it.

[flash-gordon]

I tested and it works fine. I'll just set

OCI8::BindType::Mapping[Time] = OCI8::BindType::LocalTime
OCI8::BindType::Mapping[:date] = OCI8::BindType::LocalTime

in my delvelopment environment. I'm sure that in my case it will be safe because server and local machine is in the same time zones.

Thank you.

[cmrichards]

flash-gordon's solution worked a treat. I just wish I'd found this page yesterday.

Will I have to do this for all my future ruby apps when using ruby-oci > 2.1.5 or is it going to be fixed?

[kubo]

You can do it if you are sure that your apps never use timestamp with time zone datatype. Otherwise, the following setting may cause problems.

OCI8::BindType::Mapping[Time] = OCI8::BindType::LocalTime

OCI8::BindType::LocalTime binds values as timestamp. Time zone information is not sent to the Oracle server.