Oracle adapter problem? How to fix this?

rubyonrails-talk
1

Hi,

My setup is:

Ruby 1.8.6 Rails 2.3.5 activerecord-oracle-adapter (1.0.0.9250)

I upgraded Rails and the adapter recently and I just went through hell trying to find a problem while inserting a record in a table.

Rails was giving me this error:

OCIError (ORA-02289: sequence does not exist):   stmt.c:539:in oci8lib.so   c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:753:in `exec'   c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:142:in `do_ocicall'   c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:753:in `exec'   c:/ruby/lib/ruby/site_ruby/1.8/oci8.rb:255:in `exec'   c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle- adapter-1.0.0.9250/lib/active_record/connection_adapters/ oracle_adapter.rb:700:in `exec'   c:/ruby/lib/ruby/gems/1.8/gems/activerecord-oracle- adapter-1.0.0.9250/lib/active_record/connection_adapters/ oracle_adapter.rb:229:in `next_sequence_value'   app/controllers/datafile_controller.rb:248:in `approval'

However what the real problem was is this:

ORA-01861 Literal Does Not Match Format String

As it turns out (and after lots of research and unless I am crazy already) the problem seems to be in the INSERT statement that is generated. Here goes an example (edited with just some date and datetime values):

INSERT INTO (...) VALUES('2011-01-01', '2009-12-30 00:18:54', '2010-06-30 00:00:00', '1995-01-01', '2009-12-30 00:18:54')

If I try to run manually the INSERT statement generated by Rails I get the ORA-01861 error but if I run it editing the dates or datetime values in format 'DD-MON-YY' (e.g.: '01-JAN-11'), as a post I found suggested, the record is written to the table. BTW, in case it helps all fields in the table that would receive a date or datetime value are of type DATE.

Did I miss something obvious and it's just me not knowing how to set the date/datetime values or is this a known problem?

Thank you.

2

Apologies if i'm missing the point, but you should always deal with actual Time/Date/DateTime ruby objects and let rails handle the translation into the format required by the database.

So, if you do something like

@foo = Foo.create(:time => Time.now)

rails will translate Time.now into the appropriate format.

3

Thanks Max,

I should have added a comment to my prior post. I do use ruby objects to generate/calculate the Time/Date/DateTime values. The INSERT statement I mentioned in the original message I retrieved it from the development.log file. As far as I know that is the real INSERT statement that runs against the Oracle DB and as you can see the values that were generated are not what Oracle is expecting (DD-MON- YY), if you trust that other post that helped me find the problem.

4

Ok, can you post up the rails code in question then?

5

Hi Max,

I did some more investigation and found that there is an alternative to Oracle's adapter made by Raimonds Simanovskis. He calls it 'enhanced' adapter (http://blog.rayapps.com/2008/05/13/activerecord- oracle-enhanced-adapter/). Just curious about it I read what it was about and decided to give it a try.

Check out the differences in the INSERT statements that both adapters generate for date values out of ruby objects (no code changes). Again, this is out of development.log:

Oracle's: INSERT…'2011-01-01', …, '2011-01-01', …, '2009-12-30 12:01:26', …, '1995-01-01', …, '2009-12-30 12:01:26', … enhanced's: INSERT…TO_DATE('2011-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), …, TO_DATE('2011-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), …, TO_DATE('2009-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS'), …, TO_DATE('1995-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS'), …, TO_DATE('2009-12-30 00:00:00','YYYY-MM-DD HH24:MI:SS'), …

Since I changed the adapter I have had no problems.

Thanks for your help, though.

6

You should report this issue.

You’ll save someone’s life.

7

Good idea. I'll do it, although if I remember well what I read Raimonds Simanovskis mentioned that he offered himself to Oracle to maintain the adapter and they declined, not showing much interest in enhancing it, which makes me believe not much will change, unfortunately.

8

Original Oracle adapter is not maintained anymore since Rails 2.0 when it was taken out of Rails core.

Therefore always please use oracle_enhanced adapter (http://github.com/ rsim/oracle-enhanced) which I maintain to be compatible with latest Rails releases and which we use in many production applications.

Raimonds

9

Thanks Raimonds, you've been a life saver.