Rails 3 and Oracle: NLS Settings reject decimal separator

Question

I'm working with Ruby on Rails 3.0 on a readonly oracle database (connecting via oracle_enhanced_adapter).

Running into the known "n+1 queries" problem i tried the includes method.
Differing from the description in the Rails guides the produced second query listed the required ids not as integers but as string representations of float values. The original ids are of type NUMBER.

Unfortunately the databases NLS settings are for Germany, including the NLS_NUMERIC_CHARACTERS which expects "," as decimal separator. So I always get an ORA-01722 error as described here.

More precisely:

@var.assoc.includes(:another_assoc).where("column_1 = ?", some_value)

yields

ActiveRecord::StatementInvalid: OCIError: ORA-01722: invalid number: SELECT "TABLE_A".* FROM "TABLE_A" WHERE ("TABLE_A"."ID" IN ('1715.0','1716.0','1717.0','1718.0','1719.0','1720.0','1721.0'))

(I had to simplify the Rail code above, since it contained some distracting details like "string to symbol" conversions)

As mentioned the database is readonly, so using

alter session set nls_numeric_characters = '.,'

worked direct on the database. But I was not able to find the right way to alter the rails session.

Everything I found seemed to refer to Rails 2 or used deprecated functions.
How can I solve this for Rails 3.0?

Alternatively: How can I force Rails (or maybe the oracle_enhanced_adapter) to convert all listed ids to Fixnum?

Thanks and best regards, Tim

Answer 1

We had the same problem using Oracle, and we solved it by placing the following code into an initializer (place it in config/initializers/something.rb):

BigDecimal.class_eval do
  alias :old_to_s :to_s

  def to_s(format='F')
    old_result = self.old_to_s(format)
    (old_result[-2..-1] == ".0" ? old_result[0..-3] : old_result)
  end
end

It is caused by the column-type you use as primary key. If you declare it as a NUMBER it will be converted to a BigDecimal. Alternatively you could declare your id's as NUMBER(10) or something similar which would more evidently map to a FixNum (whose id's will be converted correctly).

Hope this helps.