Choose the right Oracle data type for text, numbers, dates, LOBs, JSON, BOOLEAN and VECTOR columns, with size limits and the conversion and NULL behaviour that causes bugs.
For most Oracle tables you need five types: VARCHAR2 for text, NUMBER for numbers, DATE or TIMESTAMP for points in time, and CLOB/BLOB for large content. A typical table looks like this:
CREATE TABLE products (
product_id NUMBER GENERATED ALWAYS ASIDENTITYPRIMARY KEY,
sku VARCHAR2(20CHAR) NOT NULLUNIQUE,
product_name VARCHAR2(200CHAR) NOT NULL,
unit_price NUMBER(10,2) NOT NULL,
stock_qty NUMBER(8) DEFAULT0NOT NULL,
created_at TIMESTAMPDEFAULT SYSTIMESTAMP NOT NULL,
description CLOB
);
The rest of this page is a reference to the built-in types, their limits, and the behaviour that catches people out.
Character types
Type
Maximum size
Notes
VARCHAR2(n [BYTE|CHAR])
4,000 bytes, or 32,767 with MAX_STRING_SIZE = EXTENDED
Variable length. The standard string type
CHAR(n [BYTE|CHAR])
2,000 bytes
Fixed length, padded with spaces. Default length is 1
NVARCHAR2(n)
4,000 bytes, or 32,767 extended
Variable length in the national character set (Unicode)
Your long query needed undo that was already overwritten. How Oracle read consistency uses undo, how to read V$UNDOSTAT, and why committing inside a loop causes it.
Use VARCHAR2, not VARCHAR. Oracle currently treats VARCHAR as a synonym but reserves the right to change its behaviour.
Declare lengths in characters.VARCHAR2(20) means 20 bytes by default (the NLS_LENGTH_SEMANTICS setting). In an AL32UTF8 database a single character can take up to four bytes, so a 20-byte column may reject a 20-character value with ORA-12899: value too large for column. Writing VARCHAR2(20 CHAR) avoids this. The overall byte limit still applies.
Avoid CHAR for variable data.CHAR(10) stores 'abc' as 'abc' plus seven spaces, and comparisons against VARCHAR2 values then fail to match unless you trim.
Extended strings are opt-in.MAX_STRING_SIZE defaults to STANDARD. Switching to EXTENDED requires a documented procedure and cannot be reversed. Check the current setting with SHOW PARAMETER max_string_size.
NVARCHAR2 is rarely needed. If the database character set is AL32UTF8, which is the default for new databases, VARCHAR2 already stores any Unicode text.
Numeric types
Type
Range and storage
Notes
NUMBER(p,s)
Precision 1 to 38 digits, scale -84 to 127; 1 to 22 bytes
Exact decimal arithmetic
NUMBER
Up to 38 significant digits
No fixed precision or scale
FLOAT(p)
Binary precision 1 to 126
A NUMBER subtype; rarely needed
BINARY_FLOAT
4 bytes
IEEE 754 single precision
BINARY_DOUBLE
8 bytes
IEEE 754 double precision
NUMBER(10,2) holds up to eight digits before the decimal point and two after. Oracle rounds extra decimal places silently and rejects values with too many integer digits:
INSERT INTO products (sku, product_name, unit_price) VALUES ('A-1', 'Widget', 19.999);
SELECT unit_price FROM products WHERE sku ='A-1';
UNIT_PRICE
----------
20
INSERT INTO products (sku, product_name, unit_price) VALUES ('A-2', 'Gadget', 123456789.00);
ORA-01438: value larger than specified precision allowed for this column
The ANSI names INTEGER, INT, SMALLINT, DECIMAL and NUMERIC are accepted and mapped to NUMBER; INTEGER becomes NUMBER(38). There is no separate 4-byte or 8-byte integer type, and NUMBER storage is variable, so NUMBER(38) holding the value 5 takes no more space than NUMBER(3) holding 5.
Use NUMBER for money and anything that must be exact. Use BINARY_DOUBLE only for scientific or statistical values where speed matters more than decimal exactness; 0.1 + 0.2 is not exactly 0.3 in binary floating point.
Date and time types
Type
Stores
Notes
DATE
Year, month, day, hour, minute, second
7 bytes. Range 4712 BC to 9999 AD. No fractional seconds, no time zone
TIMESTAMP(f)
As DATE plus fractional seconds
f is 0 to 9, default 6
TIMESTAMP WITH TIME ZONE
Timestamp plus a zone offset or region name
13 bytes
TIMESTAMP WITH LOCAL TIME ZONE
Timestamp normalised to the database time zone
Returned in the session's time zone
INTERVAL YEAR TO MONTH
A span of years and months
INTERVAL DAY TO SECOND
A span of days, hours, minutes, seconds
DATE always contains a time, and so does TIMESTAMP. Comparing either to a bare date is the most common source of wrong results:
-- misses every row created after midnight on that daySELECT*FROM products WHERE created_at =DATE'2026-10-08';
-- correct, and can use an index on created_atSELECT*FROM products
WHERE created_at >=DATE'2026-10-08'AND created_at <DATE'2026-10-09';
Do not rely on implicit date conversion.WHERE created_at > '08-OCT-26' depends on the session's NLS_DATE_FORMAT and breaks when a client uses a different setting. Use ANSI literals (DATE '2026-10-08', TIMESTAMP '2026-10-08 14:30:00') or TO_DATE with an explicit format mask.
Subtracting dates gives days as a NUMBER. Subtracting timestamps gives an INTERVAL DAY TO SECOND.
For events that happen in several time zones, TIMESTAMP WITH TIME ZONE records the instant unambiguously. A plain TIMESTAMP records only the wall-clock reading.
Binary and large object types
Type
Maximum size
Notes
RAW(n)
2,000 bytes, or 32,767 extended
Short binary values such as hashes and GUIDs
BLOB
(4 GB - 1) x database block size
Images, documents, any binary content
BFILE
Operating system limit
A read-only pointer to a file outside the database
LONG RAW
2 GB
Deprecated. Use BLOB
LOB columns cannot be used in GROUP BY, ORDER BY, DISTINCT or as a primary key, and comparing them requires DBMS_LOB.COMPARE or similar. Do not use a CLOB where a VARCHAR2 would fit.
A table can have only one LONG or LONG RAW column and the type has many restrictions. Convert existing ones with ALTER TABLE t MODIFY (col CLOB);.
Types added in recent releases
Type
Available from
Purpose
JSON
21c
Native binary JSON storage. In 19c, store JSON in VARCHAR2, CLOB or BLOB with an IS JSON check constraint
BOOLEAN
23ai
TRUE, FALSE or NULL in SQL columns
VECTOR
23ai
Arrays of numbers for similarity search, for example VECTOR(768, FLOAT32)
Before 23ai there is no SQL boolean. The usual substitutes are NUMBER(1) with a check constraint for 0 and 1, or CHAR(1) with 'Y' and 'N'. On current releases:
ALTER TABLE products ADD (is_active BOOLEANDEFAULTTRUENOT NULL);
SELECT sku FROM products WHERE is_active;
Row identifiers
ROWID is the physical address of a row and the fastest way to fetch it, but it can change when a row moves (table reorganisation, partition key updates, export and import). Never store it as a permanent key. UROWID covers the logical row identifiers of index-organised tables.
Behaviour that surprises newcomers
An empty string is NULL.INSERT ... VALUES ('') stores NULL, WHERE col = '' matches nothing, and '' IS NULL is true. A NOT NULLVARCHAR2 column therefore rejects empty strings.
Implicit conversion hides indexes. Comparing a VARCHAR2 column to a number (WHERE sku = 123) makes Oracle apply TO_NUMBER to the column, which disables a normal index on it and raises ORA-01722: invalid number as soon as one row contains non-numeric text. Compare like with like.
Numbers stored as text sort as text.'10' sorts before '9'.
Shrinking a column with ALTER TABLE ... MODIFY only succeeds if every existing value fits. Widening is always allowed and is a quick metadata change for VARCHAR2 and NUMBER.
Check the types in an existing table
SELECT column_name, data_type, data_length, char_used,
data_precision, data_scale, nullable
FROM user_tab_columns
WHERE table_name ='PRODUCTS'ORDERBY column_id;
char_used shows C for character semantics and B for byte semantics. DUMP(col) shows the internal type code and stored bytes for a value.
Create, find, drop, disable and enable primary keys in Oracle, including composite keys, identity columns, the index behind the constraint and the ORA errors you will hit.
