Data Types


Master VARCHAR2, NUMBER, DATE, and more for efficient data storage and retrieval in Oracle Database.

VARCHAR2

The VARCHAR2 data type stores variable-length character strings. It is commonly used for text data where the length can vary, saving storage space.

Example 1: VARCHAR2 Declaration

CREATE TABLE employees (
    employee_id NUMBER(6),
    first_name VARCHAR2(20) -- Stores employee's first name, up to 20 characters
);

Explanation This code creates a table employees with a first_name column defined as VARCHAR2(20), allowing it to store character strings up to 20 bytes long.

Example 2: Inserting VARCHAR2 Data

INSERT INTO employees (employee_id, first_name)
VALUES (101, 'John'); -- Inserts 'John' into the first_name column

Explanation This statement inserts a record into the employees table, populating the first_name column with the string 'John'.

Example 3: Updating VARCHAR2 Data

UPDATE employees
SET first_name = 'Jonathan'
WHERE employee_id = 101; -- Updates the first_name for employee_id 101 to 'Jonathan'

Explanation This SQL updates the first_name of the employee with employee_id 101 to 'Jonathan'.

Example 4: Selecting VARCHAR2 Data

SELECT first_name
FROM employees
WHERE employee_id = 101; -- Retrieves the first_name for employee_id 101

Explanation This query selects and displays the first_name of the employee whose employee_id is 101.

Example 5: VARCHAR2 with NULL

INSERT INTO employees (employee_id, first_name)
VALUES (102, NULL); -- Inserts NULL into the first_name column

Explanation This example demonstrates inserting a NULL value into the first_name column, indicating the absence of a value.

 

NVARCHAR2

The NVARCHAR2 data type stores variable-length Unicode character strings. It is ideal for storing multilingual data, ensuring proper character representation across different languages.

Example 1: NVARCHAR2 Declaration

CREATE TABLE products (
    product_id NUMBER(6),
    product_name NVARCHAR2(50) -- Stores product name in Unicode, up to 50 characters
);

Explanation This code creates a products table with a product_name column defined as NVARCHAR2(50) for storing Unicode characters.

Example 2: Inserting NVARCHAR2 Data

INSERT INTO products (product_id, product_name)
VALUES (1, 'Café'); -- Inserts 'Café' (with an accent) into product_name

Explanation This statement inserts a record into the products table, populating the product_name column with the Unicode string 'Café'.

Example 3: Updating NVARCHAR2 Data

UPDATE products
SET product_name = '컴퓨터'
WHERE product_id = 1; -- Updates product_name to a Korean word

Explanation This SQL updates the product_name for product_id 1 to a Korean word, demonstrating NVARCHAR2's Unicode capability.

Example 4: Selecting NVARCHAR2 Data

SELECT product_name
FROM products
WHERE product_id = 1; -- Retrieves the Unicode product name

Explanation This query selects and displays the product_name from the products table for product_id 1.

Example 5: NVARCHAR2 Length Semantics

CREATE TABLE descriptions (
    item_id NUMBER,
    item_desc NVARCHAR2(10 CHAR) -- Defines length in characters, not bytes
);

Explanation This example shows NVARCHAR2 defined with CHAR length semantics, meaning it stores up to 10 characters regardless of byte length.

 

CHAR

The CHAR data type stores fixed-length character strings. If the inserted string is shorter than the defined length, it is padded with spaces to the maximum length.

Example 1: CHAR Declaration

CREATE TABLE categories (
    category_id CHAR(2), -- Stores category ID as a fixed 2-character string
    category_name VARCHAR2(30)
);

Explanation This code creates a categories table with a category_id column defined as CHAR(2), which will always store exactly 2 characters.

Example 2: Inserting CHAR Data

INSERT INTO categories (category_id, category_name)
VALUES ('A1', 'Electronics'); -- Inserts 'A1' into category_id

Explanation This statement inserts a record into the categories table, populating the category_id column with 'A1'.

Example 3: CHAR Padding

INSERT INTO categories (category_id, category_name)
VALUES ('B', 'Books'); -- 'B' will be padded to 'B '

Explanation When 'B' is inserted into the CHAR(2) column, Oracle pads it with a space to make it 'B '.

Example 4: Comparing CHAR Data

SELECT category_name
FROM categories
WHERE category_id = 'A1'; -- Comparison handles padding implicitly

Explanation This query retrieves category_name where category_id is 'A1'; Oracle handles the comparison correctly even if the stored value is padded.

Example 5: CHAR with Fixed-Length Codes

CREATE TABLE status_codes (
    code CHAR(3), -- Ideal for fixed-length codes like 'ACT', 'PEN'
    description VARCHAR2(50)
);

Explanation This example demonstrates CHAR used for code where a fixed length of 3 characters is expected, suitable for status codes.

 

NUMBER

The NUMBER data type stores fixed-point or floating-point numbers. It offers high precision and scale, suitable for various numerical data including currency and scientific measurements.

Example 1: NUMBER Declaration (Precision and Scale)

CREATE TABLE accounts (
    account_id NUMBER(10), -- Stores integers up to 10 digits
    balance NUMBER(10, 2) -- Stores numbers with 10 total digits, 2 after decimal
);

Explanation This code defines balance as NUMBER(10, 2), allowing up to 8 digits before the decimal point and exactly 2 after it.

Example 2: Inserting NUMBER Data

INSERT INTO accounts (account_id, balance)
VALUES (1001, 12345.67); -- Inserts a number with precision and scale

Explanation This statement inserts 12345.67 into the balance column, which fits the NUMBER(10, 2) definition.

Example 3: NUMBER with Implicit Scale

INSERT INTO accounts (account_id, balance)
VALUES (1002, 500); -- Inserts 500, which becomes 500.00 due to scale 2

Explanation When 500 is inserted into a NUMBER(10, 2) column, it is stored as 500.00, automatically adjusting to the defined scale.

Example 4: NUMBER for Integers (No Scale)

CREATE TABLE orders (
    order_id NUMBER(8), -- Stores integers up to 8 digits
    quantity NUMBER(3) -- Stores integers up to 3 digits
);

Explanation This example uses NUMBER(8) for order_id and NUMBER(3) for quantity, both implicitly having a scale of 0 (integers).

Example 5: NUMBER for Large Precision

CREATE TABLE sensor_readings (
    reading_id NUMBER, -- Defaults to maximum precision and scale
    temperature NUMBER(38, 10) -- Stores very precise temperature readings
);

Explanation This code demonstrates NUMBER(38, 10) for very precise numerical data, allowing up to 38 digits with 10 decimal places.

 

INTEGER

The INTEGER data type is a subtype of NUMBER used to store whole numbers. It typically maps to NUMBER(38,0), providing a wide range for integer values.

Example 1: INTEGER Declaration

CREATE TABLE products_inventory (
    product_id INTEGER, -- Stores product ID as a whole number
    stock_level INTEGER -- Stores stock quantity as a whole number
);

Explanation This code defines product_id and stock_level as INTEGER columns, suitable for whole number values.

Example 2: Inserting INTEGER Data

INSERT INTO products_inventory (product_id, stock_level)
VALUES (1001, 500); -- Inserts integer values

Explanation This statement inserts integer values into the product_id and stock_level columns.

Example 3: INTEGER with Negative Values

INSERT INTO products_inventory (product_id, stock_level)
VALUES (1002, -10); -- Inserts a negative integer

Explanation This example demonstrates that INTEGER can store negative whole numbers.

Example 4: INTEGER in Calculations

UPDATE products_inventory
SET stock_level = stock_level - 10
WHERE product_id = 1001; -- Performs arithmetic on an INTEGER column

Explanation This SQL updates stock_level by subtracting 10, demonstrating arithmetic operations on INTEGER columns.

Example 5: INTEGER and Truncation

INSERT INTO products_inventory (product_id, stock_level)
VALUES (1003, 10.5); -- 10.5 will be implicitly truncated to 10

Explanation When a decimal value is inserted into an INTEGER column, Oracle automatically truncates the decimal part.

 

FLOAT

The FLOAT data type stores floating-point numbers with binary precision. It is used for numbers requiring approximate precision, such as scientific calculations where exact decimal representation is not critical.

Example 1: FLOAT Declaration

CREATE TABLE scientific_data (
    data_id NUMBER,
    measurement FLOAT(126) -- Stores floating-point numbers with maximum precision
);

Explanation This code defines measurement as FLOAT(126), allowing it to store floating-point numbers with up to 126 binary digits of precision.

Example 2: Inserting FLOAT Data

INSERT INTO scientific_data (data_id, measurement)
VALUES (1, 3.14159265); -- Inserts a floating-point number

Explanation This statement inserts a floating-point value into the measurement column.

Example 3: FLOAT Precision Differences

INSERT INTO scientific_data (data_id, measurement)
VALUES (2, 0.3333333333333333); -- Demonstrates potential precision limitations

Explanation This example shows how FLOAT stores an approximation, as the exact decimal value may not be perfectly represented in binary.

Example 4: FLOAT in Mathematical Operations

SELECT measurement * 2
FROM scientific_data
WHERE data_id = 1; -- Performs multiplication on a FLOAT column

Explanation This query performs a multiplication operation on the measurement column, demonstrating FLOAT's use in calculations.

Example 5: FLOAT and Comparison Caution

SELECT data_id
FROM scientific_data
WHERE measurement = 3.14159265; -- Direct equality comparison with FLOAT can be problematic

Explanation Direct equality comparisons with FLOAT values can be unreliable due to their approximate nature; consider using a range-based comparison.

 

DATE

The DATE data type stores date and time information, including year, month, day, hour, minute, and second. It is commonly used for storing timestamps for events or records.

Example 1: DATE Declaration

CREATE TABLE events (
    event_id NUMBER,
    event_date DATE -- Stores date and time of the event
);

Explanation This code defines event_date as a DATE column, capable of storing both date and time components.

Example 2: Inserting Current Date

INSERT INTO events (event_id, event_date)
VALUES (1, SYSDATE); -- Inserts the current system date and time

Explanation This statement inserts the current system date and time into the event_date column using SYSDATE.

Example 3: Inserting Specific Date

INSERT INTO events (event_id, event_date)
VALUES (2, TO_DATE('2025-06-15 10:30:00', 'YYYY-MM-DD HH24:MI:SS')); -- Inserts a specific date and time

Explanation This example uses TO_DATE to insert a specific date and time string into the event_date column.

Example 4: Date Arithmetic

SELECT event_date + 7
FROM events
WHERE event_id = 1; -- Adds 7 days to the event date

Explanation This query adds 7 days to the event_date of the event with event_id 1, demonstrating date arithmetic.

Example 5: Extracting Date Components

SELECT TO_CHAR(event_date, 'YYYY-MM-DD HH:MI:SS AM')
FROM events
WHERE event_id = 1; -- Formats the date for display

Explanation This query uses TO_CHAR to format the event_date into a readable string, extracting various components.

 

TIMESTAMP

The TIMESTAMP data type extends DATE by including fractional seconds precision. It is ideal for applications requiring high precision time tracking, such as logging or financial transactions.

Example 1: TIMESTAMP Declaration

CREATE TABLE logs (
    log_id NUMBER,
    log_time TIMESTAMP(6) -- Stores timestamp with 6 decimal places for seconds
);

Explanation This code defines log_time as TIMESTAMP(6), allowing for nanosecond precision in the time component.

Example 2: Inserting Current Timestamp

INSERT INTO logs (log_id, log_time)
VALUES (1, SYSTIMESTAMP); -- Inserts the current system timestamp with fractional seconds

Explanation This statement inserts the current system timestamp, including fractional seconds, into the log_time column.

Example 3: Inserting Specific Timestamp

INSERT INTO logs (log_id, log_time)
VALUES (2, TO_TIMESTAMP('2025-06-15 10:30:00.123456', 'YYYY-MM-DD HH24:MI:SS.FF')); -- Inserts a specific timestamp

Explanation This example uses TO_TIMESTAMP to insert a specific timestamp with fractional seconds into the log_time column.

