Oracle Data Types

Every column in an Oracle table has a data type that determines what kind of value it can store, how much space it occupies, and what operations can be performed on it. Choosing the right data type at design time is one of the most important decisions in schema design — a wrong choice causes storage waste, silent data loss from truncation, or incorrect query results that are hard to trace back to their source. This lesson covers every data type you will encounter in practical Oracle work, using the DataplexaStore and DataplexaHR schemas as concrete examples throughout.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Character Data Types

Character types store text — names, codes, descriptions, email addresses, status values. Oracle provides three character types but in practice you will use almost exclusively VARCHAR2.

VARCHAR2(n) stores variable-length text up to n bytes (or characters if you use CHAR semantics). It only uses as much storage as the actual value — a VARCHAR2(150) column storing the value sarah@email.com uses 15 bytes, not 150. The maximum length is 4000 bytes in standard SQL, or 32767 bytes if the database is configured with MAX_STRING_SIZE = EXTENDED. This is Oracle's primary text type and the one you will use for almost every text column.

CHAR(n) stores fixed-length text, always padded with spaces to exactly n bytes. A CHAR(10) column storing YES stores YES        — seven trailing spaces — and queries comparing CHAR values must account for this padding. CHAR is appropriate only for values that are always a fixed length, such as two-letter country codes or single-character flag columns.

NVARCHAR2(n) stores Unicode text using the database's national character set. It is used when you need to store characters outside the standard database character set — for example, Arabic, Japanese, or Chinese text when the database is not already configured as UTF-8. In modern Oracle installations configured with AL32UTF8, VARCHAR2 already handles all Unicode characters and NVARCHAR2 is rarely needed.

-- CHAR is only appropriate for truly fixed-length values
-- active_flag is always 'Y' or 'N' — one character, never more, never less
-- review_status is always a 2-char code — CHAR(2) is correct here
CREATE TABLE product_flags (
    product_id     NUMBER(10)    NOT NULL,
    active_flag    CHAR(1)       DEFAULT 'Y' NOT NULL,
    review_status  CHAR(2)       NOT NULL
);

-- CHAR pads stored values with spaces — this comparison returns no rows
-- because CHAR(10) turns 'pending' into 'pending   ' (3 trailing spaces)
SELECT order_id FROM orders WHERE CAST(status AS CHAR(10)) = 'pending';

-- VARCHAR2 stores exactly what was inserted — no padding, comparison works as expected
SELECT order_id FROM orders WHERE status = 'pending';

-- char_used: B = byte semantics (default), C = character semantics
SELECT column_name,
       data_type,
       char_length,
       char_used
FROM   user_tab_columns
WHERE  table_name = 'CUSTOMERS'
AND    data_type IN ('VARCHAR2','CHAR','NVARCHAR2')
ORDER BY column_id;

• Always use VARCHAR2 over CHAR unless the value is genuinely fixed-length — CHAR padding causes subtle comparison bugs that are difficult to diagnose
• Size VARCHAR2 columns to the longest realistic value, not the longest theoretical value — VARCHAR2(4000) on a column that stores job codes is poor design even though it costs no extra storage
• VARCHAR2 in Oracle is not the same as VARCHAR — Oracle recommends always using VARCHAR2 because the behaviour of VARCHAR may change in future versions

Numeric Data Types

Oracle uses a single numeric type — NUMBER — for all integers and decimal values. It is stored in a variable-length binary format that is precise to 38 significant digits. You control its behaviour with two optional parameters: precision and scale.

NUMBER(p, s) — precision p is the total number of significant digits; scale s is the number of digits to the right of the decimal point. NUMBER(10, 2) stores up to 10 digits total with 2 decimal places — values like 12345678.99. NUMBER(6) stores integers up to 999999. NUMBER with no parameters stores any numeric value up to 38 digits of precision.

