Data Types

4.1 Character Types

CHAR(n [BYTE|CHAR]) Fixed length, up to 2,000 bytes, blank-padded VARCHAR2(n [BYTE|CHAR])

Variable length, 4,000 bytes (32,767 with MAX_STRING_SIZE=EXTENDED) NCHAR, NVARCHAR2 National character set (Unicode) CLOB, NCLOB Character large objects (up to 128 TB) Deprecated. Use CLOB instead.

VARCHAR is currently a synonym for VARCHAR2, but always use VARCHAR2. In Oracle, an empty string '' is treated as NULL!

4.2 Numeric Types

Precision 1–38, scale -84 to 127. NUMBER alone is a floating decimal.

INTEGER, INT, SMALLINT ANSI aliases for NUMBER(38) A NUMBER subtype with binary precision BINARY_FLOAT, BINARY_DOUBLE IEEE 754, fast arithmetic

PLS_INTEGER, BINARY_INTEGER, SIMPLE_INTEGER, NATURAL, POSITIVE

4.3 Date and Time Types

Date and time to the second (7 bytes) Fractional seconds up to 9 digits TIMESTAMP WITH TIME ZONE Stores the offset or region TIMESTAMP WITH LOCAL TIME ZONE

Normalized to the DB time zone, shown in the session time zone INTERVAL YEAR TO MONTH e.g., INTERVAL '2-6' YEAR TO MONTH INTERVAL DAY TO SECOND e.g., INTERVAL '3 04:30:00' DAY TO SECOND

4.4 Binary and LOB Types

RAW(n) (up to 2,000 / 32,767 bytes), LONG RAW (deprecated), BLOB, BFILE (a read-only pointer to an OS file).

SecureFiles (the default and recommended LOB storage) vs BasicFiles.

4.5 Row Identifiers

ROWID: the physical address of a row (object#, file#, block#, row#). The fastest access path.

UROWID: universal ROWID (for IOTs and foreign tables).

4.6 Other and Newer Types

SQL BOOLEAN (23ai). Previously PL/SQL only.

Native binary JSON (OSON), 21c+ VECTOR(dims, format) AI embeddings (23ai)

User-defined object types, VARRAY, nested tables ANYDATA, ANYTYPE

4.7 SQL Domains (23ai)

CREATE DOMAIN email_d AS VARCHAR2(255)
  CONSTRAINT email_chk CHECK (REGEXP_LIKE(email_d, '^[^@]+@[^@]+\.[a-z]{2,}$', 'i'))
  DISPLAY LOWER(email_d);
CREATE TABLE contacts (id NUMBER, email DOMAIN email_d);

4.8 Choosing Data Types: Best Practices

Use VARCHAR2 with CHAR length semantics for multibyte data (NLS_LENGTH_SEMANTICS).

Store dates in DATE or TIMESTAMP, never as strings.

Use NUMBER(p,s) with precision for money.

Match data types in joins and predicates to avoid implicit conversion (e.g., TO_NUMBER applied to an indexed column).

Database character set: AL32UTF8 is recommended.