Andrew Fraser DBA

Data from MySQL to Oracle with dg4odbc database link or mysqldump

To transfer data from MySQL into Oracle

1) Best method is to use a database link. That way the MySQL table is available in the Oracle database for use in queries, joins, DML, PL/SQL.

Simple guides on how to do to that are:

1. Shishir Tekade http://www.shishirtekade.com/2014/01/connect-oracle-to-mysql-server-through.html
2. Pythian http://www.pythian.com/blog/how-to-access-mysql-from-oracle-with-odbc-and-sql/
3. My Oracle Support 1320645.1 ‘Detailed Overview of Connecting Oracle to MySQL Using DG4ODBC Database Link’ https://support.oracle.com/epmos/faces/DocContentDisplay?id=1320645.1

1b) For a critical/enterprise system it might be worth paying for Oracle’s Hetrogenous Services Database Gateway. That works the same as above but substitutes Oracle’s proprietary driver files in place of free ODBC. The advantage is Oracle Support would fully support this, not pass the buck onto the free ODBC drivers.

2) Alternatively can have a regularly running scheduled task or cron that copies data from MySQL into Oracle. The mysqldump utility used for this is often installed by default with linux, in /usr/bin. To avoid failed copies from leaving empty tables in the target database, a safety check (e.g. count below) is needed before proceeding.

rm -f data.tmp

# 1) Extract list of insert statements from mysql into file data.tmp
mysqldump --host=myhost --user=myuser --password=mypassword --complete-insert --no-create-info --skip-opt --compatible=oracle --skip-quote-names \
    --compact mydatabase mytable > data.tmp

# 2) Change table name from mytable to mytable_tmp in insert statements
sed -i 's/mytable/mytable_tmp/g' data.tmp

# 3) Insert those into Oracle - using temp table and begin/end check to only proceed if reasonable number of rows.
sqlplus -s username/password@database << END_ORACLE
set serverout on
-- Use MySQL datetime format
ALTER SESSION SET nls_date_format='YYYY-MM-DD HH24:MI:SS' ;
DELETE FROM mytable_tmp ;
@data.tmp
-- But only copy this data to the live table if looks to be a reasonable amount of data.
DECLARE
   l_count NUMBER ;
BEGIN
   SELECT COUNT(*) INTO l_count FROM mytable_tmp ;
   IF l_count > 100
   THEN
      dbms_output.put_line ( 'Copying ' || l_count || ' rows from MySQL into mytable' ) ;
      DELETE FROM mytable ;
      INSERT INTO mytable SELECT * FROM mytable_tmp ;
   ELSE
      dbms_output.put_line ( 'Error: insufficient rows loaded, not proceeding with data copy.' ) ;
   END IF ;
END ;
/
END_ORACLE

Removing –no-create-info option from mysqldmp will generate oracle compatible create table DDL statements, useful for first setup.

h/t Slim Backwater.