Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 38 ยท Getting Started

Oracle Data Types

Learn the Oracle SQL data types: VARCHAR2, NUMBER precision and scale, DATE with time, TIMESTAMP, CLOB, BLOB, BOOLEAN in 23ai, and the empty-string rule.

Every column in an Oracle table has a data type, and the type decides what values the column accepts, how much space they use and how they sort. Oracle has fewer, more general types than many databases, and a few of its choices surprise newcomers: DATE stores a time, NUMBER covers every numeric need, and an empty string is the same as NULL.

This lesson tours the types you will meet in real schemas. The type names are Oracle-specific, so the code that defines them is read-only. Stand-ins at the end show the rounding and NULL behavior in a runnable editor.

Character types

Type Meaning Limit
VARCHAR2(n) Variable-length text. The type to use. 4000 bytes (32,767 when MAX_STRING_SIZE=EXTENDED is set)
CHAR(n) Fixed length, padded with spaces 2000 bytes
NVARCHAR2(n), NCHAR(n) Text in the national character set
CLOB Very large text (up to terabytes)

Always use VARCHAR2, never plain VARCHAR (Oracle reserves it for future change). Length can be counted in bytes or characters:

SQL
CREATE TABLE authors (
  author_id  NUMBER(10),
  name       VARCHAR2(100 CHAR),   -- 100 characters, whatever the byte size
  bio        CLOB
);

With CHAR semantics, a name such as Zoรซ with an accented letter counts as three characters, not four bytes. In an AL32UTF8 database, which is the default, VARCHAR2(100 BYTE) can hold fewer than 100 characters if some take several bytes.

NUMBER: one type for every number

NUMBER(p, s) stores numbers with up to p significant digits (precision) and s digits after the decimal point (scale).

SQL
price     NUMBER(8,2)    -- up to 999999.99
quantity  NUMBER(5)      -- whole numbers up to 99999 (scale defaults to 0)
ratio     NUMBER         -- any number, up to 38 digits

What happens when a value has more decimals than the scale? Oracle rounds it. What if it has too many digits before the point? The insert fails with ORA-01438: value larger than specified precision allowed for this column.

A negative scale rounds to the left of the point: NUMBER(5,-2) stores 12345 as 12300.

Other numeric types exist: INTEGER and INT are just NUMBER(38). BINARY_FLOAT and BINARY_DOUBLE are fast hardware floating point (they can hold NaN and infinity). FLOAT(b) uses binary precision. For money, use NUMBER, never a float.

Date and time types

Type Stores Notes
DATE Year to second, no fractions, no time zone Always has a time part, even if it is midnight
TIMESTAMP(n) Date and time with n fractional digits (default 6)
TIMESTAMP WITH TIME ZONE Timestamp plus a zone offset
TIMESTAMP WITH LOCAL TIME ZONE Stored normalized, shown in the session zone
INTERVAL YEAR TO MONTH A span such as 2 years 3 months
INTERVAL DAY TO SECOND A span such as 4 days 6 hours

DATE is the type that surprises people. SYSDATE returns the current date and time, and comparing hired = DATE '2024-05-01' matches only rows stamped exactly midnight. Later lessons show how to compare safely with ranges, and how date functions work.

Binary and special types

  • BLOB holds binary large objects such as images; RAW(n) holds short binary values.
  • ROWID is the physical address of a row, handy for fast lookups but not stable across moves.
  • JSON is a native type from 21c: documents are stored in a binary format and queried with JSON_VALUE, JSON_TABLE and dot notation.
  • VECTOR (23ai) stores embeddings for AI similarity search.
  • XMLTYPE stores XML documents.

The BOOLEAN type (23ai)

Before 23ai, Oracle had no SQL BOOLEAN column; teams used NUMBER(1) or CHAR(1) with a check constraint. Oracle Database 23ai added a true type:

SQL
CREATE TABLE tasks (
  task_id  NUMBER GENERATED ALWAYS AS IDENTITY,
  title    VARCHAR2(200) NOT NULL,
  done     BOOLEAN DEFAULT FALSE
);

SELECT title FROM tasks WHERE done;

On older releases, PL/SQL had BOOLEAN but SQL did not, so you could not select a boolean column. When your target is 19c, use NUMBER(1) CHECK (done IN (0,1)).

Type conversion happens silently when types differ: comparing a VARCHAR2 column to a number makes Oracle convert the column on every row, which can prevent index use. Match types explicitly. See conversion and NULL functions.

The empty-string rule

Oracle treats a zero-length string as NULL. That is a long-standing difference from the SQL standard and from most other databases:

SQL
SELECT CASE WHEN '' IS NULL THEN 'null' ELSE 'empty' END FROM dual;  -- null

Two consequences matter every day. A NOT NULL column rejects '', and LENGTH('') is NULL, not zero. String concatenation with || treats NULL as an empty string, so 'a' || NULL returns 'a'.

Stand-ins you can run

Rounding to a scale. NUMBER(8,2) would store 19.999 as 20.00; round does the same:

SQL Stand-in for NUMBER(8,2) rounding ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

The empty-string-equals-NULL rule. In Oracle, nullif(x, '') is unnecessary; in SQLite you need it to imitate Oracle:

SQL Stand-in for the empty string rule ยท database: bookstore

Press Run (or Ctrl + Enter) to see the result here.

Choosing types: a short guide

Pick the right type

Text VARCHAR2, CLOB for long
Numbers NUMBER, never float for money
Time DATE or TIMESTAMP
Flags BOOLEAN in 23ai

Match types in comparisons so indexes still work.

1 / 2

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does an Oracle DATE value always contain?

    DATE stores year to second. For fractions of a second use TIMESTAMP, and for a time zone use TIMESTAMP WITH TIME ZONE.
  2. 2. What happens when you insert 12.3456 into a NUMBER(6,2) column?

    Extra decimals are rounded to the scale, so the stored value is 12.35. Too many digits before the point would fail instead.
  3. 3. In Oracle, what is the value of '' (an empty string) in a VARCHAR2 column?

    Oracle treats a zero-length string as NULL, unlike most other databases.
  4. 4. Which type should you use for a monetary amount?

    NUMBER stores exact decimal values. Binary floating point types can introduce tiny rounding errors.

Summary

  • Use VARCHAR2 for text, NUMBER(p,s) for numbers, CLOB/BLOB for large objects.
  • DATE includes a time of day; TIMESTAMP adds fractional seconds and optional time zones.
  • 23ai adds a SQL BOOLEAN type, and a native JSON and VECTOR type exist too.
  • An empty string is NULL in Oracle.
  • Excess decimals round to the scale; excess integer digits raise an error.

Next: First Queries: SELECT and DUAL