Data Types

About

Oracle provides a rich set of data types that extend beyond the standard SQL types to support advanced features and performance optimizations.

In Oracle Database, data types define the kind of values a column, variable, or expression can hold. Every data element in Oracle must be declared with an appropriate type that governs:

  • The format and size of stored data

  • The set of valid operations

  • The amount of space allocated in memory or disk

  • Compatibility with other types during operations like joins or comparisons

Oracle offers both standard scalar types (such as numbers, text, and dates) and advanced or abstract types (LOBs, XML, objects).

These types are broadly grouped into categories, each optimized for a specific domain of data representation.

1. Character (Text) Data Types

Description

Character types are used to store text in either fixed or variable length. Oracle supports both single-byte character sets (like ASCII) and multi-byte Unicode character sets.

  • Character semantics vs byte semantics: Length can be interpreted in number of characters or number of bytes.

  • Padding behavior: Fixed-length types are padded with spaces to meet their defined size.

  • Encoding awareness: NCHAR, NVARCHAR2, and NCLOB are designed for internationalization.

VARCHAR is reserved for possible future standardization and should be avoided in production schemas.

For Unicode support, Oracle recommends using AL32UTF8 character set with NCHAR/NVARCHAR2

Types

Data Type
Description
Key Characteristics
Example Value

CHAR(n)

Fixed-length character string, space-padded to length n

- Always stores n bytes - Padded with spaces if shorter

'Y' is stored as 'Y ' in case CHAR(2)

VARCHAR2(n)

Variable-length character string up to n bytes

- Efficient storage - No padding

'Hello' stored as 'Hello'

VARCHAR(n)

ANSI SQL equivalent, internally treated as VARCHAR2

- Supported but not recommended - Future behavior undefined

'Hi' stored as 'Hi'

NCHAR(n)

Fixed-length Unicode character string

- Unicode-aware - Always n characters (not bytes)

'नमस्ते' stored with padding

NVARCHAR2(n)

Variable-length Unicode character string up to n characters

- Unicode-aware - No padding

'你好' stored as-is

Byte and Character Limits

Oracle imposes specific maximum limits depending on the data type and database settings (especially character set and semantics).

Character semantics: If the database or column uses character semantics, the limits apply to characters instead of bytes. This is useful in multibyte/Unicode character sets.

Data Type
Max Length (Bytes)
Max Length (Characters)
Notes

CHAR(n)

2000 bytes

Depends on byte-per-char

Fixed-length; may waste space if short data is stored.

VARCHAR2(n)

4000 bytes (SQL), 32767 bytes (PL/SQL)

Depends on byte-per-char

Most common string type; n is byte count unless CHAR semantics used.

NCHAR(n)

2000 bytes

1000 characters (UTF-16)

Always uses national character set (usually UTF-16).

NVARCHAR2(n)

4000 bytes (SQL), 32767 bytes (PL/SQL)

2000 characters (UTF-16)

Used for Unicode multilingual data.

VARCHAR(n)

Same as VARCHAR2

Deprecated

Reserved by Oracle; avoid using.

In Oracle, VARCHAR2(36 BYTE) and VARCHAR2(36 CHAR) both define a column with a maximum length of 36 characters. However, they differ in how they handle character encoding.

  1. VARCHAR2(36 BYTE): This specifies that the column can store up to 36 bytes of data. In a single-byte character set (like ASCII or ISO-8859-1), each character occupies one byte. So, in this case, we could store up to 36 characters if each character requires only one byte of storage. However, in a multi-byte character set (like UTF-8), some characters may require more than one byte to store. Therefore, we may not be able to store as many characters as the specified length suggests.

  2. VARCHAR2(36 CHAR): This specifies that the column can store up to 36 characters. Regardless of the character set being used, Oracle will ensure that we can store up to 36 characters. If we are using a single-byte character set, this behaves the same as VARCHAR2(36 BYTE). But if we are using a multi-byte character set, Oracle will allocate enough space to store up to 36 characters, even if it requires more than 36 bytes.

In summary, the difference lies in how Oracle determines the maximum storage space for characters. The BYTE qualifier considers the number of bytes, while the CHAR qualifier considers the number of characters, potentially leading to different behavior in multi-byte character sets.

Storage and Performance Implications

Data Type
Storage Efficiency
Performance Impact
Best Use Case

CHAR(n)

Low (always fixed)

Slower for short values

Fields with consistent, small size (e.g., 'Y'/'N')

VARCHAR2(n)

High

Fast and efficient

General-purpose strings

NCHAR(n)

Medium

Slight overhead (Unicode)

Fixed-width internationalized values

NVARCHAR2(n)

Medium-High

More space needed (UTF-16)

Multilingual applications

VARCHAR(n)

Same as VARCHAR2

No gain; discouraged

Not recommended

2. Numeric Data Types

Description

Numeric types in Oracle are highly flexible and configurable. They support exact (fixed-point) and approximate (floating-point) representations.

  • Precision (p): Total number of significant digits.

  • Scale (s): Number of digits to the right of the decimal point.

  • Overflow and rounding: Operations that exceed scale may be rounded or truncated based on context.

  • Binary vs decimal: Oracle supports both binary floats and precise decimal arithmetic.

Types

Oracle provides two main categories of numeric types:

  1. Exact Numeric Types

    • NUMBER

    • INTEGER, INT, SMALLINT, DEC, DECIMAL, NUMERIC (ANSI-compatible synonyms)

  2. Approximate Numeric Types

    • BINARY_FLOAT

    • BINARY_DOUBLE

NUMBER(p, s) – General Purpose Numeric Type

  • A variable-precision numeric type that can store integers and decimals.

  • p is precision (total number of significant digits).

  • s is scale (number of digits to the right of the decimal point).

Ranges

  • Maximum precision: 38 digits

  • Scale range: -84 to 127

Examples

Declaration
Description
Acceptable Values

NUMBER

Maximum flexibility. Default precision/scale.

42, 3.1415, -9999.99

NUMBER(5)

Up to 5 digits. No decimal.

12345, -9999

NUMBER(7, 2)

7 digits total, 2 after decimal.

12345.67

NUMBER(*, 0)

Any number of digits, no decimal.

Integer values only

Behavior

  • If the number exceeds the specified precision, Oracle raises an error.

  • If scale is higher than actual decimals, Oracle appends trailing zeroes.

  • If scale is lower, Oracle rounds the value.

ANSI-Compatible Synonyms

These are alternative names mapped to NUMBER:

Synonym
Oracle Equivalent

INTEGER

NUMBER(38,0)

INT

NUMBER(38,0)

SMALLINT

NUMBER(38,0)

DEC

NUMBER(p,s)

DECIMAL

NUMBER(p,s)

NUMERIC

NUMBER(p,s)

FLOAT(p) – Approximate with Decimal Precision

  • Floating-point number with p bits of binary precision.

  • FLOAT(p) is internally treated as NUMBER(p).

Although named FLOAT, Oracle does not use IEEE floating point here unless we use BINARY_FLOAT or BINARY_DOUBLE.

Example

  • FLOAT(10) means up to 10 digits of precision.

Use FLOAT with caution as its behavior may be misleading—use BINARY_FLOAT or BINARY_DOUBLE if we truly need floating-point arithmetic.

Example 1: Basic FLOAT column

CREATE TABLE sample_float_table (
    id        NUMBER,
    value     FLOAT -- same as NUMBER with default precision (usually 126)
);

We can insert values like:

INSERT INTO sample_float_table (id, value) VALUES (1, 12345.6789);
INSERT INTO sample_float_table (id, value) VALUES (2, -0.000345);

Example 2: FLOAT with precision

CREATE TABLE precise_float_table (
    id         NUMBER,
    value      FLOAT(10) -- same as NUMBER(10)
);

This means the column value can store up to 10 significant digits, e.g.,

INSERT INTO precise_float_table VALUES (1, 123456.7890); -- OK
INSERT INTO precise_float_table VALUES (2, 1234567890.12); -- Error or rounded

BINARY_FLOAT and BINARY_DOUBLE – Binary Floating Point Types

These types use IEEE 754 format for representing approximate decimal numbers.

Data Type
Size
Precision
Use Case

BINARY_FLOAT

32 bits

~6 digits

Fast approximate calculations

BINARY_DOUBLE

64 bits

~15 digits

Higher precision computations

Key Characteristics

  • Support NaN, positive/negative infinity, and subnormal numbers.

  • Faster than NUMBER due to hardware-level operations.

  • Cannot be indexed using B-tree indexes.

  • Not exact—do not use in financial or accounting systems.

Examples

CREATE TABLE temperature_data (
  city_name VARCHAR2(100),
  avg_temp  BINARY_FLOAT
);

INSERT INTO temperature_data VALUES ('Delhi', 42.57);

NUMBER vs BINARY_FLOAT/DOUBLE

Feature

NUMBER

BINARY_FLOAT / DOUBLE

Precision

Exact (up to 38 d)

Approximate

Storage

Variable

Fixed (4 or 8 bytes)

Performance

Slower

Faster

Usage

Financial

Scientific, statistical

Indexing

B-tree supported

Not supported

Rounding

Controlled

IEEE-based

3. Date & Time Data Types

Description

Temporal types store points in time or durations. Oracle aligns partially with SQL standards but also adds its own enhancements for timezone-aware storage and interval management.

  • Oracle’s DATE includes time: Unlike standard SQL, DATE in Oracle includes both date and time down to seconds.

  • Fractional seconds and precision: Timestamps support up to 9 digits of fractional seconds.

  • Timezone handling: Oracle supports both session-relative and absolute time zones.

  • Intervals as durations: Represent spans of time, not absolute moments.

DATE

Stores both date and time values, accurate to the second. This is the most basic and commonly used temporal type in Oracle.

  • Internally stored as 7 bytes:

    • Century, Year, Month, Day, Hour, Minute, Second

  • Default format: DD-MON-YY (can be changed via NLS_DATE_FORMAT)

  • Does not support fractional seconds or time zones.

DATE '2024-06-05'
TO_DATE('2024-06-05 14:30:00', 'YYYY-MM-DD HH24:MI:SS')

TIMESTAMP

Extends DATE by including fractional seconds (up to 9 digits of precision). Used where higher accuracy is required.

  • Default fractional precision is 6 digits.

  • Internally stored as 11 bytes.

  • No time zone support.

Syntax:

TIMESTAMP [(fractional_seconds_precision)]
TIMESTAMP '2024-06-05 14:30:00.123456'

TIMESTAMP WITH TIME ZONE (abbreviated: TIMESTAMP WITH TZ)

Adds time zone offset information to a timestamp, making it suitable for storing absolute moments in time globally.

  • Stores time in UTC and retains the time zone offset.

  • Time zone can be expressed as an offset (e.g., +05:30) or region name (e.g., 'Asia/Kolkata').

  • Useful for distributed systems.

Syntax:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE
TIMESTAMP '2024-06-05 14:30:00.123456 +05:30'

TIMESTAMP WITH LOCAL TIME ZONE (abbreviated: TIMESTAMP WITH LTZ)

Stores the timestamp in UTC internally, but displays it in the user's session time zone. Useful for applications with users across time zones.

  • Time zone information is not stored, but inferred from session.

  • Automatically adjusted during storage and retrieval.

  • Ideal for user-facing logs or event timestamps.

Syntax:

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE
TIMESTAMP '2024-06-05 14:30:00.123456' AT LOCAL

INTERVAL YEAR TO MONTH

Represents a duration (not a point in time) in terms of years and months.

  • Used to add/subtract months or years to/from a date.

  • Cannot include days, hours, or minutes.

  • Useful in business date arithmetic (e.g., contract terms).

INTERVAL YEAR [(precision)] TO MONTH
INTERVAL '2-3' YEAR TO MONTH   -- 2 years, 3 months

INTERVAL DAY TO SECOND

