How to Migrate table data from Oracle to MySQL?

Migration From Oracle Database To Mysql Database

This method shows hot to migrate tables data from oracle to MySQL using export flat file using query and import in MySQL using LOAD DATA INFILE.

The task of migrating an entire program from one database to another is not always easy. In addition to the physical aspect of retrieving data from one machine to another, or even from another geographic location, some of the most complex data is converted between different types of data, stored procedures and simple incompatible differences that can occur between the two databases.

STEP-1: EXPORT DATA FROM ORACLE.

Exporting existing data from Oracle is often one of the cheapest forms of migration, but it can involve more manual entry 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 has the ability to export data from Oracle tables in many formats, such as Excel, SQL inserts, SQL load formats, HTML, XML, PDF, TEXT, fixed text, etc.

You can use SQL * Plus to select and format your data, then merge it into a file. This example rewinds a CSV (comma separated values) file which can be imported via LOAD DATA INFIL or by creating a CSV table for immediate access.

Commands:

SQL> set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on

SQL> spool oradata.txt

SQL> select col1 || ',' || col2 || ',' || col3

from tab1

where col2 = 'XYZ';

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:

Commands:

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

Or Using PL-SQL Block.

Commands:

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;

STEP-2: Importing Data into MySQL

Once data has been exported from Oracle to a flat file, it can be loaded into MySQL. This step continues with the expectation that an identical schema structure has been created in the MySQL database to accept data.

To read flat file in mysql is LOAD DATA INFILE command.

Commands:

LOAD DATA INFILE 'data.txt' INTO TABLE tbl_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Above command Reads data exported from the 'data.txt' file to the 'tbl_name' table using the specified separators.

Please do not forget to comment on this Post.