Oracle SQL data types define the kind of data that can be stored in database columns, variables, and expressions. Choosing the correct data type is essential for data accuracy, storage efficiency, and database performance.
Oracle Database supports a wide range of data types, including character strings, numbers, dates, timestamps, large objects, binary data, JSON, and user-defined collections.
In this guide, you’ll learn the major Oracle SQL data types, their purposes, practical examples, and how to use them when designing database tables.
Table of Contents
- Character Data Types
- Numeric Data Types
- Date and Time Data Types
- Large Object (LOB) Data Types
- Binary and RAW Data Types
- Row Identifier Data Types
- Boolean and JSON Data Types
- Object and Collection Data Types
- Other Oracle Data Types and Aliases
- Complete Employee Table Example
- Which Oracle Data Types Should You Learn First?
1. Oracle Character Data Types
Character data types store text, names, addresses, employee codes, descriptions, and other string values.
| Data Type | Length / Limit | Example |
| CHAR(n) | Fixed length, up to 2,000 bytes | CHAR(10) |
| VARCHAR2(n) | Variable length, up to 4,000 bytes by default | VARCHAR2(100) |
| VARCHAR2(n CHAR) | Length specified in characters | VARCHAR2(100 CHAR) |
| NCHAR(n) | Fixed-length national character data, up to 2,000 characters | NCHAR(20) |
| NVARCHAR2(n) | Variable-length national character data, up to 4,000 characters | NVARCHAR2(100) |
| LONG | Legacy character data, up to approximately 2 GB | LONG |
Note: The actual limits depend on database configuration and character set. Extended data types can allow VARCHAR2 and NVARCHAR2 values up to 32,767 bytes or characters, respectively, subject to the applicable configuration.
Example: Create a table using character data types
CREATE TABLE EmployeeNames (
EmployeeName VARCHAR2(100),
EmployeeCode CHAR(10),
BanglaName NVARCHAR2(100),
Remarks VARCHAR2(500 CHAR)
);
Insert sample data
INSERT INTO EmployeeNames
VALUES (
‘Shohal’,
‘EMP001’,
N’শোহাল’,
‘IT Department’
);
Difference between CHAR and VARCHAR2
- CHAR: Fixed-length character data. Shorter values are padded with spaces.
- VARCHAR2: Variable-length character data that stores values without fixed-length padding.
- NCHAR and NVARCHAR2: Use Oracle’s national character set, which can support Unicode text such as Bangla.
- LONG: A legacy type that should generally be avoided in new database designs. Use CLOB or VARCHAR2, depending on the required size.
Best practice: For most application tables, VARCHAR2 is the preferred choice for ordinary text.
Numeric data types store integers, decimal values, and floating-point numbers.
| Data Type | Meaning | Example |
| NUMBER | General-purpose numeric value | NUMBER |
| NUMBER(p) | Number with precision of up to p digits and scale zero | NUMBER(5) |
| NUMBER(p,s) | Number with specified precision and scale | NUMBER(10,2) |
| FLOAT(p) | Floating-point subtype of NUMBER | FLOAT(126) |
| BINARY_FLOAT | 32-bit binary floating-point number | BINARY_FLOAT |
| BINARY_DOUBLE | 64-bit binary floating-point number | BINARY_DOUBLE |
Understanding NUMBER(p,s)
The NUMBER(p,s) data type uses two important parameters:
- Precision (p): The maximum number of significant decimal digits.
- Scale (s): The number of digits to the right of the decimal point. A negative scale rounds to the left of the decimal point.
For example, NUMBER(10,2) allows up to 10 significant decimal digits, with 2 decimal places.
CREATE TABLE EmployeeSalary (
EmployeeID NUMBER(10),
Salary NUMBER(10,2),
TaxRate NUMBER(5,2),
Rating NUMBER(3,2)
);
Insert sample data
INSERT INTO EmployeeSalary
VALUES (101, 55000.75, 15.50, 9.25);
NUMBER(p,s): Specified Values and Stored Values
| Data Type | Specified Value | Stored Value |
| NUMBER(3,2) | 1.234 | 1.23 |
| NUMBER(3,2) | 1.235 | 1.24 |
| NUMBER(5,2) | 123.456 | 123.46 |
| NUMBER(5,0) | 123.6 | 124 |
| NUMBER(5,-2) | 12345 | 12300 |
| NUMBER(3,2) | 10.00 | Error: exceeds precision |
Oracle rounds excess fractional digits when storing numeric values. If the rounded value exceeds the allowed precision, Oracle raises a numeric overflow error.
Example: NUMBER(3,2) allows values from -9.99 to 9.99.
Best practice: Use NUMBER(p,s) for salary, prices, tax rates, and other exact decimal calculations. Use BINARY_FLOAT and BINARY_DOUBLE for calculations where approximate binary floating-point arithmetic is appropriate.
3. Oracle Date and Time Data Types
Oracle provides date, timestamp, and interval data types for storing dates, times, time-zone information, and durations.
| Data Type | Meaning | Example |
| DATE | Date and time to the nearest second | DATE ‘2026-10-11’ |
| TIMESTAMP(p) | Date and time with fractional-second precision | TIMESTAMP(3) |
| TIMESTAMP WITH TIME ZONE | Timestamp with time-zone information | TIMESTAMP ‘2026-10-11 14:30:00 +06:00’ |
| TIMESTAMP WITH LOCAL TIME ZONE | Timestamp normalized to the database time zone and displayed in the session time zone | TIMESTAMP WITH LOCAL TIME ZONE |
| INTERVAL YEAR TO MONTH | Duration in years and months | INTERVAL ‘2-6’ YEAR TO MONTH |
| INTERVAL DAY TO SECOND | Duration in days, hours, minutes, and seconds | INTERVAL ‘3 04:30:00’ DAY TO SECOND |
Example: Create a table using date and time data types
CREATE TABLE EmployeeDates (
BirthDate DATE,
CreatedAt TIMESTAMP(3),
AppointmentTime TIMESTAMP WITH TIME ZONE,
EmploymentPeriod INTERVAL YEAR TO MONTH
);
Insert sample data
INSERT INTO EmployeeDates
VALUES (
DATE ‘1992-05-20’,
TIMESTAMP ‘2026-10-11 10:30:15.123’,
TIMESTAMP ‘2026-10-11 14:30:00 +06:00’,
INTERVAL ‘2-6’ YEAR TO MONTH
);
Important: Oracle’s DATE type stores year, month, day, hour, minute, and second. It is not a date-only type like SQL Server’s DATE.
Use TIMESTAMP when fractional seconds are required and a time-zone-aware type when time-zone information is important.
4. Oracle Large Object (LOB) Data Types
Large object data types store large amounts of text or binary content, such as documents, images, and PDFs.
| Data Type | Meaning | Typical Use |
| CLOB | Character large object | Articles and descriptions |
| NCLOB | National-character-set large object | Large multilingual text |
| BLOB | Binary large object | Images, PDFs, and file contents |
| BFILE | External binary file reference | Files stored outside the database |
Example: Store documents and files
CREATE TABLE Documents (
DocumentID NUMBER(10),
DocumentName VARCHAR2(200),
DocumentText CLOB,
DocumentFile BLOB
);
CLOB and BLOB store content inside the database. BFILE references a file stored externally and accessed through an Oracle directory object.
Best practice: Choose CLOB for large text and BLOB for binary content.
5. Oracle Binary and RAW Data Types
Binary data types store raw bytes rather than ordinary character strings.
| Data Type | Meaning | Example |
| RAW(n) | Variable-length binary data | RAW(16) |
| LONG RAW | Legacy large binary data | Avoid in new tables |
Example: Store binary data
CREATE TABLE BinaryData (
DataID NUMBER(10),
HashValue RAW(32)
);
Insert a hexadecimal value:
INSERT INTO BinaryData
VALUES (1, HEXTORAW(‘A1B2C3D4’));
RAW is useful for byte sequences, hashes, and binary identifiers. For larger binary content, use BLOB instead of LONG RAW.
6. Oracle Row Identifier Data Types
Oracle provides row identifier types for identifying the location or logical identity of rows.
| Data Type | Meaning | Example |
| ROWID | Physical row locator for supported tables | SELECT ROWID FROM Employees |
| UROWID | Universal row identifier, including logical row IDs | UROWID |
Example: Retrieve ROWID
SELECT ROWID, EmployeeID, EmployeeName
FROM Employees;
Important: A ROWID identifies a row’s location in a table. It is useful for certain database operations and diagnostics, but it should not replace a primary key.
7. Oracle BOOLEAN and JSON Data Types
Boolean and JSON support depends on the Oracle Database version.
| Data Type | Meaning | Availability |
| BOOLEAN | Represents TRUE, FALSE, or NULL | Available in PL/SQL in older releases; supported for SQL columns in Oracle Database 23ai and later |
| JSON | Native JSON data type | Supported in modern Oracle releases, including Oracle Database 21c and later |
Example: BOOLEAN in Oracle Database 23ai and later
CREATE TABLE EmployeeStatus (
EmployeeID NUMBER(10),
IsActive BOOLEAN
);
Insert a record:
INSERT INTO EmployeeStatus
VALUES (101, TRUE);
Example: Native JSON
CREATE TABLE EmployeeJSON (
EmployeeID NUMBER(10),
EmployeeData JSON
);
Insert JSON data:
INSERT INTO EmployeeJSON
VALUES (
101,
JSON(‘{“name”:”Shohal”,”department”:”IT”}’)
);
JSON can also be stored as text in VARCHAR2, CLOB, or BLOB columns when appropriate for the Oracle version and application design.
8. Oracle Object and Collection Data Types
Oracle supports user-defined object types and collections, which help represent structured data and multiple values.
| Type | Purpose | Example |
| Object type | Groups attributes into a custom type | CREATE TYPE AddressType AS OBJECT (…) |
| VARRAY | Ordered collection with a maximum size | VARRAY(5) OF NUMBER |
| Nested table | Collection that can contain multiple elements | TABLE OF VARCHAR2(100) |
| REF | Reference to an object instance | REF EmployeeType |
Example: Create a VARRAY
CREATE TYPE PhoneList AS VARRAY(3) OF VARCHAR2(20);
/
Create a table using the collection:
CREATE TABLE ContactDetails (
EmployeeID NUMBER(10),
PhoneNumbers PhoneList
);
This example allows each row to contain up to three phone numbers in the PhoneNumbers collection.
Object types and collections are especially useful for applications that need structured or nested data within Oracle Database.
9. Other Oracle Data Types and Aliases
Oracle also supports XML-related types, dynamic type containers, cursor variables, and standard SQL numeric aliases.
| Type or Name | Meaning |
| XMLTYPE | Oracle type for XML documents |
| ANYTYPE | Describes a type dynamically |
| ANYDATA | Wraps a value of a supported type |
| ANYDATASET | Represents a set of values of a described type |
| REF CURSOR | PL/SQL cursor variable for passing query result sets |
| INTEGER | Numeric subtype equivalent to NUMBER(38,0) |
| SMALLINT | Numeric subtype equivalent to NUMBER(38,0) |
| DECIMAL(p,s) | ANSI-compatible numeric type mapped to Oracle NUMBER(p,s) |
| NUMERIC(p,s) | ANSI-compatible numeric type mapped to Oracle NUMBER(p,s) |
| DOUBLE PRECISION | Floating-point numeric alias |
| REAL | Floating-point numeric alias |
Some entries are Oracle-supplied types, while others are aliases or PL/SQL types rather than independent SQL column types.
10. Complete Example: Create an Employee Table
The following example combines several commonly used Oracle SQL data types in one table.
CREATE TABLE Employees (
EmployeeID NUMBER(10) PRIMARY KEY,
EmployeeName VARCHAR2(100),
BanglaName NVARCHAR2(100),
EmployeeCode CHAR(10),
Salary NUMBER(10,2),
BirthDate DATE,
CreatedAt TIMESTAMP(3),
IsActive NUMBER(1),
EmployeeGUID RAW(16),
ProfileText CLOB,
ProfilePhoto BLOB
);
Insert a sample employee
INSERT INTO Employees (
EmployeeID,
EmployeeName,
BanglaName,
EmployeeCode,
Salary,
BirthDate,
CreatedAt,
IsActive
)
VALUES (
101,
‘Shohal’,
N’শোহাল’,
‘EMP001’,
65000.50,
DATE ‘1992-05-20’,
SYSTIMESTAMP,
1
);
This example uses:
- NUMBER(10) for the employee ID.
- VARCHAR2(100) for the employee name.
- NVARCHAR2(100) for Bangla text.
- CHAR(10) for a fixed-length employee code.
- NUMBER(10,2) for salary.
- DATE for the birth date.
- TIMESTAMP(3) for the creation timestamp.
- NUMBER(1) for a numeric active-status flag.
- RAW(16) for binary identifier data.
- CLOB for long text.
- BLOB for binary files or images.
Note: NUMBER(1) is a numeric column, not a dedicated Boolean type. It can be used for a 0/1 status convention, ideally with a check constraint if only those two values are allowed.
11. Which Oracle Data Types Should You Learn First?
If you’re learning Oracle SQL and PL/SQL for application development, focus on the following groups in order.
Beginner — Essential Types
- NUMBER
- VARCHAR2
- CHAR
- DATE
- TIMESTAMP
- CLOB
- BLOB
These cover most everyday table definitions and queries.
Intermediate — Application Development
- NVARCHAR2
- RAW
- INTERVAL
- ROWID
- UROWID
- BOOLEAN
- JSON
These become useful when working with multilingual data, time calculations, binary identifiers, and modern applications.
Advanced — Specialized Applications
- XMLTYPE
- Object types
- Nested tables
- VARRAY
- REF
- ANYDATA
- ANYTYPE
- ANYDATASET
- REF CURSOR
These are useful for specialized database architectures, structured data, XML integration, and advanced PL/SQL programming.
Conclusion
Oracle SQL data types determine how information is represented and stored in a database. Choosing the appropriate type helps maintain data integrity, avoid conversion errors, and build maintainable applications.
For most everyday Oracle database projects, start with NUMBER, VARCHAR2, CHAR, DATE, and TIMESTAMP. Add CLOB, BLOB, JSON, and collection types as your application requirements become more advanced.
For the complete, version-specific list, consult the official Oracle SQL Language Reference — Data Types.
