Platinum Partner

Switch Oracle CLOB To VARCHAR2

This rake task creates a database script to change all Oracle CLOB columns to VARCHAR2 columns.

Changing Oracle CLOBs to VARCHAR2s can result in a huge performance increase, especially if the database and app servers are not close together on the network.


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
{{ tag }}, {{tag}},

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}
{{ parent.authors[0].realName || parent.author}}

{{ parent.authors[0].tagline || parent.tagline }}

{{ parent.views }} ViewsClicks
Tweet

{{parent.nComments}}