A fast, minimal, debuggable Ruby wrapper for sqlplus and mysql binary's that pulls data out of an Oracle database and inserts it into MySQL.

Please contact us via github if you have interest in using this tool, though it works great for us, it's rough around the edges with regard to installation. If we know of folks who want to use it/contribute we'll make a point of cleaning things up and pushing to github more regularly.

• POpen4 gem
• mysql gem
• sqlplus binary in $PATH
• mysql binary in $PATH

class TheMagicMirrorer
  include OracleToMysql

  def otm_source_sql
    "select 
       col || CHR(9) ||
       col || CHR(9) ||
       ...
     from table
     where ... Oracle statement"
  end

  def otm_target_table
    "a_mysql_table_name"
  end

  def otm_target_sql
    "create table if not exists #{self.otm_target_table} (... mysql statement   "
  end
end

x = TheMagicMirrorer.new
x.otm_execute

Will mirror the contents of otm_source_sql into the table created by otm_target_sql. The "|| CHR(9) ||" is Oracle sql code that tab deliminates the column content in the spooled sqlplus SQL data output. The Mysql "load data infile" command eats this output.

If you are using with Rails, it expects database.yml to have oracle_source and mysql_target entries to get the db connect info, to override the names

TODO: More usage examples for the config options below

:atomic_rename (Default) "load data infile" the spooled oracle tab deliminted data into a temp table first then atomically rename current_target_table -> old_target_table AND new_temp_table -> new_target_table :accumulative "load data infile" directly into target_table replacing any existing rows in target when source data triggers "ON DUPLICATE KEY"

Determines how many mysql target tables should be preserved. By default it just keeps yesterdays

TODO: Integrate Chris's more general and not brittle test cases

The "tests" aka demo's assume you have a ps_term_tbl in your Oracle db. If you're running PeopleSoft at a University you'll probably have this... Otherwise, the tests won't run, it's just meant as an example that works in our world.

You'll need the thoughtbot-shoulda gem if you want to develop/hack on this gem or run the tests

To run tests: cd test ruby test_oracle_to_mysql_against_ps_term_tbl.rb OR irb -r test_oracle_to_mysql_against_ps_term_tbl.rb

This assumes you have a connection file in the test dir: oracle_to_mysql.yml copy and populate from oracle_to_mysql.example.yml

• Make a more general testing suite
• Re-release gem so the dependenies work: Should be able to do gem install oracle_to_mysql and have things actually work
• retention policy of 0: don't keep yesterdays data (aka don't create *_old table in mysql)
• retention policy > 1: keep N mysql table copies around