Rails 3 and Oracle: NLS Settings reject decimal separator

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 representat开发者_C百科ions 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

---

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.

---

If anyone comes looking for ORA-01722 raised using activerecord-oracle_enhanced-adapter gem, here's an updated answer.

ActiveRecord::StatementInvalid (OCIError: ORA-01722: invalid number)

This is still happening in pretty recent versions as well (Rails 6 & oracle gem 6.0) when saving BigDecimal

See this really good answer to fix decimal separator using database.yml config

development:
  adapter: oracle_enhanced
  username: XXX
  password: XXX
  host: XXXX
  nls_numeric_characters: '.,'