Here Are 2 Ways To Convert Oracle To MySQL Faster

By: Hassan Shareef,
Database Administrator
Since 12 years

Whenever there is a need to convert data from Oracle to MySQL the DBA's spend lot of time in choosing the right tool to accomplish this task. Because conversion from Oracle to MySQL will become quite complex depending upon the size of the existing database and amount of PL/SQL programs. The database objects like Tables, Views, Indexes, Constraints can be converted quite easily by using different tools available on the internet. Here I have listed the top 3 tools which can be used to convert Oracle to MySQL.

The Oracle and MySQL are quite similar since both of them are RDBMS but they have different syntax for many objects. Like for example Oracle uses double quotes " to enclose object names where as MySQL uses back tick ` symbol for object names as shown below

Oracle create table statement

CREATE TABLE "DEPARTMENTS" ("DEPARTMENT_ID" NUMBER(4,0), `DEPARTMENT_NAME` VARCHAR2(30)......

The same statement in MySQL will be

CREATE TABLE `DEPARTMENTS` (`DEPARTMENT_ID` NUMERIC(4,0), `DEPARTMENT_NAME` VARCHAR(30)......

Enclosing object names in enclosing character is optional in both Oracle and MySQL, but, enclosing is must if the object name contains blank spaces

Now getting on to the main topic, here are the list of tools arranged in order according to the convenience and efficiency in transferring data from Oracle to MySQL

1. Oracle to MySQL Converter
2. SQL Developer from Oracle Corporation

Oracle to MySQL Converter

This tool does the job of conversion smoothly. In fact, it's performance and efficiency surpasses it's competitors, both commercial and free. The best thing I found in this tool is that it doesn't require Oracle Client software to be installed separately before using this tool. It also doesn't require ODBC, JDBC or OLE DB Providers. Just install this tool and start using it by directly connecting to Oracle database by mentioning Hostname, Oracle SID and listener port no.

The same thing is also applicable to MySQL. It doesn't require any MySQL OLE DB providers or ODBC drivers or MySQL Client libraries to be installed separately to connect to MySQL database. This product comes with MySQL libraries.

Another good feature which needs to be mentioned is that, it supports Oracle and MySQL BULK Loading - the fastest method provided by these database vendors for loading data.

Using this tool is quite straight forward. Download and install it on a computer from where you can connect to both MySQL and Oracle. Next choose Oracle as Source and MySQL as destination

Next, specify the Oracle database connection settings like Username, Password, hostname, SID and Port No. and then click connect. Once you connected successfully you will get the list of tables in that user account and you can choose the desired tables which you need to convert to MySQL

After selecting the source tables, next you will have to connect to target MySQL database by supplying it's credentials.

If needed, you can even do column mapping between source table columns to target columns if the target table is already existing. You can even define the load options available. They are Append, Delete, Drop, UPSERT, INSERT. The last two options UPSERT and INSERT is used to Synchronize data from Oracle Source table to target MySQL table. i.e. you can ask it just load only missing rows or update matching rows.

Next it will ask you to whether you want to save this session or not. And if you choose yes then, you can define the schedule time to run this session in future. This Oracle to MySQL Converter comes with it's own scheduler.

Finally just click start and then the job is done.

 

Pros: Easy to use. Just install and start conversion, No need to write any code,  Supports Synchronization, Comes with it's own scheduler, Generates SQL Dump files which are compatible with target database.

Cons: Commercial cost money to use all the features

Download the trial version to test it yourself

SQL Developer

From Oracle Version 10g Oracle has started supplying a robust GUI tool known as SQL Developer. This tool provides graphical user interface to do common database tasks such as creating and modifying SQL statements, PL/SQL statements like triggers, procedures and packages.

Apart from this it also supports Database Export, although not directly. It generates SQL statements in dump file which later on you can modify and then run in your target database.

Oracle SQL Developer is also the main tool provided by Oracle for moving 3rd Party databases to Oracle Database. Users can connect to Access, SQL Server, Sybase ASE, DB2, or Teradata and provides wizard like process to move their objects, data, and applications to Oracle.

To Convert database from Oracle to MySQL using SQL Developer, we need to do this in 3 steps

1. Generate SQL file containing DDL and DML statements using SQL Developer
2. Edit the SQL file in any text editor to match the target database syntax
3. Run the modified SQL file in the target database

Let's see how to do the conversion by doing an example migration., In this example we will migrate HR schema in Oracle to MySQL

First, fire up SQL developer and connect to HR user and then choose Tools -> Database Export

Next, you will get the following screen

Then do the following, 1. Choose your Oracle connection , 2. Deselect all the options except shown, 3. Choose Insert option, 4. Choose Single file and select the file name.  After this click Next button. Upon clicking next button SQL Developer will now prompt you to select which types of objects you want to export. Since we want to export to MySQL we will check Tables, Views, Indexes, Constraints and uncheck all other options.

After clicking next, SQL Developer will show the list of tables, views, indexes and constraints. Click >> to select all objects and click next.

SQL Developer also lets you choose which columns you need to migrate and optionally you can also specify Where conditions to filter rows. Since we want to migrate all the columns in this example, just click Next button.

Upon clicking Next button you will come to final step of migration and SQL Developer will show the summary of Database Export session.

Now click Finish button to start the migration

Once finished, it will show the contents of the SQL dump file in the right pane of SQL Developer.

Here's is the file generated

Now we cannot directly run this SQL statements in MySQL. We need to make changes to this SQL statements syntax to make it compatible with MySQL.

1.  The first thing we need to change is data types, Oracle data type Varchar2 should be changed to Varchar, Number to Numeric/Decimal, Date to Datetime and so on. You can see list of equivalent datatypes between Oracle and MySQL
2. We have to replace double quotes (") with back tick character (`)
3. Remove COMMENT ON statements as MySQL doesn't support defining comments separately. You can define comments in the Create Table statements only
4. Enclose comment lines starting --- characters within /* ,  */ symbols
5. Remove Insert statements on Joined View. Because MySQL does support Insert Update through Views but not on Views based on Joined queries
6. Remove Enable keywords after constraints statement as it's not supported in MySQL
7. Similarly remove Organization Index clause
8. We also need to change Alter constraints statement like for example the following statement

   ALTER TABLE "COUNTRIES" MODIFY ("COUNTRY_ID" CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE);

   should be changed to

   ALTER TABLE `DEPARTMENTS` MODIFY `DEPARTMENT_NAME` VARCHAR(30)  NOT NULL;

   i.e. we need to remove constraint name and add column datatype and width also.
   
   Similarly, 

   ALTER TABLE `DEPARTMENTS` ADD CONSTRAINT `DEPT_ID_PK` PRIMARY KEY (`DEPARTMENT_ID`) USING INDEX  ENABLE;

   should be changed to

   ALTER TABLE `DEPARTMENTS` ADD CONSTRAINT `DEPT_ID_PK` PRIMARY KEY (`DEPARTMENT_ID`);

9. Since in this example, there is TO_TIMESTAMP function for inserting date values, we need to convert this to STR_TO_DATE function. i.e.

   to_timestamp('31-DEC-06','DD-MON-RR HH.MI.SSXFF AM')

   Should be changed to 

   STR_TO_DATE('17-FEB-04','%d-%b-%y')

We can use any text editor to make these changes. Here I have used HeidiSQL for editing the generated file.

Here is the modified file which is ready to run in MySQL.

Once the file is modified and made compatible with MySQL, we can execute this by creating a blank database in MySQL.

Here is the output of the target MySQL database after running the above script.

Pros: Free. It comes with Oracle

Cons: No direct way to transfer between Oracle to MySQL. Need manual changes to make it compatible with target databases.

You can download it from here