By: Hassan Shareef,
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
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
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
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
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.
should be changed to
ALTER TABLE "COUNTRIES" MODIFY ("COUNTRY_ID" CONSTRAINT "COUNTRY_ID_NN" NOT NULL ENABLE);
i.e. we need to remove constraint name and add column datatype and width also.
ALTER TABLE `DEPARTMENTS` MODIFY `DEPARTMENT_NAME` VARCHAR(30) NOT NULL;
should be changed to
ALTER TABLE `DEPARTMENTS` ADD CONSTRAINT `DEPT_ID_PK` PRIMARY KEY (`DEPARTMENT_ID`) USING INDEX ENABLE;
ALTER TABLE `DEPARTMENTS` ADD CONSTRAINT `DEPT_ID_PK` PRIMARY KEY (`DEPARTMENT_ID`);
to_timestamp('31-DEC-06','DD-MON-RR HH.MI.SSXFF AM')
Should be changed to
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