Convert MySQL to Oracle
Drop a dump from mysqldump, mariadb-dump, mysqlpump, phpMyAdmin or Adminer, as .sql or .sql.gz, and download a script for Oracle with the same tables, keys, indexes and data. Every value Oracle can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, 4 fixes, none rejected
Tables in shop_2024.sqlMySQL 8.0.46 dump · written by mysqldump · utf8mb4
customerswritten as CUSTOMERS8 rows2
7 columns ID NUMBER(10) not null NAME VARCHAR2(100 CHAR) not null EMAIL VARCHAR2(150 CHAR) not null COUNTRY VARCHAR2(2 CHAR) not null IS_ACTIVE NUMBER(1) not null SIGNED_UP DATE CREATED_AT TIMESTAMP(0) WITH TIME ZONE not null orderswritten as ORDERS10 rows2
9 columns ID NUMBER(10) not null CUSTOMER_ID NUMBER(10) not null STATUS VARCHAR2(9 CHAR) TOTAL NUMBER(10,2) not null ITEMS NUMBER(5) not null ORDERED_AT TIMESTAMP(0) not null SHIPPED_AT TIMESTAMP(0) NOTE VARCHAR2(255 CHAR) UPDATED_AT TIMESTAMP(0) WITH TIME ZONE not null
-- Written by IntoSQL from a MySQL dump. Load it with: NLS_LANG=.AL32UTF8 sqlplus -S -L <user>/<password>@<database> @<file>
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
SET DEFINE OFF;
SET SQLBLANKLINES ON;
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION "INTOSQL_CLOB" (p SYS.ODCIVARCHAR2LIST) RETURN CLOB IS
r CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(r, TRUE);
FOR i IN 1 .. p.COUNT LOOP
IF p(i) IS NOT NULL THEN
DBMS_LOB.WRITEAPPEND(r, LENGTH2(p(i)), p(i));
END IF;
END LOOP;
RETURN r;
END;
/
CREATE OR REPLACE FUNCTION "INTOSQL_BLOB" (p SYS.ODCIVARCHAR2LIST) RETURN BLOB IS
r BLOB;
piece RAW(2000);
BEGIN
DBMS_LOB.CREATETEMPORARY(r, TRUE);
FOR i IN 1 .. p.COUNT LOOP
piece := HEXTORAW(p(i));
DBMS_LOB.WRITEAPPEND(r, UTL_RAW.LENGTH(piece), piece);
END LOOP;
RETURN r;
END;
/
CREATE TABLE "CUSTOMERS" (
"ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1001),
"NAME" VARCHAR2(100 CHAR) NOT NULL,
"EMAIL" VARCHAR2(150 CHAR) NOT NULL,
"COUNTRY" VARCHAR2(2 CHAR) DEFAULT 'US' NOT NULL,
"IS_ACTIVE" NUMBER(1) DEFAULT 1 NOT NULL,
"SIGNED_UP" DATE,
"CREATED_AT" TIMESTAMP(0) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT "CK_CUSTOMERS_ID" CHECK ("ID" >= 0),
CONSTRAINT "CK_CUSTOMERS_IS_ACTIVE" CHECK ("IS_ACTIVE" IN (0, 1))
);
CREATE TABLE "ORDERS" (
"ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 5001),
"CUSTOMER_ID" NUMBER(10) NOT NULL,
"STATUS" VARCHAR2(9 CHAR) DEFAULT 'pending',
"TOTAL" NUMBER(10,2) NOT NULL,
"ITEMS" NUMBER(5) DEFAULT 1 NOT NULL,
"ORDERED_AT" TIMESTAMP(0) NOT NULL,
"SHIPPED_AT" TIMESTAMP(0),
"NOTE" VARCHAR2(255 CHAR),
"UPDATED_AT" TIMESTAMP(0) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
CONSTRAINT "CK_ORDERS_ID" CHECK ("ID" >= 0),
CONSTRAINT "CK_ORDERS_CUSTOMER_ID" CHECK ("CUSTOMER_ID" >= 0),
CONSTRAINT "CK_ORDERS_STATUS" CHECK ("STATUS" IN ('pending', 'paid', 'shipped', 'cancelled')),
CONSTRAINT "CK_ORDERS_ITEMS" CHECK ("ITEMS" >= 0)
);
BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
1,
'Ana Martínez',
'ana@example.com',
'MX',
1,
DATE '2023-01-15',
TIMESTAMP '2023-01-15 10:24:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
2,
'Zoë Müller',
'zoe@example.de',
'DE',
1,
DATE '2023-02-03',
TIMESTAMP '2023-02-03 08:11:42 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
3,
'José Peña',
'jose@example.es',
'ES',
0,
DATE '2023-03-21',
TIMESTAMP '2023-03-21 19:05:17 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
4,
'李雷',
'lilei@example.cn',
'CN',
1,
DATE '2023-05-09',
TIMESTAMP '2023-05-09 02:40:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
5,
'Noah O''Brien',
'noah@example.ie',
'IE',
1,
NULL,
TIMESTAMP '2023-06-30 14:00:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
6,
'Amélie Laurent',
'amelie@example.fr',
'FR',
1,
DATE '2024-01-02',
TIMESTAMP '2024-01-02 09:30:00 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
7,
'Ravi Patel',
'ravi@example.in',
'IN',
0,
DATE '2024-02-29',
TIMESTAMP '2024-02-29 23:59:59 +00:00'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT"
) VALUES (
8,
'Sofia Rossi 🌻',
'sofia@example.it',
'IT',
1,
DATE '2024-04-12',
TIMESTAMP '2024-04-12 12:12:12 +00:00'
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
1,
1,
'shipped',
129.90,
3,
TIMESTAMP '2024-03-01 10:00:00',
TIMESTAMP '2024-03-02 16:30:00',
NULL,
TIMESTAMP '2024-03-02 16:30:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
2,
2,
'paid',
54.00,
1,
TIMESTAMP '2024-03-02 11:15:00',
NULL,
'Gift wrap 🎁',
TIMESTAMP '2024-03-02 11:20:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
3,
1,
'cancelled',
12.50,
1,
TIMESTAMP '2024-03-03 09:45:00',
NULL,
'Customer changed their mind',
TIMESTAMP '2024-03-03 10:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
4,
4,
'shipped',
980.00,
12,
TIMESTAMP '2024-03-05 02:10:00',
TIMESTAMP '2024-03-07 08:00:00',
'请小心轻放',
TIMESTAMP '2024-03-07 08:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
5,
5,
'pending',
45.75,
2,
TIMESTAMP '2024-03-08 17:20:00',
NULL,
'Leave at the door; ring twice',
TIMESTAMP '2024-03-08 17:20:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
6,
6,
'shipped',
310.40,
5,
TIMESTAMP '2024-03-10 13:00:00',
TIMESTAMP '2024-03-11 09:00:00',
'Fragile: "glass"',
TIMESTAMP '2024-03-11 09:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
7,
8,
'paid',
76.20,
2,
TIMESTAMP '2024-03-11 18:45:00',
NULL,
NULL,
TIMESTAMP '2024-03-11 18:46:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
8,
2,
NULL,
19.99,
1,
TIMESTAMP '2024-03-12 08:00:00',
NULL,
'Status lost in an old import',
TIMESTAMP '2024-03-12 08:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
9,
3,
'shipped',
64.00,
2,
TIMESTAMP '2024-03-14 15:30:00',
NULL,
'Shipping date never recorded',
TIMESTAMP '2024-03-15 10:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"UPDATED_AT"
) VALUES (
10,
7,
'paid',
1499.00,
1,
TIMESTAMP '2024-03-15 21:05:00',
NULL,
'C:\Users\ravi\invoice.pdf',
TIMESTAMP '2024-03-15 21:05:00 +00:00'
);
COMMIT;
END;
/
-- Keys, indexes, foreign keys, counters and triggers, after the rows.
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "UQ_CUSTOMERS_UQ_EMAIL" UNIQUE ("EMAIL");
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ID");
CREATE INDEX "IX_ORDERS_IDX_CUSTOMER" ON "ORDERS" ("CUSTOMER_ID");
CREATE INDEX "IX_ORDERS_IDX_STATUS_DATE" ON "ORDERS" ("STATUS", "ORDERED_AT");
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_ORDERS_CUSTOMER" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ENABLE NOVALIDATE;
BEGIN
EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" ENABLE VALIDATE CONSTRAINT "FK_ORDERS_CUSTOMER"';
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Foreign key ' || '"FK_ORDERS_CUSTOMER"' || ' has rows without a parent: it stays unvalidated, and new rows are checked.');
END;
/
DECLARE
next_id NUMBER;
BEGIN
SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "CUSTOMERS";
IF next_id < 1001 THEN
next_id := 1001;
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE "CUSTOMERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
COMMENT ON TABLE "CUSTOMERS" IS 'People who bought at least once';
DECLARE
next_id NUMBER;
BEGIN
SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "ORDERS";
IF next_id < 5001 THEN
next_id := 5001;
END IF;
EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
CREATE OR REPLACE TRIGGER "TR_ORDERS_UPDATED_AT" BEFORE UPDATE ON "ORDERS" FOR EACH ROW
BEGIN
IF NOT UPDATING('UPDATED_AT') THEN
:NEW."UPDATED_AT" := SYSTIMESTAMP;
END IF;
END;
/
DROP FUNCTION "INTOSQL_CLOB";
DROP FUNCTION "INTOSQL_BLOB";
COMMIT;
EXIT;
Fixes
- "signed_up" of customers allows NULL now: some of its values couldn't be stored and are NULL.
- The default of "signed_up" in customers, 0000‑00‑00, isn't a date Oracle accepts, so the column has no default.
- "status" of orders allows NULL now: some of its values couldn't be stored and are NULL.
- "updated_at" of orders changes to the current time on every update; a trigger does that in Oracle.
Needs a look
- 1 value in "signed_up" of customers is a zero or impossible date such as 0000‑00‑00, which Oracle can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
- 1 value in "shipped_at" of orders is a zero or impossible date such as 0000‑00‑00 00:00:00, which Oracle can't store, so it is NULL. If those dates mean something, replace them in the source with real ones before dumping.
- 1 empty value in "status" of orders became NULL; Oracle stores an empty string as NULL. Where queries compare this column with ', compare with IS NULL instead.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from MySQL to Oracle
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
TINYINT(1), BOOL
NUMBER(1) with a CHECK of 0 and 1 when every value is 0 or 1, else NUMBER(3)
MySQL's BOOL is a TINYINT(1) that can hold any number from -128 to 127. Oracle before 23ai has no boolean column, so a column of 0 and 1 is NUMBER(1) with a check; one that holds 2 or -1 keeps its numbers.
Report line: bool_kept_numeric
UNSIGNED integers
NUMBER wide enough for the range, with a CHECK that it isn't negative
Oracle's NUMBER holds any integer of the widths MySQL has; the check keeps negative values out as MySQL did.
ZEROFILL and display widths
dropped
Zero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters.
Report line: zerofill_dropped
FLOAT(M,D)
BINARY_FLOAT or BINARY_DOUBLE, without the scale
Oracle's binary floats have no fixed scale. The stored values are copied as they are.
Report line: float_scale_dropped
DECIMAL(p,s)
NUMBER(p,s) up to 38 digits; VARCHAR2 beyond
Oracle's NUMBER holds 38 digits. Wider decimals are kept as text so every digit stays exact.
Report line: decimal_as_text
BIT(1), BIT(n)
NUMBER(1), NUMBER(19) up to 63 bits, NUMBER(20) for BIT(64)
Bit fields are stored as the number their bits make.
zero, partial or impossible dates (0000-00-00, 2024-02-30)
NULL
Oracle only stores real calendar dates. MySQL keeps zero and impossible dates when its SQL mode allows them; they become NULL and the report counts them.
Report line: invalid_date_null
NOT NULL columns with values that become NULL
NOT NULL dropped
A column whose values had to become NULL, including empty strings, can't keep NOT NULL, or the load would stop.
Report line: not_null_relaxed
empty strings and empty binary values
NULL
Oracle stores an empty string as NULL, so a value that was ' reads back as NULL. The report counts them per column.
Report line: empty_string_null
DEFAULT '0000-00-00'
no default
A default Oracle can't store is left out rather than replaced with a made-up date.
Report line: zero_date_default
TIME
INTERVAL DAY TO SECOND, which holds durations
Oracle has no time-of-day type, and MySQL's TIME is a duration from -838:59:59 to 838:59:59; an interval holds every value.
YEAR
NUMBER(4) with a CHECK of its range
Oracle has no year type; the check keeps the same values MySQL allows.
CHAR(n), VARCHAR(n)
VARCHAR2(n CHAR), counting characters
Lengths count characters as MySQL's do, and MySQL returns CHAR values without trailing spaces, as VARCHAR2 keeps them.
VARCHAR(n) and TEXT longer than 4000 bytes
CLOB
A VARCHAR2 holds 4000 bytes by default, so longer text is a CLOB; a column whose values all fit keeps VARCHAR2.
Report line: text_as_lob
DATETIME
TIMESTAMP
A DATETIME is a wall-clock reading without a time zone, like TIMESTAMP. DATE becomes Oracle's DATE, which also holds a time of day that stays at midnight.
TIMESTAMP
TIMESTAMP WITH TIME ZONE in the zone the dump states, UTC when it states none
A TIMESTAMP is a moment. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so.
Report line: timestamp_zone_assumed
ENUM
VARCHAR2 with a CHECK of its values
Text with a check keeps the same values.
ENUM holding '' (MySQL's error value)
NULL
MySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Oracle stores it as NULL, which the check allows, and the report counts it with the other empty values.
ENUM holding values outside its list
no CHECK
Values the list doesn't name would fail the check, so the check isn't created.
Report line: enum_check_skipped
SET
VARCHAR2 as MySQL writes it ('a,b')
Oracle has no set type; the text is the one MySQL returns.
JSON
CLOB
Oracle's JSON functions read JSON from text on every version, and a CLOB keeps values of any size.
NUL bytes in text
kept
VARCHAR2 and CLOB can hold a NUL character, so the value is copied as it is.
text that isn't valid in its character set
the bad bytes as the replacement character
Text has to be read as characters to be written as VARCHAR2.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The dump holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
AUTO_INCREMENT
GENERATED BY DEFAULT ON NULL AS IDENTITY, restarted past the copied ids
The copied ids load as they are, and the counter continues after the highest one or the dump's AUTO_INCREMENT, whichever is higher.
MyISAM AUTO_INCREMENT on the second column of a key
one counter for the table
MyISAM counts per group of the key's first column; Oracle has one counter per table.
Report line: myisam_group_autoincrement
ON UPDATE CURRENT_TIMESTAMP
a BEFORE UPDATE trigger
The trigger sets the column to the current time when an update doesn't set it.
Report line: on_update_trigger
generated columns
virtual columns
Oracle computes generated columns on read only; STORED columns become virtual with the same values.
Report line: stored_as_virtual
generated columns with functions Oracle lacks
a plain column
A formula that can't be written for Oracle leaves a plain column and a report line.
Report line: generated_not_translated
INVISIBLE columns
INVISIBLE columns
Oracle hides invisible columns from SELECT * as MySQL does.
CHECK constraints
translated, inline in CREATE TABLE
Inline checks are tested as each row loads.
CHECK constraints with functions Oracle lacks
not created
A check that can't be written for Oracle is left out and reported.
Report line: check_skipped
CHECK ... NOT ENFORCED
not created
MySQL keeps these checks without applying them, so the rows may break them.
Report line: check_not_enforced
defaults with functions Oracle lacks
no default
A default that can't be written for Oracle is left out and reported.
Report line: default_not_translated
keys and indexes on a prefix, c(20)
an index on SUBSTR of the column
A function index on the same start of the column keeps the same rows apart, and indexes CLOB columns Oracle can't index whole.
Report line: prefix_unique
FULLTEXT indexes
not created
Oracle Text builds full-text search with its own index type and queries.
Report line: fulltext_not_created
SPATIAL indexes
not created
Geometries are stored as WKB bytes, which a spatial index can't read.
Report line: spatial_index_not_created
functional indexes, and indexes on whole CLOB or BLOB columns
not created
Oracle can't index a large object whole, and some MySQL functions have no Oracle equivalent.
Report line: index_skipped
foreign keys
after the data ENABLE NOVALIDATE, then validated with a warning on orphans
Dumps load with FOREIGN_KEY_CHECKS=0 and MyISAM never checks, so rows without a parent are common. The key checks new rows at once and is validated last; orphans leave it unvalidated with a warning instead of stopping the load.
ON UPDATE CASCADE, SET NULL or SET DEFAULT; ON DELETE SET DEFAULT
dropped
Oracle's foreign keys act on delete only, with CASCADE or SET NULL.
Report line: foreign_key_action_unsupported
foreign keys to columns without a unique key
not created
Oracle needs a primary key or a unique constraint on the referenced columns; InnoDB doesn't.
Report line: foreign_key_skipped
table and column names
uppercase when they are plain names, else as written
Oracle stores names written without quotes in uppercase, so plain names become uppercase and queries can leave out the quotes; other names keep their case and need quotes.
Report line: identifier_renamed
names over 128 bytes
shortened
Oracle 12.2 and later names hold 128 bytes.
Report line: name_shortened
GEOMETRY, POINT, POLYGON...
BLOB holding WKB
WKB is the standard form every spatial library reads; SDO_GEOMETRY needs Oracle Spatial's metadata.
Report line: geometry_as_blob
INET4, INET6, UUID (MariaDB)
VARCHAR2(39), VARCHAR2(36)
Oracle has neither type; the text is the one MariaDB returns.
BINARY, VARBINARY, BLOB
RAW up to 2000 bytes, else BLOB
Bytes stay bytes.
PARTITION BY
one table
The rows are the same; partition the table again in Oracle if it needs it.
Report line: partitions_dropped
WITH SYSTEM VERSIONING (MariaDB)
a table with the current rows
A dump holds the current rows only.
Report line: system_versioning_dropped
MyISAM, MEMORY and other engines
a regular table
Oracle has one table engine.
Report line: engine_dropped
views, triggers, procedures, functions and events
listed in the report
Their code is written in MySQL's language, so they are listed for you to recreate.
Report line: not_converted_objects
dumps without CREATE TABLE
types read from the values
A dump made with --no-create-info only has rows, so the types come from the values.
Report line: no_schema
geometries in a dump without CREATE TABLE
BLOB holding WKB
MySQL's geometry format is recognised from the values and stored as standard WKB.
Report line: geometry_inferred
_ci collations
exact comparison
Oracle compares text exactly unless told otherwise.
Report line: case_insensitive_collation
SET NAMES utf8 from utf8mb4 tables
converted, with a warning
MySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file.
Report line: dump_lost_four_byte_chars
dumps without SET NAMES whose text isn't UTF-8
read as Latin-1 (Windows-1252)
MySQL's latin1 is Windows-1252, the usual encoding of such dumps.
Report line: charset_guessed
sjis, cp932, gbk, big5 dumps
read as UTF-8, with a warning
These encodings are not decoded yet.
Report line: unsupported_charset
UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows
evaluated
Some dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first.
other expressions in rows
NULL
A converter that doesn't run a database can't evaluate them.
Report line: expression_value
DEFAULT in VALUES
the column's default value
A literal default is copied into the row.
Report line: default_keyword_filled
DEFAULT in VALUES without a literal default
NULL
There is no value to copy.
Report line: default_keyword_null
REPLACE and INSERT IGNORE
plain rows
A dump has one row per key, so the rows are the same.
Report line: replace_as_insert
types IntoSQL doesn't know
CLOB
Text holds any value; change the type after loading.
Report line: unknown_type
values that don't fit their column
NULL
A damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads.
Report line: invalid_value_null
unreadable geometries
NULL
Bytes that aren't a geometry can't be stored as one.
Report line: bad_geometry
dumps of several databases
one schema, each table named after its database first
The script loads into the schema of the user who runs it, so tables of different databases get their database's name in front.
Report line: databases_merged
CREATE SEQUENCE (MariaDB)
CREATE SEQUENCE, at the value it had reached
Oracle sequences work the same way.
VECTOR(n) (MariaDB 11.7+)
CLOB with the floats as a JSON array
Only Oracle 23ai has a vector type; a JSON array keeps every float on every version.
Report line: type_replaced
| In MySQL | In Oracle | Report line |
|---|---|---|
| TINYINT(1), BOOL | NUMBER(1) with a CHECK of 0 and 1 when every value is 0 or 1, else NUMBER(3)MySQL's BOOL is a TINYINT(1) that can hold any number from -128 to 127. Oracle before 23ai has no boolean column, so a column of 0 and 1 is NUMBER(1) with a check; one that holds 2 or -1 keeps its numbers. | bool_kept_numeric |
| UNSIGNED integers | NUMBER wide enough for the range, with a CHECK that it isn't negativeOracle's NUMBER holds any integer of the widths MySQL has; the check keeps negative values out as MySQL did. | |
| ZEROFILL and display widths | droppedZero padding is a way of showing a number, not part of it. The numbers are kept; format them when you read them if the padding matters. | zerofill_dropped |
| FLOAT(M,D) | BINARY_FLOAT or BINARY_DOUBLE, without the scaleOracle's binary floats have no fixed scale. The stored values are copied as they are. | float_scale_dropped |
| DECIMAL(p,s) | NUMBER(p,s) up to 38 digits; VARCHAR2 beyondOracle's NUMBER holds 38 digits. Wider decimals are kept as text so every digit stays exact. | decimal_as_text |
| BIT(1), BIT(n) | NUMBER(1), NUMBER(19) up to 63 bits, NUMBER(20) for BIT(64)Bit fields are stored as the number their bits make. | |
| zero, partial or impossible dates (0000-00-00, 2024-02-30) | NULLOracle only stores real calendar dates. MySQL keeps zero and impossible dates when its SQL mode allows them; they become NULL and the report counts them. | invalid_date_null |
| NOT NULL columns with values that become NULL | NOT NULL droppedA column whose values had to become NULL, including empty strings, can't keep NOT NULL, or the load would stop. | not_null_relaxed |
| empty strings and empty binary values | NULLOracle stores an empty string as NULL, so a value that was ' reads back as NULL. The report counts them per column. | empty_string_null |
| DEFAULT '0000-00-00' | no defaultA default Oracle can't store is left out rather than replaced with a made-up date. | zero_date_default |
| TIME | INTERVAL DAY TO SECOND, which holds durationsOracle has no time-of-day type, and MySQL's TIME is a duration from -838:59:59 to 838:59:59; an interval holds every value. | |
| YEAR | NUMBER(4) with a CHECK of its rangeOracle has no year type; the check keeps the same values MySQL allows. | |
| CHAR(n), VARCHAR(n) | VARCHAR2(n CHAR), counting charactersLengths count characters as MySQL's do, and MySQL returns CHAR values without trailing spaces, as VARCHAR2 keeps them. | |
| VARCHAR(n) and TEXT longer than 4000 bytes | CLOBA VARCHAR2 holds 4000 bytes by default, so longer text is a CLOB; a column whose values all fit keeps VARCHAR2. | text_as_lob |
| DATETIME | TIMESTAMPA DATETIME is a wall-clock reading without a time zone, like TIMESTAMP. DATE becomes Oracle's DATE, which also holds a time of day that stays at midnight. | |
| TIMESTAMP | TIMESTAMP WITH TIME ZONE in the zone the dump states, UTC when it states noneA TIMESTAMP is a moment. Dumps state the zone they wrote it in (mysqldump writes UTC); without that, the values are read as UTC and the report says so. | timestamp_zone_assumed |
| ENUM | VARCHAR2 with a CHECK of its valuesText with a check keeps the same values. | |
| ENUM holding '' (MySQL's error value) | NULLMySQL stores an empty string when an invalid value goes into an ENUM in a lenient SQL mode. Oracle stores it as NULL, which the check allows, and the report counts it with the other empty values. | |
| ENUM holding values outside its list | no CHECKValues the list doesn't name would fail the check, so the check isn't created. | enum_check_skipped |
| SET | VARCHAR2 as MySQL writes it ('a,b')Oracle has no set type; the text is the one MySQL returns. | |
| JSON | CLOBOracle's JSON functions read JSON from text on every version, and a CLOB keeps values of any size. | |
| NUL bytes in text | keptVARCHAR2 and CLOB can hold a NUL character, so the value is copied as it is. | |
| text that isn't valid in its character set | the bad bytes as the replacement characterText has to be read as characters to be written as VARCHAR2. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe dump holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
| AUTO_INCREMENT | GENERATED BY DEFAULT ON NULL AS IDENTITY, restarted past the copied idsThe copied ids load as they are, and the counter continues after the highest one or the dump's AUTO_INCREMENT, whichever is higher. | |
| MyISAM AUTO_INCREMENT on the second column of a key | one counter for the tableMyISAM counts per group of the key's first column; Oracle has one counter per table. | myisam_group_autoincrement |
| ON UPDATE CURRENT_TIMESTAMP | a BEFORE UPDATE triggerThe trigger sets the column to the current time when an update doesn't set it. | on_update_trigger |
| generated columns | virtual columnsOracle computes generated columns on read only; STORED columns become virtual with the same values. | stored_as_virtual |
| generated columns with functions Oracle lacks | a plain columnA formula that can't be written for Oracle leaves a plain column and a report line. | generated_not_translated |
| INVISIBLE columns | INVISIBLE columnsOracle hides invisible columns from SELECT * as MySQL does. | |
| CHECK constraints | translated, inline in CREATE TABLEInline checks are tested as each row loads. | |
| CHECK constraints with functions Oracle lacks | not createdA check that can't be written for Oracle is left out and reported. | check_skipped |
| CHECK ... NOT ENFORCED | not createdMySQL keeps these checks without applying them, so the rows may break them. | check_not_enforced |
| defaults with functions Oracle lacks | no defaultA default that can't be written for Oracle is left out and reported. | default_not_translated |
| keys and indexes on a prefix, c(20) | an index on SUBSTR of the columnA function index on the same start of the column keeps the same rows apart, and indexes CLOB columns Oracle can't index whole. | prefix_unique |
| FULLTEXT indexes | not createdOracle Text builds full-text search with its own index type and queries. | fulltext_not_created |
| SPATIAL indexes | not createdGeometries are stored as WKB bytes, which a spatial index can't read. | spatial_index_not_created |
| functional indexes, and indexes on whole CLOB or BLOB columns | not createdOracle can't index a large object whole, and some MySQL functions have no Oracle equivalent. | index_skipped |
| foreign keys | after the data ENABLE NOVALIDATE, then validated with a warning on orphansDumps load with FOREIGN_KEY_CHECKS=0 and MyISAM never checks, so rows without a parent are common. The key checks new rows at once and is validated last; orphans leave it unvalidated with a warning instead of stopping the load. | |
| ON UPDATE CASCADE, SET NULL or SET DEFAULT; ON DELETE SET DEFAULT | droppedOracle's foreign keys act on delete only, with CASCADE or SET NULL. | foreign_key_action_unsupported |
| foreign keys to columns without a unique key | not createdOracle needs a primary key or a unique constraint on the referenced columns; InnoDB doesn't. | foreign_key_skipped |
| table and column names | uppercase when they are plain names, else as writtenOracle stores names written without quotes in uppercase, so plain names become uppercase and queries can leave out the quotes; other names keep their case and need quotes. | identifier_renamed |
| names over 128 bytes | shortenedOracle 12.2 and later names hold 128 bytes. | name_shortened |
| GEOMETRY, POINT, POLYGON... | BLOB holding WKBWKB is the standard form every spatial library reads; SDO_GEOMETRY needs Oracle Spatial's metadata. | geometry_as_blob |
| INET4, INET6, UUID (MariaDB) | VARCHAR2(39), VARCHAR2(36)Oracle has neither type; the text is the one MariaDB returns. | |
| BINARY, VARBINARY, BLOB | RAW up to 2000 bytes, else BLOBBytes stay bytes. | |
| PARTITION BY | one tableThe rows are the same; partition the table again in Oracle if it needs it. | partitions_dropped |
| WITH SYSTEM VERSIONING (MariaDB) | a table with the current rowsA dump holds the current rows only. | system_versioning_dropped |
| MyISAM, MEMORY and other engines | a regular tableOracle has one table engine. | engine_dropped |
| views, triggers, procedures, functions and events | listed in the reportTheir code is written in MySQL's language, so they are listed for you to recreate. | not_converted_objects |
| dumps without CREATE TABLE | types read from the valuesA dump made with --no-create-info only has rows, so the types come from the values. | no_schema |
| geometries in a dump without CREATE TABLE | BLOB holding WKBMySQL's geometry format is recognised from the values and stored as standard WKB. | geometry_inferred |
| _ci collations | exact comparisonOracle compares text exactly unless told otherwise. | case_insensitive_collation |
| SET NAMES utf8 from utf8mb4 tables | converted, with a warningMySQL 5.x mysqldump writes 3-byte utf8 by default and replaces every emoji with a question mark before any converter sees the file. | dump_lost_four_byte_chars |
| dumps without SET NAMES whose text isn't UTF-8 | read as Latin-1 (Windows-1252)MySQL's latin1 is Windows-1252, the usual encoding of such dumps. | charset_guessed |
| sjis, cp932, gbk, big5 dumps | read as UTF-8, with a warningThese encodings are not decoded yet. | unsupported_charset |
| UNHEX('..'), CONVERT(b'..', UNSIGNED), CONVERT(".." USING x), ST_GeomFromText('..') in rows | evaluatedSome dump tools write values as these calls; they are read as the values they produce, with geographic WKT turned to longitude first. | |
| other expressions in rows | NULLA converter that doesn't run a database can't evaluate them. | expression_value |
| DEFAULT in VALUES | the column's default valueA literal default is copied into the row. | default_keyword_filled |
| DEFAULT in VALUES without a literal default | NULLThere is no value to copy. | default_keyword_null |
| REPLACE and INSERT IGNORE | plain rowsA dump has one row per key, so the rows are the same. | replace_as_insert |
| types IntoSQL doesn't know | CLOBText holds any value; change the type after loading. | unknown_type |
| values that don't fit their column | NULLA damaged or hand-written dump can hold values its column can't; they become NULL so the rest loads. | invalid_value_null |
| unreadable geometries | NULLBytes that aren't a geometry can't be stored as one. | bad_geometry |
| dumps of several databases | one schema, each table named after its database firstThe script loads into the schema of the user who runs it, so tables of different databases get their database's name in front. | databases_merged |
| CREATE SEQUENCE (MariaDB) | CREATE SEQUENCE, at the value it had reachedOracle sequences work the same way. | |
| VECTOR(n) (MariaDB 11.7+) | CLOB with the floats as a JSON arrayOnly Oracle 23ai has a vector type; a JSON array keeps every float on every version. | type_replaced |
Load the script into Oracle
The script creates the tables, copies the rows, then adds keys, indexes and foreign keys, so it loads fast and stops at the first error.
NLS_LANG=.AL32UTF8 sqlplus -S -L user@//localhost/mydb @mysql.oracle.sqlForeign keys are added ENABLE NOVALIDATE, so new rows are checked at once, and then validated. If an old row points to a row that doesn't exist, the script says which constraint and leaves it unvalidated, instead of failing half way.
For a large dump, run SQL*Plus on the same machine as the database. The script commits every 100 rows. The user that runs it needs CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER and CREATE PROCEDURE, and quota on its tablespace.
Dump MySQL for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- --default-character-set=utf8mb4 keeps emoji and other 4-byte characters, which a utf8 dump loses before the converter sees it.
- --hex-blob writes binary columns as hexadecimal, which survives copying between systems.
- --routines --triggers --events add those objects to the dump, so the report can list them.
What isn't converted
Views, triggers, stored procedures, functions and events are written in each database's own language. They are listed in the report by name, never dropped silently, so you can rewrite them for Oracle.
Questions about MySQL to Oracle
Not answered here? Write to us from the contact page and a person replies. Contact page
Is my dump uploaded?
No. The converter runs in your browser and reads the dump from your disk in pieces, so it never leaves your computer. The desktop app and the command line convert on your computer too.
How large can the dump be?
The converter never holds the whole dump in memory: it reads the file twice, by ranges, and writes the script as it goes. Multi-gigabyte dumps convert in the browser, in the desktop app and with the command line.
Does it read MariaDB dumps?
Yes. Dumps from mariadb-dump and from MariaDB 10.3 to 12 convert the same way, including MariaDB's INET6, INET4 and UUID columns.
What happens to 0000-00-00 dates?
Oracle has no zero date, so they become NULL and the column accepts NULL. The report says how many there were in each column. Empty strings become NULL too, as Oracle stores them.
Are AUTO_INCREMENT values kept?
Yes. Each AUTO_INCREMENT column becomes an identity column, every row keeps its id, and the next id continues after the highest one, or after the table's AUTO_INCREMENT if that is higher.
Does TINYINT(1) become boolean?
Oracle before 23ai has no boolean column. A column whose values are all 0 or 1 becomes NUMBER(1) with a check that keeps it that way; one that holds other numbers keeps them, and the report says why.
What about stored procedures and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language. ON UPDATE CURRENT_TIMESTAMP is the exception: it becomes a trigger in Oracle.
Which Oracle versions does the script load on?
Oracle 19c and later. Every test dump loads on 21c and 23ai, stopping at the first error if anything goes wrong.