How to convert Oracle to MySQL

Nowadays many companies migrate their Oracle databases to MySQL in order to reduce TCO or take other benefits of MySQL. This article explores how to process most database objects when convert Oracle to MySQL.

Table Definition

Oracle expose table definition through DESC table name statement. Based on this information you can compose CREATE TABLE statement for target MySQL database. Although data types are similar for these two DBMS, there are particular differences listed below:

Oracle MySQL
BFILE VARCHAR(255)
BINARY_FLOAT FLOAT
BINARY_DOUBLE DOUBLE
CLOB LONGTEXT
INTERVAL YEAR(p) TO MONTH VARCHAR(30)
INTERVAL DAY(p) TO SECOND(s) VARCHAR(30)
LONG LONGTEXT
LONG RAW LONGBLOB
NCLOB NVARCHAR(max)
NUMBER(p,0), NUMBER(p), 1 <= p < 3

NUMBER(p,0), NUMBER(p), 3 <= p < 5

NUMBER(p,0), NUMBER(p), 5 <= p < 9

NUMBER(p,0), NUMBER(p), 9 <= p < 19

NUMBER(p,0), NUMBER(p), 19 <= p <= 38

TINYINT

SMALLINT

INT

BIGINT

DECIMAL(p)

NUMBER(p,s) DECIMAL(p,s)
RAW(n), 1 ⇐ n ⇐ 255

RAW(n),256 ⇐ n ⇐ 2000

BINARY(n)

VARBINARY(n)

ROWID CHAR(10)
TIMESTAMP(p) DATETIME(p)
XMLTYPE LONGTEXT

Table DUAL

Oracle has special one-row and one-column table DUAL to run some queries that does not require any table, for example:

SELECT 1 FROM DUAL;

MySQL does not have such table, so it can be emulated for possible use in queries as follows:

create table `dual`(dummy varchar(1) not null );

insert into `dual`(dummy) values(‘x’);

Embedded functions

Each of Oracle embedded functions must be converted into MySQL equivalent according to this table:

Oracle MySQL
add_months($datetime, $n) dateadd((month, $n, $datetime)
decode() no direct equivalent, see conversion below
LISTAGG GROUP_CONCAT
nvl($var, $expr) isnull($var, $expr)
nvl2($var,$expr1,$expr2) CASE WHEN $var IS NOT NULL THEN $arg1 ELSE $arg2 END
string1 || string2 string1 + string2
to_char($date,$format) date_format($date,$format)
to_date($string,$format) str_to_date($string,$format)

Also, pay attention to difference between date format specifiers in Oracle and MySQL illustrated by this table:

Oracle MySQL Meaning
DD %d Day (1 – 31)
DY %a Abbreviatedday (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 Abbreviatedmonth (Jan – Dec)
MONTH %M Monthname (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

Decode() function

This is one of Oracle functions that do not have direct equivalent in MS SQL. If must be replaced by CASE expression as follows:

SELECT colorid,

CASE colorid

WHEN ‘1’ THEN ‘red’

WHEN ‘2’ THEN ‘green’

WHEN ‘3’ THEN ‘blue’

ELSE NULL

END AS ‘colorname’

FROM colors

Views

Statements to create views are similar in Oracle and MySQL but not identical. That’s why it is necessary to process each of those statements before loading it to MySQL. Basically, there are three steps of processing:

  1. Oracle provides multiple features for creating views that are not supported by MySQL. Such features must be removed during conversion:
    • DEFAULT
    • FORCE / NO FORCE
    • WITH CHECK OPTION
    • WITH OBJECT IDENTIFIER
    • WITH READ ONLY
    • UNDER
    • XMLType views are not supported by MySQL
  2. Replace all Oracle specific functions by MySQL equivalents according the table above
  3. Oracle has special operator (+) that is specific notation for LEFT OUTER JOIN. So, must be converted into MySQL format 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

Sequences

Unlike Oracle, MySQL does not support for sequences. Instead it provides special property for numeric data types (tinyint, smallint, int, bigint, decimal, numeric) called “auto_increment”:

CREATE TABLE mytab

( id intauto_increment, another_columnvarchar(100) )

Leave a Reply

Your email address will not be published. Required fields are marked *