Oracle to MySQL Migration

SQLines provides open source tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to MySQL.

We also help convert embedded SQL statements in C/C++ (ODBC, Pro*C, OCI, OCCI), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications.

  • Oracle 12c, 11g, 10g and 9i
  • MySQL 5.x

Oracle to MySQL Migration Tools

SQLines SQL Converter Tool

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Oracle to MySQL.

SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Oracle database use SQLines Data tool.

Try SQLines Online or download a free Desktop Version.

Oracle to MySQL Migration Reference

Most Complex Migration Issues

Identifiers

Converting identifiers:

Oracle MySQL
First character Letter Letter or digit
Subsequent characters Letter, digit, _ , # and $ Letter, digit, _ and $
Identifier quote character ” (double quotes) ` (backtick) ” if ANSI_QUOTES is set (not set by default)

Max length:

Oracle MySQL
Tables, columns, indexes, views, procedures, triggers 30 64
Databases 8 64

SQL Language Elements

Converting SQL language elements:

Oracle MySQL
1 || Operator String concatenation CONCAT function
2 ORDER BY NULLS FIRST | LAST NULLs in ORDER BY ORDER BY Different default order
3 cursor%FOUND Cursor fetched NOT_FOUND = 0 HANDLER FOR NOT FOUND
4 cursor%NOTFOUND No row found NOT_FOUND = 1

Data Types

Converting data types:

Oracle MySQL
1 BFILE Pointer to binary file, ⇐ 4G VARCHAR(255)
2 BINARY_FLOAT 32-bit floating-point number FLOAT
3 BINARY_DOUBLE 64-bit floating-point number DOUBLE
4 BLOB Binary large object, ⇐ 4G LONGBLOB
5 CHAR(n), CHARACTER(n) Fixed-length string, 1 ⇐ n ⇐ 255 CHAR(n), CHARACTER(n)
6 CHAR(n), CHARACTER(n) Fixed-length string, 256 ⇐ n ⇐ 2000 VARCHAR(n)
7 CLOB Character large object, ⇐ 4G LONGTEXT
8 DATE Date and time DATETIME
9 DECIMAL(p,s), DEC(p,s) Fixed-point number DECIMAL(p,s), DEC(p,s)
10 DOUBLE PRECISION Floating-point number DOUBLE PRECISION
11 FLOAT(p) Floating-point number DOUBLE
12 INTEGER, INT 38 digits integer INT DECIMAL(38)
13 INTERVAL YEAR(p) TO MONTH Date interval VARCHAR(30)
14 INTERVAL DAY(p) TO SECOND(s) Day and time interval VARCHAR(30)
15 LONG Character data, ⇐ 2G LONGTEXT
16 LONG RAW Binary data, ⇐ 2G LONGBLOB
17 NCHAR(n) Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255 NCHAR(n)
18 NCHAR(n) Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000 NVARCHAR(n)
19 NCHAR VARYING(n) Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 NCHAR VARYING(n)
20 NCLOB Variable-length Unicode string, ⇐ 4G NVARCHAR(max)
21 NUMBER(p,0), NUMBER(p) 8-bit integer, 1 <= p < 3 TINYINT (0 to 255)
16-bit integer, 3 <= p < 5 SMALLINT
32-bit integer, 5 <= p < 9 INT
64-bit integer, 9 <= p < 19 BIGINT
Fixed-point number, 19 <= p <= 38 DECIMAL(p)
22 NUMBER(p,s) Fixed-point number, s > 0 DECIMAL(p,s)
23 NUMBER, NUMBER(*) Floating-point number DOUBLE
24 NUMERIC(p,s) Fixed-point number NUMERIC(p,s)
25 NVARCHAR2(n) Variable-length UTF-8 string, 1 ⇐ n ⇐ 4000 NVARCHAR(n)
26 RAW(n) Variable-length binary string, 1 ⇐ n ⇐ 255 BINARY(n)
27 RAW(n) Variable-length binary string, 256 ⇐ n ⇐ 2000 VARBINARY(n)
28 REAL Floating-point number DOUBLE
29 ROWID Physical row address CHAR(10)
30 SMALLINT 38 digits integer DECIMAL(38)
31 TIMESTAMP(p) Date and time with fraction DATETIME(p)
32 TIMESTAMP(p) WITH TIME ZONE Date and time with fraction and time zone DATETIME(p)
33 UROWID(n) Logical row addresses, 1 ⇐ n ⇐ 4000 VARCHAR(n)
34 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
35 VARCHAR2(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
36 XMLTYPE XML data LONGTEXT

Data type attributes and options:

Oracle MySQL
BYTE and CHAR column size semantics Size is always in characters

Built-in SQL Functions

Converting built-in SQL functions from Oracle to MySQL:

Oracle MySQL
1 ABS(num) Get the absolute value ABS(num)
2 ACOS(num) Get the arc cosine ACOS(num)
3 ADD_MONTHS(date, num) Add num months to date TIMESTAMPADD(MONTH, num, date)
4 ASCII(str) Get ASCII code of left-most char ASCII(str)
5 ASCIISTR(string) Get ASCII code version of string
6 ASIN(num) Get the arcsine ASIN(num)
7 ATAN(num) Get the arc tangent ATAN(num)
8 ATAN2(x, y) Get the arc tangent of x and y ATAN2(x, y)
9 BIN_TO_NUM(bit1, bit2, …) Convert bit vector to number
10 BITAND(exp1, exp2) Perform bitwise AND (exp1 & exp2)
11 CEIL(num) Get the smallest following integer CEIL(num)
12 CHR(num) Get character from ASCII code CHAR(num USING ASCII)
13 COALESCE(exp1, exp2, …) Return first non-NULL expression COALESCE(exp1, exp2, …)
14 CONCAT(char1, char2) String concatenation CONCAT(char1, char2)
15 CONVERT(string, charset) Convert string to charset CONVERT(string USING charset)
16 COS(num) Get the cosine COS(num)
17 COSH(num) Get hyperbolic cosine (EXP(num) + EXP(-num)) / 2
18 CURRENT_DATE Get the current date NOW()
19 CURRENT_TIMESTAMP Get the current date and time NOW()
20 DECODE(exp, when, then, …) Evaluate conditions CASE expression
21 EXP(n) Raise e to the nth power EXP(n)
22 EXTRACT(YEAR FROM date) Extract year from date YEAR(date)
23 EXTRACT(MONTH FROM date) Extract month from date MONTH(date)
24 EXTRACT(DAY FROM date) Extract day from date DAY(date)
25 EXTRACT(HOUR FROM time) Extract hour from time HOUR(time)
26 EXTRACT(MINUTE FROM time) Extract minute from time MINUTE(time)
27 EXTRACT(SECOND FROM time) Extract second from time SECOND(time)
28 FLOOR(num) Get the largest preceding integer FLOOR(num)
29 GREATEST(exp, exp2, …) Get the maximum value in a set GREATEST(exp, exp2, …)
30 INITCAP(string) Capitalize words User-defined function
31 INSTR(str, substr) Get position of substring INSTR(str, substr)
INSTR(str, substr, pos) LOCATE(str, substr, pos)
INSTR(str, substr, pos, num) User-defined function
32 LAST_DAY(date) Get last day of the month LAST_DAY(date)
33 LEAST(exp, exp2, …) Get the minimum value in a set LEAST(exp, exp2, …)
34 LENGTH(string) Get length of string in chars CHAR_LENGTH(string)
35 LENGTHB(string) Get length of string in bytes LENGTH(string)
36 LN(num) Get natural logarithm of num LN(num)
37 LOCALTIMESTAMP Get the current date and time LOCALTIMESTAMP
LOCALTIMESTAMP([prec]) LOCALTIMESTAMP()
38 LOG(num1, num2) Get logarithm, base num1, of num2 LOG(num1, num2)
39 LOWER(string) Lowercase string LOWER(string)
40 LPAD(string, len) Pad the left-side of string LPAD(string, len, ' ')
LPAD(string, len, pad) LPAD(string, len, pad)
41 LTRIM(string) Remove leading spaces LTRIM(string)
LTRIM(string, set) Remove leading chars TRIM(LEADING set FROM string)
42 MONTHS_BETWEEN(date1, date2) Get number of months between
date1 and date2
User-defined function
43 MOD(dividend, divisor) Get remainder MOD(dividend, divisor)
44 NEXT_DAY Get the next date by day name NEXT_DAY user-defined function
45 NULLIF(exp1, exp2) Return NULL if exp1 = exp2 NULLIF(exp1, exp2)
46 NVL(exp, replacement) Replace NULL with the specified value IFNULL(exp, replacement)
47 NVL2(exp1, exp2, exp3) Return exp2 if exp1 is not NULL,
otherwise exp3
CASE expression
48 POWER(value, n) Raise value to the nth power POWER(value, n)
49 REMAINDER(n1, n2) Get remainder (n1 - n2*ROUND(n1/n2))
50 REPLACE(str, search) Remove search-string REPLACE(str, search, '')
REPLACE(str, search, replace) Replace search-string REPLACE(str, search, replace)
51 ROUND(num, integer) Get rounded value ROUND(num, integer)
52 RPAD(string, len) Pad the right-side of string RPAD(string, len, ' ')
RPAD(string, len, pad) RPAD(string, len, pad)
53 RTRIM(string) Remove trailing spaces RTRIM(string)
RTRIM(string, set) Remove trailing chars TRIM(TRAILING set FROM string)
54 SIGN(exp) Get sign of exp SIGN(exp)
55 SIN(num) Get sine SIN(num)
56 SINH(num) Get hyperbolic sine (EXP(num) - EXP(-num)) / 2
57 SOUNDEX(string) Get 4-character sound code SOUNDEX(string)
58 SQRT(num) Get square root SQRT(num)
59 SUBSTR(string, pos, len) Get a substring of string SUBSTR(string, pos, len)
60 SYS_GUID() Get GUID, 32 characters without dashes REPLACE(UUID(), '-', '')
61 SYSDATE Get current date and time SYSDATE()
62 SYSTIMESTAMP Get current timestamp CURRENT_TIMESTAMP
63 TAN(num) Get tangent TAN(num)
64 TANH(num) Get hyperbolic tangent (EXP(2*num) - 1)/(EXP(2*num) + 1)
65 TO_CHAR(datetime, format) Convert datetime to string DATE_FORMAT(datetime, format)
TO_CHAR(number, format) Convert number to string FORMAT(number, decimal_digits)
66 TO_DATE(string, format) Convert string to datetime STR_TO_DATE(string, format)
67 TO_LOB(exp) Convert to LOB
68 TO_NCHAR(exp) Convert to NCHAR
69 TO_NCLOB(exp) Convert to NCLOB
70 TO_NUMBER(exp) Convert to NUMBER
71 TO_SINGLE_BYTE(exp) Convert to single-byte character
72 TO_TIMESTAMP(exp) Convert to TIMESTAMP
73 TRANSLATE(string, from, to) Replace characters User-defined function
74 TRIM([type trim FROM] string) Remove characters TRIM([type trim FROM] string)
75 TRUNC(num) Truncate num TRUNCATE(num, 0)
TRUNC(num, num2) TRUNCATE(num, num2)
76 TRUNC(datetime) Truncate datetime DATE(datetime), DATE_FORMAT
77 UNISTR(string) Convert Unicode code points to chars CHAR(string USING UCS2)
78 UPPER(string) Uppercase string UPPER(string)
79 USER Get the current user USER()
80 USERENV('parameter') Get the current session information
81 VSIZE(exp) Get the size of exp in bytes
82 XMLAGG(exp) Get a aggregated XML document
83 XMLCAST(exp AS datatype) Convert exp to datatype
84 XMLCDATA(exp) Generate a CDATA section
85 XMLCOMMENT(exp) Generate an XML comment
86 XMLCONCAT(exp, exp2, …) Concatenate XML expressions
87 XMLDIFF(doc, doc2) Compare two XML documents
88 XMLELEMENT(NAME element) Get an XQuery element node
89 XMLFOREST(exp, exp2, …) Get a forest of XML expressions
90 XMLISVALID(exp) Check XML exp
91 XMLPARSE(DOCUMENT exp) Parse XML document
92 XMLPATCH(doc, doc2) Patch XML document
93 XMLPI(NAME identifier) Get XML processing instruction
94 XMLROOT(exp, VERSION exp2) Create a new XML value
95 XMLSEQUENCE(exp) Get a varray of the top-level nodes
96 XMLSERIALIZE(CONTENT exp
AS datatype)
Get a serialized XML value
97 XMLTRANSFORM(instance, exp) Transform XML document

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Oracle MySQL
1 DEFAULT any expression DEFAULT only constant can be specified
2 CHECK constraint Parsed but ignored by MySQL, a trigger can be used
3 ENABLE constraint attribute Removed

Storage and physical attributes:

Oracle MySQL
1 PCTFREE num Removed
2 PCTUSED num Removed
3 INITRANS num Removed
4 MAXTRANS num Removed
5 COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS Removed
6 LOGGING | NOLOGGING Removed
7 SEGMENT CREATION IMMEDIATE | DEFERRED Removed
8 TABLESPACE tablespace_name Removed
9 LOB (column) STORE AS BASIC FILE (params) Removed
10 PARALLEL num Removed

STORAGE clause:

Oracle MySQL
1 INITIAL num Removed
2 NEXT num Removed
3 MINEXTENTS num Removed
4 MAXEXTENTS num Removed
5 PCTINCREASE num Removed
6 FREELISTS num Removed
7 FREELIST GROUPS num Removed
8 BUFFER_POOL DEFAULT | KEEP | RECYCLE Removed
9 FLASH_CACHE DEFAULT | KEEP | NONE Removed
10 CELL_FLASH_CACHE DEFAULT | KEEP | NONE Removed

LOB storage clause:

Oracle MySQL
1 TABLESPACE name Removed
2 DISABLE | ENABLE STORAGE IN ROW Removed
3 CHUNK num Removed
4 NOCACHE Removed
5 LOGGING Removed

CREATE VIEW Statement

Converting views from Oracle to MySQL:

Oracle MySQL
1 CREATE OR REPLACE VIEW name CREATE OR REPLACE VIEW name
2 FORCE keyword Create view even if tables not exist Removed
3 WITH READ ONLY Removed

Sequences

Oracle DROP SEQUENCE, CREATE SEQUENCE and NEXTVAL emulation in MySQL using stored procedures and user-defined functions.

Oracle MySQL
1 CREATE SEQUENCE name START WITH start INCREMENT BY inc CreateSequence('name', start, inc)
2 DROP SEQUENCE name DropSequence('name')
3 name.NEXTVAL NextVal('name')

SELECT Statement

Converting SQL SELECT statement and its clauses:

Oracle MySQL
1 ROWNUM = 1 Return 1 row only LIMIT 1
2 ROWNUM <= n Row limit LIMIT n
ROWNUM < n LIMIT n - 1
3 FOR UPDATE Lock selected rows FOR UPDATE
4 SKIP LOCKED Skip locked rows Not supported by MySQL, commented

CREATE PROCEDURE Statement

Converting stored procedures from Oracle to MySQL:

Oracle MySQL
1 CREATE OR REPLACE PROCEDURE DROP PROCEDURE IF EXISTS and CREATE PROCEDURE
2 param IN | OUT | IN OUT datatype Parameter definition IN | OUT | INOUT param datatype(length)
3 IS | AS Removed
4 Variable declaration is before BEGIN Variable declaration is after BEGIN
5 END sp_name END

For more information, see Conversion of PL/SQL Statements.

CREATE FUNCTION Statement

Converting user-defined functions from Oracle to MySQL:

Oracle MySQL
1 CREATE OR REPLACE FUNCTION DROP FUNCTION IF EXISTS and CREATE FUNCTION
2 param IN | OUT | IN OUT datatype Parameter definition param datatype(length)
3 RETURN datatype Return value RETURNS datatype(length)
4 IS | AS Removed
5 Variable declaration is before BEGIN Variable declaration is after BEGIN
6 END func_name END

For more information, see Conversion of PL/SQL Statements.

PL/SQL Statements

Converting PL/SQL statements and clauses from Oracle to MySQL:

Oracle MySQL
1 variable datatype := value Variable declaration DECLARE variable datatype DEFAULT value
2 variable := value Assignment statement SET variable = value
3 CURSOR cur (params) IS SELECT Cursor declaration DECLARE cur CURSOR FOR SELECT
4 Variable and cursor declarations can be mixed in any order Variable declarations must be before cursor and handlers
5 FOR rec IN cursor LOOP Cursor loop OPEN cursor WHILE-FETCH-CLOSE
6 IF THEN ELSIF ELSE END IF IF statement IF THEN ELSEIF ELSE END IF
7 WHILE condition LOOP sql END LOOP A loop statement WHILE condition DO sql END WHILE
8 EXIT WHEN condition Exit from a loop IF condition THEN LEAVE label END IF

EXCEPTION block:

Oracle MySQL
1 BEGIN stmts EXCEPTION … END Exception block structure BEGIN DECLARE HANDLER … stmts END
2 WHEN DUP_VAL_ON_INDEX Duplicate key DECLARE EXIT HANDLER FOR SQLSTATE '23000'
3 WHEN NO_DATA_FOUND No rows found DECLARE EXIT HANDLER FOR NOT FOUND
4 WHEN OTHERS All exceptions DECLARE EXIT HANDLER FOR SQLEXCEPTION

SQL Statements

Converting SQL statements and clauses from Oracle to MySQL:

Oracle MySQL
1 COMMENT ON COLUMN table.column IS 'string' Moved to CREATE TABLE as COMMENT 'string' clause
2 COMMENT ON TABLE table IS 'string' ALTER TABLE table COMMENT 'string'
3 CREATE SEQUENCE CreateSequence stored procedure
4 DROP SEQUENCE DropSequence stored procedure
5 LOCK TABLE name IN EXCLUSIVE MODE LOCK TABLES name WRITE
LOCK TABLE name IN SHARE MODE LOCK TABLES name READ

Built-in PL/SQL Packages

Converting built-in PL/SQL packages from Oracle to MySQL:

Oracle MySQL
1 DBMS_OUTPUT.ENABLE(buffer) Enable calls to PUT/GET Removed

SQL*Plus Commands

Converting Oracle SQL*Plus commands:

Oracle MySQL
1 REM | REMARK text Single line comment -- text
2 SET DEFINE ON | OFF Variable substitution Commented

Data Transfer

Migration Validation