MySQL to Oracle Migration

SQLines tools to help you migrate from MySQL to Oracle:

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

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

SQLines tool is available in Online and Desktop editions:

Try SQLines Online or download a Desktop Version.

Technical information on migration from MySQL to Oracle.

Converting SQL language elements:

	MySQL			Oracle
1	'abc\'de'	'abc''de'	Escape single quote (') in string literal	'abc''de'
2	--	#	Single line comment	--

Datetime interval expressions:

	MySQL		Oracle
1	INTERVAL number DAY	Interval in days	INTERVAL 'number' DAY
	INTERVAL variable DAY		NUMTODSINTERVAL(variable, 'DAY')
2	INTERVAL number HOUR	Interval in hours	INTERVAL 'number' HOUR
	INTERVAL variable HOUR		NUMTODSINTERVAL(variable, 'HOUR')

Converting identifiers:

	MySQL	Oracle
Quoted Identifiers	` (backtick) and " (double quotes)	" (double quotes)

Converting data types:

	MySQL		Oracle
1	BIGINT	64-bit integer	NUMBER(19)
2	BINARY(n)	Fixed-length byte string, 1 ⇐ n ⇐ 255	RAW(n)
3	BLOB(n)	Binary large object, ⇐ 64K	BLOB
4	BOOLEAN, BOOL	0 or 1 value; NULL is not allowed	CHAR(1)
5	CHAR(n), CHARACTER(n)	Fixed-length string, 1 ⇐ n ⇐ 255	CHAR(n), CHARACTER(n)
6	CHARACTER VARYING(n)	Variable-length string, 1 ⇐ n ⇐ 65535	VARCHAR2(n)
7	DATE	Date (year, month and day)	DATE	Also includes time part
8	DATETIME(p)	Date and time data with fraction	TIMESTAMP(p)
9	DECIMAL(p,s), DEC(p,s)	Fixed-point number	NUMBER(p,s)
10	DOUBLE [PRECISION]	Double-precision floating-point number	BINARY_DOUBLE
11	FIXED(p,s)	Fixed-point number	NUMBER(p,s)
12	FLOAT(p)	Floating-point number	BINARY_DOUBLE
13	FLOAT4(p)	Floating-point number	BINARY_DOUBLE
14	FLOAT8	Double-precision floating-point number	BINARY_DOUBLE
15	INT, INTEGER	32-bit integer	NUMBER(10)
16	INT1	8-bit integer	NUMBER(3)
17	INT2	16-bit integer	NUMBER(5)
18	INT3	24-bit integer	NUMBER(7)
19	INT4	32-bit integer	NUMBER(10)
20	INT8	64-bit integer	NUMBER(19)
21	LONGBLOB	Binary large object, ⇐ 4G	BLOB
22	LONGTEXT	Character large object, ⇐ 4G	CLOB
23	LONG VARBINARY	Binary large object, ⇐ 16M	BLOB
24	LONG, LONG VARCHAR	Character large object, ⇐ 16M	CLOB
25	MEDIUMBLOB	Binary large object, ⇐ 16M	BLOB
26	MEDIUMINT	24-bit integer	NUMBER(7)
27	MEDIUMTEXT	Character large object, ⇐ 16M	CLOB
28	MIDDLEINT	24-bit integer	NUMBER(7)
29	NCHAR(n)	Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255	NCHAR(n)
30	NVARCHAR(n)	Varying-length UTF-8 string, 1 ⇐ n ⇐ 65535	NVARCHAR2(n)
31	NUMERIC(p,s)	Fixed-point number	NUMBER(p,s)
32	REAL	Double-precision floating-point number	BINARY_DOUBLE
33	SERIAL	64-bit autoincrementing integer	Sequence and trigger
34	SMALLINT	16-bit integer	NUMBER(5)
35	TEXT	Character large object, ⇐ 64K	CLOB
36	TIME(p)	Time (Hour, minute, second and fraction)	TIMESTAMP(p)
37	TIMESTAMP(p)	Auto-updated datetime	TIMESTAMP(p)
38	TINYBLOB	Binary data, ⇐ 255 bytes	RAW(255)
39	TINYINT	8-bit integer	NUMBER(3)
40	TINYTEXT	Character data, ⇐ 255 bytes	VARCHAR2(255)
41	VARBINARY(n)	Variable-length byte string, 1 ⇐ n ⇐ 65535	RAW(n)
42	VARCHAR(n)	Variable-length string, 1 ⇐ n ⇐ 65535	VARCHAR2(n)
43	YEAR[(2 \| 4)]	Year in 2-digit or 4-digit format	NUMBER(4)

Other data types:

	MySQL		Oracle
1	BIT(n)	Fixed-length bit string, 1 <= n <= 64, default is 1	RAW(n/8)
2	ENUM	Value from a list	VARCHAR2 and CHECK constraint
3	SET	One or more values from a list	VARCHAR2

Data type attributes and range:

MySQL	Oracle
Display width for integers INT(d)	Removed
UNSIGNED	CHECK (col_name > 0)
COLLATE collate_name	Removed
CHARACTER SET charset_name	Removed
COMMENT 'string'	COMMENT ON COLUMN statement
ON UPDATE clause for TIMESTAMP columns	Not supported
BINARY	RAW(1)
BINARY(0)	RAW(1)
VARCHAR(n) BINARY	RAW(n)

Converting functions from MySQL to Oracle:

	MySQL		Oracle
1	ABS(num)	Get the absolute value	ABS(num)
2	ACOS(num)	Get the arc cosine	ACOS(num)
3	ADDDATE(date, interval)	Add interval to date	date + interval
3	ADDDATE(date, num)	Add num days to date	date + num
4	ADDTIME(datetime, time)	Add time to a datetime
5	AES_DECRYPT(crypt_str, key_str)	Decrypt using AES	User-defined function
6	AES_ENCRYPT(str, key_str)	Encrypt using AES	User-defined function
7	ASCII(str)	Get ASCII code of left-most char	ASCII(str)
8	ASIN(num)	Get the arc sine	ASIN(num)
9	ATAN(num)	Get the arc tangent	ATAN(num)
9	ATAN(x, y)	Get the arc tangent of x and y	ATAN2(x, y)
10	ATAN2(x, y)	Get the arc tangent of x and y	ATAN2(x, y)
11	BIN(num)	Get binary representation of a num	User-defined function
12	CEILING(num)	Get the smallest following integer	CEIL(num)
12	CEIL(num)	Get the smallest following integer	CEIL(num)
13	CHAR(num1, num2, …)	Get character from each ASCII code	CHR(num1) \|\| CHR(num2) \|\| …
14	CHAR_LENGTH(string)	Get length of string in characters	LENGTH(string)
14	CHARACTER_LENGTH(string)	Get length of string in characters	LENGTH(string)
15	COALESCE(exp1, exp2, …)	Return first non-NULL expression	COALESCE(exp1, exp2, …)
16	CONCAT(str1, str2)	Concatenate strings	CONCAT(str1, str2)
16	CONCAT(str1, str2, …)	Concatenate strings	str1 \|\| str2 \|\| …
17	CONCAT_WS(separator, str1, str2, …)	Concatenate strings with separator	str1 \|\| separator \|\| str2 \|\| …
18	CONNECTION_ID()	Get the connection ID	SYS_CONTEXT('USERENV', 'SESSIONID')
19	CONV(num, from_base, to_base)	Convert num between various bases
20	CONVERT(exp, datatype)	Convert exp to datatype	CAST(exp AS datatype)
21	CONVERT_TZ(datetime, from, to)	Convert from one timezone to another
22	COS(num)	Get the cosine	COS(num)
23	COT(num)	Get the cotangent	1 / TAN(num)
24	CRC32(exp)	Get cyclic redundancy check value	User-defined function
25	CURDATE()	Get the current date	TRUNC(SYSDATE)
	CURRENT_DATE
	CURRENT_DATE()
26	CURTIME()	Get the current time	SYSTIMESTAMP
	CURRENT_TIME
	CURRENT_TIME()
27	CURRENT_TIMESTAMP	Get the current date and time	CURRENT_TIMESTAMP
27	CURRENT_TIMESTAMP()	Get the current date and time	CURRENT_TIMESTAMP
28	CURRENT_USER	Get the authenticated user name	USER
28	CURRENT_USER()	Get the authenticated user name	USER
29	DATABASE()	Get the current database name	SYS_CONTEXT('USERENV', 'DB_NAME')
30	DATE_ADD(date, interval)	Add interval to date	date + interval
31	DATE_FORMAT(date, format)	Convert date to string	TO_CHAR(date, format)
32	DATE_SUB(date, interval)	Subtract interval from date	date - interval
33	DATE(datetime)	Extract date from datetime	TRUNC(datetime)
34	DATEDIFF(date1, date2)	Date difference in days	date1 - date2
35	DAY(datetime)	Extract day from datetime	EXTRACT(DAY FROM datetime)
35	DAYOFMONTH(datetime)	Extract day from datetime	EXTRACT(DAY FROM datetime)
36	DAYNAME(datetime)	Get the name of the weekday	TO_CHAR(datetime, 'Day')
37	DAYOFWEEK(datetime)	Get the weekday number (1-7)	TO_NUMBER(TO_CHAR( datetime, 'D'))
38	DAYOFYEAR(datetime)	Get the day of the year	TO_NUMBER(TO_CHAR( datetime, 'DDD'))
39	DEGREES(num)	Convert radians to degrees	(num) * 180/3.1415926535
40	ELT(position, exp1, exp2, …)	Get selected argument from list	CASE expression
41	EXP(n)	Raise e to the nth power	EXP(n)
42	IF(condition, exp2, exp3)	If condition is true, not 0, not NULL return exp2 otherwise exp3	CASE WHEN condition THEN exp2 ELSE exp3 END
43	IFNULL(exp, exp2)	Return exp2 if exp is NULL, otherwise exp	NVL(exp, exp2)
44	NOW()	Get the current date and time	SYSTIMESTAMP
45	SUBDATE(date, interval)	Subtract interval from date	date - interval
46	SYSDATE()	Get the time at which the function executes	SYSDATE

Converting table definition statement from MySQL to Oracle:

	MySQL		Oracle
1	IF NOT EXISTS clause		Removed
2	AUTO_INCREMENT column property		Emulated using a sequence and trigger
3	AUTO_INCREMENT = start initial value		Used as the start value in the sequence
4	DEFAULT exp		DEFAULT must be specified after data type, before NOT NULL etc.
5	COMMENT 'text'	Inline column comment	COMMENT ON COLUMN tab.col IS 'text'	Standalone statement
6	UNIQUE KEY \| INDEX name (column, …)		CONSTRAINT name UNIQUE (column, …)
7	KEY name (column, …)	Inline non-unique index	CREATE INDEX name ON table(column, …)	Standalone statement

Foreign key constraint options:

	MySQL	Oracle
1	ON DELETE NO ACTION	Default behavior, clause removed
2	ON UPDATE NO ACTION
3	ON UPDATE CASCADE	Commented

Table and storage options:

	MySQL		Oracle
1	Storage ENGINE = engine_type clause		Removed
2	DEFAULT CHARSET character_set table option		Removed
3	COLLATE = collate_name table option		Removed
4	USING BTREE \| HASH		Removed
5	COMMENT = 'table comment'		COMMENT ON TABLE table IS 'table comment'
6	PACK_KEYS = 0 \| 1 \| DEFAULT	Pack columns for MyISAM	Removed
7	ROW_FORMAT = type \| DEFAULT	Physical row format	Removed

Converting view definition statement from MySQL to Oracle:

	MySQL	Oracle
1	CREATE OR REPLACE VIEW name	CREATE OR REPLACE VIEW name
2	ALGORITHM = value	Removed
3	DEFINER = user	Removed
4	SQL SECURITY { DEFINER \| INVOKER }	Removed

Converting SQL queries from MySQL to Oracle:

	MySQL		Oracle
1	SELECT without FROM clause		SELECT … FROM dual
2	Standalone SELECT returning a result set from a stored procedure		OPEN cur FOR SELECT … and OUT SYS_REFCURSOR parameter
3	SET var = (SELECT c FROM …)	Assignment statement	SELECT c INTO var FROM …
4	SET var = exp(SELECT …)	In assignment expression	SELECT exp(SELECT …) INTO var FROM dual

Limit rows (Since Oracle 12c):

	MySQL		Oracle
1	SELECT … ORDER BY c LIMIT rows	With or without sorting	SELECT … ORDER BY c FETCH NEXT rows ROWS ONLY
2	SELECT … ORDER BY c LIMIT offset, rows	With or without sorting	SELECT … ORDER BY c OFFSET offset ROWS FETCH NEXT rows ROWS ONLY

Limit rows (Migration to Oracle 11g/10g):

	MySQL		Oracle
1	SELECT … LIMIT n	Without sorting	SELECT … WHERE rownum <= n
2	SELECT … ORDER BY c LIMIT n	With sorting	SELECT * (SELECT … ORDER BY c) WHERE rownum <= n

Converting CREATE PROCEDURE statement:

	MySQL		Oracle
1	CREATE PROCEDURE name		CREATE OR REPLACE PROCEDURE name
2	DEFINER = user		Removed
3	IN \| OUT \| INOUT param datatype(length)	Parameter definition	param IN \| OUT \| IN OUT datatype
4	No AS keyword before outer BEGIN END block		AS keyword added
5	Standalone SELECT returning a result set		OPEN cur FOR SELECT and OUT SYS_REFCURSOR parameter
6	User-defined delimiter at the end		/

For more information, see Conversion of Procedural SQL Statements.

Converting CREATE FUNCTION statement:

	MySQL		Oracle
1	CREATE FUNCTION		CREATE OR REPLACE FUNCTION
2	param datatype(length)	Parameter definition	param datatype
3	RETURNS datatype(length)		RETURN datatype
4	DETERMINISTIC		DETERMINISTIC
5	NOT DETERMINISTIC		Removed, this is the default behavior
6	LANGUAGE SQL		Removed
7	CONTAINS SQL		Removed
8	NO SQL		Removed
9	READS SQL DATA		Removed
10	MODIFIES SQL DATA		Removed
11	COMMENT 'text'		Removed
12	SQL SECURITY DEFINER	Invoker rights	AUTHID DEFINER
13	SQL SECURITY INVOKER	Invoker rights	AUTHID CURRENT_USER
14	No AS keyword before outer BEGIN END block		AS keyword added
15	Optional outer BEGIN END block for single statement		BEGIN END block is added

For more information, see Conversion of Procedural SQL Statements.

Converting CREATE TRIGGER statement:

	MySQL		Oracle
1	CREATE TRIGGER		CREATE OR REPLACE TRIGGER
2	Optional outer BEGIN END block for single statement		BEGIN END block is added
3	new.column	Referencing the new value for column	:new.column

For more information, see Conversion of Procedural SQL Statements.

Procedural SQL statements used in stored procedures, functions and triggers:

Variable declaration and assignment:

	MySQL		Oracle
1	DECLARE var datatype(len) [DEFAULT value]	Variable declaration	var datatype(len) [DEFAULT value];
2	SET variable = value	Assignment statement	variable := value

Flow-of-control statements:

	MySQL		Oracle
1	IF condition THEN … END IF;	IF statement	IF condition THEN … END IF;
2	LEAVE label;	Leave a loop or block	EXIT label;
2	LEAVE outer_proc_label;	Leave the procedure	RETURN;
3	LOOP stmts END LOOP;	A loop statement	LOOP stmts END LOOP;
3	label: LOOP stmts END LOOP label;	A loop statement	<<label>> LOOP stmts END LOOP label;
4	REPEAT stmts UNTIL condition END REPEAT;	Conditional loop	LOOP stmts EXIT WHEN condition; END LOOP;

Other statements and procedural language elements;

	MySQL		Oracle
1	CALL proc(param, …)	Call a procedure	proc(param, …)
2	label:	Label declaration	<<label>>

Converting other SQL statements:

	MySQL		Oracle
1	CREATE DATABASE IF NOT EXISTS name	Create a database	CREATE USER name IDENTIFIED BY name
2	CREATE SCHEMA IF NOT EXISTS name
3	DROP DATABASE IF EXISTS name	Drop a database	DROP USER name CASCADE
4	DROP SCHEMA IF EXISTS name
5	DROP TABLE IF EXISTS table		EXECUTE IMMEDIATE and EXCEPTION
6	DROP TRIGGER IF EXISTS trigger		EXECUTE IMMEDIATE and EXCEPTION
7	INSERT Multiple Rows		INSERT SELECT UNION ALL
8	USE dbname		ALTER SESSION SET CURRENT_SCHEMA = dbname

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference

MySQL to Oracle Migration

MySQL to Oracle Migration Tools

SQLines SQL Converter Tool

MySQL to Oracle Migration Reference

Language Elements

Identifiers

Data Types

Built-in SQL Functions

CREATE TABLE Statement

CREATE VIEW Statement

SELECT Statement

CREATE PROCEDURE Statement

CREATE FUNCTION Statement

CREATE TRIGGER Statement

Procedural SQL Statements

Other SQL Statements