Switch Oracle CLOB to VARCHAR2

dcmanges (Dan Manges) — Mon, 20 Nov 2006 08:23:21 GMT

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

DZone Snippets: clob code

Switch Oracle CLOB to VARCHAR2