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

Namespaces:

Operators:

	Oracle		MySQL
1	|| Operator	String concatenation	CONCAT function
2	+ and - Operators for datetime	Datetime arithmetic	Interval expressions and functions
3	exp IS JSON	Check if JSON is valid	JSON_VALID(exp)

Converting character data types:

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

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 date and time data types:

	Oracle		MySQL
1	DATE	Date and time	DATETIME
2	INTERVAL YEAR(p) TO MONTH	Date interval	VARCHAR(30)
3	INTERVAL DAY(p) TO SECOND(s)	Date and time interval	VARCHAR(30)
4	TIMESTAMP(p)	Date and time with fraction, 0 ⇐ p ⇐ 9, default is 6	DATETIME(p), 0 ⇐ p ⇐ 6
5	TIMESTAMP(p) WITH TIME ZONE	Date and time with fraction and time zone	DATETIME(p)

Converting other data types:

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(str, start, len)	Get substring	SUBSTR(str, start, 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
3	SYSTIMESTAMP	Get current datetime	NOW(6)

Numeric functions:

Converting NULL processing functions:

Aggregation and window functions:

	Oracle		MySQL
1	DENSE_RANK() OVER (...)	Rank without gaps	DENSE_RANK() OVER (...)

Converting math functions:

Converting LOB functions:

Converting system functions:

	Oracle		MySQL
1	SYS_CONTEXT('USERENV', 'OS_USER')	Get OS user	USER()
1	SYS_CONTEXT('USERENV', 'SESSION_USER')	Get current user	USER()
2	USER	Get current user	USER()

Converting other functions:

	Oracle		MySQL
1	SYS_GUID()	Generate UUID	UUID()

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))
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	TAN(num)	Get tangent	TAN(num)
49	TANH(num)	Get hyperbolic tangent	(EXP(2*num) - 1)/(EXP(2*num) + 1)
50	TO_DATE(string, format)	Convert string to datetime	STR_TO_DATE(string, format)
51	TO_LOB(exp)	Convert to LOB
52	TO_NCHAR(exp)	Convert to NCHAR
53	TO_NCLOB(exp)	Convert to NCLOB
54	TO_NUMBER(exp)	Convert to NUMBER
55	TO_SINGLE_BYTE(exp)	Convert to single-byte character
56	TO_TIMESTAMP(exp)	Convert to TIMESTAMP
57	TRIM([type trim FROM] string)	Remove characters	TRIM([type trim FROM] string)
58	TRUNC(num)	Truncate num	TRUNCATE(num, 0)
	TRUNC(num, num2)		TRUNCATE(num, num2)
59	TRUNC(datetime)	Truncate datetime	DATE(datetime), DATE_FORMAT
60	UNISTR(string)	Convert Unicode code points to chars	CHAR(string USING UCS2)
61	UPPER(string)	Uppercase string	UPPER(string)
63	USERENV('parameter')	Get the current session information
64	VSIZE(exp)	Get the size of exp in bytes
65	XMLAGG(exp)	Get a aggregated XML document
66	XMLCAST(exp AS datatype)	Convert exp to datatype
67	XMLCDATA(exp)	Generate a CDATA section
68	XMLCOMMENT(exp)	Generate an XML comment
69	XMLCONCAT(exp, exp2, …)	Concatenate XML expressions
70	XMLDIFF(doc, doc2)	Compare two XML documents
71	XMLELEMENT(NAME element)	Get an XQuery element node
72	XMLFOREST(exp, exp2, …)	Get a forest of XML expressions
73	XMLISVALID(exp)	Check XML exp
74	XMLPARSE(DOCUMENT exp)	Parse XML document
75	XMLPATCH(doc, doc2)	Patch XML document
76	XMLPI(NAME identifier)	Get XML processing instruction
77	XMLROOT(exp, VERSION exp2)	Create a new XML value
78	XMLSEQUENCE(exp)	Get a varray of the top-level nodes
79	XMLSERIALIZE(CONTENT exp AS datatype)	Get a serialized XML value
80	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	CROSS JOIN	Cartesian product	CROSS JOIN
4	CONNECT BY PRIOR	Hierarchical queries	Recursive Common Table Expressions (CTE)
5	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:

	Oracle		MySQL
1	GENERATED AS IDENTITY	Identity column	AUTO_INCREMENT
2	CONSTRAINT name NOT NULL	Named NOT NULL constraint	NOT NULL
3	DEFAULT ON NULL exp	Default when NULL is inserted	DEFAULT exp
4	ENABLE	Constraint enabled attribute	Removed
5	col AS (exp)	Computed column	type col AS (exp)	Data type must be specified

Temporary tables:

	Oracle		MySQL
1	CREATE GLOBAL TEMPORARY TABLE	Global temporary table	CREATE TEMPORARY TABLE
2	schema.name	Qualified object name	db_name.name

Partitioned tables:

	Oracle		MySQL
1	PARTITION BY RANGE	Range partitions	PARTITION BY RANGE
2	PARTITION BY LIST	List partitions	PARTITION BY LIST

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:

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

Cursor attributes:

Flow-of-control statements:

	Oracle		MySQL
1	GOTO label	Jump to label	LEAVE label and BEGIN-END block

Executing stored procedures from a PL/SQL block:

	Oracle		MySQL
1	sp_name(param1, ...)	Execute procedure	CALL sp_name(param1, ...)
	sp_name	Procedure without parameters	CALL sp_name
	sp_name(param1 => value1, ...)	Procedure with named parameters	sp_name(value1, ...)

Error handling:

EXCEPTION block:

Table manipulation statements:

	Oracle		MySQL
1	ALTER TABLE ... ADD FOREIGN KEY	Add foreign key	ALTER TABLE ... ADD FOREIGN KEY Data type issues
2	ALTER TABLE ... ADD UNIQUE ... USING INDEX	Add unique constraint with existing index	Index commented
3	ALTER TABLE ... MODIFY (coldef, ...)	Modify column(s)	ALTER TABLE ... MODIFY coldef, ...

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

System and Control statements:

	Oracle		MySQL
1	ALTER USER	Alter user	ALTER USER
2	CREATE USER	Create user	CREATE USER
3	GRANT	Grant privileges	GRANT

Converting built-in PL/SQL packages:

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