Oracle 23ai: New INSERT Statement Features and Practical Examples (Part 2)

 

Introduction

With Oracle Database 23ai, the humble INSERT statement has received some genuinely useful upgrades. What was once a straightforward data-loading operation now supports more advanced practical use cases, especially for modern applications. Whether you’re working with AI-related data, handling high-volume inserts, or trying to simplify everyday SQL logic, these new features are worth paying attention to. Oracle has clearly focused on reducing complexity while improving performance and flexibility. In this blog, I’ll walk through four notable INSERT enhancements in Oracle 23ai. Each one addresses a real-world problem that developers and DBAs commonly face.

Prerequisites

To follow along with this blog and reproduce the examples, the following environment and database objects were used:

• Oracle Database 23ai Free, running on a VirtualBox Appliance, with a database version 23.9 or higher.
• The HR sample schema (which exists in Oracle AI Database 23ai Free VirtualBox Appliance)
• Vector Table: The DOCS table must be created to demonstrate AI Vector Search integration:

CREATE TABLE docs (
    id          NUMBER,
    title       VARCHAR2(100),
    embedding   VECTOR(3)
);

1. INSERT... RETURNING

INSERT … RETURNING allows you to immediately retrieve values from a row you just inserted, without running a separate SELECT statement.

When you insert a row, Oracle can return column values such as generated primary keys, sequence values, defaults, or computed columns directly back to your application or PL/SQL variables. This is especially useful when the database generates values automatically (for example, an identity column).

Because the data is returned as part of the same operation, it reduces round-trips between the application and the database and improves performance. It also makes code cleaner and easier to maintain.

Example:

In this example, I first create a sequence to generate the employee_id, similar to how primary keys are typically handled in real-world applications. I then use this sequence in the INSERT statement and capture the generated value using the RETURNING clause. This demonstrates the real power of INSERT … RETURNING, allowing the database-generated identifier to be retrieved immediately without requiring an additional query.

CREATE SEQUENCE emp_id_seq START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

VARIABLE v_emp_id NUMBER

INSERT INTO employees (employee_id, first_name, last_name, Email, Hire_date, JOB_ID)
VALUES (emp_id_seq.NEXTVAL, 'Amir', 'Kordestani', 'k.amir@eclipsys.com', sysdate, 'DB_ADMIN')
RETURNING employee_id
INTO :v_emp_id;

COMMIT;

PRINT v_emp_id  

2. Inserting AI Vector Data

Vector data allows the database to store and manage AI embeddings directly alongside relational data, eliminating the need for external vector stores. From a developer’s perspective, the most compelling part is how simple this feels—vector data can be inserted using familiar SQL syntax. This makes it easy to enrich existing applications with semantic search and AI-driven features without changing the overall data model. The following example shows just how straightforward it is to insert vector embeddings into a table.

Example:

In this example, a new document record is inserted into the DOCS table (I provided the table definition in the prerequisite section of this blog as well) along with its corresponding vector embedding. The embedding column is defined using the AI_VECTOR data type and stores a three-dimensional vector representing the document’s semantic meaning. The AI_VECTOR constructor is used to supply the individual vector values directly in the INSERT statement. Aside from the vector column, the operation looks no different from a standard insert, which highlights how naturally vector data integrates with relational tables. Once stored, this vector can be used for similarity searches and other AI-driven queries.

CREATE TABLE docs (
    id          NUMBER,
    title       VARCHAR2(100),
    embedding   VECTOR(3)
);

INSERT INTO docs (id, title, embedding)
VALUES (
    101,
    'Quarterly Sales Strategy',
     VECTOR('[0.75, 0.22, 0.41]') 
);  

set line 300;
col title format  a30;
col embedding format a60;
SELECT id, title, embedding
FROM docs
WHERE id = 101;

3. DEFAULT ON NULL for INSERT

Before Oracle 12c, column default values were only applied when a column was omitted from an INSERT. If a user or application explicitly inserted NULL, the default was ignored and NULL was stored instead. Oracle 12c improved this behavior by introducing DEFAULT ON NULL, allowing defaults to be applied even when NULL is provided.

With Oracle 23ai, this feature has been refined further with clearer and more explicit syntax such as DEFAULT ON NULL FOR INSERT ONLY and DEFAULT ON NULL FOR INSERT AND UPDATE. These options make it easier to control exactly when a default value should be used.

Using DEFAULT ON NULL FOR INSERT ONLY is especially useful when you want to ensure that newly inserted rows always have meaningful values, while still allowing updates to set the column to NULL later if that makes sense for the application.

Why DEFAULT ON NULL FOR INSERT ONLY Also Helps Performance

Using a default value instead of allowing NULL can also help with performance, especially for queries and indexes. In real systems, columns with many NULL values often lead to more complex SQL, extra IS NULL checks, and functions like NVL or COALESCE, which can make execution plans less predictable.

This becomes more important with indexes. In Oracle, B-tree indexes do not store rows where all indexed columns are NULL, so those rows may be skipped entirely during index access. As a result, the optimizer may fall back to full table scans or less efficient plans.

By applying a meaningful default value at insert time, more rows become indexable from the start. This usually leads to simpler queries, more consistent execution plans, and indexes that behave as expected. While not a guaranteed win in every case, using DEFAULT ON NULL FOR INSERT ONLY is a small design choice that often improves both data quality and query behavior in transactional systems.

Example:

In this example, I make sure that every new country gets a region, even when the application explicitly inserts NULL. Instead of storing NULL, a default value (region_id = 5, meaning Unspecified) is applied at insert time. The syntax for changing the column behavior is shown below.

ALTER TABLE countries
MODIFY region_id DEFAULT ON NULL FOR INSERT ONLY 5;

Note: In the above image, you can see that the REGION_ID column appears as NOT NULL. It’s important to understand that DEFAULT ON NULL does not logically add a NOT NULL constraint; rather, DESCRIBE shows it as NOT NULL because Oracle can now guarantee that a value is assigned at insert time.

Insert a Row with an Explicit NULL. Even though NULL is explicitly provided, Oracle replaces it with the default value because of DEFAULT ON NULL FOR INSERT ONLY.

NOTE: Because COUNTRIES.region_id has a foreign key (COUNTR_REG_FK) to the REGIONS table, I’ll insert region_id = 5 into REGIONS so the parent key exists.

INSERT INTO regions (region_id, region_name)
VALUES (5, 'Unspecified');

INSERT INTO countries (country_id, country_name, region_id)
VALUES ('XX', 'Atlantis', NULL);

SELECT country_id, country_name, region_id
FROM countries
WHERE country_id = 'XX';

Conclusion

The INSERT enhancements in Oracle Database 23ai may seem incremental at first, but together they make a noticeable difference in day-to-day development. Features like INSERT … RETURNING help streamline application logic, while vector data insertion opens the door to native AI use cases, and DEFAULT ON NULL reduces the need for defensive SQL coding. These changes reflect Oracle’s continued effort to modernize SQL without breaking familiar patterns. If you work regularly with Oracle databases, these features are well worth incorporating into your workflow. They make data insertion cleaner, faster, and more aligned with how applications are built today.