Convert mysqldump to Oracle

Working from a mysqldump file

mysqldump writes a plain-text .sql file with a very consistent shape, which makes it one of the easiest dumps to read: its own syntax announces the dialect, and its rows are inline rather than in a separate archive.

• →Every mysqldump file contains ENGINE=InnoDB or AUTO_INCREMENT, which is exactly what the dialect detection looks for, so backtick-quoted identifiers and MySQL types parse without you choosing anything.
• →mysqldump packs many rows into one extended INSERT by default, and each tuple in it becomes a row, so there is no need to dump again with --skip-extended-insert.
• →The file is decoded as UTF-8 with unreadable bytes replaced, so a legacy latin1 dump arrives with damaged accented text. Dump with --default-character-set=utf8mb4, or run the file through iconv first.
• →mysqldump --tab is not a dump at all: the schema goes to a .sql file and the rows to headerless tab-separated .txt files. Neither half is convertible on its own, and the data files have no header row to name their columns, so dump again without --tab.

Oracle-specific edges in the script

Oracle diverges further from ordinary SQL than any other target here, and the script is shaped around that. Three of its choices are worth reading before you load, because each is a place where the source can hold something Oracle has no room for.

• →Oracle's reserved list is full of words that legacy tables use as column names, so those get a trailing underscore: a column called DATE becomes DATE_, NUMBER becomes NUMBER_, LEVEL becomes LEVEL_.
• →Binary values are written as HEXTORAW('...'), which a plain SQL script can carry only up to 2000 hex characters - 1000 bytes. Anything larger needs PL/SQL chunking that no portable script can express, so a table of real attachments moves better through the SQLite or DuckDB target.
• →A string column of unknown length is declared VARCHAR2(4000) and anything longer becomes a CLOB. Under the default settings VARCHAR2 counts bytes rather than characters, so 4000 characters of Cyrillic or CJK will not fit in one.
• →Rows are batched a hundred to a statement, each block closed off with SELECT 1 FROM DUAL, so a 100,000-row table arrives as a thousand statements rather than a hundred thousand.

Reading mysqldump

• →Parsed with a real SQL parser, not regular expressions, so a value containing a closing parenthesis or a semicolon cannot split a row in half.
• →CREATE TABLE types, primary keys, NOT NULL and AUTO_INCREMENT are all read from the dump.
• →The source dialect is detected from the dump itself — MySQL, PostgreSQL and SQL Server dumps all parse.

Writing Oracle

• →Uses INSERT ALL rather than multi-row VALUES, which Oracle rejects outright — the most common reason a generated Oracle script fails to load.
• →Dates are written with TO_DATE and an explicit format mask.
• →Booleans become NUMBER(1), because Oracle had no BOOLEAN column type before 23c.
• →Identifiers are capped at 30 characters so the script loads on Oracle 12.1 and earlier too.
• →Note that Oracle itself treats an empty string as NULL — that is Oracle behaviour, not a conversion error.

Convert mysqldump to something else

• MySQL
• PostgreSQL
• SQL Server
• SQLite
• DuckDB
• CSV
• Excel

Convert something else to Oracle

• CSV
• Excel
• SQLite
• DBF
• JSON
• XML
• Parquet
• DuckDB
• MS Access
• PostgreSQL backup