DEFAULT ON NULL: Smarter Column Defaults in Oracle 23ai

Oracle has had DEFAULT column values for a long time. But there was an annoying gap: if you explicitly INSERT NULL into a column that has a default, Oracle uses NULL — not the default. The default only applies when the column is omitted from the INSERT statement entirely.

Oracle 23ai closes this gap with DEFAULT ON NULL.

Classic DEFAULT behavior (still applies):

CREATE TABLE requests (
    id       NUMBER,
    priority VARCHAR2(10) DEFAULT 'NORMAL'
);

-- Omitting priority: uses default → 'NORMAL'
INSERT INTO requests (id) VALUES (1);

-- Explicitly inserting NULL: stores NULL (default ignored)
INSERT INTO requests (id, priority) VALUES (2, NULL);

With DEFAULT ON NULL:

CREATE TABLE requests (
    id       NUMBER,
    priority VARCHAR2(10) DEFAULT ON NULL 'NORMAL'
);

-- Explicitly inserting NULL: uses default → 'NORMAL'
INSERT INTO requests (id, priority) VALUES (2, NULL);

-- NULL can no longer be stored in this column via INSERT or UPDATE

Adding to an existing column:

ALTER TABLE requests MODIFY priority DEFAULT ON NULL 'NORMAL';

Real-world uses:

• Audit columns like created_by that should always have a value
• Status columns where NULL is never a valid state
• Configuration values with meaningful defaults that should survive explicit NULL inserts from ORMs or API frameworks that always pass all columns

This bridges the semantic gap between application-layer “I didn’t provide a value” and “I provided NULL,” which are often treated the same way by ORMs, REST frameworks, and bulk insert tools.