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:
Posts
0 comments:
Post a Comment