Oracle also accepts ANSI standard type names as aliases: INTEGER, INT, SMALLINT, FLOAT, and DECIMAL — all are stored internally as NUMBER. There is no separate integer type in Oracle's native storage engine.

-- This INSERT succeeds — 149.99 fits within NUMBER(10,2)
INSERT INTO products (product_id, product_name, category, unit_price, stock_qty)
VALUES (99, 'Test Product', 'Electronics', 149.99, 500);

-- This fails with ORA-01438 — 12345678901.99 exceeds the total 10-digit precision of NUMBER(10,2)
INSERT INTO products (product_id, product_name, category, unit_price, stock_qty)
VALUES (100, 'Test Product', 'Electronics', 12345678901.99, 500);

-- Inspect precision and scale of numeric columns in the data dictionary
SELECT column_name,
       data_type,
       data_precision,
       data_scale
FROM   user_tab_columns
WHERE  table_name = 'PRODUCTS'
AND    data_type = 'NUMBER'
ORDER BY column_id;

-- ROUND rounds to n decimal places; TRUNC drops decimals without rounding
SELECT product_name,
       unit_price,
       ROUND(unit_price * 1.2, 2)  AS price_with_vat,
       TRUNC(unit_price, 0)        AS price_floor,
       MOD(stock_qty, 10)          AS remainder
FROM   products
WHERE  active_flag = 'Y'
ORDER BY unit_price DESC;

• Always specify precision and scale for financial columns — NUMBER without parameters allows any value up to 38 digits, which means invalid data can be stored silently
• ROUND(x, n) rounds to n decimal places; TRUNC(x, n) truncates without rounding — use TRUNC when you need to strip decimals from a price for display without changing the stored value
• Oracle does not have separate INT, BIGINT, or FLOAT storage types — all numeric data is stored as NUMBER internally regardless of what alias you use in the column definition

Date and Time Data Types

Date and time handling is one of the most important differences between Oracle and other databases. Oracle provides four types, and the choice between them matters significantly for applications that deal with timezones, sub-second precision, or interval arithmetic.

DATE stores both date and time — year, month, day, hour, minute, second. This surprises developers coming from SQL Server or MySQL where DATE stores only the date part. In Oracle, DATE '2024-01-15' is stored as 15-JAN-2024 00:00:00 — midnight on that day. Functions like SYSDATE return the current date and time as a DATE.

TIMESTAMP(n) extends DATE by adding fractional seconds with up to 9 digits of precision. TIMESTAMP(6) stores microseconds; TIMESTAMP(3) stores milliseconds. Use TIMESTAMP when sub-second precision matters — for event logging, audit trails, or financial transaction timestamps.

TIMESTAMP WITH TIME ZONE stores the timestamp together with the timezone offset. Use this when your application stores data from multiple time zones and you need to preserve the original local time along with its offset from UTC.

TIMESTAMP WITH LOCAL TIME ZONE converts the stored value to the database timezone on storage and converts it back to the session timezone on retrieval. This is the recommended type for applications where all users should see times in their local timezone.

-- Oracle DATE stores date AND time — TRUNC strips the time portion
-- DUAL is Oracle's built-in single-row dummy table for evaluating expressions
SELECT SYSDATE                                    AS current_datetime,
       TRUNC(SYSDATE)                             AS date_only,
       TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') AS formatted
FROM   DUAL;

-- Oracle DATE arithmetic works in whole days — fractions of a day represent hours (1/24 = 1 hour)
SELECT order_id,
       order_date,
       order_date + 7              AS due_in_7_days,
       order_date + (2/24)         AS plus_2_hours,
       ROUND(SYSDATE - order_date, 0) AS days_since_order
FROM   orders
ORDER BY order_date;

-- SYSTIMESTAMP includes fractional seconds and timezone offset
SELECT SYSTIMESTAMP                              AS current_ts,
       SYSTIMESTAMP AT TIME ZONE 'UTC'           AS utc_time,
       SYSTIMESTAMP AT TIME ZONE 'Europe/London' AS london_time