Example 4: TIMESTAMP with Time Zone

CREATE TABLE appointments (
    appt_id NUMBER,
    appt_time TIMESTAMP WITH TIME ZONE -- Stores timestamp with time zone information
);

Explanation This code defines appt_time as TIMESTAMP WITH TIME ZONE, crucial for applications spanning multiple time zones.

Example 5: TIMESTAMP Arithmetic

SELECT log_time + INTERVAL '1' HOUR
FROM logs
WHERE log_id = 1; -- Adds one hour to the timestamp

Explanation This query adds one hour to the log_time of the log with log_id 1, demonstrating timestamp arithmetic.

 

CLOB

The CLOB (Character Large Object) data type stores large blocks of character data, up to 4 gigabytes or more depending on system configuration. It is used for storing extensive text, such as documents or articles.

Example 1: CLOB Declaration

CREATE TABLE documents (
    doc_id NUMBER,
    doc_content CLOB -- Stores large text documents
);

Explanation This code defines doc_content as a CLOB column, suitable for storing very large amounts of text.

Example 2: Inserting CLOB Data (Short String)

INSERT INTO documents (doc_id, doc_content)
VALUES (1, 'This is a short test document for CLOB storage.'); -- Inserts a small string into CLOB

Explanation Even small strings can be inserted into a CLOB column, which handles them efficiently.

Example 3: Inserting CLOB from File (conceptual)

-- This is a conceptual example. Actual CLOB loading often involves client-side programming
-- or using Oracle utilities like SQL*Loader or DBMS_LOB package.
-- INSERT INTO documents (doc_id, doc_content) VALUES (2, EMPTY_CLOB()) RETURNING doc_content INTO :clob_locator;
-- Then, in client application, write file content to :clob_locator

Explanation Loading large CLOB data usually involves streaming content from a file using specific programming interfaces or Oracle utilities.

Example 4: Updating CLOB Data

UPDATE documents
SET doc_content = doc_content || CHR(10) || 'Appended more content.'
WHERE doc_id = 1; -- Appends text to existing CLOB content

Explanation This SQL appends additional text to the doc_content of the document with doc_id 1.

Example 5: Selecting CLOB Data (Partial)

SELECT SUBSTR(doc_content, 1, 50) AS partial_content
FROM documents
WHERE doc_id = 1; -- Retrieves the first 50 characters of the CLOB

Explanation This query retrieves only the first 50 characters of the CLOB content, useful for previewing large documents.

 

BLOB

The BLOB (Binary Large Object) data type stores large blocks of unstructured binary data, such as images, audio, or video files. Like CLOB, it can store up to 4 gigabytes or more.

Example 1: BLOB Declaration

CREATE TABLE media_files (
    file_id NUMBER,
    file_data BLOB -- Stores binary data like images or videos
);

Explanation This code defines file_data as a BLOB column, designed for storing raw binary information.

Example 2: Inserting BLOB Data (conceptual)

-- Similar to CLOB, BLOB data is typically loaded from files
-- using client-side programming or Oracle utilities.
-- INSERT INTO media_files (file_id, file_data) VALUES (1, EMPTY_BLOB()) RETURNING file_data INTO :blob_locator;
-- Then, in client application, write binary file content to :blob_locator

Explanation Inserting large BLOB data involves streaming binary content from a file using client applications or specialized Oracle tools.

Example 3: BLOB for Image Storage

INSERT INTO media_files (file_id, file_data)
VALUES (2, HEXTORAW('89504E470D0A1A0A0000000D49484452...')); -- Example of inserting a small piece of binary data (PNG header)

Explanation This example shows a conceptual insertion of binary data (represented as hexadecimal) into a BLOB column.

Example 4: Updating BLOB Data (conceptual)

-- Updating BLOB often involves replacing the entire content or using DBMS_LOB functions for partial updates.
-- UPDATE media_files SET file_data = EMPTY_BLOB() WHERE file_id = 1;
-- Then, reload the new binary data.

Explanation Updating BLOB data usually means replacing the entire binary content, often done via client-side code interacting with DBMS_LOB.

Example 5: Retrieving BLOB Size

SELECT DBMS_LOB.GETLENGTH(file_data) AS blob_size_bytes
FROM media_files
WHERE file_id = 1; -- Retrieves the size of the BLOB in bytes

Explanation This query uses DBMS_LOB.GETLENGTH to determine the size of the BLOB data in bytes, useful for managing storage.

 

RAW

The RAW data type stores binary data of variable length, similar to VARCHAR2 but for uninterpreted byte sequences. It is often used for storing data that does not have a character set, such as encrypted data or object IDs.

Example 1: RAW Declaration

CREATE TABLE encrypted_data (
    data_id NUMBER,
    encrypted_value RAW(2000) -- Stores raw binary data up to 2000 bytes
);

Explanation This code defines encrypted_value as RAW(2000), allowing it to store up to 2000 bytes of uninterpreted binary data.

Example 2: Inserting RAW Data (Hexadecimal)

INSERT INTO encrypted_data (data_id, encrypted_value)
VALUES (1, HEXTORAW('0123456789ABCDEF')); -- Inserts binary data from a hexadecimal string

Explanation This statement inserts binary data into the encrypted_value column by converting a hexadecimal string using HEXTORAW.

Example 3: Generating RAW (SYS_GUID)

INSERT INTO encrypted_data (data_id, encrypted_value)
VALUES (2, SYS_GUID()); -- Inserts a globally unique identifier (RAW)

Explanation This example inserts a unique 16-byte RAW value generated by SYS_GUID(), commonly used for unique keys.

Example 4: Selecting RAW Data (Hexadecimal)

SELECT RAWTOHEX(encrypted_value) AS hex_representation
FROM encrypted_data
WHERE data_id = 1; -- Retrieves RAW data as its hexadecimal representation

Explanation This query converts the RAW data to its hexadecimal string representation for display using RAWTOHEX.

Example 5: RAW for Hash Values

CREATE TABLE user_hashes (
    user_id NUMBER,
    password_hash RAW(32) -- Stores a 32-byte hash value
);

Explanation This example uses RAW(32) to store fixed-length cryptographic hash values, which are typically binary.

 

ROWID

The ROWID data type stores the physical address of a row in an Oracle database. It is a pseudocolumn, meaning it's not a true column but a unique identifier for each row's physical location.

Example 1: Selecting ROWID

SELECT employee_id, first_name, ROWID
FROM employees
WHERE employee_id = 101; -- Retrieves the ROWID for a specific row

Explanation This query retrieves the ROWID along with other column data for the employee with employee_id 101.

Example 2: Using ROWID for Fast Access (Caution)

SELECT *
FROM employees
WHERE ROWID = 'AAARn0AAFAAAABSAGA'; -- Accesses a row directly using its ROWID (example ROWID)

Explanation Using ROWID in a WHERE clause provides the fastest possible access to a specific row, as it points directly to the row's physical location.

Example 3: ROWID Uniqueness

SELECT COUNT(DISTINCT ROWID) AS unique_rowids, COUNT(*) AS total_rows
FROM employees; -- Demonstrates that ROWID is unique for each row

Explanation This query shows that ROWID is unique for every row in a table, even if other column values are duplicated.

Example 4: ROWID and Table Movement

-- ROWID can change if the row is moved (e.g., due to table reorganization, export/import).
-- Example: Altering a table to move data segment
-- ALTER TABLE employees MOVE; -- This operation can change ROWIDs

Explanation It's important to know that ROWID is a physical address and can change if the table's physical storage is altered (e.g., ALTER TABLE MOVE).

Example 5: ROWID in JOIN (Less Common)

-- Although possible, joining on ROWID is rare and generally not recommended
-- unless there's a specific, performance-critical reason and deep understanding of ROWID behavior.
-- SELECT e.first_name, d.doc_content
-- FROM employees e, documents d
-- WHERE e.ROWID = d.ROWID; -- Very unlikely to match

Explanation While syntactically possible, joining tables based on their ROWID is highly unusual and not practical for most scenarios, as ROWID is table-specific.