Oracle and MySQL are outstanding Relational Database Management System; the duo have powerful sets of administrative tools and Programming APIs. However, these two popular RDBMS differ in licensing structure and cost of ownership. While MySQL is open source and licensing structure less restrictive; the cost of ownership of Oracle is high and licensing structure very restrictive. For these reasons, migrating database from Oracle to MySQL is often considered by businesses and corporate organization.
When planning database migration, the following steps are usually involved:
- Extract Oracle table structures in form of SQL data definition language (DDL) statements
- Convert these data definition statements into the target format and import to MySQL database
- Export data from Oracle database into comma separated values (.csv) file
- Translate the data into MySQL format (string representation of dates, binary data, etc) and import it into the target database
- Export Oracle views, stored procedures and triggers as SQL statements and source code
- Convert all of these materials into MySQL format and load into the database
When manually done, the process of migrating database from Oracle to MySQL can be quite tasking and prone to data loss or corruption due to human error. It is advisable to use specialized software to automate the entire process.
Oracle to MySQL developed by Intelligent Converters is foremost among the conversion tools that can be used to accomplish database migration. Intelligent Converters is a software development firm specialized in developing applications for database conversion, migration and synchronization since 2001. Oracle to MySQL converter is of exceptional high performance because it is designed to read and write data without using any middleware libraries. Moreover, it supports all versions of MySQL and Oracle including those hosted on Cloud servers. An outstanding feature of Oracle to MySQL is that it goes beyond database conversions to facilitating synchronization of an Oracle database to an existing MySQL database. The software also features a command line utility that can be used to automate and schedule database conversion.
Another important feature is the capability to filter data by using the SELECT query. This option gives the power to streamline selections by columns or records and also facilitate transformation of data before conversion to MySQL format. Enumerated below are ways in which this feature can be applied in different scenarios.
- Filtering of records: SELECT * FROM SampleTable WHERE KeyColumn > 1000
- Select and rename each column: SELECT column1 AS Name, column2 as Phone FROM SampleTable
- Skip empty or NULL values: SELECT * FROM SampleTable WHERE Phone IS NOT NULL
There are times when changing of the column type in the resulting database is required. In order to accomplish this, Oracle to MySQL provides a feature called “custom column mapping”. The feature that allows changing name, type, default values and NULL attributes for any column within the table and also exclude predefined columns from conversion.
If the security settings of the remote server prohibit remote connection, the program can alternatively export all required data as an SQL script. In this method, the source database is exported into a local file containing SQL statements which will be used to create tables with all indexes as well as attributes and corresponding data. Consequently, the database administrator can then import the SQL script file into the MySQL server though any of the under-listed tools.
- MySQL console client (psql)
mysql -h host -u user –p –vvf database_name < script_file
(In using this process, do note that phpMy Admin only allow dump action on existing database only. So the dump sql file should not contain ‘CREATE DATABASE’ statement.)
- Click on the target database name in the left frame.
- Click ‘SQL’ tab on the top of the screen.
- Enter full path to the dump sql file in ‘Location of the textfile’ field or click ‘Browse’ button to locate it through the file explorer.
- Click ‘Go’ button.
Oracle to MySQL converter combines easy-to-use interface with sufficient capabilities to handle complicated database migration projects. The product is supplied with 24/7 unlimited support and subscription for updates within one year after the order has been placed.
For more details about Intelligent Converters and the product, please visit Intelligent Converters website.