Friday, September 10, 2010

Ruby Script to Identify CLOBs and Required Length of VARCHAR2 for Conversion

All text type fields in Rails are stored in Oracle as CLOBs (at time of writing), so finding CLOB types may be as easy as looking in db/schema.rb and looking for text type fields. But what if you want to find the length of the largest value of those CLOBs, or are thinking about converting them to string types?

The following script can be saved to loads/save_me_from_the_clob.rb and then loaded via script/console and load 'loads/save_me_from_the_clob.rb'. It will list all DB tables with CLOBs with the required length of VARCHAR2 based on the current maximum value/length of the CLOB column, so you wouldn't truncate any existing values if you converted them, in theory.

# avoid: ActiveRecord::StatementInvalid (OCIError: ORA-00932: inconsistent datatypes: expected - got CLOB
# by indentifying CLOB columns and their maximum values, which can be used to create a migration.

# Written by Gary S. Weaver 9/10/2010

class MaxLengthUtility < ActiveRecord::Base

  # remove the database table dependency
  def self.columns() @columns ||= []; end
  
  def self.find_max_length(table_name, column_name)
    sql = ''
    begin
      # Note: commented out version will cause errors with length > 3999, I think
      #sql = <<-SQL_END
      #SELECT * FROM (SELECT LENGTH(DBMS_LOB.SUBSTR(#{column_name})) AS "LENGTH" FROM #{table_name} WHERE LENGTH(DBMS_LOB.SUBSTR(#{column_name})) IS NOT NULL ORDER BY LENGTH(DBMS_LOB.SUBSTR(#{column_name})) DESC) WHERE ROWNUM = 1
      #SQL_END
      sql = <<-SQL_END
      SELECT * FROM (
        SELECT DBMS_LOB.GETLENGTH(#{column_name}) AS "LENGTH" 
        FROM #{table_name} 
        WHERE DBMS_LOB.GETLENGTH(#{column_name}) IS NOT NULL 
        ORDER BY DBMS_LOB.GETLENGTH(#{column_name}) DESC) 
      WHERE ROWNUM = 1
      SQL_END
      resultset = self.find_by_sql(sql)
      resultset.empty? ? nil : resultset[0].try(:length).try(:to_i)
    rescue => ex
      puts "Failed for '#{sql}'\nwith #{ex.class}: #{ex.backtrace}"
    end
  end
end

ActiveRecord::Base.connection.tables.each do |table_name|
  begin
    #puts "classifying #{table_name}"
    model = nil
    model_name = table_name.classify
    begin
      model = eval(model_name)
    rescue => ex2
      puts "There was a table #{table_name} but no corresponding model named #{model_name}, so we're cowardly abandoning attempts to check for CLOBs in it."
    end
    if model
      #puts "iterating #{table_name} columns"
      model.columns.each do |column|
        #puts "checking #{column.name}"
        if column.sql_type == "CLOB"
          puts "#{table_name}.#{column.name} is a CLOB. Maybe it could be a VARCHAR2(#{MaxLengthUtility.find_max_length(table_name, column.name)})"
        end
      end
    end
  rescue => ex
    puts "Failed for #{table_name} with #{ex.class}: #{ex.backtrace}"
  end
end

See also:

No comments: