Move Oracle to MySQL

 

Should I Move to MySQL?

Oracle to MySQL migration offers several benefits: low-cost of ownership, free database engine, tight integration with web, wide circle of MySQL database professionals and even more. However, if a person is setting up a sophisticated and huge scale corporate database, it’s reasonable to stick with Oracle considering that the DBMS provides ultimate variety of features and tools for this specific purpose.

One can learn about the most vital variations between Oracle and MySQL available in this article. 

Oracle to MySQL Migration

The entire process of converting Oracle database to PostgreSQL includes the following steps: 

• every Oracle table definition is extracted into “CREATE TABLE” statements
• 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

Whether each of these steps are applied manually or commercially, tools are required to automate the whole migration process, the individual in control has to understand all possible bottlenecks and techniques of validating the outcomes. Your concerns and statements has been tested from SQL*Plus, however every other Oracle client application can also be used. This is actually a command line to connect with the database via SQL*Plus:

sqlplus username/password@database

Table Definitions

Oracle table descriptions are taken out in form of DDL statements that has to be transformed into MySQL format then. This is one way to list out all tables in Oracle database:

SQL> select table_name from user_tables;

In order to get definition of Oracle table run the following statement from any command line client:

SQL> set long 1000

SQL> set pagesize 0

SQL> select DBMS_METADATA.GET_DDL(‘TABLE’,'

'[,’SCHEMA’]) from DUAL

The output DDL script must be manually processed 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 (`)
• convert Oracle data types into MySQL equivalents based on the range of accepted values

Data

Data can be migrated from Oracle to MySQL via an intermediate storage like comma separated values (CSV) files. Oracle provides the following commands to export 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;

Then use LOAD DATA statement to load the resulting CSV files into MySQL database from any command line client application:

LOAD DATA LOCAL INFILE ‘a_table.csv’

INTO TABLE a_table

FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘”‘

LINES TERMINATED BY ‘\r\n’;

Indexes and Constraints

List of all indexes that belong to table “mytable” cab be extracted using this query:

SQL> select * from all_indexes where table_name = ‘

’;

And these statements allow to get the definition of particular index:

select VIEW_NAME, TEXT from SYS.USER_VIEWS SQL> select DBMS_METADATA.GET_DDL(‘INDEX’,'’) from DUAL;

Views

Use the following query to get list of all views from Oracle database in form of CREATE VIEW statements:

select VIEW_NAME, TEXT from SYS.USER_VIEWS;

Syntax of statements to develop views in Oracle and MySQL is comparable however, not identical. This is exactly why it is vital to process at the same time statement before passing it to the destination database management system. 

Oracle offers multiple features for creating views that aren’t backed up by MySQL. Such features has to be removed during conversion:

1. • DEFAULT
   • FORCE / NO FORCE
   • WITH CHECK OPTION
   • WITH OBJECT IDENTIFIER
   • WITH READ ONLY
   • UNDER
   • XMLType views are not supported by PostgreSQL

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 illustrated by this table: 

Oracle	MySQL	Meaning
DD	%d	Day (1 – 31)
DY	%a	Abbreviated day (Sun – Sat)
HH or HH12	%h	Hour (1 – 12)
HH24	%H	Hour (0 – 23)
MI	%i	Minutes (0 – 59)
MM	%m	Month (1 – 12)
MON	%b	Abbreviated month (Jan – Dec)
MONTH	%M	Month name (January – December)
RR	%y	2-digit year, 20th century for 00-49
RRRR	%Y	2 or 4-digit year, 20th century for 00-49
SS	%s	Seconds (0 – 59)
YY	%y	2-digit year
YYYY	%Y	4-digit year

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 if $var is not NULL must be replaced by the following statement: 

CASE WHEN $var 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. It can be replace by the following statement: 

CASE $exp WHEN $when THEN $then … END

Oracle has special operator (+) that is specific notation for LEFT OUTER JOIN, it must be converted into classic syntax before passing to MySQL. In Oracle: 

SELECT …

FROM a,b

WHERE a.id=b.id(+)

In MySQL: 

SELECT …

FROM a

LEFT JOIN b ON b.id = a.id

Finally, all Oracle functions missing in MySQL have to be replaced by the appropriate MySQL equivalents: 

Oracle	MySQL
CHR(num)	CHAR(num USING ASCII)
CURRENT_DATE	NOW()
CURRENT_TIMESTAMP	NOW()
EXTRACT(YEAR FROM date)	YEAR(date)
EXTRACT(MONTH FROM date)	MONTH(date)
EXTRACT(DAY FROM date)	DAY(date)
EXTRACT(HOUR FROM time)	HOUR(time)
EXTRACT(MINUTE FROM time)	MINUTE(time)
EXTRACT(SECOND FROM time)	SECOND(time)
LENGTH	CHAR_LENGTH
LISTAGG	GROUP_CONCAT()
NVL(expr, replace)	IFNULL(expr, replace)
SYS_GUID()	REPLACE(UUID(), ‘-‘, ”)
TRUNC	TRUNCATE