When you start working with Oracle Database 23ai, one of the first things you need to learn is how to add data into your tables. Inserting data is a basic yet powerful operation because every application depends on storing and retrieving meaningful information. Oracle 23ai introduces new features like MULTIVALUE INSERT and the VECTOR data type, which make it easier to work with bulk data and AI-driven use cases. In this tutorial, you will learn how to insert data into tables in Oracle 23ai with practical examples. Each example also shows where to use COMMIT so your changes are saved permanently.
Creating a Sample Table
Before inserting rows, you need a table. Let’s create a table for storing student details.
CREATE TABLE students (
student_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
email VARCHAR2(100),
enrolled_date DATE DEFAULT SYSDATE
);
Here student_id is an identity column, so Oracle 23ai will automatically generate a unique value. The enrolled_date column has a default value of the current date, so you don’t need to provide it unless you want a custom date.
To work with Oracle Database, you need SQL Plus or Oracle SQL Developer. If you don't have it, check this guide on how to install Oracle SQL Developer on Windows.
As this tutorial is based on Oracle 23ai, if you want to learn how to install it, check this guide on how to install Oracle Database 23ai on Windows.
Inserting a Single Row into a Table in Oracle 23ai
The most common way to add a new row is with INSERT INTO ... VALUES.
INSERT INTO students (first_name, last_name, email)
VALUES ('John', 'Doe', 'john.doe@example.com');
COMMIT;
The COMMIT command is important. Without it, your data only exists in the current session. If the session ends or rolls back, the inserted row is lost. After committing, the row is permanent. To undo the insert, use the ROLLBACK command.
You can confirm the insert by running:
SELECT * FROM students;
Inserting Multiple Rows with MULTIVALUE INSERT (New in Oracle 23ai)
Oracle 23ai introduced MULTIVALUE INSERT, which lets you insert several rows in one statement. In earlier versions, you needed multiple INSERT statements or UNION ALL.
INSERT INTO students (first_name, last_name, email)
VALUES
('Alice', 'Smith', 'alice.smith@example.com'),
('Bob', 'Johnson', 'bob.johnson@example.com'),
('Emma', 'Williams', 'emma.williams@example.com');
COMMIT;
This single command inserts three rows at once. Using COMMIT makes the new rows available to everyone.
Inserting Data from Another Table
You can insert rows directly from another table. Suppose you have an old_students table with similar columns.
INSERT INTO students (first_name, last_name, email, enrolled_date) SELECT first_name, last_name, email, enrolled_date FROM old_students WHERE enrolled_date >= DATE '2025-01-01'; COMMIT;
This adds all students from old_students who enrolled after January 1, 2025.
Inserting JSON Data in Oracle 23ai
Oracle 23ai has a new JSON data type that lets you store structured JSON documents efficiently.
First, create a table with a JSON column:
CREATE TABLE products (
product_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
product_data JSON
);
Insert JSON data:
INSERT INTO products (product_data)
VALUES (JSON('{
"product_name": "Laptop",
"price": 899.99,
"specs": {
"cpu": "Intel i7",
"ram": "16GB",
"storage": "512GB SSD"
}
}'));
COMMIT;
Now query specific JSON fields:
-- Extract scalar values from JSON column
SELECT
JSON_VALUE(product_data, '$.product_name') AS product_name,
JSON_VALUE(product_data, '$.specs.cpu') AS cpu
FROM products;
This retrieves the product name and CPU from the JSON column.
Inserting Vector Data (Oracle 23ai Feature for AI)
Oracle 23ai introduces the VECTOR data type, which is designed for storing AI embeddings and supporting semantic search.
Create a table with a vector column:
CREATE TABLE documents (
doc_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title VARCHAR2(200),
content CLOB,
embedding VECTOR(4) -- 4 dimensions for demo; real AI models use 768 or more
);
Insert a document with its vector embedding:
INSERT INTO documents (title, content, embedding)
VALUES (
'AI in Healthcare',
'This document explains how AI is transforming healthcare with diagnostics and drug discovery.',
TO_VECTOR('[0.12, 0.56, -0.87, 0.33]')
);
COMMIT;
You can then run a similarity search:
SELECT doc_id, title,
VECTOR_DISTANCE(embedding, TO_VECTOR('[0.10, 0.60, -0.80, 0.30]')) AS similarity
FROM documents
ORDER BY similarity ASC
FETCH FIRST 3 ROWS ONLY;
This finds the three most similar documents to the provided embedding.
Returning Inserted Values
When inserting rows, you might want to know the newly generated ID. Use RETURNING INTO.
DECLARE
v_id NUMBER;
BEGIN
INSERT INTO students (first_name, last_name, email)
VALUES ('Sophia', 'Brown', 'sophia.brown@example.com')
RETURNING student_id INTO v_id;
COMMIT;
DBMS_OUTPUT.PUT_LINE('New Student ID: ' || v_id);
END;
/
This saves the new student and prints the auto-generated ID.
Using Default Values with INSERT INTO
If a column has a default value, you can either skip it in the insert or explicitly use DEFAULT.
INSERT INTO students (first_name, last_name, email, enrolled_date)
VALUES ('Liam', 'Davis', 'liam.davis@example.com', DEFAULT);
COMMIT;
Here the enrolled_date will automatically use the current system date.
Handling Errors with DML Error Logging
When inserting many rows, some may fail due to duplicate keys or invalid data. Oracle allows you to log those errors without stopping the entire insert.
First create an error log table:
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('students');
END;
/
Now insert with error logging:
INSERT INTO students (first_name, last_name, email)
SELECT first_name, last_name, email
FROM old_students
LOG ERRORS INTO ERR$_STUDENTS ('Insert Error') REJECT LIMIT UNLIMITED;
COMMIT;
Valid rows are saved, and any failed rows are written into ERR$_STUDENTS for analysis.
Bulk Inserts with PL/SQL FORALL
For very large datasets, the FORALL statement is faster than inserting row by row because it reduces context switching.
DECLARE
TYPE t_names IS TABLE OF VARCHAR2(50);
TYPE t_emails IS TABLE OF VARCHAR2(100);
l_first_names t_names := t_names('Ethan', 'Olivia', 'Mason');
l_last_names t_names := t_names('Green', 'Taylor', 'Clark');
l_emails t_emails := t_emails('ethan.green@example.com',
'olivia.taylor@example.com',
'mason.clark@example.com');
BEGIN
FORALL i IN 1..l_first_names.COUNT
INSERT INTO students (first_name, last_name, email)
VALUES (l_first_names(i), l_last_names(i), l_emails(i));
COMMIT;
END;
/
This example inserts three rows efficiently and commits them together.
Best Practices for Inserting Data in Oracle 23ai
Always issue a COMMIT after successful inserts so that changes are permanent. Use ROLLBACK if something goes wrong and you need to undo uncommitted inserts. Take advantage of MULTIVALUE INSERT for small sets of rows, and use FORALL for large bulk operations inside PL/SQL. Define identity columns to avoid manually handling IDs. Use the new JSON and VECTOR data types in Oracle 23ai to handle modern application needs. When inserting large datasets, enable DML error logging so errors do not stop the entire process. Always verify your data with SELECT after committing.
Conclusion
Inserting data into Oracle Database 23ai tables is straightforward, but the database also offers powerful features to handle modern use cases and large data volumes. You can insert single rows, multiple rows at once, structured JSON documents, or AI embeddings using the new VECTOR type. By combining these options with COMMIT, RETURNING INTO, bulk operations, and error logging, you ensure data accuracy, efficiency, and reliability. Learning these methods helps you build stronger applications and make the most of Oracle 23ai.
