Oracle to MySQL Migration

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

Databases:

  • Oracle 21c, 19c, 18c, 12c, 11g, 10g and 9i
  • MySQL 8.x, 6.x, 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.

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 128 (was 30 before Oracle 12c) 64
Databases 8 64

Object names:

Oracle MySQL
Index CREATE INDEX schema.index_name CREATE INDEX index_name Can contain index name only

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

Character data types:

Oracle MySQL
1 CHAR(n) Fixed-length string, 1 <= n <= 2000 CHAR(n), VARCHAR(n) n <= 255 for CHAR

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
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 ⇐ 2000 VARBINARY(n)
27 REAL Floating-point number DOUBLE
28 ROWID Physical row address CHAR(10)
29 SMALLINT 38 digits integer DECIMAL(38)
30 TIMESTAMP(p) Date and time with fraction DATETIME(p)
31 TIMESTAMP(p) WITH TIME ZONE Date and time with fraction and time zone DATETIME(p)
32 UROWID(n) Logical row addresses, 1 ⇐ n ⇐ 4000 VARCHAR(n)
33 VARCHAR(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
34 VARCHAR2(n) Variable-length string, 1 ⇐ n ⇐ 4000 VARCHAR(n)
35 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 string functions:

Oracle MySQL
1 CHR(num) Get character from ASCII code CHAR(num USING ASCII)
2 INITCAP(str) Capitalize words in string User-defined function
3 INSTR(str, substr) Get position of substring INSTR(str, substr)
INSTR(str, substr, pos) LOCATE(substr, str, pos) param order
INSTR(str, substr, pos, num) User-defined function
4 LENGTH(string) Get length of string in chars CHAR_LENGTH(string)
5 LPAD(string, len) Pad the left-side of string LPAD(string, len, ' ')
LPAD(string, len, pad) LPAD(string, len, pad)
6 LTRIM(string) Remove leading spaces LTRIM(string)
LTRIM(string, set) Remove leading chars TRIM(LEADING set FROM string)
7 MONTHS_BETWEEN(dt1, dt2) Get difference in months User-defined function
8 REPLACE(str, search) Remove search-string REPLACE(str, search, '')
REPLACE(str, search, replace) Replace search-string REPLACE(str, search, replace)
9 RPAD(string, len) Pad the right-side of string RPAD(string, len, ' ')
RPAD(string, len, pad) RPAD(string, len, pad)
10 RTRIM(string) Remove trailing spaces RTRIM(string)
RTRIM(string, set) Remove trailing chars TRIM(TRAILING set FROM string)
11 SUBSTR(string, pos, len) Get a substring of string SUBSTR(string, pos, len)
12 TRANSLATE(string, from, to) Replace characters Nested REPLACE or User-defined function

Converting date and time functions:

Oracle MySQL
1 LAST_DAY(datetime) Get last day of the month,
includes time
LAST_DAY(datetime) Returns date only

Converting NULL processing functions:

Oracle MySQL
1 NVL(exp, replacement) Replace NULL with the specified value IFNULL(exp, replacement)

Converting math functions:

Oracle MySQL
1 POWER(value, n) Raise value to the nth power POWER(value, n)

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)
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
32 LEAST(exp, exp2, …) Get the minimum value in a set LEAST(exp, exp2, …)
34 LENGTHB(string) Get length of string in bytes LENGTH(string)
35 LN(num) Get natural logarithm of num LN(num)
36 LOCALTIMESTAMP Get the current date and time LOCALTIMESTAMP
LOCALTIMESTAMP([prec]) LOCALTIMESTAMP()
37 LOG(num1, num2) Get logarithm, base num1, of num2 LOG(num1, num2)
38 LOWER(string) Lowercase string LOWER(string)
42 MOD(dividend, divisor) Get remainder MOD(dividend, divisor)
43 NEXT_DAY Get the next date by day name NEXT_DAY user-defined function
44 NULLIF(exp1, exp2) Return NULL if exp1 = exp2 NULLIF(exp1, exp2)
46 NVL2(exp1, exp2, exp3) Return exp2 if exp1 is not NULL,
otherwise exp3
CASE expression
48 REMAINDER(n1, n2) Get remainder (n1 - n2*ROUND(n1/n2))
49 ROUND(num, integer) Get rounded value ROUND(num, integer)
52 SIGN(exp) Get sign of exp SIGN(exp)
53 SIN(num) Get sine SIN(num)
54 SINH(num) Get hyperbolic sine (EXP(num) - EXP(-num)) / 2
55 SOUNDEX(string) Get 4-character sound code SOUNDEX(string)
56 SQRT(num) Get square root SQRT(num)
57 SYS_GUID() Get GUID, 32 characters without dashes REPLACE(UUID(), '-', '')
58 SYSDATE Get current date and time SYSDATE()
59 SYSTIMESTAMP Get current timestamp CURRENT_TIMESTAMP
60 TAN(num) Get tangent TAN(num)
61 TANH(num) Get hyperbolic tangent (EXP(2*num) - 1)/(EXP(2*num) + 1)
62 TO_CHAR(datetime, format) Convert datetime to string DATE_FORMAT(datetime, format)
TO_CHAR(number, format) Convert number to string FORMAT(number, decimal_digits)
63 TO_DATE(string, format) Convert string to datetime STR_TO_DATE(string, format)
64 TO_LOB(exp) Convert to LOB
65 TO_NCHAR(exp) Convert to NCHAR
66 TO_NCLOB(exp) Convert to NCLOB
67 TO_NUMBER(exp) Convert to NUMBER
68 TO_SINGLE_BYTE(exp) Convert to single-byte character
69 TO_TIMESTAMP(exp) Convert to TIMESTAMP
71 TRIM([type trim FROM] string) Remove characters TRIM([type trim FROM] string)
72 TRUNC(num) Truncate num TRUNCATE(num, 0)
TRUNC(num, num2) TRUNCATE(num, num2)
73 TRUNC(datetime) Truncate datetime DATE(datetime), DATE_FORMAT
74 UNISTR(string) Convert Unicode code points to chars CHAR(string USING UCS2)
75 UPPER(string) Uppercase string UPPER(string)
76 USER Get the current user USER()
77 USERENV('parameter') Get the current session information
78 VSIZE(exp) Get the size of exp in bytes
79 XMLAGG(exp) Get a aggregated XML document
80 XMLCAST(exp AS datatype) Convert exp to datatype
81 XMLCDATA(exp) Generate a CDATA section
82 XMLCOMMENT(exp) Generate an XML comment
83 XMLCONCAT(exp, exp2, …) Concatenate XML expressions
84 XMLDIFF(doc, doc2) Compare two XML documents
85 XMLELEMENT(NAME element) Get an XQuery element node
86 XMLFOREST(exp, exp2, …) Get a forest of XML expressions
87 XMLISVALID(exp) Check XML exp
88 XMLPARSE(DOCUMENT exp) Parse XML document
89 XMLPATCH(doc, doc2) Patch XML document
90 XMLPI(NAME identifier) Get XML processing instruction
91 XMLROOT(exp, VERSION exp2) Create a new XML value
92 XMLSEQUENCE(exp) Get a varray of the top-level nodes
93 XMLSERIALIZE(CONTENT exp
AS datatype)
Get a serialized XML value
94 XMLTRANSFORM(instance, exp) Transform XML document

