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.

• SQLines SQL Converter - SQL Scripts Conversion and Assessment tool. Try Online
• SQLines Data - Data Transfer, Schema Migration and Validation tool

Databases:

• Oracle 23, 22c, 21c, 19c, 18c, 12c, 11g, 10g and 9i
• MySQL 9.x, 8.x, 6.x, 5.x

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

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

Converting character data types:

	Oracle		MySQL
1	CHAR(n)	Fixed-length string, 1 ⇐ n ⇐ 2000	CHAR(n)	0 ⇐ n ⇐ 255
2	VARCHAR(n)	Synonym for VARCHAR2	VARCHAR(n)
3	VARCHAR2(n)	Variable-length string, 1 ⇐ n ⇐ 32,767	VARCHAR(n)	0 ⇐ n ⇐ 65,535

Data type attributes and options:

Converting numeric data types:

	Oracle		MySQL
1	BINARY_FLOAT	32-bit floating-point number	FLOAT
2	BINARY_DOUBLE	64-bit floating-point number	DOUBLE
3	DECIMAL(p,s)	Fixed-point number	DECIMAL(p,s)
4	DOUBLE PRECISION	Floating-point number	DOUBLE PRECISION
5	FLOAT(p)	Floating-point number	DOUBLE
6	INTEGER, INT	38 digits integer	INT	DECIMAL(38)
7	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)
8	NUMBER(p,s)	Fixed-point number, s > 0	DECIMAL(p,s)
9	NUMBER, NUMBER(*)	Floating-point number	DECIMAL(p,s)	DOUBLE
10	NUMERIC(p,s)	Fixed-point number	NUMERIC(p,s)
11	REAL	Floating-point number	DOUBLE
12	SMALLINT	38 digits integer	DECIMAL(38)

Converting other data types:

	Oracle		MySQL
1	BFILE	Pointer to binary file, ⇐ 4G	VARCHAR(255)
2	BLOB	Binary large object, ⇐ 4G	LONGBLOB
3	CLOB	Character large object, ⇐ 4G	LONGTEXT
4	DATE	Date and time	DATETIME
5	INTERVAL YEAR(p) TO MONTH	Date interval	VARCHAR(30)
6	INTERVAL DAY(p) TO SECOND(s)	Day and time interval	VARCHAR(30)
7	LONG	Character data, ⇐ 2G	LONGTEXT
8	LONG RAW	Binary data, ⇐ 2G	LONGBLOB
9	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255	NCHAR(n)
10	NCHAR(n)	Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000	NVARCHAR(n)
11	NCHAR VARYING(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000	NCHAR VARYING(n)
12	NCLOB	Variable-length Unicode string, ⇐ 4G	NVARCHAR(max)
13	NVARCHAR2(n)	Variable-length UTF-8 string, 1 ⇐ n ⇐ 4000	NVARCHAR(n)
14	RAW(n)	Variable-length binary string, 1 ⇐ n ⇐ 2000	VARBINARY(n)
15	ROWID	Physical row address	CHAR(10)
16	TIMESTAMP(p)	Date and time with fraction	DATETIME(p)
17	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	DATETIME(p)
18	UROWID(n)	Logical row addresses, 1 ⇐ n ⇐ 4000	VARCHAR(n)
19	XMLTYPE	XML data	LONGTEXT

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	LISTAGG(exp, delim)...	Aggregate concatenation	GROUP_CONCAT(exp SEPARATOR delim)
6	LPAD(string, len)	Pad the left-side of string	LPAD(string, len, ' ')
	LPAD(string, len, pad)		LPAD(string, len, pad)
7	LTRIM(string)	Remove leading spaces	LTRIM(string)
	LTRIM(string, set)	Remove leading chars	TRIM(LEADING set FROM string)
8	MONTHS_BETWEEN(dt1, dt2)	Get difference in months	User-defined function
9	REPLACE(str, search)	Remove search-string	REPLACE(str, search, '')
	REPLACE(str, search, replace)	Replace search-string	REPLACE(str, search, replace)
10	RPAD(string, len)	Pad the right-side of string	RPAD(string, len, ' ')
	RPAD(string, len, pad)		RPAD(string, len, pad)
11	RTRIM(string)	Remove trailing spaces	RTRIM(string)
	RTRIM(string, set)	Remove trailing chars	TRIM(TRAILING set FROM string)
12	TO_CHAR(datetime, format)	Convert datetime to string	DATE_FORMAT(datetime, format)
	TO_CHAR(number, format)	Convert number to string	FORMAT(number, decimal_digits)
13	SUBSTR(string, pos, len)	Get a substring of string	SUBSTR(string, pos, len)
14	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
2	SYSDATE	Get current date and time	SYSDATE()	Includes time

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

Converting queries:

	Oracle		MySQL
1	SELECT * FROM (SELECT ...)	Subquery alias is optional	SELECT * FROM (SELECT ...) t	Alias required
2	t1 JOIN t2 USING (column_name)	USING clause for joins	t1 JOIN t2 USING (column_name)
3	ORDER BY col NULLS FIRST | LAST	Order of NULLs	ORDER BY col

Row limitation:

	Oracle		MySQL
1	OFFSET k FETCH FIRST n ROWS ONLY	Row limiting	LIMIT k, n
2	ROWNUM = 1	Return 1 row only	LIMIT 1
	ROWNUM <= n	Row limit	LIMIT n
	ROWNUM < n		LIMIT n - 1
	ROWNUM < (10+2)	Arbitrary expression	Only integer is allowed

Other clauses:

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		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		Clause not supported, removed

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

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		DELIMITER // DROP TRIGGER IF EXISTS and CREATE TRIGGER
2	BEFORE | AFTER | INSTEAD OF		BEFORE | AFTER
3	INSERT OR UPDATE OR DELETE	Multiple events in one trigger	Only one event in trigger
4	ON table		ON table
5	REFERENCING OLD AS old_name NEW AS new_name		Clause is not supported, OLD and NEW used
6	:NEW.col, :OLD.col	Referencing column values	NEW.col, OLD.col
7	FOR EACH ROW	Row-level trigger	FOR EACH ROW
8	BEGIN stmt; … END; /		BEGIN stmt; … END; // DELIMITER ;

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

Executing stored procedures from a PL/SQL block:

Error handling:

EXCEPTION block:

Converting SQL statements and clauses:

	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	DELETE [FROM] table	Delete rows	DELETE FROM table	FROM keyword is required
6	DROP SEQUENCE		DropSequence stored procedure
7	LOCK TABLE name IN EXCLUSIVE MODE		LOCK TABLES name WRITE
	LOCK TABLE name IN SHARE MODE		LOCK TABLES name READ

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

Converting Oracle SQL*Plus commands:

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

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

• Data Transfer Validation