Understanding Data Types in Oracle Database 23ai

Every value stored in an Oracle Database must follow a data type. A data type defines what kind of data can be stored, how much space it takes, and what operations you can perform on it. Oracle Database 23ai continues to support the classic set of SQL data types while also adding new ones designed for AI workloads, such as JSON and VECTOR. This article explains the common categories of data types, provides practical examples, and highlights new features introduced in 23ai.

Why Data Types Matter

Data types are the foundation of any table definition. Choosing the right data type helps you:

• Save storage space
• Improve query performance
• Enforce data integrity
• Use advanced features like indexing and constraints

Using the wrong type can cause wasted space or errors when running SQL queries.

Quick Reference Table of Data Types in Oracle 23ai

Main Categories of Data Types

Character Data Types

Used to store text values.

-- Fixed length string (always 10 characters)
CREATE TABLE fixed_char_example (
  col1 CHAR(10)
);

-- Variable length string (up to 50 characters)
CREATE TABLE var_char_example (
  col1 VARCHAR2(50)
);

• CHAR: Fixed-length, pads with spaces if shorter
• VARCHAR2: Variable-length, most common text type
• CLOB: Character large object, used for very long text

Numeric Data Types

Used for integers, decimals, and floating-point values.

CREATE TABLE number_example (
  id NUMBER(5),          -- up to 5 digits
  salary NUMBER(10,2),   -- 10 digits total, 2 after decimal
  ratio BINARY_FLOAT,    -- single-precision floating point
  measure BINARY_DOUBLE  -- double-precision floating point
);

• NUMBER(p,s): Precise numbers with optional precision and scale
• BINARY_FLOAT / BINARY_DOUBLE: Fast floating-point types for scientific calculations

Date and Time Data Types

Oracle supports storing dates, times, and time zones.

CREATE TABLE date_example (
  hire_date DATE,  
  start_time TIMESTAMP,  
  event_time TIMESTAMP WITH TIME ZONE,  
  offset_time TIMESTAMP WITH LOCAL TIME ZONE
);

• DATE: Stores date and time up to seconds
• TIMESTAMP: Stores date and time with fractional seconds
• TIMESTAMP WITH TIME ZONE: Includes time zone offset
• INTERVAL: Represents a span of time

LOB (Large Object) Data Types

Used for large unstructured data such as images, audio, or large documents.

CREATE TABLE lob_example (
  doc_id NUMBER,
  content CLOB,       -- Character large object
  picture BLOB        -- Binary large object
);

• CLOB: Character large object
• BLOB: Binary large object
• BFILE: External file reference stored outside the database

JSON Data Type in Oracle 23ai

Oracle 23ai introduces JSON as a true native type, instead of just storing JSON in CLOB or VARCHAR2. This allows faster queries, indexing, and dot-notation access.

CREATE TABLE products (
  product_id NUMBER PRIMARY KEY,
  product_data JSON
);

-- Insert JSON
INSERT INTO products VALUES (1,
  '{"name":"Laptop","specs":{"cpu":"Intel i7","ram":"16GB"}}'
);

VECTOR Data Type in Oracle 23ai

A major new feature of Oracle 23ai is the VECTOR type for storing AI embeddings.

CREATE TABLE documents (
  doc_id NUMBER PRIMARY KEY,
  content VARCHAR2(200),
  embedding VECTOR
);

-- Insert a vector
INSERT INTO documents VALUES
(1, 'Oracle Database 23ai introduces AI features', '[0.10, 0.80, 0.50]');

You can then run similarity search with functions like VECTOR_DISTANCE or build IVF vector indexes to accelerate queries.

-- Exact nearest neighbor search
SELECT doc_id, content
FROM documents
ORDER BY VECTOR_DISTANCE(embedding, '[0.15,0.75,0.55]', COSINE)
FETCH FIRST 1 ROW ONLY;

User-Defined Data Types

Oracle also supports user-defined object types for modeling complex data.

-- Define an object type
CREATE TYPE address_type AS OBJECT (
  street VARCHAR2(100),
  city   VARCHAR2(50),
  zip    VARCHAR2(10)
);

-- Use it in a table
CREATE TABLE customers (
  id NUMBER,
  name VARCHAR2(50),
  address address_type
);

Choosing the Right Data Type

• Use VARCHAR2 instead of CHAR for most text
• Use NUMBER when precision matters, BINARY_DOUBLE for scientific speed
• Use TIMESTAMP WITH TIME ZONE when dealing with global applications
• Use JSON when storing structured but flexible data
• Use VECTOR when building AI-driven applications with embeddings
• Use LOBs only when necessary, since they consume more storage

Conclusion

Data types in Oracle Database 23ai form the backbone of every table and query. By understanding the different categories, you can design efficient schemas that handle both traditional business workloads and modern AI features. With native support for JSON and VECTOR, Oracle 23ai allows developers to build applications that seamlessly combine relational data, unstructured documents, and AI-powered search.

See also:

1. How to Install Oracle Database 23ai on Windows
2. Installing Oracle SQL Developer on Windows