ORA-01704 : string literal too long

Change in Column data type from String to text in Rails

So this is something tricky I came to know while working on one of the project on Ruby on Rails.  I am using Oracle as the database. I have a plan_template model, which has an attribute ‘value’ whose data type is string with limit as 4000 as :

 t.string  :value, :limit => 4000

So the requirement was to change its limit. So i came up with an idea of changing it to text.

I simply ran the migration as bundle exec rails destroy migration ChangeColumnInPlanElements

class ChangeColumnInPlanElements < ActiveRecord::Migration
def self.up
change_column :plan_elements, :value, :text

def self.down

& on doing rake  db:migrate I came across an error as :

== ChangeColumnInPlanElements: migrating =====================================
— change_column(:plan_elements, :value, :text)
rake aborted!
An error has occurred, all later migrations canceled:

OCIError: ORA-22859: invalid modification of columns: ALTER TABLE “PLAN_ELEMENTS” MODIFY “VALUE” CLOB
stmt.c:235:in oci8lib.so

Then browsing on net i came to know When we try to modify a datatype from varchar2(4000) to Clob in a Oracle table, we get this error.

So Then I came up with the approach to do this it is a four steps task :


which is equivalent to
1. Add a  temporary column with type as text(CLOB)
2. Update all the records and copy text from value column to temporary column
3. Remove value column
4. Rename temporary column to value

so then I modified my migration file as

class ChangeColumnInPlanElements < ActiveRecord::Migration

def self.up
add_column :plan_elements, :value_temp, :text
PlanElement.all.each do |et|
et.update_attribute(“value_temp”, et.value)
remove_column :plan_elements, :value
rename_column :plan_elements, :value_temp, :value
def self.down

And it worked like a charm for me…. !


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s