This is an old revision of the document!
SQLines provides open source tools to help you transfer data, convert database schema (DDL), views, stored procedures, functions, packages, triggers, queries and SQL scripts from Oracle to MySQL.
We also help convert embedded SQL statements in C/C++ (ODBC, Pro*C, OCI, OCCI), C#, Java, PowerBuilder, VB/VB.NET, ASP/ASP.NET, Perl, PHP, Python, Linux shell and other applications.
SQLines tools to help you migrate from Oracle to MySQL:
SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, functions and triggers from Oracle to MySQL.
SQLines tool converts SQL scripts and standalone SQL statements. To migrate data and database schema from an Oracle database use SQLines Data tool.
Try SQLines Online or download a free Desktop Version.
Technical information on migration from Oracle to MySQL.
Oracle features that may require significant re-design when migrating to MySQL:
Converting identifiers:
Oracle | MySQL | ||
First character | Letter | Letter or digit | |
Subsequent characters | Letter, digit, _ , # and $ | Letter, digit, _ and $ | |
Identifier quote character | ” (double quotes) | ` (backtick) | ” if ANSI_QUOTES is set (not set by default) |
Max length:
Oracle | MySQL | |
Tables, columns, indexes, views, procedures, triggers | 30 | 64 |
Databases | 8 | 64 |
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 data types:
Oracle | MySQL | |||
1 | BFILE | Pointer to binary file, ⇐ 4G | VARCHAR(255) | |
2 | BINARY_FLOAT | 32-bit floating-point number | FLOAT | |
3 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE | |
4 | BLOB | Binary large object, ⇐ 4G | LONGBLOB | |
5 | CHAR(n), CHARACTER(n) | Fixed-length string, 1 ⇐ n ⇐ 255 | CHAR(n), CHARACTER(n) | |
6 | CHAR(n), CHARACTER(n) | Fixed-length string, 256 ⇐ n ⇐ 2000 | VARCHAR(n) | |
7 | CLOB | Character large object, ⇐ 4G | LONGTEXT | |
8 | DATE | Date and time | DATETIME | |
9 | DECIMAL(p,s), DEC(p,s) | Fixed-point number | DECIMAL(p,s), DEC(p,s) | |
10 | DOUBLE PRECISION | Floating-point number | DOUBLE PRECISION | |
11 | FLOAT(p) | Floating-point number | DOUBLE | |
12 | INTEGER, INT | 38 digits integer | INT | DECIMAL(38) |
13 | INTERVAL YEAR(p) TO MONTH | Date interval | VARCHAR(30) | |
14 | INTERVAL DAY(p) TO SECOND(s) | Day and time interval | VARCHAR(30) | |
15 | LONG | Character data, ⇐ 2G | LONGTEXT | |
16 | LONG RAW | Binary data, ⇐ 2G | LONGBLOB | |
17 | NCHAR(n) | Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255 | NCHAR(n) | |
18 | NCHAR(n) | Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000 | NVARCHAR(n) | |
19 | NCHAR VARYING(n) | Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 | NCHAR VARYING(n) | |
20 | NCLOB | Variable-length Unicode string, ⇐ 4G | NVARCHAR(max) | |
21 | 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) | |||
22 | NUMBER(p,s) | Fixed-point number, s > 0 | DECIMAL(p,s) | |
23 | NUMBER, NUMBER(*) | Floating-point number | DOUBLE | |
24 | NUMERIC(p,s) | Fixed-point number | NUMERIC(p,s) | |
25 | NVARCHAR2(n) | Variable-length UTF-8 string, 1 ⇐ n ⇐ 4000 | NVARCHAR(n) | |
26 | RAW(n) | Variable-length binary string, 1 ⇐ n ⇐ 255 | BINARY(n) | |
27 | RAW(n) | Variable-length binary string, 256 ⇐ n ⇐ 2000 | VARBINARY(n) | |
28 | REAL | Floating-point number | DOUBLE | |
29 | ROWID | Physical row address | CHAR(10) | |
30 | SMALLINT | 38 digits integer | DECIMAL(38) | |
31 | TIMESTAMP(p) | Date and time with fraction | DATETIME(p) | |
32 | TIMESTAMP(p) WITH TIME ZONE | Date and time with fraction and time zone | DATETIME(p) | |
33 | UROWID(n) | Logical row addresses, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
34 | VARCHAR(n) | Variable-length string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
35 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
36 | XMLTYPE | XML data | LONGTEXT |
Data type attributes and options:
Oracle | MySQL |
BYTE and CHAR column size semantics | Size is always in characters |
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) |
12 | CHR(num) | Get character from ASCII code | CHAR(num USING ASCII) |
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 | INSTR(str, substr) | Get position of substring | INSTR(str, substr) |
INSTR(str, substr, pos) | LOCATE(str, substr, pos) | ||
INSTR(str, substr, pos, num) | User-defined function | ||
32 | LAST_DAY(date) | Get last day of the month | LAST_DAY(date) |
33 | LEAST(exp, exp2, …) | Get the minimum value in a set | LEAST(exp, exp2, …) |
34 | LENGTH(string) | Get length of string in chars | CHAR_LENGTH(string) |
35 | LENGTHB(string) | Get length of string in bytes | LENGTH(string) |
36 | LN(num) | Get natural logarithm of num | LN(num) |
37 | LOCALTIMESTAMP | Get the current date and time | LOCALTIMESTAMP |
LOCALTIMESTAMP([prec]) | LOCALTIMESTAMP() | ||
38 | LOG(num1, num2) | Get logarithm, base num1, of num2 | LOG(num1, num2) |
39 | LOWER(string) | Lowercase string | LOWER(string) |
40 | LPAD(string, len) | Pad the left-side of string | LPAD(string, len, ' ') |
LPAD(string, len, pad) | LPAD(string, len, pad) | ||
41 | LTRIM(string) | Remove leading spaces | LTRIM(string) |
LTRIM(string, set) | Remove leading chars | TRIM(LEADING set FROM string) | |
42 | MONTHS_BETWEEN(date1, date2) | Get number of months between date1 and date2 | User-defined function |
43 | MOD(dividend, divisor) | Get remainder | MOD(dividend, divisor) |
44 | NEXT_DAY | Get the next date by day name | NEXT_DAY user-defined function |
45 | NULLIF(exp1, exp2) | Return NULL if exp1 = exp2 | NULLIF(exp1, exp2) |
46 | NVL(exp, replacement) | Replace NULL with the specified value | IFNULL(exp, replacement) |
47 | NVL2(exp1, exp2, exp3) | Return exp2 if exp1 is not NULL, otherwise exp3 | CASE expression |
48 | POWER(value, n) | Raise value to the nth power | POWER(value, n) |
49 | REMAINDER(n1, n2) | Get remainder | (n1 - n2*ROUND(n1/n2)) |
50 | REPLACE(str, search) | Remove search-string | REPLACE(str, search, '') |
REPLACE(str, search, replace) | Replace search-string | REPLACE(str, search, replace) | |
51 | ROUND(num, integer) | Get rounded value | ROUND(num, integer) |
52 | RPAD(string, len) | Pad the right-side of string | RPAD(string, len, ' ') |
RPAD(string, len, pad) | RPAD(string, len, pad) | ||
53 | RTRIM(string) | Remove trailing spaces | RTRIM(string) |
RTRIM(string, set) | Remove trailing chars | TRIM(TRAILING set FROM string) | |
54 | SIGN(exp) | Get sign of exp | SIGN(exp) |
55 | SIN(num) | Get sine | SIN(num) |
56 | SINH(num) | Get hyperbolic sine | (EXP(num) - EXP(-num)) / 2 |
57 | SOUNDEX(string) | Get 4-character sound code | SOUNDEX(string) |
58 | SQRT(num) | Get square root | SQRT(num) |
59 | SUBSTR(string, pos, len) | Get a substring of string | SUBSTR(string, pos, len) |
60 | SYS_GUID() | Get GUID, 32 characters without dashes | REPLACE(UUID(), '-', '') |
61 | SYSDATE | Get current date and time | SYSDATE() |
62 | SYSTIMESTAMP | Get current timestamp | CURRENT_TIMESTAMP |
63 | TAN(num) | Get tangent | TAN(num) |
64 | TANH(num) | Get hyperbolic tangent | (EXP(2*num) - 1)/(EXP(2*num) + 1) |
65 | TO_CHAR(datetime, format) | Convert datetime to string | DATE_FORMAT(datetime, format) |
TO_CHAR(number, format) | Convert number to string | FORMAT(number, decimal_digits) | |
66 | TO_DATE(string, format) | Convert string to datetime | STR_TO_DATE(string, format) |
67 | TO_LOB(exp) | Convert to LOB | |
68 | TO_NCHAR(exp) | Convert to NCHAR | |
69 | TO_NCLOB(exp) | Convert to NCLOB | |
70 | TO_NUMBER(exp) | Convert to NUMBER | |
71 | TO_SINGLE_BYTE(exp) | Convert to single-byte character | |
72 | TO_TIMESTAMP(exp) | Convert to TIMESTAMP | |
73 | TRANSLATE(string, from, to) | Replace characters | User-defined function |
74 | TRIM([type trim FROM] string) | Remove characters | TRIM([type trim FROM] string) |
75 | TRUNC(num) | Truncate num | TRUNCATE(num, 0) |
TRUNC(num, num2) | TRUNCATE(num, num2) | ||
76 | TRUNC(datetime) | Truncate datetime | DATE(datetime), DATE_FORMAT |
77 | UNISTR(string) | Convert Unicode code points to chars | CHAR(string USING UCS2) |
78 | UPPER(string) | Uppercase string | UPPER(string) |
79 | USER | Get the current user | USER() |
80 | USERENV('parameter') | Get the current session information | |
81 | VSIZE(exp) | Get the size of exp in bytes | |
82 | XMLAGG(exp) | Get a aggregated XML document | |
83 | XMLCAST(exp AS datatype) | Convert exp to datatype | |
84 | XMLCDATA(exp) | Generate a CDATA section | |
85 | XMLCOMMENT(exp) | Generate an XML comment | |
86 | XMLCONCAT(exp, exp2, …) | Concatenate XML expressions | |
87 | XMLDIFF(doc, doc2) | Compare two XML documents | |
88 | XMLELEMENT(NAME element) | Get an XQuery element node | |
89 | XMLFOREST(exp, exp2, …) | Get a forest of XML expressions | |
90 | XMLISVALID(exp) | Check XML exp | |
91 | XMLPARSE(DOCUMENT exp) | Parse XML document | |
92 | XMLPATCH(doc, doc2) | Patch XML document | |
93 | XMLPI(NAME identifier) | Get XML processing instruction | |
94 | XMLROOT(exp, VERSION exp2) | Create a new XML value | |
95 | XMLSEQUENCE(exp) | Get a varray of the top-level nodes | |
96 | XMLSERIALIZE(CONTENT exp AS datatype) | Get a serialized XML value | |
97 | XMLTRANSFORM(instance, exp) | Transform XML document |
Converting CREATE TABLE statement keywords and clauses:
Oracle | MySQL | |
1 | DEFAULT any expression | DEFAULT only constant can be specified |
2 | CHECK constraint | Parsed but ignored by MySQL, a trigger can be used |
3 | ENABLE constraint attribute | Removed |
Storage and physical attributes:
Oracle | MySQL | |
1 | PCTFREE num | Removed |
2 | PCTUSED num | Removed |
3 | INITRANS num | Removed |
4 | MAXTRANS num | Removed |
5 | COMPRESS [BASIC] | COMPRESS num | NOCOMPRESS | Removed |
6 | LOGGING | NOLOGGING | Removed |
7 | SEGMENT CREATION IMMEDIATE | DEFERRED | Removed |
8 | TABLESPACE tablespace_name | Removed |
9 | LOB (column) STORE AS BASIC FILE (params) | Removed |
10 | PARALLEL num | Removed |
STORAGE clause:
Oracle | MySQL | |
1 | INITIAL num | Removed |
2 | NEXT num | Removed |
3 | MINEXTENTS num | Removed |
4 | MAXEXTENTS num | Removed |
5 | PCTINCREASE num | Removed |
6 | FREELISTS num | Removed |
7 | FREELIST GROUPS num | Removed |
8 | BUFFER_POOL DEFAULT | KEEP | RECYCLE | Removed |
9 | FLASH_CACHE DEFAULT | KEEP | NONE | Removed |
10 | CELL_FLASH_CACHE DEFAULT | KEEP | NONE | Removed |
LOB storage clause:
Oracle | MySQL | |
1 | TABLESPACE name | Removed |
2 | DISABLE | ENABLE STORAGE IN ROW | Removed |
3 | CHUNK num | Removed |
4 | NOCACHE | Removed |
5 | LOGGING | Removed |
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 | Removed |
Oracle DROP SEQUENCE, CREATE SEQUENCE and NEXTVAL emulation in MySQL using stored procedures and user-defined functions.
Oracle | MySQL | |
1 | CREATE SEQUENCE name START WITH start INCREMENT BY inc | CreateSequence('name', start, inc) |
2 | DROP SEQUENCE name | DropSequence('name') |
3 | name.NEXTVAL | NextVal('name') |
Converting SQL SELECT statement and its clauses:
Oracle | MySQL | |||
1 | ROWNUM = 1 | Return 1 row only | LIMIT 1 | |
2 | ROWNUM <= n | Row limit | LIMIT n | |
ROWNUM < n | LIMIT n - 1 | |||
3 | FOR UPDATE | Lock selected rows | FOR UPDATE | |
4 | SKIP LOCKED | Skip locked rows | Not supported by MySQL, commented |
Converting stored procedures from Oracle to MySQL:
Oracle | MySQL | |||
1 | CREATE OR REPLACE PROCEDURE | DROP PROCEDURE IF EXISTS and CREATE PROCEDURE | ||
2 | param IN | OUT | IN OUT datatype | Parameter definition | IN | OUT | INOUT param datatype(length) | |
3 | IS | AS | Removed | ||
4 | Variable declaration is before BEGIN | Variable declaration is after BEGIN | ||
5 | END sp_name | END |
For more information, see Conversion of PL/SQL Statements.
Converting user-defined functions from Oracle to MySQL:
Oracle | MySQL | |||
1 | CREATE OR REPLACE FUNCTION | DROP FUNCTION IF EXISTS and CREATE FUNCTION | ||
2 | param IN | OUT | IN OUT datatype | Parameter definition | param datatype(length) | |
3 | RETURN datatype | Return value | RETURNS datatype(length) | |
4 | IS | AS | Removed | ||
5 | Variable declaration is before BEGIN | Variable declaration is after BEGIN | ||
6 | END func_name | END |
For more information, see Conversion of PL/SQL Statements.
Converting PL/SQL statements and clauses from Oracle to MySQL:
EXCEPTION block:
Oracle | MySQL | ||
1 | BEGIN stmts EXCEPTION … END | Exception block structure | BEGIN DECLARE HANDLER … stmts END |
2 | WHEN DUP_VAL_ON_INDEX | Duplicate key | DECLARE EXIT HANDLER FOR SQLSTATE '23000' |
3 | WHEN NO_DATA_FOUND | No rows found | DECLARE EXIT HANDLER FOR NOT FOUND |
4 | WHEN OTHERS | All exceptions | DECLARE EXIT HANDLER FOR SQLEXCEPTION |
Converting SQL statements and clauses from Oracle to MySQL:
Oracle | MySQL | |
1 | COMMENT ON COLUMN table.column IS 'string' | Moved to CREATE TABLE as COMMENT 'string' clause |
2 | COMMENT ON TABLE table IS 'string' | ALTER TABLE table COMMENT 'string' |
3 | CREATE SEQUENCE | CreateSequence stored procedure |
4 | DROP SEQUENCE | DropSequence stored procedure |
5 | 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:
Oracle | MySQL | ||
1 | DBMS_OUTPUT.ENABLE(buffer) | Enable calls to PUT/GET | Removed |
Converting Oracle SQL*Plus commands:
Oracle | MySQL | ||
1 | REM | REMARK text | Single line comment | -- text |
2 | SET DEFINE ON | OFF | Variable substitution | Commented |