Migrate Oracle to SQLite
Certified — live-tested pathThe converter maps 15 Oracle column types to SQLite. 3 of them change kind on the way (NUMBER(19) becomes TEXT, DATE becomes TEXT, TIMESTAMP becomes TEXT); the table below lists every one. DBShifts converts the schema, copies the rows in parallel batches and checks the result with row counts and checksums.
Free tier · no credit card
Oracle vs SQLite
Oracle
Oracle Database, the long-standing enterprise standard.
SQLite
the embedded database that ships inside everything.
Oracle to SQLite data type mapping
Pulled directly from DBShifts's own conversion rules — not a general reference table, this is what actually runs.
| Oracle | SQLite |
|---|---|
| NUMBER(10) | INTEGER |
| NUMBER(19) | TEXT |
| NUMBER(10,2) | NUMERIC |
| VARCHAR2(255) | TEXT |
| NVARCHAR2(255) | TEXT |
| CHAR(10) | TEXT |
| CLOB | TEXT |
| NCLOB | TEXT |
| BLOB | BLOB |
| DATE | TEXT |
| TIMESTAMP | TEXT |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | REAL |
| RAW | BLOB |
| LONG | TEXT |
Example: Oracle to SQLite schema conversion
Oracle
CREATE TABLE orders ( id NUMBER(10) GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, customer_id NUMBER(10) NOT NULL, total NUMBER(10,2) NOT NULL, notes CLOB, reference VARCHAR2(255), placed_at TIMESTAMP NOT NULL );
SQLite
CREATE TABLE IF NOT EXISTS "orders" (
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"customer_id" INTEGER NOT NULL,
"total" NUMERIC NOT NULL,
"notes" TEXT,
"reference" TEXT,
"placed_at" TEXT NOT NULL
);A very large engine to a very small one
Oracle's feature set has no SQLite counterpart in almost every dimension that matters operationally: no PL/SQL, no packages, no partitioning, no materialised views, no roles or fine-grained access control, no parallel query. This is not a like-for-like migration and should not be planned as one.
It makes sense for a specific goal: an offline or embedded copy, a portable test fixture, or a single-file extract someone can inspect without Oracle access. For those, the right scope is usually a subset of tables with a subset of rows, not the whole database.
NUMBER, and precision you cannot keep
Oracle NUMBER supports 38 significant digits. SQLite stores numbers as 64-bit integers or 8-bit-exponent floats, which gives about 15-17 significant digits for anything non-integer. Values beyond that lose precision silently, because SQLite does not raise on precision loss.
For financial data the practical answer is to store the value as an integer count of minor units, or as text, and convert on read. Either preserves the exact value; a float does not.
Dates, and Oracle's DATE carrying a time
An Oracle DATE includes hours, minutes and seconds, unlike the DATE type in most other engines. Converting it to a date-only representation drops the time component without any error, which is one of the easier ways to lose data on this route.
SQLite has no temporal type, so the value becomes ISO-8601 text. Keeping the full timestamp in that text, rather than truncating to a day, is what preserves the original value.
Oracle → SQLite conversion notes
Engine-specific rules baked into the conversion and transfer pipeline — verified by live certification of this exact pair.
- NUMBER columns are fetched as exact decimals (38 digits), not lossy floats.
- Identifier case differences (Oracle uppercases unquoted names) are reconciled automatically during transfer.
- Empty string = NULL semantics are accounted for in validation so comparisons stay honest.
- High-precision decimals land in TEXT columns on purpose — SQLite's NUMERIC affinity would silently convert them to lossy REAL.
- Unsigned BIGINT values beyond the signed 64-bit range are stored as exact text rather than corrupted floats.
- Dates and times are stored as ISO-8601 text, the SQLite convention.
Frequently asked questions
Is Oracle to SQLite migration production-ready in DBShifts?
Yes. Oracle to SQLite is a certified pair: it passes the live certification suite on real Oracle and SQLite servers, which checks schema fidelity, row counts, type-aware checksums and paging across batches.
What happens to Oracle types that SQLite doesn't have?
Each gets the nearest SQLite type the converter treats as safe: NUMBER(19) becomes TEXT, DATE becomes TEXT, TIMESTAMP becomes TEXT. Every such decision is in the migration plan before anything runs, and any mapping can be overridden per column.
Can I keep Oracle and SQLite in sync after the initial migration?
Yes, until you cut over. DBShifts reads Flashback Version Query between SCNs, from an SCN captured before the first row is copied. A change has to still be in undo when it is read, so undo retention sets how long the stream can pause.
Which Oracle data types need a second look when moving to SQLite?
The table above carries a note on 3 of them: NUMBER(10) (INTEGER), CLOB (TEXT), RAW (BLOB). Analysis flags the same columns on your own schema before any data moves.
Go deeper
How We Live-Certified All 49 Migration Paths
The certification harness behind this page's claims.
What Is Database Migration? A Plain-English Guide
What a migration involves, and why it is harder than copying files.
Reference
Compared with other tools
Errors you may hit
Ready to move Oracle to SQLite?
Set up in under two minutes. Validation and rollback included.
Start Free Migration