Switch Oracle CLOB to VARCHAR2 (See related posts)

namespace :db do
  task :fix_clobs => [:environment] do |t|
    @outfile = File.expand_path(File.join(RAILS_ROOT,"db","fix_clobs.sql"))
  
    File.open(@outfile, "w") do |file|
      ActiveRecord::Base.connection.tables.each do |table_name|
        begin
          model = eval(table_name.classify)
          model.columns.each do |column|
            if column.sql_type == "CLOB"
              file.write("ALTER TABLE #{table_name} ADD #{column.name}_temp VARCHAR2(4000);\n")
              file.write("UPDATE #{table_name} SET #{column.name}_temp = #{column.name};\n")
              file.write("COMMIT;\n")
              file.write("ALTER TABLE #{table_name} DROP COLUMN #{column.name};\n")
              file.write("ALTER TABLE #{table_name} RENAME COLUMN #{column.name}_temp TO #{column.name};\n")
              file.write("\n")
            end
          end
         rescue => ex
           puts "Failed for #{table_name} with #{ex.class}"
         end
      end
    end
  end
end