INSERT query generator for Oracle

insert_sql_generator.rb

#
# INSERT query generator for Oracle
#
#   Requirements:
#     - Ruby 2.0 or later (https://www.ruby-lang.org/)
#     - ruby-oci8 (gem install ruby-oci8)
#     - Oracle Database
#
require 'oci8'

class InsertSqlGenerator
  attr_reader :oci, :connection_string

  def initialize(connection_string)
    @connection_string = connection_string
    @oci = OCI8.new(connection_string)
  end

  class Column
    attr_reader :column_name, :data_type

    def initialize(column_name, data_type)
      @column_name = column_name
      @data_type = data_type
    end
  end

  def create_insert_sql(table_name, where_clause = nil)
    columns = []
    query = "SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = :table_name ORDER BY COLUMN_ID"
    @oci.exec(query, table_name) { |rec|
      columns << Column.new(rec[0], rec[1])
    }

    date_format = 'YYYY-MM-DD HH24:MI:SS'
    select_query = create_select_query(table_name, columns, date_format, where_clause)
    create_values_clause = create_values_clause_generator(columns, date_format)

    values = []
    @oci.exec(select_query) { |rec|
      values << create_values_clause.call(rec)
    }

    create_insert_query(table_name, columns, values)
  end

  def logoff
    @oci.logoff
  end

  private
  def create_values_clause_generator(columns, date_format)
    proc { |rec|
      raise unless columns.size == rec.size
      values = []
      columns.zip(rec) { |tuple|
        column, value = *tuple
        if value.nil?
          values << 'NULL'
        else
          case column.data_type
          when 'DATE'
            values << "TO_DATE('#{value}', '#{date_format}')"
          when 'TIMESTAMP'
            values << "TO_TIMESTAMP('#{value}', '#{date_format}')"
          when 'VARCHAR', 'VARCHAR2', 'CHAR', 'CLOB', 'BLOB'
            escaped_value = value.gsub("'", "''")
            values << "'#{escaped_value}'"
          else
            values << value
          end
        end
      }
      values
    }
  end

  def create_select_query(table_name, columns, date_format, where_clause)
    columns_str = columns.map { |column|
      case column.data_type
      when 'DATE', 'TIMESTAMP'
        "TO_CHAR(#{column.column_name}, '#{date_format}')"
      else
        column.column_name
      end
    }.join(', ')

    query = "SELECT #{columns_str} FROM #{table_name}"
    query += " WHERE #{where_clause}" if where_clause
    query
  end

  def create_insert_query(table_name, columns, values)
    colmn_names = columns.map { |c| c.column_name }.join(', ')
    values_clause = values.map { |v| "(#{v.join(', ')})" }.join(', ')

    query = values.map { |v| "(#{v.join(', ')})" }.map { |value|
      "INSERT INTO #{table_name} (#{colmn_names}) VALUES #{value};"
    }.join("\n")
    query
  end
end

if __FILE__ == $0
  # set NLS_LANG=JAPANESE_JAPAN.JA16SJIS
  connection_string = 'scott/tiger@localhost:1521/XE'
  table_name = 'EMP'
  where_clause = "ENAME IN ('SMITH', 'ALLEN')"

  insert_sql_generator = InsertSqlGenerator.new(connection_string)
  puts insert_sql_generator.create_insert_sql(table_name, where_clause)
  insert_sql_generator.logoff
end