Problem with oracle enhanced adapter and rails 2.3.4

[kylepeyton]

I am trying to get my app working after upgrading to rails 2.3.4. I
launch my script/console and get this error:

/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
date.rb:1537:in `new_by_frags':ArgumentError: invalid date

I delved in to that file and outputted the trace for that error and
got this:

/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
date.rb:1584:in `parse'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/ruby-
oci8-2.0.2/lib/oci8/datetime.rb:9
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:521:in
`new_constants_in'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/ruby-
oci8-2.0.2/lib/oci8.rb:72
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:36:in `gem_original_require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:36:in `require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:521:in
`new_constants_in'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/
connection_adapters/oracle_enhanced_oci_connection.rb:4
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:521:in
`new_constants_in'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/
connection_adapters/oracle_enhanced_connection.rb:114
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:521:in
`new_constants_in'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-oracle_enhanced-adapter-1.2.2/lib/active_record/
connection_adapters/oracle_enhanced_adapter.rb:33
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `gem_original_require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/
site_ruby/1.8/rubygems/custom_require.rb:31:in `require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:521:in
`new_constants_in'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activesupport-2.3.4/lib/active_support/dependencies.rb:156:in
`require'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-2.3.4/lib/active_record/connection_adapters/abstract/
connection_specification.rb:71:in `establish_connection'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-2.3.4/lib/active_record/connection_adapters/abstract/
connection_specification.rb:60:in `establish_connection'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
activerecord-2.3.4/lib/active_record/connection_adapters/abstract/
connection_specification.rb:55:in `establish_connection'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
rails-2.3.4/lib/initializer.rb:438:in `initialize_database'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
rails-2.3.4/lib/initializer.rb:141:in `process'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
rails-2.3.4/lib/initializer.rb:113:in `send'
/home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/
rails-2.3.4/lib/initializer.rb:113:in `run'
/home/cad/rorapps/libflow/staging/releases/20091014152037/config/
environment.rb:16
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb/init.rb:252:in `require'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb/init.rb:252:in `load_modules'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb/init.rb:250:in `each'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb/init.rb:250:in `load_modules'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb/init.rb:21:in `setup'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
irb.rb:54:in `start'
/home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/bin/irb:13

My question is, why is it loading the OCI8 library? I thought the
oracle_enhanced adapter gem was supposed to do what oci8 does but
without it. Am I wrong?

[KUBO Takehiro]

On Thu, Oct 15, 2009 at 7:13 AM, kylepeyton <kylep...@gmail.com> wrote:
>
> /home/cad/rorapps/env/sprinkle/../libflow/stg/tools_32/lib/ruby/1.8/
> date.rb:1584:in `parse'
> /home/cad/rorapps/env/libflow/stg/tools_32/lib/ruby/gems/1.8/gems/ruby-
> oci8-2.0.2/lib/oci8/datetime.rb:9

What version of ruby do you use? Could you post the output of the following
command:
ruby -v

datetime.rb:9 calls ::DateTime.parse('0001-01-01 00:00:00.000000001') to guess
the unit of fractional second which depends on ruby version. It raises no
exception as far as I have checked.

[Raimonds Simanovskis]

Just wanted to comment that oracle_enhanced adapter is not replacing
ruby-oci8 - it is sitting on top of it to provide ActiveRecord
interface for Oracle database.

And in your case please try to find out the value of data field on
which ruby-oci8 is failing. There was mentioned one similar issue when
Oracle database stored strange value in DATE column which could not be
retrieved by ruby-oci8.

Raimonds

On Oct 18, 12:44 pm, KUBO Takehiro <kubo.takeh...@gmail.com> wrote:

[Martijn van Rheenen]

Just for clarification: I had this similar issue with Oracle date values, found after converting from a legacy Oracle 8 database to Oracle 10. Here's the post in which I try to find the origins of this data. I also 'hacked' a fix into Oracle_Enhanced for this, it is in the mailinglist. Here's my last post from that thread:

OCI8::VERSION gives '1.0.6'.

When a date field is NULL in Oracle, it *is* retrieved as 'nil' by ruby-oci8. So that is working perfectly, also in the Oracle Enhanced code.

The problem occurs, when the date field value is not 'nil' nor filled with a valid date value: it is empty. An 'empty' date is returned as a datetime with only '0' values by OCI8. And this is where the problem occured.

I created the following SQL to get a better look at the data of a record that does work, and a record that causes the error:

select fllid, creation_datetime , nvl(creation_datetime, Sysdate), length(creation_datetime), to_char(creation_datetime) from filelist where fllid=959 or fllid=198

It outputs:

FLLID  creation_datetime  nvl()               length()  to_char()
-----  -----------------  ------------------  --------  ---------
198                       24-9-2009 13:49:38
959                                           9         00-000-00

The date with fllid '198' works perfectly and is treated as a 'nil' value by OCI and hence by the Oracle Adapter.
The date with fllid '959' is the one that will cause problems: As you can see, it IS 9 characters long (???) but it is not 'null' (indicated by the empty nvl(..) column) and it is displayed as an 'empty' date just like the date above! Only when converting it to 'char', the '0' values are shown...

I tried to create an insert statement that can reproduce a date like this, but have not succeeded so far. I already tried insert statements like this:

insert into datatest (id) values (1) -- just inserts as 'NULL'
insert into datatest (id, dt) values (2, '') -- just inserts as 'NULL'
insert into datatest (id, dt) values (5, to_date('00/00/0000','MM/DD/YYYY')) -- is not valid
insert into datatest (id, dt) values (5, to_date('01/01/4712 bc','DD/MM/YYYY bc')) -- inserts as normal date
insert into datatest (id, dt) VALUES (6, to_date( '00/00/0000 00:00:00 00', 'MM/DD/YYYY HH:MI:SS AM')) -- is not valid

So I'm really a bit at a loss here, how this data was inserted at all into the database, as you can imagine. I do know this Oracle 10 database was created by importing (with Oracle's imp.exe) an export (using Oracle's exp.exe) from a Oracle 8i database...