CREATE TABLE Statement

Converting CREATE TABLE statement keywords and clauses:

Oracle MySQL
1 GENERATED AS IDENTITY Identity column AUTO_INCREMENT
2 CONSTRAINT name NOT NULL Named NOT NULL constraint NOT NULL
3 ENABLE Constraint enabled 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 | PARALLEL (DEGREE n INSTANCES k) Removed
11 INMEMORY | NO INMEMORY 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 INDEX Statement

Converting CREATE INDEX statement keywords and clauses:

Oracle MySQL
1 CREATE INDEX schema.index_name CREATE INDEX index_name Can contain index name only

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 t1 JOIN t2 USING (column_name) USING clause for joins t1 JOIN t2 USING (column_name)
2 ROWNUM = 1 Return 1 row only LIMIT 1
3 ROWNUM <= n Row limit LIMIT n
ROWNUM < n LIMIT n - 1
4 FOR UPDATE Lock selected rows FOR UPDATE
5 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.

CREATE TRIGGER Statement

Converting triggers from Oracle to MySQL:

Oracle MySQL
1 CREATE OR REPLACE TRIGGER DROP TRIGGER IF EXISTS and CREATE TRIGGER
2 INSERT OR UPDATE OR DELETE Multiple events in one trigger Only one event in trigger
3 REFERENCING OLD AS old_name NEW AS new_name Clause is not supported, OLD and NEW used
4 :NEW.colN, :OLD.colN Referencing column values NEW.colN, OLD.colN

For more information, see a trigger conversion example and 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
5 RAISE Re-raise the current exception RESIGNAL

SQL Statements

Converting SQL statements and clauses from Oracle to MySQL:

Oracle MySQL
1 ALTER TRIGGER name ENABLE Commented
2 COMMENT ON COLUMN table.column IS 'string' Moved to CREATE TABLE as COMMENT 'string' clause
3 COMMENT ON TABLE table IS 'string' ALTER TABLE table COMMENT 'string'
4 CREATE SEQUENCE CreateSequence stored procedure
5 DROP SEQUENCE DropSequence stored procedure
6 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