 |
Forums |
Admin Discussion Forums: help Start New Thread
By: Kubo Takehiro
 RE: Exception with DATE columns in Oracle 11g [ reply ]
2012-01-29 00:00
|
> Will keep an eye out for that patch.
Sorry, I have not done it yet as a official release.
But it works if you add the following code:
OCI8::BindType::Mapping[:date] = OCI8::BindType::LocalTime
OCI8::BindType::Mapping[:timestamp] = OCI8::BindType::LocalTime
The reason I didn't add it to ruby-oci8 2.1.0's new featuers is just one; I have not written its test cases. But it will works well.
|
By: Kubo Takehiro
RE: Exception with DATE columns in Oracle 11g [ reply ]
2011-06-08 03:58
|
I'll change ruby-oci8 not to use TIMESTAMP WITH TIMEZONE type for DATE, TIMESTAMP and TIMESTAMP WITH LOCAL TIMEZONE columns.
It will be ruby-oci8 2.1.
The future plan is:
- Release 2.0.5
- Drop Oracle 8i support to make ruby-oci8 simple.
- Change ruby-oci8 to use TIMESTAMP type for DATE, TIMESTAMP and TIMESTAMP WITH LOCAL TIMEZONE columns.
- Release 2.1.0
After that, you can fetch TIMESTAMP columns, excluding TIMESTAMP WITH TIMEZONE, from Oracle even though client and server timezone versions are different.
|
By: Wiehann Matthysen
RE: Exception with DATE columns in Oracle 11g [ reply ]
2011-05-27 10:53
|
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?
|
By: Kubo Takehiro
RE: Exception with DATE columns in Oracle 11g [ reply ]
2010-12-21 13:44
|
> I can't easily change that C file and re-compile unfortunately, as I don't have control over the server.
Could you use gcc on the server?
If so, you can compile ruby-oci8 and use it without installation as follows:
tar xvfz ruby-oci8-2.0.4.tar.gz
cd ruby-oci8-2.0.4
make
irb -Iext/oci8 -Ilib -roci8
The key point is "-Iext/oci8 and -Ilib."
Could you try the patch in the following URL?
https://gist.github.com/749935
cd ruby-oci8-2.0.4
patch -p0 < timeoffset_patch.dif
make
Then check the following SQL statements before and after "alter session set time_zone = ..." in irb.
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")
conn.select_one("select to_timestamp('2010-01-02 03:04:05', 'YYYY-MM-DD HH24:MI:SS') from dual")
conn.select_one("select to_date('2010-01-02 03:04:05', 'YYYY-MM-DD HH24:MI:SS') from dual")
|
By: Brian Ploetz
RE: Exception with DATE columns in Oracle 11g [ reply ]
2010-12-20 14:00
|
Hi Kubo,
The script fails for me with the same error I was seeing in my app. Here's the result:
$ gem list
*** LOCAL GEMS ***
bundler (1.0.7)
rake (0.8.7)
ruby-oci8 (2.0.4)
$ ruby -v
ruby 1.9.2p0 (2010-08-18 revision 29036) [x86_64-linux]
$ irb
ruby-1.9.2-p0 > gem 'ruby-oci8'
=> true
ruby-1.9.2-p0 > require 'oci8'
=> true
ruby-1.9.2-p0 > conn = OCI8.new "XXX", "XXX", "XXX"
=> #<OCI8:XXX>
ruby-1.9.2-p0 > puts "Client Version: #{OCI8.oracle_client_version}"
Client Version: 11.2.0.2.0
=> nil
ruby-1.9.2-p0 > puts "Server Version: #{conn.oracle_server_version}"
Server Version: 11.2.0.1.0
=> nil
ruby-1.9.2-p0 > puts "ENV['TZ'] : #{ENV['TZ']}"
ENV['TZ'] :
=> nil
ruby-1.9.2-p0 > conn.exec 'drop table test_date purge' rescue nil
=> nil
ruby-1.9.2-p0 > conn.exec 'create table test_date (dt date)'
=> 0
ruby-1.9.2-p0 > conn.exec 'insert into test_date values (sysdate)'
=> 1
ruby-1.9.2-p0 > conn.commit
=> #<OCI8:XXX>
ruby-1.9.2-p0 > print 'before alter session: '
before alter session: => nil
ruby-1.9.2-p0 > p conn.select_one 'select * from test_date' rescue p $!
[2010-12-20 07:47:47 -0600]
=> [2010-12-20 07:47:47 -0600]
ruby-1.9.2-p0 > conn.exec "alter session set time_zone = 'US/Eastern'"
=> 0
ruby-1.9.2-p0 > print 'after alter session: '
after alter session: => nil
ruby-1.9.2-p0 > p conn.select_one 'select * from test_date' rescue p $!
#<NoMethodError: undefined method `*' for nil:NilClass>
=> #<NoMethodError: undefined method `*' for nil:NilClass>
ruby-1.9.2-p0 > conn.logoff
=> true
I can't easily change that C file and re-compile unfortunately, as I don't have control over the server. I'll see if I can get our sysadmin to do that.....
|
By: Kubo Takehiro
RE: Exception with DATE columns in Oracle 11g [ reply ]
2010-12-19 05:27
|
1. Does the following script work for you? It works fine for me.
-----------------------
gem 'ruby-oci8'
require 'oci8'
conn = OCI8.new 'ruby/oci8' # Change This Line!
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'"
print 'after alter session: '
p conn.select_one 'select * from test_date' rescue p $!
conn.logoff
-----------------------
2. Could you change ext/ocidatetime.c near line 122 as follows and test again? I want to know the error message of OCIDateTimeGetTimeZoneOffset.
-----------------------
rv = OCIDateTimeGetTimeZoneOffset(oci8_envhp, oci8_errhp, dttm, &tz_hour, &tz_minute);
if (rv != OCI_SUCCESS) oci8_raise(oci8_errhp, rv, NULL); /* ADD THIS LINE. */
have_tz = (rv == OCI_SUCCESS);
-----------------------
|
By: Brian Ploetz
Exception with DATE columns in Oracle 11g [ reply ]
2010-12-14 23:39
|
Hi,
I first reported this over at the Oracle Enhanced Adapter for ActiveRecord forum (http://groups.google.com/group/oracle-enhanced/browse_thread/thread/cf63b8079dd02926) but after some debugging we believe this is a bug in the ruby-oci8 driver.
Environment:
Ruby 1.9.2
Rails 3.0.3
Oracle Enhanced Adapter 1.3.1
Ruby OCI 8 2.0.4
Oracle 11g (with full 11.2 client)
Ubuntu 10.04
I'm getting the following error when running a simple select on a table which has columns defined as DATEs:
Service Load (1.6ms) SELECT "SW_SERVICE".* FROM "SW_SERVICE" WHERE
(namespace = 'SW') AND ROWNUM <= 1
NoMethodError: undefined method `*' for nil:NilClass: SELECT
"SW_SERVICE".* FROM "SW_SERVICE" WHERE (namespace = 'SW') AND ROWNUM
<= 1
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:202:in `rescue
in log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:194:in `log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:948:in `log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:906:in `select'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:54:in `block
in select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:68:in
`cache_sql'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:54:in
`select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/base.rb:467:in `find_by_sql'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation.rb:64:in `to_a'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation/finder_methods.rb:333:in `find_first'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation/finder_methods.rb:122:in `first'
/app/sw-api/releases/20101213215753/app/controllers/
users_controller.rb:35:in `create'
The root cause is being swallowed by the Oracle enhanced ActiveRecord adapter, so by adding a line to see the root cause Exception, we get this:
ROOT CAUSE:
NoMethodError: undefined method `*' for nil:NilClass
/app/sw-api/shared/bundle/ruby/1.9.1/gems/ruby-oci8-2.0.4/lib/oci8/
datetime.rb:193:in `ocitimestamp_to_time'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/ruby-oci8-2.0.4/lib/oci8/
datetime.rb:268:in `get'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_oci_connection.rb:110:in `fetch'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_oci_connection.rb:110:in `select'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:907:in `block in select'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:197:in `block in
log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activesupport-3.0.3/lib/
active_support/notifications/instrumenter.rb:21:in `instrument'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract_adapter.rb:195:in `log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:948:in `log'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-oracle_enhanced-
adapter-1.3.1/lib/active_record/connection_adapters/
oracle_enhanced_adapter.rb:906:in `select'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/database_statements.rb:7:in
`select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:54:in `block
in select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:68:in
`cache_sql'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/connection_adapters/abstract/query_cache.rb:54:in
`select_all'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/base.rb:467:in `find_by_sql'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation.rb:64:in `to_a'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation/finder_methods.rb:333:in `find_first'
/app/sw-api/shared/bundle/ruby/1.9.1/gems/activerecord-3.0.3/lib/
active_record/relation/finder_methods.rb:122:in `first'
/app/sw-api/releases/20101214143730/app/controllers/
users_controller.rb:35:in `create'
If we take the Oracle enhanced ActiveRecord adapter out of the picture and use the ruby-oci8 driver directly in irb (mimicking what the Oracle enhanced ActiveRecord adapter is doing), we're able to re-produce this:
deploy@web1:~$ irb
ruby-1.9.2-p0 > require "rubygems"
=> true
ruby-1.9.2-p0 > gem "ruby-oci8"
=> true
ruby-1.9.2-p0 > require "oci8"
=> true
ruby-1.9.2-p0 > ENV['TZ'] = 'US/Eastern'
=> "US/Eastern"
ruby-1.9.2-p0 > conn = OCI8.new "XXXX", "XXXX", "XXXX"
=> #<OCI8:XXXX>
ruby-1.9.2-p0 > conn.exec "alter session set time_zone =
'#{ENV['TZ']}'"
=> 0
ruby-1.9.2-p0 > cursor = conn.exec "SELECT \"SW_SERVICE\".* FROM
\"SW_SERVICE\" WHERE
ruby-1.9.2-p0"> (namespace = 'SW') AND ROWNUM <= 1"
=> #<OCI8::Cursor:0x00000001836e08>
ruby-1.9.2-p0 > cursor.fetch
NoMethodError: undefined method `*' for nil:NilClass
from /usr/local/rvm/gems/ruby-1.9.2-p0/gems/ruby-oci8-2.0.4/lib/oci8/
datetime.rb:193:in `ocitimestamp_to_time'
from /usr/local/rvm/gems/ruby-1.9.2-p0/gems/ruby-oci8-2.0.4/lib/oci8/
datetime.rb:268:in `get'
from (irb):9:in `fetch'
from (irb):9
from /usr/local/rvm/rubies/ruby-1.9.2-p0/bin/irb:16:in `<main>'
Removing the "alter session set time_zone = '#{ENV['TZ']}'" eliminates the exception:
deploy@web1:~$ irb
ruby-1.9.2-p0 > require "rubygems"
=> true
ruby-1.9.2-p0 > gem "ruby-oci8"
=> true
ruby-1.9.2-p0 > require "oci8"
=> true
ruby-1.9.2-p0 > conn = OCI8.new "XXXX", "XXXX", "XXXX"
=> #<OCI8:XXXX>
ruby-1.9.2-p0 > cursor = conn.exec "SELECT \"SW_SERVICE\".* FROM
\"SW_SERVICE\" WHERE
ruby-1.9.2-p0"> (namespace = 'SW') AND ROWNUM <= 1"
=> #<OCI8::Cursor:0x00000001eabb80>
ruby-1.9.2-p0 > cursor.fetch
=> [3, "/sw/", "SW", "Y", 2004-11-14 00:00:00 -0600, 2025-12-31
00:00:00 -0600]
Here's the code in ruby-oci8-2.0.4/lib/oci8/datetime.rb it's failing on:
if @@time_new_accepts_timezone
# after ruby 1.9.2
def ocitimestamp_to_time(ary)
return nil if ary.nil?
year, month, day, hour, minute, sec, fsec, tz_hour, tz_min
= ary
sec += fsec / Rational(1000000000)
HERE ---> utc_offset = tz_hour * 3600 + tz_min * 60
return ::Time.new(year, month, day, hour, minute, sec,
utc_offset)
end
else
Given that the error is "undefined method `*' for nil:NilClass", it looks like that tz_hour object is the one that's nil. The columns in question are defined as DATE:
START_DATE NOT NULL DATE
END_DATE DATE
Note this snippet from datetime_to_array() where it's setting tz_hour and tz_min to nil:
# time zone
if val.respond_to? :offset
# DateTime
tz_min = (val.offset * 1440).to_i
elsif val.respond_to? :utc_offset
# Time
tz_min = val.utc_offset / 60
else
tz_hour = nil
tz_min = nil
end
|
|
|
