Skip to content
IntoSQL home
Convert a file

Migration guides

Oracle to SQLite migration guide

Convert an Oracle export from SQL Developer or SQLcl into a SQLite script with the same tables, keys, indexes and data, and every rule the converter applies.

View as Markdown

IntoSQL reads the .sql file SQL Developer's Database Export or SQLcl writes, with DBMS_METADATA's table definitions and Insert into statements, and turns it into one SQLite script with the same tables, columns, keys, indexes, foreign keys, defaults, counters and rows. It evaluates what those exports write for values: to_date and to_timestamp with their format, TO_CLOB and HEXTORAW pieces, decimal commas from German or Spanish sessions. Every value SQLite can't hold as it is, and every object it doesn't carry, is listed in the report. The script loads on SQLite 3.31 and later.

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

Export for the best result

In SQL Developer, choose Tools, Database Export, with DDL and the Insert data format, as one file. SQLcl writes the same data with SET SQLFORMAT INSERT. Before exporting, set a date format that keeps the time and the century:

SQL
ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'SYYYY-MM-DD HH24:MI:SS.FF9';
  • The default format, DD-MON-RR, writes each date without its time and with two digits of the year; the report says so (oracle_date_format) and each date is read as its day, between 1950 and 2049.
  • A .dmp file from exp or expdp is binary. Import it into an Oracle database, Oracle Database Free runs in Docker, and export it with SQL Developer; the converter says so when it gets one (dump_oracle_binary).
  • Every test export comes from Oracle 21c and 23ai, in American, German and ISO sessions, and is compared with its source cell by cell.

Convert

In the browser, open Oracle to SQLite or the converter on the home page, drop the export and choose SQLite. 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.sql
intosql convert shop.sql --to sqlite --output shop.sqlite.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, an export converts like any other file: open a conversion with its name and "output": {"dialect": "sqlite"}.

Load into SQLite

Terminal
sqlite3 -bail shop.sqlite < shop.sqlite.sql

The script creates each table with its keys, copies the rows and adds indexes and counters, all in one transaction with syncing off. An identity column that is the whole primary key becomes INTEGER PRIMARY KEY AUTOINCREMENT and continues after the highest id. Dates are text in ISO 8601, with their time. The script ends with PRAGMA foreign_key_check, which lists any row that points to a missing row.

Or load it straight in

intosql load and the desktop app load the dump into SQLite without a script, with the same tables, keys and indexes the script creates:

Terminal
intosql load shop.sql --into shop.db

It stops before writing anything when one of the dump's tables already exists. It loads in one transaction, and a file the load created is removed if it fails. Loading into SQLite is free. See Loading.

What isn't converted

Views, packages, procedures, functions, triggers other than one that fills a column from a sequence, types and synonyms are written in PL/SQL. They are listed in the report by kind and name, never dropped silently, so you can recreate them in SQLite.

Data types

Oracle to SQLite 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 Oracle In SQLite Why Report line
collations that ignore case (BINARY_CI) exact comparison SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. case_insensitive_collation
CREATE SEQUENCE not created The export writes where a sequence has reached as its START WITH, so it continues from there. sequence_not_converted
virtual columns (GENERATED ALWAYS AS ... VIRTUAL) GENERATED ALWAYS AS, STORED when PERSISTED, VIRTUAL otherwise SQLite computes them from the same formula; the values the export wrote for them are left out. None
virtual columns with functions IntoSQL doesn't write, and the || operator a plain column A formula that can't be written for SQLite leaves a plain column and a report line; the values the export wrote for virtual columns are left out, and SQLite has none to compute. generated_not_translated
defaults with functions IntoSQL doesn't write (SYS_GUID(), ||) no default A default that can't be written is left out and reported. 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
CHECK constraints that are DISABLE or ENABLE NOVALIDATE not created Oracle doesn't apply these checks to the rows already there, so the rows may break them. check_not_enforced
function-based, bitmap and domain indexes not created IntoSQL writes b-tree indexes on whole columns; create the others again after loading. index_skipped
foreign keys in CREATE TABLE, loaded unchecked, then listed by PRAGMA foreign_key_check SQLite takes foreign keys only in CREATE TABLE and checks them only with PRAGMA foreign_keys on. The rows load unchecked and the script ends listing any row without a parent. None
foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones not created A key can't name a table that isn't there, and one Oracle doesn't check may have rows without a parent. foreign_key_skipped
partitioned tables one table with every partition's rows The rows are the same; partition the table again in SQLite if it needs it. partitions_dropped
several schemas in one export one file, tables named after their schema first A SQLite file is one database, so the schema name keeps tables with the same name apart; an export of one schema keeps its tables' names. schemas_merged
NOT NULL columns with values that become NULL NOT NULL dropped A column whose values had to become NULL can't keep NOT NULL, or the load would stop. not_null_relaxed
views, synonyms, types, packages, procedures, functions and other triggers listed in the report Their code is written in PL/SQL, so they are listed for you to recreate. not_converted_objects
exports without CREATE TABLE (data only) types read from the values A data-only export has only rows, so the types come from the values. no_schema
expressions in INSERT values other than the ones exports write NULL A converter that doesn't run a database can't evaluate them; to_date, to_timestamp, TO_CLOB, HEXTORAW and the others exports write are read. expression_value
text decoded twice (ñ) copied as it is The script holds exactly these characters, so they are copied; the report points them out. mojibake_suspected