Skip to content
IntoSQL home
Convert a file

Migration guides

SQLite to Oracle migration guide

Convert a SQLite database or dump into an Oracle script with the same tables, keys, indexes and data: how to dump, convert, load, and every rule the converter applies.

View as Markdown

IntoSQL reads a SQLite database file, or the SQL that SQLite's .dump writes, and turns it into one Oracle script with the same tables, columns, keys, indexes, foreign keys, defaults, counters and rows. SQLite keeps any value in any column, so the declared type is a hint: each column gets the Oracle type all of its values fit, and a column holding mixed values keeps their text. Every value Oracle can't hold as it is, and every object it doesn't carry, is listed in the report. The script loads on Oracle 19c and later.

Convert in the browser: SQLite to Oracle opens the converter set up for this pair.

The file or a dump

Drop the database file itself (.db, .sqlite, .sqlite3): it is read page by page, in UTF-8 or UTF-16, and text with NUL characters and every digit of a double arrive as stored. Close the app that writes to it first, so no change is left behind in its -wal file.

A dump converts too, as .sql or .sql.gz:

Terminal
sqlite3 shop.db .dump > shop.sql
  • DB Browser for SQLite writes the same SQL with File, Export, Database to SQL file, and so does Python's iterdump().
  • .dump --nosys converts too. A --data-only dump has no CREATE TABLE, so its columns are named column_1, column_2 and so on, and the types come from the values.
  • Every test file and dump comes from sqlite3 3.31 to 3.54, with STRICT and WITHOUT ROWID tables, and is compared with its source cell by cell.

Convert

In the browser, open SQLite to Oracle or the converter on the home page, drop the file and choose Oracle. It reads the file from your disk in pieces, twice, and writes the script as it goes, so the file is never uploaded and its size isn't limited by memory. The desktop app does the same with the file on disk.

With the command line:

Terminal
intosql inspect shop.db
intosql convert shop.db --to oracle --output shop.oracle.sql --report shop.report.json

inspect lists the tables, their rows and what won't be converted, without writing anything. convert writes the script and, with --report, the report as JSON.

Through the API, a database file or a dump converts like any other file: open a conversion with its name and "output": {"dialect": "oracle"}.

Load into Oracle

Terminal
NLS_LANG=.AL32UTF8 sqlplus -S -L shop@//localhost/FREEPDB1 @shop.oracle.sql

NLS_LANG makes SQL*Plus read the script as UTF-8. The user needs CREATE TABLE, CREATE SEQUENCE, CREATE TRIGGER and CREATE PROCEDURE, and quota on its tablespace. The script stops at the first error, creates the tables, copies the rows in blocks of 100 with a commit after each, then adds keys, indexes, foreign keys and identities. Text is VARCHAR2 when every value fits 4000 bytes (750 characters in a key) and CLOB otherwise (text_as_lob). Infinite doubles stay infinite as BINARY_DOUBLE_INFINITY.

Oracle stores an empty string as NULL, so values that were '' read back as NULL; the report counts them per column (empty_string_null). Plain names become uppercase (customers is CUSTOMERS), so queries can leave the quotes out.

What isn't converted

Views and triggers are written in SQLite's own language. They are listed in the report by kind and name, never dropped silently, so you can recreate them in Oracle. Virtual tables, such as FTS5 and R*Tree, are listed too, and their shadow tables are left out.

Data types

SQLite to Oracle data types lists each type that changes in the script: what it becomes, why, and the report line.

Every other rule

Generated from the engine, where each rule is decided.

In SQLite In Oracle Why Report line
COLLATE NOCASE exact comparison Oracle compares text exactly unless told otherwise; NOCASE columns lose ignoring case. case_insensitive_collation
generated columns virtual columns Oracle computes generated columns on read only; STORED columns become virtual with the same values. stored_as_virtual
generated columns with functions IntoSQL doesn't write, and the || operator a plain column A formula that can't be written leaves a plain column and a report line; the dump has no values for generated columns. generated_not_translated
defaults with functions IntoSQL doesn't write (strftime, randomblob) no default A default that can't be written is left out and reported; CURRENT_TIMESTAMP and datetime('now') are written. default_not_translated
CHECK constraints translated The same checks, tested as each row loads. None
CHECK constraints with functions IntoSQL doesn't write not created A check that can't be written is left out and reported. check_skipped
partial and expression indexes not created IntoSQL writes indexes on whole columns; create the others again after loading. index_skipped
foreign keys (unenforced unless PRAGMA foreign_keys=ON) after the data ENABLE NOVALIDATE, then validated SQLite checks foreign keys only when told to, so rows without a parent are common: they load, and the key is left unchecked with a warning instead of stopping the load. None
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. foreign_key_action_unsupported
foreign keys to tables the dump doesn't create not created A key can't name a table that isn't there. foreign_key_skipped
primary keys holding NULL, and NOT NULL columns with values that become NULL NOT NULL dropped SQLite lets a primary key other than the rowid hold NULL; the column can't be NOT NULL in the target, or the load would stop. not_null_relaxed
views, triggers and virtual tables (FTS, R*Tree) listed in the report Their code is written in SQLite's own language, and virtual tables need SQLite's modules; they are listed for you to recreate. Their internal tables aren't copied. not_converted_objects
dumps without CREATE TABLE (--data-only) types read from the values A data-only dump has only rows, so the types come from the values and the columns are numbered. no_schema
expressions in INSERT values other than the text functions dumps write NULL unistr(), replace(), char() and || are evaluated as the shell writes them; other expressions can't be without a database. expression_value
text decoded twice (ñ) copied as it is The dump holds exactly these characters, so they are copied; the report points them out. mojibake_suspected