MySQL to Oracle Migration

SQLines provides tools and services to help you transfer data, convert database schema (DDL), views, stored procedures and functions, triggers, queries, embedded SQL statements and SQL scripts from MySQL to Oracle.

We also help convert embedded SQL statements in C/C++ (ODBC, MySQL C API), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET and Perl/PHP applications.

• MySQL 5.x and 4.x
• Oracle 12c, 11g R2, 10g and 9i

SQLines tools to help you migrate from MySQL to Oracle:

• SQLines SQL Converter - SQL Scripts Conversion Tool
• SQLines Data - Data Transfer and Database 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 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:

	Online Edition	Desktop Edition
Conversion Features
DDL - Tables and Indexes
Queries, Views and DML Statements
Stored Procedures, Functions and Triggers
Embedded SQL in Applications
Interfaces
GUI
Command Line	n/a
API	n/a
Platforms
Windows 32-bit	Hosted
Linux x86 32-bit	Hosted
Licensing and Support
Technical Support
Price	Purchase

Try SQLines Online or download a Trial Version.

Technical information on migration from MySQL to Oracle.

• Language Elements
• Identifiers
• Data Types
• Built-in SQL Functions
• CREATE TABLE
• CREATE VIEW
• SELECT
• CREATE PROCEDURE
• CREATE FUNCTION
• CREATE TRIGGER
• Procedural SQL Statements
• Other SQL Statements

Last Update: MySQL 5.6 and Oracle 11g R2

Converting SQL language elements:

Datetime interval expressions:

Converting identifiers:

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

Data type attributes and range:

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
	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)
	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)
	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)
	CHARACTER_LENGTH(string)
15	COALESCE(exp1, exp2, …)	Return first non-NULL expression	COALESCE(exp1, exp2, …)
16	CONCAT(str1, str2)	Concatenate strings	CONCAT(str1, str2)
	CONCAT(str1, str2, …)		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
	CURRENT_TIMESTAMP()
28	CURRENT_USER	Get the authenticated user name	USER
	CURRENT_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)
	DAYOFMONTH(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	UNIQUE KEY | INDEX name (column, …)		CONSTRAINT name UNIQUE (column, …)
6	KEY name (column, …)	Inline non-unique index	CREATE INDEX name ON table(column, …)

Foreign key options:

Table and storage options:

Converting view definition statement from MySQL to Oracle:

Converting SQL queries from MySQL to Oracle:

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

Converting CREATE PROCEDURE statement:

For more information, see Conversion of Procedural SQL Statements.

Converting CREATE FUNCTION statement:

For more information, see Conversion of Procedural SQL Statements.

Converting CREATE TRIGGER statement:

For more information, see Conversion of Procedural SQL Statements.

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

Variable declaration and assignment:

Flow-of-control statements:

Other statements and procedural language elements;

Converting other SQL statements: