Skip to content
IntoSQL home
Convert a file

Data types

SQLite to Oracle data types

Every SQLite data type that Oracle stores differently, what the converter writes for it, when, and the report line.

View as Markdown

Each row is a type from SQLite that Oracle stores differently: what the Oracle script gets, why, and the line the report writes about it. The rows come from the converter's own rules, so this page matches what it does.

Convert in the browser: SQLite to Oracle opens the converter set up for this pair. The SQLite to Oracle migration guide covers dumping, loading and the rules for keys, indexes and names.

A report line is a fix when the converter changed something and lists it, or a problem when a value or object is worth a look.

Numbers

In SQLite In Oracle Why Report line
DECIMAL(p,s), NUMERIC the exact type when every value fits it, else a double SQLite stores these as integers or doubles; a declared precision holds only when the values fit it. None

Booleans and bits

In SQLite In Oracle Why Report line
BOOLEAN, BOOL NUMBER(1) with a CHECK of 0 and 1 When every value is 0 or 1; a column that also holds other values keeps them as numbers or text. None

Text

In SQLite In Oracle Why Report line
TEXT, VARCHAR(n), CHAR(n), CLOB VARCHAR2(n CHAR) when every value fits, else CLOB SQLite never enforces a length; a declared length holds only when every value fits it. None
TEXT and VARCHAR(n) longer than 4000 bytes VARCHAR2(4000 CHAR) when every value fits 4000 bytes (750 characters in a key), else CLOB A VARCHAR2 holds 4000 bytes by default and Oracle can't index a CLOB, so text keeps VARCHAR2 when its values fit and is a CLOB when they don't. text_as_lob (fix)
empty strings and empty blobs NULL Oracle stores an empty string as NULL, so a value that was ' reads back as NULL. The report counts them per column. empty_string_null (problem)
NUL characters in text kept The sqlite3 shell and Python cut text at a NUL character when they dump it, so only what precedes it is in the dump. None
text that isn't valid UTF-8 the bad bytes as the replacement character Dumps write such text as raw bytes; the script is UTF-8. invalid_utf8_replaced (problem)

Dates and times

In SQLite In Oracle Why Report line
DATE, DATETIME, TIMESTAMP holding ISO 8601 text DATE or TIMESTAMP(p), by what the values hold When every value is a date or a date and time; epoch numbers and other text keep their values as they are. None

Binary

In SQLite In Oracle Why Report line
BLOB, and columns holding bytes BLOB Bytes stay bytes; text and numbers in the same column are kept as their bytes. None

JSON, spatial and other types

In SQLite In Oracle Why Report line
declared types (any text: VARCHAR(255), DATETIME, BOOLEAN, JSON or none) the type every value in the column fits SQLite keeps any value in any column, so the declared type is a hint: each column gets the type all of its values fit, and a column holding mixed values keeps their text. None
JSON holding JSON text CLOB When every value is a JSON object or array. None
Infinity in REAL columns kept as BINARY_DOUBLE_INFINITY Oracle's doubles hold infinity. None

Counters and identities

In SQLite In Oracle Why Report line
INTEGER PRIMARY KEY, AUTOINCREMENT GENERATED BY DEFAULT ON NULL AS IDENTITY, continuing after the highest id or sqlite_sequence's Only a column declared exactly INTEGER PRIMARY KEY is the rowid; every row keeps its id, and the counter starts after the highest id ever handed out. None

Other pairs