How to Copy Data from Oracle to MySQL

This example shows how to copy a table from Oracle to MySQL. First create a table in Oracle and add some data, including some null values and a date column:

 

SQL> create table andrews_table

  2  (first_name varchar2(10),

  3   dob        date,

  4   salary     number)

  5  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Fred', '25-DEC-1980', null)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  ('Jim', null, 10000)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  (null, '01-SEP-1983', 20000)

  3  /

 

1 row created.

 

SQL> select * from andrews_table

  2  /

 

FIRST_NAME DOB           SALARY

---------- --------- ----------

Fred       25-DEC-80

Jim                       10000

           01-SEP-83      20000

 

SQL>

 

Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:

 

UNIX > cat extract.sql

set feedback off

set lines 100

set pages 0

set termout off

set trimspool on

spool extract

select nvl(first_name,'\N')||'|'||

       nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||

       nvl(to_char(salary),'\N')

from andrews_table

/

spool off

UNIX >

 

... and run the SQL:

 

SQL> @extract

SQL>

 

The output file will look like this:

 

UNIX > cat extract.lst

Fred|1980-12-25|\N

Jim|\N|10000

\N|1983-09-01|20000

UNIX >

 

Copy it to a machine running MySQL then you can load it into a table as follows:

 

--------------

create table andrews_table

(first_name varchar(10),

dob        date,

salary     int)

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

load data local infile 'extract.lst'

into table andrews_table

fields terminated by '|'

lines terminated by '\n'

(first_name, dob, salary)

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

--------------

select * from andrews_table

--------------

 

+------------+------------+--------+

| first_name | dob        | salary |

+------------+------------+--------+

| Fred       | 1980-12-25 |   NULL |

| Jim        | NULL       |  10000 |

| NULL       | 1983-09-01 |  20000 |

+------------+------------+--------+

3 rows in set (0.00 sec)

 

mysql>