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
end

def self.down
end
end

& 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 :

ALTER TABLE SCHEMANAME.TABLENAME ADD NEWCOLUMN CLOB;
UPDATE SCHEMANAME.TABLENAME SET NEWCOLUMN = OLDCOLUMN;
ALTER TABLE SCHEMANAME.TABLENAME drop COLUMN OLDCOLUMN;
ALTER TABLE SCHEMANAME.TABLENAME rename COLUMN NEWCOLUMN TO OLDCOLUMN;

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)
end
remove_column :plan_elements, :value
rename_column :plan_elements, :value_temp, :value
end
def self.down
end
end

And it worked like a charm for me…. !