Represents a duration in days, hours, minutes, seconds, and fractional seconds.

  • Supports up to 9 digits of fractional second precision.

  • Used for time spans, e.g., tracking elapsed time.

Syntax:

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]
INTERVAL '5 12:30:15.123456' DAY TO SECOND
-- 5 days, 12 hours, 30 minutes, 15.123456 seconds

4. Large Object (LOB) Data Types

Description

Large Object (LOB) data types in Oracle are designed to store large blocks of unstructured data, such as:

  • Large text (e.g. articles, emails, XML)

  • Binary files (e.g. images, PDFs, videos)

  • External file references (e.g. stored on the OS filesystem)

LOBs are essential for enterprise systems dealing with document management, media storage, or data lakes inside databases.

  • Can store data up to 4 GB and beyond (depending on configuration and database block size).

  • Stored separately from table rows, though the row holds a locator to the LOB data.

  • Supports streaming APIs for efficient reading/writing.

  • Supports transactional consistency (except BFILE).

  • Can be deferred-loaded (loaded only when accessed), saving memory.

LOB Types

LOB Type
Description
Use Case

CLOB (Character LOB)

Stores large character data in database character set

Text documents, HTML, XML

NCLOB (National CLOB)

Same as CLOB, but uses national character set (Unicode)

Multilingual documents

BLOB (Binary LOB)

Stores large binary data

Images, videos, PDFs, encrypted files

BFILE

Stores a pointer to a file in the OS file system (read-only)

External file referencing

Internal LOB vs External LOB

Feature

Internal LOB (BLOB, CLOB, NCLOB)

External LOB (BFILE)

Stored In

Database tablespace

OS file system

Read/Write

Fully readable and writable

Read-only from SQL

Transactional

Yes

No

Secure

Protected under Oracle security

Depends on OS permissions

Backup

Included in database backup

Must back up separately

Storage Characteristics

Oracle stores LOB data out-of-line by default (separate from table row), but:

  • If the LOB data is small (less than ~4 KB), it can be stored in-line using ENABLE STORAGE IN ROW.

  • LOBs can use basicfiles (legacy) or securefiles (modern, better performance & compression).

When to Use Which LOB Type?

Requirement
Recommended LOB

Long text documents

CLOB

Multilingual/unicode documents

NCLOB

Images, media, encrypted files

BLOB

Link to file outside database

BFILE

SecureFile LOBs vs BasicFile LOBs

Oracle 11g+ introduced SecureFile LOBs for better performance and manageability:

Feature
BasicFile
SecureFile

Compression

No

Yes

Encryption

No

Yes

Deduplication

No

Yes

Space-saving

Minimal

Significant

Performance

Lower

Higher

Enable SecureFiles like this:

CREATE TABLE image_store (
    id NUMBER,
    img BLOB
) LOB (img) STORE AS SECUREFILE (COMPRESS HIGH ENCRYPT);

Example

Step 1: Oracle Table Definition

CREATE TABLE user_photos (
    id NUMBER PRIMARY KEY,
    username VARCHAR2(100),
    photo BLOB
);

Step 2: JDBC Code – Insert Image into BLOB

import java.sql.*;
import java.io.*;

public class ImageUploader {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@//localhost:1521/orclpdb";
        String username = "your_username";
        String password = "your_password";
        String imagePath = "/path/to/photo.jpg";

