Data Types and Constraints

Choosing the right data types and enforcing constraints is fundamental to good database design. Oracle provides a rich set of data types and constraint mechanisms to ensure data integrity, performance, and storage efficiency.

---

Character Data Types

Data Type	Description	Max Size
VARCHAR2(n)	Variable-length string (most common)	4,000 bytes (32,767 with MAX_STRING_SIZE=EXTENDED)
CHAR(n)	Fixed-length string (padded with spaces)	2,000 bytes
NVARCHAR2(n)	Variable-length Unicode string	4,000 bytes
NCHAR(n)	Fixed-length Unicode string	2,000 bytes
CLOB	Character Large Object	Up to 128 TB
NCLOB	Unicode Character Large Object	Up to 128 TB

Tip: Always use VARCHAR2 over CHAR unless fixed-length is specifically required. CHAR pads with trailing spaces, which can cause unexpected comparison behaviour.

---

Numeric Data Types

Data Type	Description
NUMBER(p, s)	Precision p (1-38 digits), scale s (number of decimal places)
NUMBER	Floating-point number (no precision/scale limit)
INTEGER	Equivalent to NUMBER(38)
FLOAT(p)	Floating-point with binary precision
BINARY_FLOAT	32-bit IEEE 754 floating point
BINARY_DOUBLE	64-bit IEEE 754 floating point

NUMBER Examples

Declaration	Stores	Example
NUMBER(8, 2)	Up to 999,999.99	Salary: 75000.50
NUMBER(10)	Up to 9,999,999,999 (integer)	Employee ID: 12345
NUMBER(3)	Up to 999	Age: 45
NUMBER	Any number	Scientific calculation

---

Date and Timestamp Data Types

Data Type	Description	Precision
DATE	Date and time	Seconds
TIMESTAMP	Date and time with fractional seconds	Up to nanoseconds
TIMESTAMP WITH TIME ZONE	Timestamp + time zone offset	Named zone or offset
TIMESTAMP WITH LOCAL TIME ZONE	Normalised to database time zone	Session time zone on retrieval
INTERVAL YEAR TO MONTH	Duration in years and months	—
INTERVAL DAY TO SECOND	Duration in days, hours, minutes, seconds	—

-- DATE stores date and time (to the second)
CREATE TABLE events (
  event_id   NUMBER PRIMARY KEY,
  event_name VARCHAR2(100),
  event_date DATE DEFAULT SYSDATE
);

-- TIMESTAMP for higher precision
CREATE TABLE audit_log (
  log_id      NUMBER PRIMARY KEY,
  action      VARCHAR2(50),
  created_at  TIMESTAMP(6) DEFAULT SYSTIMESTAMP
);

---

Large Object (LOB) Data Types

Data Type	Description
CLOB	Character data (text documents, XML)
BLOB	Binary data (images, PDFs, video)
NCLOB	Unicode character data
BFILE	Pointer to an external file on the OS

---

Other Data Types

Data Type	Description
RAW(n)	Variable-length binary data (up to 2,000 bytes)
LONG RAW	Binary data up to 2 GB (deprecated — use BLOB)
ROWID	Physical row address
XMLTYPE	Native XML storage and querying
JSON	Native JSON type (21c+)
BOOLEAN	TRUE/FALSE/NULL (23ai+)
VECTOR	AI vector embeddings (23ai+)

---

CREATE TABLE