How to avoid `ORA-12899: Value too Large for Column` in your Rails application

Posted by Patrick Baselier on 28-7-2016

When your Ruby on Rails application uses an Oracle database you might have seen the following error:

1
  ORA-12899: value too large for column title (actual: 26, maximum: 25)

As the error states, this is caused when your value entered is larger than the field definition in the database. When you’re not handling this error nicely, the server raises a 500 and the end-user will end up seeing the infamous Something Went Wrong page.

Of course, solving this issue is easy: just add validates :title, length: { maximum: 25 } to the model, but adding validations for every VARCHAR2 field in the database is a tedious and error prone job. Don’t forget to do this for every model in your application…

Luckily we can use some metaprogramming and define a validates for every VARCHAR2 field dynamically.

The following concern scans the model’s database table for every VARCHAR2 and add a validates macro with the appropriate length used as the maximum value.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#app/models/concerns/oracle_max_length_validations.rb
module OracleMaxLengthValidations
  extend ActiveSupport::Concern

  included do
    string_fields.each { |c| validates c.name, length: { maximum: c.cast_type.limit } }
  end

  class_methods do
    private

    def string_fields
      return [] unless table_exists?
      
      columns.select { |c| c.cast_type.type == :string }
    end
  end
end

This code sample applies to Rails 4.2. In Rails 4.1 you need to use c.limit instead of c.cast_type.limit (same goes for c.cast_type.type).

To use this in a model, just include the concern:

1
2
3
4
5
6
#app/models/person.rb
class Person
  include OracleMaxLengthValidations
  
  ...
end

Patrick Baselier

I’m a professional Ruby on Rails-, front-end- and unprofessional (that is: not professionally… yet) Ember developer from The Netherlands, I love sharing knowledge and one day I hope to be a more than a novice guitar player.