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:
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 (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 ![]() |
Namespaces:
Oracle | MySQL | |
Tables, views, procedures, functions, packages, sequences | Share the same namespace | Tables, views |
Constraints, indexes, triggers | Have own namespace | Constraints, indexes, triggers |
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 character data types:
Oracle | MySQL | |||
1 | CHAR(n) | Fixed-length string, 1 ⇐ n ⇐ 2000 | CHAR(n) | 0 ⇐ n ⇐ 255 ![]() |
2 | NVARCHAR2(n) | Variable-length Unicode string, 1 ⇐ n ⇐ 32,767 ![]() | NVARCHAR(n) | 0 ⇐ n ⇐ 65,535 ![]() |
3 | VARCHAR(n) | Synonym for VARCHAR2 | VARCHAR(n) | |
4 | VARCHAR2(n) | Variable-length string, 1 ⇐ n ⇐ 32,767 ![]() | VARCHAR(n) | 0 ⇐ n ⇐ 65,535 ![]() |
Converting numeric data types:
Oracle | MySQL | |||
1 | BINARY_FLOAT | 32-bit floating-point number | FLOAT | |
2 | BINARY_DOUBLE | 64-bit floating-point number | DOUBLE | |
3 | DECIMAL(p,s) | Fixed-point number | DECIMAL(p,s) | |
4 | DOUBLE PRECISION | Floating-point number | DOUBLE PRECISION | |
5 | FLOAT(p) | Floating-point number | DOUBLE | |
6 | INTEGER, INT | 38 digits integer | INT | DECIMAL(38) |
7 | 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) | |||
8 | NUMBER(p,s) | Fixed-point number, s > 0 | DECIMAL(p,s) | |
9 | NUMBER, NUMBER(*) | Floating-point number | DECIMAL(p,s) | DOUBLE ![]() |
10 | NUMERIC(p,s) | Fixed-point number | NUMERIC(p,s) | |
11 | REAL | Floating-point number | DOUBLE | |
12 | SMALLINT | 38 digits integer | DECIMAL(38) |
Converting date and time data types:
Oracle | MySQL | |||
1 | DATE | Date and time | DATETIME | |
2 | INTERVAL YEAR(p) TO MONTH | Date interval | VARCHAR(30) | |
3 | INTERVAL DAY(p) TO SECOND(s) | Date and time interval | VARCHAR(30) | |
4 | TIMESTAMP(p) | Date and time with fraction, 0 ⇐ p ⇐ 9, default is 6 | DATETIME(p), 0 ⇐ p ⇐ 6 ![]() |
|
5 | TIMESTAMP(p) WITH TIME ZONE | Date and time with fraction and time zone | DATETIME(p) ![]() |
Converting other data types:
Oracle | MySQL | |||
1 | BFILE | Pointer to binary file, ⇐ 4G | VARCHAR(255) | |
2 | BLOB | Binary large object, ⇐ 4G | LONGBLOB | |
3 | CLOB | Character large object, ⇐ 4G | LONGTEXT | |
4 | LONG | Character data, ⇐ 2G | LONGTEXT | |
5 | LONG RAW | Binary data, ⇐ 2G | LONGBLOB | |
6 | NCHAR(n) | Fixed-length UTF-8 string, 1 ⇐ n ⇐ 255 | NCHAR(n) | |
7 | NCHAR(n) | Fixed-length UTF-8 string, 256 ⇐ n ⇐ 2000 | NVARCHAR(n) | |
8 | NCHAR VARYING(n) | Varying-length UTF-8 string, 1 ⇐ n ⇐ 4000 | NCHAR VARYING(n) | |
9 | NCLOB | Variable-length Unicode string, ⇐ 4G | LONGTEXT | |
10 | RAW(n) | Variable-length binary string, 1 ⇐ n ⇐ 2000 | VARBINARY(n) | |
11 | ROWID | Physical row address | CHAR(10) | |
12 | UROWID(n) | Logical row addresses, 1 ⇐ n ⇐ 4000 | VARCHAR(n) | |
13 | XMLTYPE | XML data | LONGTEXT |
Converting string functions:
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 ![]() |
2 | SYSDATE | Get current date and time | SYSDATE() | Includes time |
3 | SYSTIMESTAMP | Get current datetime | NOW(6) |
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 other functions:
Oracle | MySQL | ||
1 | SYS_GUID() | Generate UUID | UUID() ![]() |
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 |
31 | LEAST(exp, exp2, …) | Get the minimum value in a set | LEAST(exp, exp2, …) |
32 | LENGTHB(string) | Get length of string in bytes | LENGTH(string) ![]() |
33 | LN(num) | Get natural logarithm of num | LN(num) |
34 | LOCALTIMESTAMP | Get the current date and time | LOCALTIMESTAMP |
LOCALTIMESTAMP([prec]) | LOCALTIMESTAMP() | ||
35 | LOG(num1, num2) | Get logarithm, base num1, of num2 | LOG(num1, num2) |
36 | LOWER(string) | Lowercase string | LOWER(string) |
37 | MOD(dividend, divisor) | Get remainder | MOD(dividend, divisor) |
38 | NEXT_DAY | Get the next date by day name | NEXT_DAY user-defined function |
39 | NULLIF(exp1, exp2) | Return NULL if exp1 = exp2 | NULLIF(exp1, exp2) |
40 | NVL2(exp1, exp2, exp3) | Return exp2 if exp1 is not NULL, otherwise exp3 | CASE expression |
41 | REMAINDER(n1, n2) | Get remainder | (n1 - n2*ROUND(n1/n2)) |
42 | ROUND(num, integer) | Get rounded value | ROUND(num, integer) |
43 | SIGN(exp) | Get sign of exp | SIGN(exp) |
44 | SIN(num) | Get sine | SIN(num) |
45 | SINH(num) | Get hyperbolic sine | (EXP(num) - EXP(-num)) / 2 |
46 | SOUNDEX(string) | Get 4-character sound code | SOUNDEX(string) |
47 | SQRT(num) | Get square root | SQRT(num) |
48 | TAN(num) | Get tangent | TAN(num) |
49 | TANH(num) | Get hyperbolic tangent | (EXP(2*num) - 1)/(EXP(2*num) + 1) |
50 | TO_DATE(string, format) | Convert string to datetime | STR_TO_DATE(string, format) ![]() |
51 | TO_LOB(exp) | Convert to LOB | |
52 | TO_NCHAR(exp) | Convert to NCHAR | |
53 | TO_NCLOB(exp) | Convert to NCLOB | |
54 | TO_NUMBER(exp) | Convert to NUMBER | |
55 | TO_SINGLE_BYTE(exp) | Convert to single-byte character | |
56 | TO_TIMESTAMP(exp) | Convert to TIMESTAMP | |
57 | TRIM([type trim FROM] string) | Remove characters | TRIM([type trim FROM] string) |
58 | TRUNC(num) | Truncate num | TRUNCATE(num, 0) |
TRUNC(num, num2) | TRUNCATE(num, num2) | ||
59 | TRUNC(datetime) | Truncate datetime | DATE(datetime), DATE_FORMAT |
60 | UNISTR(string) | Convert Unicode code points to chars | CHAR(string USING UCS2) ![]() |
61 | UPPER(string) | Uppercase string | UPPER(string) |
62 | USER | Get the current user | USER() |
63 | USERENV('parameter') | Get the current session information | |
64 | VSIZE(exp) | Get the size of exp in bytes | |
65 | XMLAGG(exp) | Get a aggregated XML document | |
66 | XMLCAST(exp AS datatype) | Convert exp to datatype | |
67 | XMLCDATA(exp) | Generate a CDATA section | |
68 | XMLCOMMENT(exp) | Generate an XML comment | |
69 | XMLCONCAT(exp, exp2, …) | Concatenate XML expressions | |
70 | XMLDIFF(doc, doc2) | Compare two XML documents | |
71 | XMLELEMENT(NAME element) | Get an XQuery element node | |
72 | XMLFOREST(exp, exp2, …) | Get a forest of XML expressions | |
73 | XMLISVALID(exp) | Check XML exp | |
74 | XMLPARSE(DOCUMENT exp) | Parse XML document | |
75 | XMLPATCH(doc, doc2) | Patch XML document | |
76 | XMLPI(NAME identifier) | Get XML processing instruction | |
77 | XMLROOT(exp, VERSION exp2) | Create a new XML value | |
78 | XMLSEQUENCE(exp) | Get a varray of the top-level nodes | |
79 | XMLSERIALIZE(CONTENT exp AS datatype) | Get a serialized XML value | |
80 | XMLTRANSFORM(instance, exp) | Transform XML document |
Converting queries:
Oracle | MySQL | |||
1 | SELECT * FROM (SELECT ...) | Subquery alias is optional | SELECT * FROM (SELECT ...) t | Alias required ![]() |
2 | t1 JOIN t2 USING (column_name) | USING clause for joins | t1 JOIN t2 USING (column_name) | |
3 | ORDER BY col NULLS FIRST | LAST | Order of NULLs | ORDER BY col ![]() |
Row limitation:
Oracle | MySQL | |||
1 | OFFSET k FETCH FIRST n ROWS ONLY | Row limiting | LIMIT k, n | |
2 | ROWNUM = 1 | Return 1 row only | LIMIT 1 | |
ROWNUM <= n | Row limit | LIMIT n | ||
ROWNUM < n | LIMIT n - 1 | |||
ROWNUM < (10+2) | Arbitrary expression | Only integer is allowed ![]() |
Other clauses:
Oracle | MySQL | |||
1 | FOR UPDATE | Lock selected rows | FOR UPDATE | |
2 | SKIP LOCKED | Skip locked rows | Not supported by MySQL, commented |
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 | |
4 | col AS (exp) | Computed column | type col AS (exp) | Data type must be specified ![]() |
Temporary tables:
Oracle | MySQL | ||
1 | CREATE GLOBAL TEMPORARY TABLE | Global temporary table | CREATE TEMPORARY TABLE ![]() |
2 | schema.name | Qualified object name | db_name.name |
Partitioned tables:
Oracle | MySQL | ||
1 | PARTITION BY RANGE | Range partitions | PARTITION BY RANGE ![]() |
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 | Clause not supported, 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 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:
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 | DELIMITER // DROP TRIGGER IF EXISTS and CREATE TRIGGER |
||
2 | BEFORE | AFTER | INSTEAD OF ![]() | BEFORE | AFTER | ||
3 | INSERT OR UPDATE OR DELETE | Multiple events in one trigger | Only one event in trigger ![]() |
|
4 | ON table | ON table | ||
5 | REFERENCING OLD AS old_name NEW AS new_name | Clause is not supported, OLD and NEW used | ||
6 | :NEW.col, :OLD.col | Referencing column values | NEW.col, OLD.col | |
7 | FOR EACH ROW | Row-level trigger | FOR EACH ROW | |
8 | BEGIN stmt; … END; / | BEGIN stmt; … END; // DELIMITER ; |
For more information, see a trigger conversion example and Conversion of PL/SQL Statements.
Converting PL/SQL statements and clauses from Oracle to MySQL:
Executing stored procedures from a PL/SQL block:
Oracle | MySQL | ||
1 | sp_name(param1, ...) | Execute procedure | CALL sp_name(param1, ...) |
sp_name | Procedure without parameters | CALL sp_name |
Error handling:
Oracle | MySQL | ||
1 | RAISE_APPLICATION_ERROR(-20xxx, text) | Raise user error | SIGNAL SQLSTATE '45xxx' SET MESSAGE_TEXT text |
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 |
Table manipulation statements:
Oracle | MySQL | |||
1 | ALTER TABLE ... ADD FOREIGN KEY | Add foreign key | ALTER TABLE ... ADD FOREIGN KEY Data type issues ![]() |
|
2 | ALTER TABLE ... ADD UNIQUE ... USING INDEX | Add unique constraint with existing index | Index commented ![]() |
|
3 | ALTER TABLE ... MODIFY (coldef, ...) | Modify column(s) | ALTER TABLE ... MODIFY coldef, ... ![]() |
Converting SQL statements and clauses:
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 | DELETE [FROM] table | Delete rows | DELETE FROM table | FROM keyword is required ![]() |
6 | DROP SEQUENCE | DropSequence stored procedure | ||
7 | LOCK TABLE name IN EXCLUSIVE MODE | LOCK TABLES name WRITE | ||
LOCK TABLE name IN SHARE MODE | LOCK TABLES name READ |
System and Control statements:
Oracle | MySQL | |||
1 | ALTER USER | Alter user | ALTER USER ![]() |
|
2 | CREATE USER | Create user | CREATE USER ![]() |
|
3 | GRANT | Grant privileges | GRANT ![]() |
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 |
Oracle features that may require significant re-design when migrating to MySQL: