Skip to content

Instantly share code, notes, and snippets.

@ser1zw
Created May 5, 2015 02:04
Show Gist options
  • Save ser1zw/103d1cc626f2344acc70 to your computer and use it in GitHub Desktop.
Save ser1zw/103d1cc626f2344acc70 to your computer and use it in GitHub Desktop.
INSERT query generator for Oracle
#
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment