Exporting Data from Oracle
Exporting the existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual input and can be slower than using a migration tool. One of the most popular tools for exporting data from Oracle is Oracle SQL Developer. The Oracle SQL Developer tool is capable of exporting data from Oracle tables in numerous formats, like Excel, SQL insert statements, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, etc.
You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that could either be imported via LOAD DATA INFILE or by creating a CSV table for immediate access.
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
You can also use the "set colsep" command if you don't want to put the commas in by hand. This saves a lot of typing. Example:
set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1 where col2 = 'XYZ';
spool off
Using PL/SQL
PL/SQL's UTL_FILE package can also be used to unload data. Example:
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Importing Data into MySQL
Once the data has been exported from Oracle into a flat file, then it can be loaded into MySQL. This step continues with the expectation that an identical schema structure was created in the MySQL database to accept the data.
The most common method to read a flat file into MySQL is using the LOAD DATA INFILE command. This is of the form:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
This will read the exported data from the 'data.txt' file into the table 'tbl_name' using the specified separators.
There is also the LOAD XML INFILE if the exported data is in XML format.
Another method that may be even quicker for load is to use the CSV storage engine capabilities. This requires the table to again be created with the appropriate schema and the storage engine specified as CSV. For example:
CREATE TABLE mytable (
name varchar(50) NOT NULL,
address varchar(100) NOT NULL,
zipcode CHAR(5) NOT NULL
) ENGINE=CSV;
Once the table is created, a .csv file will appear in the data directory corresponding to the table ready to store data. Copy the exported CSV file over the existing 'mytable.csv' data file and execute a FLUSH TABLES to update the table information. Now you have access to the data and can use a INSERT INTO new_table ... SELECT to read data quickly from the CSV table into a more appropriate table for your production system.
Migration Tools
Depending on the setup you use, the following may be interest in your migration tasks:
There are a host of third party tools, some of which are open source. For example:
http://kettle.pentaho.com/
http://www.convert-in.com/ora2sql.htm
http://www.ispirer.com/products/oracle-to-mysql-migration
Exporting the existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual input and can be slower than using a migration tool. One of the most popular tools for exporting data from Oracle is Oracle SQL Developer. The Oracle SQL Developer tool is capable of exporting data from Oracle tables in numerous formats, like Excel, SQL insert statements, SQL loader format, HTML, XML, PDF, TEXT, Fixed text, etc.
You can use SQL*Plus to select and format your data and then spool it to a file. This example spools out a CSV (comma separated values) file that could either be imported via LOAD DATA INFILE or by creating a CSV table for immediate access.
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
You can also use the "set colsep" command if you don't want to put the commas in by hand. This saves a lot of typing. Example:
set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1 where col2 = 'XYZ';
spool off
Using PL/SQL
PL/SQL's UTL_FILE package can also be used to unload data. Example:
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Importing Data into MySQL
Once the data has been exported from Oracle into a flat file, then it can be loaded into MySQL. This step continues with the expectation that an identical schema structure was created in the MySQL database to accept the data.
The most common method to read a flat file into MySQL is using the LOAD DATA INFILE command. This is of the form:
LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
This will read the exported data from the 'data.txt' file into the table 'tbl_name' using the specified separators.
There is also the LOAD XML INFILE if the exported data is in XML format.
Another method that may be even quicker for load is to use the CSV storage engine capabilities. This requires the table to again be created with the appropriate schema and the storage engine specified as CSV. For example:
CREATE TABLE mytable (
name varchar(50) NOT NULL,
address varchar(100) NOT NULL,
zipcode CHAR(5) NOT NULL
) ENGINE=CSV;
Once the table is created, a .csv file will appear in the data directory corresponding to the table ready to store data. Copy the exported CSV file over the existing 'mytable.csv' data file and execute a FLUSH TABLES to update the table information. Now you have access to the data and can use a INSERT INTO new_table ... SELECT to read data quickly from the CSV table into a more appropriate table for your production system.
Migration Tools
Depending on the setup you use, the following may be interest in your migration tasks:
There are a host of third party tools, some of which are open source. For example:
http://kettle.pentaho.com/
http://www.convert-in.com/ora2sql.htm
http://www.ispirer.com/products/oracle-to-mysql-migration
