Oracle adapter problem? How to fix this?

Rails
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.

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

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

4

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.

On Dec 30, 8:25 am, Max W. [email protected] wrote:

Posted viahttp://www.ruby-forum.com/.
–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.

On Dec 30, 10:57 am, Max W. [email protected] wrote:

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

Posted viahttp://www.ruby-forum.com/.

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

6

You should report this issue.

You’ll save someone’s life.

On Wed, Dec 30, 2009 at 8:16 PM, pepe [email protected] wrote:

generate for date values out of ruby objects (no code changes). Again,

–

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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.

On Dec 30 2009, 5:25 pm, Rodrigo D. [email protected]
wrote:

oracle-enhanced-adapter/http://blog.rayapps.com/2008/05/13/activerecord- oracle-enhanced-ad...).
HH24:MI:SS’), …, TO_DATE(‘2011-01-01 00:00:00’,'YYYY-MM-DD

Ok, can you post up the rails code in question then?
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

8

Awesome! :slight_smile:

Pepe, thanks for letting us know about it.

Gotta love RoR community, thanks Raimonds, kudos!

[]'s
Rodrigo D.

On Sun, Jan 3, 2010 at 9:01 AM, Raimonds Simanovskis <
[email protected]> wrote:

On Jan 1, 6:47 pm, pepe [email protected] wrote:

oracle-enhanced-adapter/<
Oracle’s: INSERT…’2011-01-01’, …, ‘2011-01-01’, …, '2009-12-30
Thanks for your help, though.
“Ruby on Rails: Talk” group.

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

9

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

On Jan 1, 6:47 pm, pepe [email protected] wrote:

oracle-enhanced-adapter/http://blog.rayapps.com/2008/05/13/activerecord- oracle-enhanced-ad...).
HH24:MI:SS’), …, TO_DATE(‘2011-01-01 00:00:00’,'YYYY-MM-DD

Ok, can you post up the rails code in question then?
.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

–

You received this message because you are subscribed to the Google
Groups “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

10

Thanks Raimonds, you’ve been a life saver.

On Jan 3, 6:01Â am, Raimonds Simanovskis