Convert Oracle to SQLite
Drop the .sql file SQL Developer's Database Export or SQLcl writes, with its tables and Insert statements, and download a script for SQLite with the same tables, keys, indexes and data. Every value SQLite can't hold as it is, and every object it doesn't carry, is listed in the report.
18 rows converted, no fixes, none rejected
Tables in shop_2024_oracle.sqlOracle dump · written by SQL Developer · utf-8
customers8 rows
7 columns id INTEGER not null name VARCHAR(100) not null email VARCHAR(150) not null country VARCHAR(2) not null is_active INTEGER not null signed_up DATETIME created_at DATETIME not null orders10 rows
9 columns id INTEGER not null customer_id INTEGER not null invoice INTEGER status VARCHAR(10) not null total DECIMAL(10,2) not null items INTEGER not null ordered_at DATETIME not null shipped_at DATETIME note VARCHAR(255)
-- Written by IntoSQL from a Oracle dump. Load it with: sqlite3 -bail <database> < <file>
PRAGMA foreign_keys = OFF;
PRAGMA synchronous = OFF;
BEGIN;
CREATE TABLE customers ( -- People who bought at least once
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL,
country VARCHAR(2) DEFAULT 'US' NOT NULL,
is_active INTEGER DEFAULT 1 NOT NULL,
signed_up DATETIME,
created_at DATETIME DEFAULT (datetime('now', 'localtime')) NOT NULL,
CONSTRAINT customers_check1 CHECK (is_active IN (0, 1))
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER NOT NULL,
invoice INTEGER,
status VARCHAR(10) DEFAULT 'pending' NOT NULL,
total DECIMAL(10,2) NOT NULL,
items INTEGER DEFAULT 1 NOT NULL,
ordered_at DATETIME NOT NULL,
shipped_at DATETIME,
note VARCHAR(255),
CONSTRAINT orders_check1 CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
CONSTRAINT orders_customer_fk FOREIGN KEY (customer_id) REFERENCES customers (id)
);
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(1,'Ana Martínez','ana@example.com','MX',1,'2023-01-15 00:00:00','2023-01-15 10:24:00.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(2,'Zoë Müller','zoe@example.de','DE',1,'2023-02-03 00:00:00','2023-02-03 08:11:42.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(3,'José Peña','jose@example.es','ES',0,'2023-03-21 00:00:00','2023-03-21 19:05:17.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(4,'李雷','lilei@example.cn','CN',1,'2023-05-09 00:00:00','2023-05-09 02:40:00.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(5,'Noah O''Brien','noah@example.ie','IE',1,NULL,'2023-06-30 14:00:00.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(6,'Amélie Laurent','amelie@example.fr','FR',1,'2024-01-02 00:00:00','2024-01-02 09:30:00.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(7,'Ravi Patel','ravi@example.in','IN',0,'2024-02-29 00:00:00','2024-02-29 23:59:59.000000000');
INSERT INTO customers (id, name, email, country, is_active, signed_up, created_at) VALUES
(8,'Sofia Rossi 🌻','sofia@example.it','IT',1,'2024-04-12 00:00:00','2024-04-12 12:12:12.000000000');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(1,1,1000,'shipped',129.9,3,'2024-03-01 10:00:00','2024-03-02 16:30:00',NULL);
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(2,2,1001,'paid',54,1,'2024-03-02 11:15:00',NULL,'Gift wrap 🎁');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(3,1,1002,'cancelled',12.5,1,'2024-03-03 09:45:00',NULL,'Customer changed their mind');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(4,4,1003,'shipped',980,12,'2024-03-05 02:10:00','2024-03-07 08:00:00','请小心轻放');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(5,5,1004,'pending',45.75,2,'2024-03-08 17:20:00',NULL,'Leave at the door; ring twice');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(6,6,1005,'shipped',310.4,5,'2024-03-10 13:00:00','2024-03-11 09:00:00','Fragile: "glass"');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(7,8,1006,'paid',76.2,2,'2024-03-11 18:45:00',NULL,NULL);
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(8,2,1007,'paid',19.99,1,'2024-03-12 08:00:00',NULL,'Paid at the counter');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(9,3,1008,'shipped',64,2,'2024-03-14 15:30:00',NULL,'Shipping date never recorded');
INSERT INTO orders (id, customer_id, invoice, status, total, items, ordered_at, shipped_at, note) VALUES
(10,7,1009,'paid',1499,1,'2024-03-15 21:05:00',NULL,'C:\Users\ravi\invoice.pdf');
-- Indexes, triggers and counters, after the rows so each index builds once.
CREATE UNIQUE INDEX customers_customers_email_uk ON customers (email);
CREATE INDEX orders_orders_status_date_ix ON orders (status, ordered_at);
UPDATE sqlite_sequence SET seq = max(seq, 0) WHERE name = 'customers';
INSERT INTO sqlite_sequence (name, seq) SELECT 'customers', 0 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'customers');
UPDATE sqlite_sequence SET seq = max(seq, 0) WHERE name = 'orders';
INSERT INTO sqlite_sequence (name, seq) SELECT 'orders', 0 WHERE NOT EXISTS (SELECT 1 FROM sqlite_sequence WHERE name = 'orders');
COMMIT;
-- Rows whose foreign key finds no parent, if any: the load doesn't stop for them.
PRAGMA foreign_key_check;
Fixes
Nothing needed fixing.
Needs a look
- 1 sequence isn't created; SQLite has no sequences. Keep the next value in a table if your application needs it.
- The default of "invoice" in orders can't be written for SQLite (a sequence), so the column has no default. Add the default by hand if new rows need it.
Your file is converted on this computer and never uploaded.
Other samples:
What changes from Oracle to SQLite
Each row is a rule the converter applies, with the report line it writes when the rule changes something in your data.
BOOLEAN (23ai)
INTEGER 0 or 1 with a CHECK
SQLite has no boolean column, so TRUE and FALSE are 1 and 0.
VARCHAR2(n BYTE|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG
TEXT
Lengths count characters, whether Oracle counted bytes or characters, so every value fits.
NUMBER, NUMBER(p,s), FLOAT(b)
INTEGER, or NUMERIC kept exact as text past 15 digits
NUMBER keeps every digit; a NUMBER without precision whose values are all whole numbers of up to 18 digits is a 64-bit integer, and a negative scale holds as many digits before the point.
Report line: decimal_as_text
INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND
TEXT with Oracle's text, such as +01-06
SQLite has no interval type; the text keeps every part.
Report line: type_replaced
DATE, TIMESTAMP(p)
DATETIME as ISO 8601 text, every digit kept
Oracle's DATE holds a time to the second, so it is a date and time. An export in an American or German session writes dates without their time or century (DD-MON-RR); export in an ISO session (SYYYY-MM-DD HH24:MI:SS) to keep them.
TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP as ISO 8601 text in UTC
SQLite has no type that keeps an offset, so each moment is written in UTC: the same instant whatever offset it had.
Report line: moments_as_utc
JSON (21c), and CLOB holding JSON
TEXT
The same JSON values; a SQL NULL the export wrote as 'null' stays NULL.
BLOB, RAW(n), LONG RAW
BLOB
Bytes stay bytes.
ROWID, UROWID, XMLTYPE, SDO_GEOMETRY
TEXT
Oracle's own types have no equivalent; their values keep their text, ROWID values included, though they mean nothing off Oracle.
Report line: type_replaced
collations that ignore case (BINARY_CI)
exact comparison
SQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters.
Report line: case_insensitive_collation
identity columns, and a sequence a BEFORE INSERT trigger assigns
INTEGER PRIMARY KEY AUTOINCREMENT, continuing after the highest id
The copied ids load as they are, and the counter continues after the highest one. A column a trigger fills from a sequence (the way before 12c) is an identity too.
identity columns the target can't count
INTEGER without a counter
SQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs.
Report line: counter_dropped
CREATE SEQUENCE
not created
The export writes where a sequence has reached as its START WITH, so it continues from there.
Report line: 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.
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.
Report line: 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.
Report line: default_not_translated
CHECK constraints
translated
The same checks, tested as each row loads.
CHECK constraints with functions IntoSQL doesn't write
not created
A check that can't be written is left out and reported.
Report line: 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.
Report line: 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.
Report line: 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.
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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: 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.
Report line: expression_value
types IntoSQL doesn't know, and object types
TEXT
Text holds any value; change the type after loading.
Report line: unknown_type
dates before year 1 and time zones given as region names
NULL
A value that isn't a date becomes NULL and the report counts it.
Report line: date_out_of_range
NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE
Infinity as 9e999; NaN as NULL
SQLite reads 9e999 back as infinity and has no NaN.
NUL characters in text
kept
SQLite text can hold a NUL character, so the value is copied as it is.
text that isn't valid in its encoding
the bad bytes as the replacement character
The script is UTF-8 and every value has to be valid text.
Report line: invalid_utf8_replaced
text decoded twice (ñ)
copied as it is
The script holds exactly these characters, so they are copied; the report points them out.
Report line: mojibake_suspected
| In Oracle | In SQLite | Report line |
|---|---|---|
| BOOLEAN (23ai) | INTEGER 0 or 1 with a CHECKSQLite has no boolean column, so TRUE and FALSE are 1 and 0. | |
| VARCHAR2(n BYTE|CHAR), NVARCHAR2(n), CHAR(n), NCHAR(n), CLOB, NCLOB, LONG | TEXTLengths count characters, whether Oracle counted bytes or characters, so every value fits. | |
| NUMBER, NUMBER(p,s), FLOAT(b) | INTEGER, or NUMERIC kept exact as text past 15 digitsNUMBER keeps every digit; a NUMBER without precision whose values are all whole numbers of up to 18 digits is a 64-bit integer, and a negative scale holds as many digits before the point. | decimal_as_text |
| INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND | TEXT with Oracle's text, such as +01-06SQLite has no interval type; the text keeps every part. | type_replaced |
| DATE, TIMESTAMP(p) | DATETIME as ISO 8601 text, every digit keptOracle's DATE holds a time to the second, so it is a date and time. An export in an American or German session writes dates without their time or century (DD-MON-RR); export in an ISO session (SYYYY-MM-DD HH24:MI:SS) to keep them. | |
| TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP as ISO 8601 text in UTCSQLite has no type that keeps an offset, so each moment is written in UTC: the same instant whatever offset it had. | moments_as_utc |
| JSON (21c), and CLOB holding JSON | TEXTThe same JSON values; a SQL NULL the export wrote as 'null' stays NULL. | |
| BLOB, RAW(n), LONG RAW | BLOBBytes stay bytes. | |
| ROWID, UROWID, XMLTYPE, SDO_GEOMETRY | TEXTOracle's own types have no equivalent; their values keep their text, ROWID values included, though they mean nothing off Oracle. | type_replaced |
| collations that ignore case (BINARY_CI) | exact comparisonSQLite compares text exactly unless told otherwise, and its NOCASE only folds ASCII letters. | case_insensitive_collation |
| identity columns, and a sequence a BEFORE INSERT trigger assigns | INTEGER PRIMARY KEY AUTOINCREMENT, continuing after the highest idThe copied ids load as they are, and the counter continues after the highest one. A column a trigger fills from a sequence (the way before 12c) is an identity too. | |
| identity columns the target can't count | INTEGER without a counterSQLite only counts a column that is the whole primary key, so the column keeps its values and new rows need theirs. | counter_dropped |
| CREATE SEQUENCE | not createdThe 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 otherwiseSQLite computes them from the same formula; the values the export wrote for them are left out. | |
| virtual columns with functions IntoSQL doesn't write, and the || operator | a plain columnA 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 defaultA default that can't be written is left out and reported. | default_not_translated |
| CHECK constraints | translatedThe same checks, tested as each row loads. | |
| CHECK constraints with functions IntoSQL doesn't write | not createdA check that can't be written is left out and reported. | check_skipped |
| CHECK constraints that are DISABLE or ENABLE NOVALIDATE | not createdOracle 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 createdIntoSQL 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_checkSQLite 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. | |
| foreign keys to tables the export doesn't create, and DISABLE or NOVALIDATE ones | not createdA 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 rowsThe 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 firstA 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 droppedA 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 reportTheir 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 valuesA 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 | NULLA 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 |
| types IntoSQL doesn't know, and object types | TEXTText holds any value; change the type after loading. | unknown_type |
| dates before year 1 and time zones given as region names | NULLA value that isn't a date becomes NULL and the report counts it. | date_out_of_range |
| NaN and Infinity in BINARY_FLOAT and BINARY_DOUBLE | Infinity as 9e999; NaN as NULLSQLite reads 9e999 back as infinity and has no NaN. | |
| NUL characters in text | keptSQLite text can hold a NUL character, so the value is copied as it is. | |
| text that isn't valid in its encoding | the bad bytes as the replacement characterThe script is UTF-8 and every value has to be valid text. | invalid_utf8_replaced |
| text decoded twice (ñ) | copied as it isThe script holds exactly these characters, so they are copied; the report points them out. | mojibake_suspected |
Load the script into SQLite
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.
sqlite3 -bail mydb.sqlite < oracle.sqlite.sqlForeign keys are declared in each table and the rows load with foreign key checks off. The script then lists any row that points to a row that doesn't exist, instead of failing half way.
For a large dump, load into a new file on a local disk. The script loads everything in one transaction with syncing off, so it runs at disk speed, and a load that stops half way leaves no partial tables.
The command line loads the file straight into a SQLite database, without writing a script. It's free.
Dump Oracle for the best result
The converter reads what the dump holds. These options keep more of your data in it:
- In SQL Developer, choose Tools, Database Export, with DDL and the Insert data format. SQLcl writes the same with SET SQLFORMAT INSERT.
- Run ALTER SESSION SET NLS_DATE_FORMAT = 'SYYYY-MM-DD HH24:MI:SS' before exporting: the default format writes dates without their time or century.
- A .dmp file from exp or expdp is binary: import it into an Oracle database, Oracle Database Free in Docker for one, and export it with SQL Developer.
What isn't converted
Views, packages, procedures, functions, triggers, types and synonyms are written in PL/SQL. They are listed in the report by name, never dropped silently, so you can rewrite them for SQLite.
Questions about Oracle to SQLite
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.
Can I convert a .dmp file from exp or expdp?
Not directly: Data Pump and the original Export write a binary format only Oracle reads. Import it into any Oracle database, Oracle Database Free runs in Docker, and export it with SQL Developer's Database Export or SQLcl. The converter says so when it gets a .dmp file.
Which Oracle versions can the export come from?
Exports from SQL Developer and SQLcl, with DBMS_METADATA's table definitions, from Oracle 11g on. Every test export comes from Oracle 21c and 23ai, in American, German and ISO sessions, and is compared with its source cell by cell.
What happens to NUMBER, DATE and the other Oracle types?
NUMBER becomes INTEGER for whole numbers and keeps every digit otherwise; DATE becomes DATETIME text with its time; CLOB TEXT, BLOB and RAW BLOB. Infinity is kept; NaN becomes NULL.
Are identity columns and sequences kept?
An identity column that is the table's whole primary key becomes INTEGER PRIMARY KEY AUTOINCREMENT and continues after the highest id. SQLite has no sequences, so they are reported.
What about PL/SQL packages, procedures and triggers?
They are listed in the report by name and not converted, because each database writes them in its own language.
Which SQLite versions does the script load on?
SQLite 3.31 and later. Every test export loads on 3.31 and 3.54, stopping at the first error if anything goes wrong.