I recently created a model for an existing database table using the legacy_data gem and was confused when my primary key showed up in scientific notation. It turned out the issue was due to sloppiness in the table definition and could be easily fixed once I understood what ActiveRecord was doing.

I created a Person model connected to the people table

class Person < ActiveRecord::Base
end

but when I went into script/console the primary key showed up as a BigDecimal when I expected an integer.

$ script/console 
Loading development environment (Rails 2.3.4)
>> Person.first.id
=> #

This wasn't what I wanted and would cause problems in my app when it tried to build a url with that id like http://localhost:3000/people/10024844425.0. The rails routing engine would see the .0, treat it as a format (like .xml or .json) and get confused. Let's look at why this is happening.

>> Person.columns_hash['id']
=> #<ActiveRecord::ConnectionAdapters::OracleEnhancedColumn:0x37391c0 @default=nil, @type=:decimal,
   #   @null=true, @name="id", @table_name="people", @scale=nil, @sql_type="NUMBER", @precision=nil,
   #   @primary=true, @forced_column_type=nil, @limit=nil>
>> Person.columns_hash['id'].type
=> :decimal
>> Person.columns_hash['id'].sql_type
=> "NUMBER"

We see that ActiveRecord is treating this column as a :decimal because it's sql_type is NUMBER. It turns out this is correct because an Oracle number is a decimal unless you specify it to have 0 digits after the decimal point (scale of 0). Here's the documentation from Oracle (the last sentence is my bold)

NUMBER Datatype

The NUMBER datatype stores fixed and floating-point numbers. Numbers of virtually any magnitude can be stored and are
guaranteed portable among different systems operating Oracle, up to 38 digits of precision.

The following numbers can be stored in a NUMBER column:
  * Positive numbers in the range 1 x 10-130 to 9.99...9 x 10125 with up to 38 significant digits
  * Negative numbers from -1 x 10-130 to 9.99...99 x 10125 with up to 38 significant digits
  * Zero
  * Positive and negative infinity (generated only by importing from an Oracle Version 5 database)

For numeric columns, you can specify the column as:
  column_name NUMBER 

Optionally, you can also specify a precision (total number of digits) and scale (number of digits to the right of the decimal point):
  column_name NUMBER (precision, scale) 

If a precision is not specified, the column stores values as given. If no scale is specified, the scale is zero.

Oracle guarantees portability of numbers with a precision equal to or less than 38 digits. You can specify a scale and no precision:
  column_name NUMBER (*, scale) 

In this case, the precision is 38, and the specified scale is maintained.

When you specify numeric fields, it is a good idea to specify the precision and scale. This provides extra integrity checking on input.

Let's look in my database and sure enough the ID is a number

$ sqlplus myusername/mypassword@localhost:1521/mydatabase.world
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Jan 27 09:15:09 2010
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Release 10.2.0.4.0 - Production
SQL> desc people;
Name        Null?    Type
----------------------------------------- -------- ----------------------------
ID                   NUMBER
NAME                 VARCHAR2(10)

If you are allowed to change your database you can create a migration like

$ script/generate migration change_person_id_to_integer
STUBBING MckinseyLDAP
      exists  db/migrate
      create  db/migrate/20100127145747_change_person_id_to_integer.rb

now edit the migration

class ChangePersonIdToInteger < ActiveRecord::Migration
  def self.up
    change_column(:people, :id, :integer)  
  end
  def self.down
    change_column(:people, :id, :decimal)  
  end
end

In my case there were other applications using this table and I was not allowed to change it so I implemented a fix in Ruby to tell my model to treat this column as an integer even though it was defined as a decimal in the database.

#config/initializers/legacy_data_type_coercion.rb
module LegacyDataTypeCoercion
  def set_integer_columns *col_names
    col_names.each do |col_name|
      columns_hash[col_name.to_s].instance_eval do
        @type = :integer
      end
    end
  end
end
ActiveRecord::Base.extend(LegacyDataTypeCoercion)


#app/models/person.rb
class Person < ActiveRecord::Base
  set_integer_columns :id
end

We defined a method set_integer_columns that will force ActiveRecord to treat the columns we specify as integers. In our Person model we declare :id is an integer column. Let's test it out!

$ script/console 
Loading development environment (Rails 2.3.4)
>> Person.first.id
=> 10024844425
>> Person.columns_hash['id'].type
=> :integer

Just as expected id is now an integer and we can go ahead building the rest of our application.

This is not an issue with all Oracle tables as if the column was defined as NUMBER(10) (with a precision and implicit scale of 0) then ActiveRecord will interpret it as an integer automatically based on the parentheses in the data type - i.e NUMBER(10) ActiveRecord or Oracle Enhanced).