FROM   DUAL;

-- Always TRUNC a DATE column when comparing to a date-only value
-- A stored value of '10-JAN-2024 09:45:00' will NOT equal DATE '2024-01-10' (which is midnight)
SELECT order_id, order_date
FROM   orders
WHERE  TRUNC(order_date) = DATE '2024-01-10';

• Oracle DATE always stores time — when you insert DATE '2024-01-15' Oracle stores midnight; always use TRUNC(date_column) in WHERE clauses when comparing date-only values to avoid missing rows that have a non-midnight time component
• DUAL is a special one-row, one-column table that Oracle provides for evaluating expressions — use it when you need to run a query that does not read from any real table, such as calling SYSDATE or a sequence's NEXTVAL
• Use TIMESTAMP WITH LOCAL TIME ZONE in applications serving users across multiple time zones — Oracle handles the conversion automatically so your application code does not need to manage timezone offsets manually

Large Object (LOB) Data Types

Standard character types cap out at 4000 bytes. For storing large volumes of text, binary files, or XML documents you need a LOB type. Oracle provides four LOB types, each suited to a different kind of large data.

CLOB (Character Large Object) stores large text — up to 128 TB. Use it for long product descriptions, article bodies, HTML content, or any column that regularly exceeds 4000 characters. CLOB uses the database character set.

NCLOB is the Unicode equivalent of CLOB — it uses the national character set. Use it when storing large Unicode text that cannot be represented in the database character set.

BLOB (Binary Large Object) stores raw binary data — images, PDFs, audio files, video files. Up to 128 TB. Generally it is better practice to store file paths in the database and keep the actual files on a file server or cloud storage, but BLOB exists for cases where binary storage in the database is required.

BFILE stores a pointer to a file stored outside the database on the operating system. The file is read-only from Oracle's perspective — Oracle does not manage its contents. Useful when files must remain on the filesystem but you want to reference them from SQL.

-- CLOB is needed when content regularly exceeds VARCHAR2's 4000-byte limit
ALTER TABLE products
ADD (full_description CLOB);

UPDATE products
SET    full_description = 'The Wireless Noise-Cancelling Headphones feature industry-leading
active noise cancellation powered by three microphones. Designed for all-day comfort with
memory foam ear cushions and an adjustable headband. The 30-hour battery life with quick
charge (10 minutes for 3 hours of playback) ensures you are never left without sound.
Compatible with all Bluetooth 5.0 devices. Includes a carrying case and 3.5mm audio cable
for wired use when battery is depleted.'
WHERE  product_name = 'Wireless Noise-Cancelling Headphones';

-- LENGTH() does not work on CLOB — use DBMS_LOB.GETLENGTH instead
SELECT product_name,
       DBMS_LOB.GETLENGTH(full_description) AS description_chars
FROM   products
WHERE  full_description IS NOT NULL;

SELECT column_name,
       data_type
FROM   user_tab_columns
WHERE  table_name = 'PRODUCTS'
AND    data_type IN ('CLOB','NCLOB','BLOB','BFILE')
ORDER BY column_id;

• CLOB columns cannot be used in WHERE clause equality comparisons directly — use DBMS_LOB.INSTR or LIKE for text searches, or Oracle Text for full-text indexing
• Avoid storing large binary files as BLOB unless there is a specific requirement to do so — storing image and document files on a filesystem or object storage (S3, Azure Blob) and saving the URL in a VARCHAR2 column is simpler to manage and more performant
• LENGTH() does not work on CLOB columns — always use DBMS_LOB.GETLENGTH() to measure the size of a LOB value

Summary

Practice Questions

Quiz

Next up — Creating Tables in Oracle — Learn how to define tables with the correct data types, constraints, and defaults using everything covered in this lesson applied directly to the DataplexaStore and DataplexaHR schemas.