Oracle Database – Data Types

Oracle's type list is short, which is good, but several of the types behave differently from the same-named type in every other database. Getting these wrong is cheap to fix on day one and expensive to fix once there are 200 million rows in the table.

Strings

VARCHAR exists as a synonym for VARCHAR2, and Oracle's own documentation asks you not to use it because they reserve the right to change its semantics. Follow that advice; you will look like you know the platform.

CHAR is a trap

CREATE TABLE t (a CHAR(10), b VARCHAR2(10));
INSERT INTO t VALUES ('abc', 'abc');

SELECT length(a), length(b) FROM t;    -- 10, 3   -- a was padded
SELECT * FROM t WHERE b = 'abc';       -- 1 row
SELECT * FROM t WHERE b = a;           -- 0 rows  -- 'abc       ' <> 'abc'

The last comparison is VARCHAR2 vs CHAR, so Oracle uses non-padded semantics and the trailing spaces make it unequal. Two CHAR columns would have compared blank-padded and matched. Avoid the whole question: use VARCHAR2.

CHAR vs BYTE length

This one is genuinely important with UTF-8. VARCHAR2(10) means 10 bytes by default, and in AL32UTF8 a single emoji or CJK character can take four. So a 10-byte column holds two or three characters, and the insert fails with ORA-12899: value too large for column on data that looks well within the limit.

CREATE TABLE t (
  bytes_col VARCHAR2(10 BYTE),   -- 10 bytes  (the default)
  chars_col VARCHAR2(10 CHAR)    -- 10 characters, whatever they cost
);

INSERT INTO t (chars_col) VALUES ('日本語テキストです');   -- 9 chars, 27 bytes: fine
INSERT INTO t (bytes_col) VALUES ('日本語テキストです');   -- ORA-12899

Always write CHAR explicitly on text columns. You can flip the session or database default with NLS_LENGTH_SEMANTICS, but relying on a session setting means the same DDL behaves differently depending on who ran it. Be explicit in the DDL.

The ceiling is 4000 bytes, or 32767 if the DBA has set MAX_STRING_SIZE = EXTENDED. Past that, CLOB.

Numbers

NUMBER is a variable-length decimal — exact, up to 38 significant digits, and the default for everything.

NUMBER            -- whatever you give it, up to 38 digits
NUMBER(10)        -- integer, 10 digits          -> Java int/long
NUMBER(19)        -- big integer                 -> Java long
NUMBER(12,2)      -- money: 10 before the point, 2 after
NUMBER(*, 2)      -- max precision, scale 2
NUMBER(3, -3)     -- rounds to thousands: 12345 stores as 12000

Because NUMBER is decimal, not binary, 0.1 + 0.2 = 0.3 is exactly true — unlike FLOAT, BINARY_FLOAT and BINARY_DOUBLE, which are IEEE 754 and should be used only for scientific data where the speed matters more than the last digit. Never store money in a binary float.

There is no INT type, though INTEGER is accepted as an alias for NUMBER(38). Prefer an explicit precision — it documents intent and it is what makes JDBC hand you an int or a long instead of a BigDecimal.

Dates and times

The single most surprising thing in Oracle: DATE includes a time, down to the second. There is no date-only type.

SELECT to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
-- 2024-02-05 14:22:47      <- a DATE, with a time in it

Which is why this comparison drops most of a day's rows:

-- Broken: matches only rows stamped exactly midnight
SELECT * FROM orders WHERE order_date = DATE '2024-02-05';

-- Correct: a half-open range, and it can still use an index
SELECT * FROM orders
WHERE  order_date >= DATE '2024-02-05'
AND    order_date <  DATE '2024-02-06';

-- Also correct, but TRUNC() disables a plain index on order_date
SELECT * FROM orders WHERE trunc(order_date) = DATE '2024-02-05';

For an audit column recording "when did this happen, globally", use TIMESTAMP WITH TIME ZONE and store UTC. It is unambiguous, it survives a server moving region, and it maps cleanly to OffsetDateTime.

Useful functions, and note that two of them do not mean what the name suggests:

SYSDATE          -- DATE, in the DATABASE SERVER's time zone
SYSTIMESTAMP     -- TIMESTAMP WITH TIME ZONE, server zone
CURRENT_DATE     -- DATE, in the SESSION's time zone
CURRENT_TIMESTAMP-- TIMESTAMP WITH TIME ZONE, session zone

SELECT sysdate + 1                          FROM dual;  -- tomorrow: DATE + n is n days
SELECT sysdate + INTERVAL '90' MINUTE       FROM dual;
SELECT add_months(sysdate, -3)              FROM dual;  -- handles month lengths
SELECT months_between(sysdate, hire_date)   FROM dual;
SELECT last_day(sysdate), trunc(sysdate, 'MM') FROM dual;
SELECT extract(YEAR FROM sysdate)           FROM dual;

Subtracting two DATEs gives a number of days as a fraction — so (end_date - start_date) * 24 is hours. Subtracting two TIMESTAMPs gives an INTERVAL instead. Different types, different arithmetic; mixing them is a common source of confusion.

Large objects

CREATE TABLE documents (
  id        NUMBER(19) GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  body      CLOB,          -- character data, unlimited
  thumbnail BLOB,          -- binary
  meta      JSON           -- native JSON type, 21c and later
);

LOBs are stored out of line once they get big, which means an extra I/O to read one. Do not put a CLOB in a table you scan constantly if you rarely need the column — or at least never write SELECT * against it. LONG and LONG RAW still exist for backward compatibility; treat them as removed.

Booleans

Oracle had no SQL BOOLEAN until 23ai. PL/SQL always had one, SQL did not, so everything before 23ai uses a stand-in:

-- 23ai and later
is_active BOOLEAN DEFAULT FALSE NOT NULL

-- 19c and earlier — pick one and use it everywhere
is_active NUMBER(1)  DEFAULT 0   NOT NULL CHECK (is_active IN (0, 1))
is_active CHAR(1)    DEFAULT 'N' NOT NULL CHECK (is_active IN ('Y', 'N'))

NUMBER(1) is the friendlier of the two for JDBC and JPA — Hibernate maps a boolean field onto it with no converter. The CHECK constraint is what stops the column quietly accumulating a third value.

The empty string

Not a type, but it belongs here because it surprises everyone:

INSERT INTO t (b) VALUES ('');
SELECT count(*) FROM t WHERE b = '';      -- 0
SELECT count(*) FROM t WHERE b IS NULL;   -- 1

An empty string is stored as NULL. There is no way to distinguish "present but blank" from "absent" in a VARCHAR2. Oracle documents this as behaviour that may change one day, so do not write code that depends on it either way — normalise blanks in the application, and remember that NOT NULL on a VARCHAR2 also rejects ''.

Mapping to Java

An unqualified NUMBER becomes a BigDecimal in Java, because the driver has no precision to work with. Declaring NUMBER(19) instead of NUMBER for an id is the difference between a long and a BigDecimal in every DTO the column touches.

Next

Types chosen, tables next: constraints, identity columns, and sequences.