Oracle 23ai SQL Domains — Writing Less Code While Enforcing More Rules

One of the quieter but genuinely useful features in Oracle 23ai is SQL Domains. It doesn’t have the headline appeal of AI Vector Search, but for development teams building new applications on Oracle, it addresses a real and persistent problem: the same validation logic duplicated across dozens of table columns, the same formatting rules re-implemented in every application layer, the same data type decisions made inconsistently by different developers over time.

SQL Domains are Oracle’s answer to this — a first-class database object that encodes business rules about a data type, reusable across any number of columns.

The Problem With How We Define Columns Today

In a typical Oracle schema built over several years, you find patterns like this:

sql

			
-- Table 1: email stored as VARCHAR2(100) with a CHECK constraint
CREATE TABLE customers (
    email VARCHAR2(100)
    CONSTRAINT chk_cust_email CHECK (email LIKE '%@%.%')
);
-- Table 2: email stored as VARCHAR2(255), different constraint
CREATE TABLE suppliers (
    contact_email VARCHAR2(255)
    CONSTRAINT chk_sup_email CHECK (contact_email LIKE '%_@_%.__%')
);
-- Table 3: email stored as VARCHAR2(200), no constraint
CREATE TABLE employees (
    work_email VARCHAR2(200)
);

		

Three tables, three different implementations of “email address.” Different max lengths, different validation rules, one has no validation. This is the reality of schemas built incrementally by multiple developers. SQL Domains eliminate this by defining the rules once.

Creating and Using SQL Domains

sql

			
-- Define a domain for email addresses
CREATE DOMAIN email_address AS VARCHAR2(255)
    DEFAULT ON NULL 'unknown@example.com'
    CONSTRAINT valid_email
        CHECK (VALUE LIKE '%_@_%.__%')
        DEFERRABLE INITIALLY IMMEDIATE
    DISPLAY LOWER(VALUE)
    ORDER LOWER(VALUE)
    ANNOTATIONS (
        'Description' IS 'Standard email address field',
        'UI_LABEL' IS 'Email Address'
    );
-- Use it in multiple tables
CREATE TABLE customers (
    customer_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name         VARCHAR2(100) NOT NULL,
    email        email_address   -- domain applied here
);
CREATE TABLE suppliers (
    supplier_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    company_name VARCHAR2(200) NOT NULL,
    contact      email_address   -- same domain, consistent rules
);
CREATE TABLE employees (
    employee_id  NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    full_name    VARCHAR2(200) NOT NULL,
    work_email   email_address   -- same domain again
);

		

Now every email column in your schema has the same validation constraint, the same display transformation (lowercase), and the same ordering behavior. Change the domain definition and it propagates to all columns using it.

Domain Properties — What Each One Does

CONSTRAINT: Validation rule. Fires on INSERT and UPDATE. The VALUE keyword refers to the column value being validated. Supports deferred constraint checking.

DEFAULT ON NULL: Value substituted when NULL is inserted. More specific than column-level DEFAULT — applies whenever NULL arrives, whether explicitly inserted or from a missing column in INSERT.

DISPLAY: Expression for display transformation. DOMAIN_DISPLAY(column_name) returns the display value without changing stored data. Useful for consistent formatting in reports.

sql

			
-- DOMAIN_DISPLAY returns the display expression result
SELECT customer_id,
       DOMAIN_DISPLAY(email) formatted_email,  -- runs LOWER(VALUE)
       email raw_email                          -- stored value unchanged
FROM customers;

		

ORDER: Expression for sorting. DOMAIN_ORDER(column_name) returns the sort key. For case-insensitive sorting: ORDER LOWER(VALUE) means ORDER BY DOMAIN_ORDER(email) sorts case-insensitively without the developer remembering to add LOWER().

ANNOTATIONS: Metadata about the domain. Key-value pairs stored in the data dictionary. Useful for documentation, UI generation, and tooling integration.

Numeric and Date Domains

Domains aren’t just for VARCHAR2. They’re particularly useful for numeric business rules:

sql

			
-- Domain for monetary amounts
CREATE DOMAIN monetary_amount AS NUMBER(15,2)
    CONSTRAINT positive_amount CHECK (VALUE >= 0)
    CONSTRAINT max_amount CHECK (VALUE <= 9999999999999.99)
    ANNOTATIONS ('currency' IS 'USD', 'format' IS 'FM$999,999,999,990.00');
-- Domain for percentage values
CREATE DOMAIN percentage AS NUMBER(5,2)
    CONSTRAINT valid_pct CHECK (VALUE BETWEEN 0 AND 100)
    DEFAULT 0
    DISPLAY TO_CHAR(VALUE, 'FM990.00') || '%';
-- Domain for future dates only
CREATE DOMAIN future_date AS DATE
    CONSTRAINT must_be_future CHECK (VALUE > SYSDATE);
-- Using them together
CREATE TABLE contracts (
    contract_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    value         monetary_amount NOT NULL,
    discount_pct  percentage,
    expiry_date   future_date NOT NULL
);

		

Querying Domain Metadata

sql

			
-- View all domains in a schema
SELECT owner, name, data_type, data_length,
       default_on_null, nullable
FROM all_domains
WHERE owner = 'MY_SCHEMA';
-- View domain constraints
SELECT owner, domain_name, constraint_name,
       search_condition, status
FROM all_domain_constraints
WHERE owner = 'MY_SCHEMA';
-- Find all columns using a specific domain
SELECT owner, table_name, column_name, domain_owner, domain_name
FROM all_tab_columns
WHERE domain_name = 'EMAIL_ADDRESS'
AND domain_owner = 'MY_SCHEMA'
ORDER BY owner, table_name;

		

Enum Domains — Replacing Magic Numbers and String Constants

A particularly clean use case: enumerated values that currently live as application constants or undocumented CHECK constraints.

sql

			
-- Instead of: CHECK (status IN ('PENDING', 'ACTIVE', 'CLOSED', 'CANCELLED'))
-- scattered across multiple tables...
CREATE DOMAIN order_status AS VARCHAR2(20)
    CONSTRAINT valid_status
        CHECK (VALUE IN ('PENDING', 'PROCESSING', 'SHIPPED', 'DELIVERED',
                         'CANCELLED', 'RETURNED'))
    ANNOTATIONS ('description' IS 'Valid order lifecycle statuses');
-- Now the valid values are self-documenting in the catalog
-- Any table tracking order status uses this domain

		

The business rule lives once, in the database, visible to anyone querying the data dictionary. No hunting through application code to understand what values a status column accepts.

HELIOS BLOG

Yorum bırakın Cevabı iptal et

Oracle 23ai SQL Domains — Writing Less Code While Enforcing More Rules

Bunu paylaş:

Yorum bırakın Cevabı iptal et