Migrating Oracle database to MySQL offers the following advantages: low cost of ownership, tight integration with web, wide community of MySQL professionals with affordable rates and others. However, for IT projects and infrastructures that require a sophisticated and huge scale database, it’s reasonable to stick with Oracle considering that the DBMS provides ultimate variety of features and tools for this specific purpose.
This article analyzes the important differences between Oracle and MySQL and conversion rules to switch from one DBMS to another.
Oracle to MySQL Migration
The entire process of database migration from Oracle to MySQL includes the following steps:
• Definitions of Oracle tables are extracted in form of SQL DML statements
• Those SQL-statements are created ideal for MySQL format and loaded into the target server
• The content of every Oracle table is stored into CSV files
• CSV files are translated in line with the target format (when it is necessary) and imported into MySQL database
• Oracle views, triggers, stored procedures and operations are exported into SQL statements and plain text source code
• These statements and code are transformed based on MySQL syntax and loaded to the target database
Although many database professionals suggest to use special Oracle to MySQL converter instead of implementing those steps manually, the person responsible for migration has to understand all possible bottlenecks and techniques of validating the outcomes.
Table Definitions
According to the database migration approach specified above, Oracle table descriptions are extracted in form of DDL statements. The following query is a method to list out all Oracle tables:
SQL> select table_name from user_tables;
Use the following statements to get definition of the particular Oracle table:
SQL> set long 1000
SQL> set pagesize 0x
SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'<TABLE NAME>'[,’SCHEMA’]) from DUAL
The resulting DDL script must be manually transformed via steps below before loading to MySQL:
- remove all keywords that are specific for Oracle and have no MySQL equivalents located at the end of CREATE TABLE statements (starting from “USING INDEX PCTFREE…”)
- replace all double quotes around Oracle database object names (“) by MySQL equivalent (`)
- all data types must be converted into MySQL equivalents based on the range of accepted values
Data
Oracle data can be migrated through intermediate storage like comma separated values (CSV) files. Oracle offers the following queries to extract data into CSV format:
SQL> set heading off
SQL> spool filename.csv
SQL> select column1 || ‘,’ || column2 || … from mytable;
SQL> set colsep ‘,’
SQL> select * from my_table;
SQL> spool off;
MySQL offers LOAD DATA statement to import the resulting CSV files into the target database:
LOAD DATA LOCAL INFILE ‘a_table.csv’
INTO TABLE a_table
FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\r\n’;
Indexes and Constraints
In Oracle all indexes that belong to table “mytable” cab be listed via this query:
SQL> select * from all_indexes where table_name = ‘<TABLE NAME>’;
Having index name, it is possible to get the definition of the particular index through this statement:
SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'<INDEX NAME>’) from DUAL;
Views
All views can be extracted in form of CREATE VIEW statements from the source Oracle database using this statement:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
Syntax of views creation statements is similar yet not identical in these database management systems, so post-processing is required for most views. For instance, Oracle to MySQL converter must remove the following keywords that are not supported by the destination DBMS:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
- XMLType (this kind of views is not supported by MySQL)
After removing specific keywords, Oracle to MySQL converter must replace built-in functions and operators missing in the target DBMS by the appropriate equivalents as follows.
Built-in function TO_DATE must be replaced by STR_TO_DATE and TO_CHAR – by DATE_FORMAT MySQL function with respect with difference in date format specifiers in Oracle and MySQL.
Oracle function ADD_MONTHS(datetime, n) that adds n months to datetime must be replaced by DATEADD(month, n, datetime) in MySQL
Oracle function nvl(var, expr) that returns expr if var is NULL must be replaced by ISNULL(var, expr) in MySQL, while its more complicated version nvl2(var,expr1,expr2) that returns arg2 if var is NULL and arg1 otherwise must be replaced by the following statement:
CASE WHEN $ar IS NOT NULL THEN arg1 ELSE arg2 END
Oracle function DECODE(exp, when, then, …) used to evaluate a condition has no direct equivalent in MySQL and therefore must be replaced by the following expression:
CASE exp WHEN when THEN then … END
Oracle provides operator (+) that is short form of LEFT OUTER JOIN. Since MySQL does not support this feature, it must be converted into classic syntax as follows. In Oracle:
SELECT …
FROM a,b
WHERE a.id=b.id(+)
In MySQL:
SELECT …
FROM a
LEFT JOIN b ON b.id = a.id
For those who is looking for Oracle to MySQL converter automating most part of the database migration, it is suggested to consider the related product offered by Intelligent Converters software company. Their tool supports command line for automation purpose and connects to the source and destination databases without using ODBC or any other middleware to guaranty best performance.
