Rails and Oracle - can select date but cannot save

rubyonrails-talk
1

I'm seeing a behavior in Rails that I find very strange. Having worked quite a bit with Oracle with other languages I would always dutifully do ALTER SESSION SET NLS_DATE_FORMAT = <my_date_format> and ensure I use that date format in my application. With Rails I still have not figured out how to execute an ALTER SESSION statement after connecting to the database (I'm using the the jdbc adapter). And sure enough I get a "date format not recognized" error when trying to save a value to a DATE column. But surprisingly when I select pre-inserted dates correctly. The default Rails date format in my platform is YYYY-MM-DD. The default date format of my Oracle database is DD-MMM-YY. Ultimately what I'm after is getting my dates to work both on select and save but I would welcome an explanation as to why Rails can select correctly with the application and the database date format being out of sync.

2

Found how to set the date format for DB conversion on the Rails side as opposed to Oracle. It's good but it's not enough. I put the following in overrides.rb:

module ActiveSupport #:nodoc:   module CoreExtensions #:nodoc:     module Date #:nodoc:       # Converting dates to formatted strings, times, and datetimes.       module Conversions         DATE_FORMATS[:db] = "%d-%b-%y"       end     end   end end

This gives me , for Aug 13th 2010, 13-Aug-10. However, Oracle expects 12-AUG-10 (i.e. month all caps) and continues to reject the date on save. Needless to say select continues to work mysteriously. Need to figure out how to set NLS_DATE_FORMAT when the connection is established.

3

DATE_FORMATS[:db] = '%d-%b-%y'.upcase

Regardless, you might want to try the oracle_enhanced adapter -- <http://github.com/rsim/oracle-enhanced&gt;

FWIW,

4

What's the benefit of using Oracle enhanced over activerecord-jdbc-adapter? Is is any faster/more reliable?

I think I figured out the problem which imho is a bug in Oracle's activerecord-jdbc-adapter. Through debugging I noticed that Rails converts the date using the Oracle's TIMESTAMP function for example:

INSERT INTO <table_name> VALUES (TIMESTAMP'2010-03-29').

However Oracle expects TIMESTAMP to be ALWAYS invoked with the format 'YYYY-MM-DD H24:MI:SS'. That spells always, regardless of the NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT settings. If you the time portion is not specified, the statement errors out with a "ORA-01861: literal does not match format string" message. Using the jdbc adapter from activerecord-jdbc-adapter 0.9.7 I found the problem to be with the JdbcSpec::Oracle::quoted_date method:

module ::JdbcSpec

  module Oracle

    def quoted_date(value)         %Q{TIMESTAMP'#{super}'}       end     end   end end

The method first converts the value to the db format as specified by:

ActiveSupport::CoreExtensions::date::Conversions::DATE_FORMATS[:db] which is pre-set to Y-%m-%d".

which gets the date portion right will NEVER work with TIMESTAMP since the time portion is missing. For zero times the DATE method should be used instead. With the following fix everything works like a charm:

module ::JdbcSpec

  module Oracle

    def quoted_date(value)       if value.acts_like?(:time)         %Q{TIMESTAMP'#{super}'}       else         %Q{DATE'#{super}'}       end     end   end end

5

Less buggy -- I had problems with the activerecord-jdbc-adapter that were fixed by switching to oracle_enhanced. This was a while ago, and I don't remember the details off the top of my head.