        try (
            Connection conn = DriverManager.getConnection(url, username, password);
            PreparedStatement pstmt = conn.prepareStatement(
                "INSERT INTO user_photos (id, username, photo) VALUES (?, ?, ?)");
            FileInputStream fis = new FileInputStream(new File(imagePath))
        ) {
            pstmt.setInt(1, 101);
            pstmt.setString(2, "john_doe");
            pstmt.setBinaryStream(3, fis, fis.available()); // set BLOB as stream

            int rows = pstmt.executeUpdate();
            System.out.println("Rows inserted: " + rows);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Step 3: Retrieve Image from Oracle and Save as File

import java.sql.*;
import java.io.*;

public class ImageDownloader {
    public static void main(String[] args) {
        String url = "jdbc:oracle:thin:@//localhost:1521/orclpdb";
        String username = "your_username";
        String password = "your_password";
        String outputFile = "/path/to/output.jpg";

        try (
            Connection conn = DriverManager.getConnection(url, username, password);
            PreparedStatement pstmt = conn.prepareStatement(
                "SELECT photo FROM user_photos WHERE id = ?");
        ) {
            pstmt.setInt(1, 101);
            ResultSet rs = pstmt.executeQuery();

            if (rs.next()) {
                InputStream input = rs.getBinaryStream("photo");
                OutputStream output = new FileOutputStream(outputFile);

                byte[] buffer = new byte[4096];
                int bytesRead;
                while ((bytesRead = input.read(buffer)) != -1) {
                    output.write(buffer, 0, bytesRead);
                }

                System.out.println("Image saved to: " + outputFile);
                output.close();
                input.close();
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

Best Practices

1. Character (Text) Data Types

Practice
Recommendation

Use variable-length strings

Prefer VARCHAR2 over CHAR to save space and avoid padding overhead.

Avoid deprecated types

Do not use VARCHAR; use VARCHAR2 instead, as Oracle may redefine VARCHAR.

Enable character semantics

Use CHARACTER SET and CHARACTER LENGTH SEMANTICS wisely, especially in multilingual applications.

Use NCHAR/NVARCHAR2 for Unicode

When storing multilingual data (e.g., Chinese, Arabic), use national character types.

Set proper length

Avoid allocating the max (e.g., 4000) unless necessary; oversized columns waste memory and I/O.

Normalize case if required

Store and compare text in consistent case (e.g., all UPPER) for indexing and querying.

2. Numeric Data Types

Practice
Recommendation

Use NUMBER(p,s) for precision

Always define precision and scale to avoid unexpected rounding or overflow.

Avoid overly large precision

Don't default to NUMBER without limits; specify NUMBER(10,2) for currency, for example.

Choose integer types wisely

For whole numbers, use NUMBER(p,0) (e.g., NUMBER(5,0) for counters).

Use BINARY_FLOAT/BINARY_DOUBLE only for scientific computing

They are faster but less precise—avoid for financial or critical data.

Avoid FLOAT for exact values

It's an approximate representation—use only when precision is not critical.

Use constraints for validation

Apply CHECK constraints for acceptable numeric ranges when business rules allow.

3. Date & Time Data Types

Practice
Recommendation

Use TIMESTAMP instead of DATE when fractional seconds matter

DATE only stores up to seconds; TIMESTAMP supports micro/nanoseconds.

Prefer TIMESTAMP WITH TIME ZONE for globally relevant data

It ensures consistent time tracking across regions.

Avoid TIMESTAMP WITH LOCAL TIME ZONE unless session-based logic is needed

Its behavior depends on user sessions and can lead to confusion.

Use INTERVAL types for durations, not DATE subtraction

INTERVAL types clearly express intent and avoid conversion issues.

Normalize time zones in application logic

Ensure consistent time zone use between DB and application layer.

Avoid storing as CHAR or VARCHAR2

Store actual dates/times using native types for indexing, sorting, and formatting.

4. Large Object (LOB) Data Types

Practice
Recommendation

Prefer CLOB/BLOB over LONG/LONG RAW

The latter are deprecated and unsupported in many modern features.

Use BLOB for binary content (images, audio)

Do not store binary files as RAW or encode them as base64 in text fields.

Use CLOB for large text (documents, logs)

Enables full-text indexing and large-capacity storage.

Consider chunked access for very large LOBs

Use streaming APIs (DBMS_LOB) to read/write in pieces instead of loading full content.

Use SecureFile LOBs if available

They provide better performance and features (e.g., compression, deduplication).

Avoid unnecessary LOB indexing

Index only if you need full-text search; LOBs are heavy and slow to scan.

Store large static files outside database

Use BFILE for read-only access to large external content when database storage is not required.

PreviousOracle SpecificNextBLOB

Last updated