How to Migrate from Oracle to MySQL

Why would I migrate from Oracle to MySQL?

There are multiple benefits of moving your database to MySQL. The cost of ownership of a MySQL database is lower than one from Oracle. This is related to the next advantage. MySQL IDE is built on an open-source database engine while Oracle is proprietary and has a very strict license agreement. Additionally, due to its popularity, it is tightly integrated with many web applications and has a huge developer community in case you need any help in case of a technical issue. There are reasons to keep using an Oracle database if you have large, complex database for a corporate organization, but smaller databases will often not need many of the extra features that the database management system provides.

How can I migrate from Oracle to MySQL?

The following steps can be used to migrate databases from Oracle to MySQL:

1. Export the table definitions from your Oracle database in the form of DDL statements
2. These statements need to be edited to make them fit to be able to be imported into a MySQL database. Then they can be loaded into a target database.
3. Similarly, data from the Oracle source database is exported into CSV files.
4. You may have started to notice a similar pattern. You need to convert this data into the correct format when necessary and then import it into your MySQL database.
5. Now you need to export procedures and functions, as well as triggers and views from your Oracle database into plain SQL and source code.
6. These need to be converted into a format that fits in with MySQL and, again, loaded into the database that you want to use them in.

This is how you can convert a database from Oracle to MySQL. This can be done both manually, as well as through automatic database migration software. This may be preferable, especially if the database contains critical information. However, regardless, having a general idea of what the process is will be essential to validating the results at the end.

So, here’s a detailed overview of how the process works.

First you need to connect to the Oracle database using a suitable client application. Then once you’ve extracted the table definitions, here’s what you need to do to convert it into MySQL format.  First extract the definition of each table and make the following changes:

• Remove any Oracle specific keywords
• Replace the Oracle database object’s name separator – quote character (“) with the MySQL alternative (`)
• Convert datatypes to the relevant ones in MySQL

Then, you need to export the Oracle data into a CSV file. Here you probably won’t need to do as much editing as MySQL can load data in the form of CSV files as long as you import it with the correct commands.

Similarly, indexes and constraints are fairly straight forward to import into MySQL. There’s just a single command you need to use to export them from Oracle. Importing them into MySQL is also fairly simple.

This is where the process gets really complicated. The process of exporting views from Oracle in the form of DDL statements is not hard and is just a single command. However, the format for these statements is different in Oracle and MySQL. In many aspects, the structure and syntax are similar, but it is not the same. So, you need to process each of the statements and remove any features that are not present in MySQL and convert any functions that are different. The built in TO_DATE function in Oracle for example needs to be replaced by the similar STR_TO_DATE function in MySQL. You may need to look up conversions for other commands, especially those related to the handling of dates and time.

Some functions have no direct analogues in MySQL. An example is the function nvl($var, $expr) and it’s more complicated counterpart nvl2($var, $expr1, $expr2). This needs to be replaced by a complicated CASE statement in MySQL. Similarly, the DECODE function, used to evaluate a condition has no MySQL equivalent and can only be written as a CASE statement.

There are also special operators that are used to shorten some statements in Oracle, that need to be replaced by the full form statements in MySQL. The (+) operator for example stands for LEFT OUTER JOIN and it must be converted to the full MySQL format. There are many other similar operators that need to be converted from Oracle to MySQL format.

There are also many other Oracle functions that are missing in MySQL that can either be replaced by an alternative function, a statement, a collection of statements or some combination of all of these. Clearly, it can get really complicated and become a real hassle if you are doing it all manually. If you are a great programmer you might be able to write a script to handle this for you, however, double checking and validation will be tough process, and if you make a mistake it might not get spotted till much later when it could turn out to be costly.

This is where an automatic converter could come in handy. This would handle database migration and ease the process of validation by a significant amount, and since so many people are relying on it, any bugs are ironed out very early in the product cycle. So, as long as you pick a reliable company, like Intelligent Conversions, this is sure to be a less hectic and more importantly, less error prone process. However, you certainly can do it on your own as long as you are careful at each step and make sure to double check and validate your work!