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.

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.