DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Dan Manges

Dan has posted 4 posts at DZone. View Full User Profile

Switch Oracle CLOB To VARCHAR2

11.20.2006

| 17979 views |

        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

Tags:

Comments

Snippets Manager replied on Thu, 2007/04/19 - 11:50am

I was a little snippy in the firest comment, it looked like simple not testing, but after a little research I think the syntax might be fine in oracle 9i. The following syntax gets you the samething in 8i. ALTER TABLE ALL_TAB_COLUMNS_LOCAL ADD DATA_DEFAULT_temp VARCHAR2(4000); UPDATE ALL_TAB_COLUMNS_LOCAL SET DATA_DEFAULT_temp = dbms_lob.substr(DATA_DEFAULT,4000,1); COMMIT; ALTER TABLE ALL_TAB_COLUMNS_LOCAL DROP COLUMN DATA_DEFAULT; ALTER TABLE ALL_TAB_COLUMNS_LOCAL ADD DATA_DEFAULT VARCHAR2(4000); UPDATE ALL_TAB_COLUMNS_LOCAL SET DATA_DEFAULT = DATA_DEFAULT_temp; COMMIT; ALTER TABLE ALL_TAB_COLUMNS_LOCAL DROP COLUMN DATA_DEFAULT_temp;

Snippets Manager replied on Thu, 2007/04/19 - 11:50am

This would be great if it actually worked. I pulled the alter and update scripts out of this and tried to run them on one of my tabels, version 8.1.7 database. the update fails because of inconsistant data types. While I have never been on this board before and don't know the ettique I would think a little basic testing would be good before posting.