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.
Databases:
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.
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 | 128 (was 30 before Oracle 12c) | 64 |
Databases | 8 | 64 |
Object names:
Oracle | MySQL | ||
Index | CREATE INDEX schema.index_name | CREATE INDEX index_name | Can contain index name only ![]() |
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 |
Character data types:
Oracle | MySQL | |||
1 | CHAR(n) | Fixed-length string, 1 <= n <= 2000 | CHAR(n), VARCHAR(n) | n <= 255 for CHAR |
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 | |
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 ⇐ 2000 | VARBINARY(n) | |
27 | REAL | Floating-point number | DOUBLE | |
28 | ROWID | Physical row address | CHAR(10) | |
29 | SMALLINT | 38 digits integer | DECIMAL(38) | |
30 | TIMESTAMP(p) | Date and time with fraction | DATETIME(p) | |
31 | TIMESTAMP(p) WITH TIME ZONE | Date and time with fraction and time zone | DATETIME(p) ![]() |
|
32 | UROWID(n) | Logical row addresses, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
33 | VARCHAR(n) | Variable-length string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
34 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
35 | XMLTYPE | XML data | LONGTEXT |
Data type attributes and options:
Oracle | MySQL |
BYTE and CHAR column size semantics | Size is always in characters |
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 | LPAD(string, len) | Pad the left-side of string | LPAD(string, len, ' ') | |
LPAD(string, len, pad) | LPAD(string, len, pad) | |||
6 | LTRIM(string) | Remove leading spaces | LTRIM(string) | |
LTRIM(string, set) | Remove leading chars | TRIM(LEADING set FROM string) | ||
7 | MONTHS_BETWEEN(dt1, dt2) | Get difference in months | User-defined function ![]() |
|
8 | REPLACE(str, search) | Remove search-string | REPLACE(str, search, '') | |
REPLACE(str, search, replace) | Replace search-string | REPLACE(str, search, replace) | ||
9 | RPAD(string, len) | Pad the right-side of string | RPAD(string, len, ' ') | |
RPAD(string, len, pad) | RPAD(string, len, pad) | |||
10 | RTRIM(string) | Remove trailing spaces | RTRIM(string) | |
RTRIM(string, set) | Remove trailing chars | TRIM(TRAILING set FROM string) | ||
11 | SUBSTR(string, pos, len) | Get a substring of string | SUBSTR(string, pos, len) | |
12 | 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 ![]() |
Converting NULL processing functions:
Oracle | MySQL | |||
1 | NVL(exp, replacement) | Replace NULL with the specified value | IFNULL(exp, replacement) |
Converting math functions:
Oracle | MySQL | |||
1 | POWER(value, n) | Raise value to the nth power | POWER(value, n) |
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 |
32 | LEAST(exp, exp2, …) | Get the minimum value in a set | LEAST(exp, exp2, …) |
34 | LENGTHB(string) | Get length of string in bytes | LENGTH(string) ![]() |
35 | LN(num) | Get natural logarithm of num | LN(num) |
36 | LOCALTIMESTAMP | Get the current date and time | LOCALTIMESTAMP |
LOCALTIMESTAMP([prec]) | LOCALTIMESTAMP() | ||
37 | LOG(num1, num2) | Get logarithm, base num1, of num2 | LOG(num1, num2) |
38 | LOWER(string) | Lowercase string | LOWER(string) |
42 | MOD(dividend, divisor) | Get remainder | MOD(dividend, divisor) |
43 | NEXT_DAY | Get the next date by day name | NEXT_DAY user-defined function |
44 | NULLIF(exp1, exp2) | Return NULL if exp1 = exp2 | NULLIF(exp1, exp2) |
46 | NVL2(exp1, exp2, exp3) | Return exp2 if exp1 is not NULL, otherwise exp3 | CASE expression |
48 | REMAINDER(n1, n2) | Get remainder | (n1 - n2*ROUND(n1/n2)) |
49 | ROUND(num, integer) | Get rounded value | ROUND(num, integer) |
52 | SIGN(exp) | Get sign of exp | SIGN(exp) |
53 | SIN(num) | Get sine | SIN(num) |
54 | SINH(num) | Get hyperbolic sine | (EXP(num) - EXP(-num)) / 2 |
55 | SOUNDEX(string) | Get 4-character sound code | SOUNDEX(string) |
56 | SQRT(num) | Get square root | SQRT(num) |
57 | SYS_GUID() | Get GUID, 32 characters without dashes | REPLACE(UUID(), '-', '') |
58 | SYSDATE | Get current date and time | SYSDATE() |
59 | SYSTIMESTAMP | Get current timestamp | CURRENT_TIMESTAMP |
60 | TAN(num) | Get tangent | TAN(num) |
61 | TANH(num) | Get hyperbolic tangent | (EXP(2*num) - 1)/(EXP(2*num) + 1) |
62 | TO_CHAR(datetime, format) | Convert datetime to string | DATE_FORMAT(datetime, format) ![]() |
TO_CHAR(number, format) | Convert number to string | FORMAT(number, decimal_digits) ![]() |
|
63 | TO_DATE(string, format) | Convert string to datetime | STR_TO_DATE(string, format) ![]() |
64 | TO_LOB(exp) | Convert to LOB | |
65 | TO_NCHAR(exp) | Convert to NCHAR | |
66 | TO_NCLOB(exp) | Convert to NCLOB | |
67 | TO_NUMBER(exp) | Convert to NUMBER | |
68 | TO_SINGLE_BYTE(exp) | Convert to single-byte character | |
69 | TO_TIMESTAMP(exp) | Convert to TIMESTAMP | |
71 | TRIM([type trim FROM] string) | Remove characters | TRIM([type trim FROM] string) |
72 | TRUNC(num) | Truncate num | TRUNCATE(num, 0) |
TRUNC(num, num2) | TRUNCATE(num, num2) | ||
73 | TRUNC(datetime) | Truncate datetime | DATE(datetime), DATE_FORMAT |
74 | UNISTR(string) | Convert Unicode code points to chars | CHAR(string USING UCS2) ![]() |
75 | UPPER(string) | Uppercase string | UPPER(string) |
76 | USER | Get the current user | USER() |
77 | USERENV('parameter') | Get the current session information | |
78 | VSIZE(exp) | Get the size of exp in bytes | |
79 | XMLAGG(exp) | Get a aggregated XML document | |
80 | XMLCAST(exp AS datatype) | Convert exp to datatype | |
81 | XMLCDATA(exp) | Generate a CDATA section | |
82 | XMLCOMMENT(exp) | Generate an XML comment | |
83 | XMLCONCAT(exp, exp2, …) | Concatenate XML expressions | |
84 | XMLDIFF(doc, doc2) | Compare two XML documents | |
85 | XMLELEMENT(NAME element) | Get an XQuery element node | |
86 | XMLFOREST(exp, exp2, …) | Get a forest of XML expressions | |
87 | XMLISVALID(exp) | Check XML exp | |
88 | XMLPARSE(DOCUMENT exp) | Parse XML document | |
89 | XMLPATCH(doc, doc2) | Patch XML document | |
90 | XMLPI(NAME identifier) | Get XML processing instruction | |
91 | XMLROOT(exp, VERSION exp2) | Create a new XML value | |
92 | XMLSEQUENCE(exp) | Get a varray of the top-level nodes | |
93 | XMLSERIALIZE(CONTENT exp AS datatype) | Get a serialized XML value | |
94 | XMLTRANSFORM(instance, exp) | Transform XML document |
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 | ENABLE | Constraint enabled 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 | PARALLEL (DEGREE n INSTANCES k) | Removed |
11 | INMEMORY | NO INMEMORY | 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 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 | 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 | t1 JOIN t2 USING (column_name) | USING clause for joins | t1 JOIN t2 USING (column_name) | |
2 | ROWNUM = 1 | Return 1 row only | LIMIT 1 | |
3 | ROWNUM <= n | Row limit | LIMIT n | |
ROWNUM < n | LIMIT n - 1 | |||
4 | FOR UPDATE | Lock selected rows | FOR UPDATE | |
5 | 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 triggers from Oracle to MySQL:
Oracle | MySQL | |||
1 | CREATE OR REPLACE TRIGGER | DROP TRIGGER IF EXISTS and CREATE TRIGGER | ||
2 | INSERT OR UPDATE OR DELETE | Multiple events in one trigger | Only one event in trigger ![]() |
|
3 | REFERENCING OLD AS old_name NEW AS new_name | Clause is not supported, OLD and NEW used | ||
4 | :NEW.colN, :OLD.colN | Referencing column values | NEW.colN, OLD.colN |
For more information, see a trigger conversion example and 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 |
5 | RAISE | Re-raise the current exception | RESIGNAL |
Converting SQL statements and clauses from Oracle to MySQL:
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 | DROP SEQUENCE | DropSequence stored procedure |
6 | 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 |