Rails and Oracle - can select date but cannot save

Rails
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

On Fri, Aug 13, 2010 at 12:05 PM, Tomasz R.
[email protected] wrote:

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:

   DATE_FORMATS[:db] = "%d-%b-%y"

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.

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

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

FWIW,

Hassan S. ------------------------ [email protected]
twitter: @hassan

4

On Sat, Aug 14, 2010 at 8:18 AM, Tomasz R.
[email protected] wrote:

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

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.

–
Hassan S. ------------------------ [email protected]
twitter: @hassan

5

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