| Message: 96535 |
 |
BY: Wiehann Matthysen (wcmatthysen)
DATE: 2011-05-27 10:53
SUBJECT: RE: Exception with DATE columns in Oracle 11g Hi Kubo,
I'm having the exact same problem as described by Brian. I downloaded the ruby-oci8-2.0.4.tar.gz file, extracted it, applied your timeoffset_patch.dif patch. I then ran the following script:
********************************
gem 'ruby-oci8', '= 2.0.4'
require 'oci8'
conn = OCI8.new('hr', 'hr', '//localhost/orcl')
puts "Client Version: #{OCI8.oracle_client_version}"
puts "Server Version: #{conn.oracle_server_version}"
puts "ENV['TZ'] : #{ENV['TZ']}"
conn.exec 'drop table test_date purge' rescue nil
conn.exec 'create table test_date (dt date)'
conn.exec 'insert into test_date values (sysdate)'
conn.commit
print 'before alter session: '
p conn.select_one 'select * from test_date' rescue p $!
conn.exec "alter session set time_zone = 'US/Eastern'"
puts 'after alter session: '
p conn.select_one 'select * from test_date' rescue p $!
p conn.select_one("select to_timestamp_tz('2010-01-02 03:04:05 06:07', 'YYYY-MM-DD HH24:MI:SS TZH:TZM') from dual") rescue p $!
p conn.select_one("select to_timestamp('2010-01-02 03:04:05', 'YYYY-MM-DD HH24:MI:SS') from dual") rescue p $!
p conn.select_one("select to_date('2010-01-02 03:04:05', 'YYYY-MM-DD HH24:MI:SS') from dual") rescue p $!
conn.logoff
********************************
The output that I'm getting is:
Client Version: 11.2.0.2.0
Server Version: 11.2.0.1.0
ENV['TZ'] :
before alter session: [2011-05-27 12:38:03 +0200]
after alter session:
#<OCIError: ORA-01805: possible error in date/time operation>
[2010-01-02 03:04:05 +0607]
#<OCIError: ORA-01805: possible error in date/time operation>
#<OCIError: ORA-01805: possible error in date/time operation>
I also noticed, when running tests in ruby-plsql that it points to: ocidatetime.c:126:in oci8lib_191.so as the source of the error.
oci_lc(OCIDateTimeGetDate(oci8_envhp, oci8_errhp, dttm, &year, &month, &day));
oci_lc(OCIDateTimeGetTime(oci8_envhp, oci8_errhp, dttm, &hour, &minute, &sec, &fsec));
rv = OCIDateTimeGetTimeZoneOffset(hndl, oci8_errhp, dttm, &tz_hour, &tz_minute);
if (rv != OCI_SUCCESS) {
/* Get time zone of the specified handle. */
OCIDateTime *sys_date;
oci_lc(OCIDescriptorAlloc(oci8_envhp, (dvoid*)&sys_date, OCI_DTYPE_TIMESTAMP_TZ, 0, 0));
=======>oci_lc(OCIDateTimeSysTimeStamp(hndl, oci8_errhp, sys_date));
rv = OCIDateTimeGetTimeZoneOffset(hndl, oci8_errhp, sys_date, &tz_hour, &tz_minute);
OCIDescriptorFree(sys_date, OCI_DTYPE_TIMESTAMP_TZ);
}
have_tz = (rv == OCI_SUCCESS);
From the ruby code I wrapped the datetime_to_array function located in datetime.rb and I don't think the tz_hour = nil, tz_min = nil is the bug as Brian described though. All output produced by this function is correct in the sense that tz_hour and tz_min is not null. I think the source of the problem is the oci_make_ocitimestamp function in ext/oci8/ocidatetime.c.
Do you have any suggestions on how I might fix this? | |
Start New Thread
Exception with DATE columns in Oracle 11g