Comprehensive Guide to Oracle Data Types: Numeric, Character, Date, and LOB Types Explained

When creating tables in Oracle Database, you must define what type of data each column will store. This is done by assigning a data type to each column.

In this article, we will explain the most commonly used data types in Oracle—numeric, character, date/time, and LOB (Large Object)—in a clear and beginner-friendly format, with text-based diagrams included.

What Is a Data Type?

A data type defines the kind of data (numeric, text, date, etc.) that can be stored in a column and how much space it will take up.

Benefits of Choosing the Right Data Type:

Optimized storage efficiency
Prevention of data type mismatch errors
Potential performance improvements

Categories of Oracle Data Types

Oracle data types can be categorized into the following:

┌──────────────────┐
│  Oracle Data Types │
└──────────────────┘
         ↓
┌────────┬──────────┬─────────┬────────┐
│ Numeric│ Character│ Date/Time│   LOB  │
└────────┴──────────┴─────────┴────────┘

We’ll explore each of these categories in detail below.

1. Numeric Data Types

These are used to store integer and decimal numbers.

NUMBER (most commonly used numeric type)

col_name NUMBER(5, 2)

5 = total number of digits (precision)
2 = number of digits after the decimal (scale)
Maximum precision is 38 digits

Example: NUMBER(5,2) allows values up to 999.99

Usage Example

CREATE TABLE product (
  id NUMBER(3),
  price NUMBER(7,2)
);

Note: Even for integer-only values, Oracle uses the NUMBER type (there’s no native INT or BIGINT in Oracle).

2. Character Data Types

These are used to store strings.

CHAR(n): Fixed-length character

col_name CHAR(10)

Always reserves 10 characters
Pads unused space with spaces

VARCHAR2(n): Variable-length character

col_name VARCHAR2(10)

Stores only actual length
Maximum 4000 bytes (up to 32,767 with MAX_STRING_SIZE=EXTENDED from 12c)

Usage Example

CREATE TABLE employee (
  emp_id NUMBER,
  emp_name VARCHAR2(50),
  emp_type CHAR(1)
);

Tip: In most cases, use VARCHAR2 instead of CHAR for storage efficiency.

3. Date/Time Data Types

Used to store dates and timestamps.

DATE

Stores date and time (up to seconds)
Range: 4712 BC to 9999 AD

col_name DATE

TIMESTAMP

Includes fractional seconds for higher precision
Example: TIMESTAMP(3) stores milliseconds up to 3 digits

col_name TIMESTAMP(6)

Usage Example

CREATE TABLE orders (
  order_id NUMBER,
  order_date DATE,
  updated_at TIMESTAMP(3)
);

Use TIMESTAMP for logging or auditing with microsecond/millisecond precision.

4. LOB Data Types (Large Objects)

Used to store large data such as documents, images, or videos.

Data Type	Description
`CLOB`	Large Character Object (text)
`BLOB`	Binary Large Object (binary data)

Usage Example

CREATE TABLE documents (
  doc_id NUMBER,
  doc_text CLOB,
  doc_file BLOB
);

LOBs are stored separately from the main table and may impact performance—manage with care.

Choosing the Right Data Type

Select a data type based on:

The type of data: number, text, date, binary
Fixed-length or variable-length strings
Precision needed for timestamps
Byte-size impact due to multibyte characters

Storage Behavior of Character Data Types

VARCHAR2(100) → If you store 10 characters, only 10 are stored
CHAR(100)     → Always stores 100 characters (padded with spaces)

So VARCHAR2 saves space for variable-length input, while CHAR always reserves fixed space.

Common Misconceptions and Clarifications

Misconception	Correction
Oracle supports `INT` data type	Use `NUMBER` instead
You can use `VARCHAR`	Oracle only supports `VARCHAR2` (use this)
`CHAR` is faster than `VARCHAR2`	`CHAR` may waste space and hurt performance

Summary Table

Category	Data Types	Use Cases
Numeric	`NUMBER`	Integer or decimal numbers
Character	`CHAR`, `VARCHAR2`	Fixed or variable-length text
Date/Time	`DATE`, `TIMESTAMP`	Dates and high-precision times
LOB	`CLOB`, `BLOB`	Large text and binary files

Key Takeaways

Oracle provides flexible and powerful data types such as NUMBER, VARCHAR2, DATE, CLOB
Choose the appropriate type based on the nature and size of data
Proper use of data types improves performance, storage efficiency, and data integrity

[reference]
CREATE TABLE