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

SQLines tools to help you migrate from Oracle to MySQL:

• SQLines Data - Data Transfer, Schema Migration and Validation Tool
• Online SQL Conversion 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.

• Most Complex Migration Issues
• Identifiers
• SQL Language Elements
• Data Types
• Built-in SQL Functions
• CREATE TABLE
• CREATE INDEX
• CREATE VIEW
• Sequences
• SELECT
• CREATE PROCEDURE
• CREATE FUNCTION
• CREATE TRIGGER
• PL/SQL Statements
• SQL Statements
• PL/SQL Built-in Packages
• Data Transfer
• Migration Validation

Oracle features that may require significant re-design when migrating to MySQL:

Converting identifiers:

Max length:

Object names:

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

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

Character 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
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:

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:

Converting math 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)
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

Converting CREATE TABLE statement keywords and clauses:

Storage and physical attributes:

STORAGE clause:

LOB storage clause:

Converting CREATE INDEX statement keywords and clauses:

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

Converting views from Oracle to MySQL:

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

Converting SQL SELECT statement and its clauses:

Converting stored procedures from Oracle to MySQL:

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

Converting user-defined functions from Oracle to MySQL:

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

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.

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:

Converting SQL statements and clauses from Oracle to MySQL:

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

Converting Oracle SQL*Plus commands:

• Drop existing parent tables (Removing or disable foreign key constraints)

• Data Transfer Validation