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

MySQL to Oracle Migration Tools

SQLines SQL Converter 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:

Try SQLines Online or download a Desktop Version.

MySQL to Oracle Migration Reference

Language Elements

Converting SQL language elements:

MySQL Oracle
1 -- | # 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')

Identifiers

Converting identifiers:

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

Data Types

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:

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)

Built-in SQL Functions

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

CREATE TABLE Statement

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:

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

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

CREATE VIEW Statement

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

SELECT Statement

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 (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

CREATE PROCEDURE Statement

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.

CREATE FUNCTION Statement

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 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.

CREATE TRIGGER Statement

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

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;
LEAVE outer_proc_label; Leave the procedure RETURN;
3 LOOP stmts END LOOP; A loop statement LOOP stmts END LOOP;
label: LOOP stmts END LOOP label; <<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>>

Other SQL Statements

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