Oracle adapter problem? How to fix this?

[pepe]

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.

[Max Williams]

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.
--
Posted via http://www.ruby-forum.com/.

[pepe]

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.

[Max Williams]

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

[pepe]

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.

[Rodrigo Dellacqua]

You should report this issue.

You'll save someone's life.

--

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 rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

[pepe]

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 Dellacqua <rgoyta...@gmail.com>
wrote:

> You should report this issue.
>
> You'll save someone's life.
>

> On Wed, Dec 30, 2009 at 8:16 PM, pepe <P...@betterrpg.com> wrote:
> > 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/<http://blog.rayapps.com/2008/05/13/activerecord-%0Aoracle-enhanced-ad...>).

> > 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 Williams <li...@ruby-forum.com> 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 rubyonra...@googlegroups.com.
> > To unsubscribe from this group, send email to

> > rubyonrails-ta...@googlegroups.com<rubyonrails-talk%2Bunsu...@googlegroups.com>

[Raimonds Simanovskis]

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

> > > rubyonrails-ta...@googlegroups.com<rubyonrails-talk%2Bunsubscrib e...@googlegroups.com>

[Rodrigo Dellacqua]

Awesome! :)

Pepe, thanks for letting us know about it.

Gotta love RoR community, thanks Raimonds, kudos!

[]'s
Rodrigo Dellacqua

To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.

[pepe]

Thanks Raimonds, you've been a life saver.

On Jan 3, 6:01 am, Raimonds Simanovskis