| Message: 95497 |
 |
BY: Brian Ploetz (bploetz)
DATE: 2010-12-14 23:39
SUBJECT: Exception with DATE columns in Oracle 11g 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
| |
Start New Thread
Exception with DATE columns in Oracle 11g