Oracle SQL INSERT: Getting Started with Adding Data

Bronze_en

Introduction

The basic operation for adding a new row (record) to a database is oracle insert. This article explains the correct way to use insert oracle with plain language and “copy-and-run” examples. We verified everything on Oracle Database 19c, covering: creating a user → creating a table → INSERT → verification → error handling.

See here for information on creating a table.

A Complete Guide to Creating Tables in Oracle Database [CREATE TABLE Explained]
Creating tables is one of the most fundamental skills when working with Oracle Database.This article provides a thorough...
oracle-master00000.com
2025.07.30

Page Goals (learn in ~3 minutes)

  • Understand the safe way to write INSERT (explicit column list).
  • Write dates, timestamps, and numbers without mistakes.
  • Explain the difference and proper use of IDENTITY and SEQUENCE.
  • Diagnose common errors and fixes on your own.

Start with the Basics: How to Think About insert oracle

An INSERT statement specifies “which table,” “which columns,” and “what values.” To make your code resilient to future changes, always specify column names.

Example: Basic Form

-- Recommended: specify column names (safe and readable)
INSERT INTO table_name (col1, col2, ...) VALUES (val1, val2, ...);

-- Not recommended: rely on all columns in order (breaks when columns are added)
INSERT INTO table_name VALUES (val1, val2, ...);

Key Points

  • Columns omitted in the INSERT list receive DEFAULT values; if no default, they become NULL.
  • Omitting a NOT NULL column raises an error (ORA-01400).
  • For dates and times, explicitly specify formats using TO_DATE / TO_TIMESTAMP for safety.

Note: Omitting Column Names and Specifying NULL

  • When supplying values for every column, you may omit the column list (but this is fragile if columns are added later; in practice, specifying columns is safer).
  • If you want to leave a column empty, write NULL inside VALUES. (If you omit a column, the DEFAULT applies; if no default, it becomes NULL.)
-- Omitting the column list (provide values for all columns in table order)
INSERT INTO customers
VALUES (DEFAULT, 'Foo', 'foo@example.com', SYSDATE, 'A');

-- Explicitly insert NULL for columns (set joined_on and email to NULL)
INSERT INTO customers (name, email, joined_on, status)
VALUES ('Bar', NULL, NULL, 'A');

Text Diagram: Flow of INSERT

┌──── Client (SQL*Plus / SQLcl / App) ────┐
│ INSERT INTO T(colA,colB) VALUES(:A,:B); │
└───────────────↓─────────────────────────┘
┌──────── Oracle Server ────────┐   ┌─ Tablespace ─┐
│ ① Parse ② Constraint checks   │→ │ Write rows    │
│   (NOT NULL/Unique/ForeignKey)│   │ Update indexes│
└───────────────────────────────┘   └───────────────┘

Safe Input by Data Type (avoid implicit conversion)

Oracle’s implicit conversions can be surprising. Insert data using the intended type.

-- String (enclose in single quotes)
INSERT INTO t(c_vc2) VALUES ('山田太郎');

-- Date (explicit format)
INSERT INTO t(c_date) VALUES (TO_DATE('2025-08-01','YYYY-MM-DD'));

-- Timestamp (with milliseconds)
INSERT INTO t(c_ts) VALUES (
  TO_TIMESTAMP('2025-08-01 10:20:30.123','YYYY-MM-DD HH24:MI:SS.FF3')
);

-- Number (no quotes)
INSERT INTO t(c_num) VALUES (12345);

Two Ways to Auto-Number: Choosing Between IDENTITY and SEQUENCE

“When you want the primary key to auto-increment,” use an IDENTITY column or a SEQUENCE. Both generate numbers, but they fit different designs and operations.

Bottom Line First (quick chooser)

┌──────────────────────┬─────────────────────────────────────────────┐
│ What you want        │ Recommendation                              │
├──────────────────────┼─────────────────────────────────────────────┤
│ Easiest auto-number  │ IDENTITY column (defined on the table)      │
│ Fine-grained control │ SEQUENCE (start/increment/cache/cycle, etc.)│
│ App already uses NEXTVAL │ SEQUENCE                                │
└──────────────────────┴─────────────────────────────────────────────┘

IDENTITY Column (simple, great for learning)

What it is: A column property that “auto-numbers” rows. Internally, it uses a sequence-like mechanism.

Main options

  • GENERATED ALWAYS AS IDENTITY … DB always assigns the value (manual input is an error)
  • GENERATED BY DEFAULT AS IDENTITY … DB assigns by default; manual override is allowed
  • (Advanced) BY DEFAULT ON NULL … DB assigns only when the inserted value is NULL

Basic example

CREATE TABLE demo_customers (
  id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name      VARCHAR2(50)  NOT NULL,
  email     VARCHAR2(255) UNIQUE,
  joined_on DATE          DEFAULT SYSDATE,
  status    CHAR(1)       DEFAULT 'A' CHECK (status IN ('A','I'))
);

-- DB auto-assigns id
INSERT INTO demo_customers (name, email)
VALUES ('Sato Ichiro','s.ichiro@example.com');

Command execution example

SQL> CREATE TABLE demo_customers (
  2  id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  3  name VARCHAR2(50) NOT NULL,
  4  email VARCHAR2(255) UNIQUE,
  5  joined_on DATE DEFAULT SYSDATE,
  6  status CHAR(1) DEFAULT 'A' CHECK (status IN ('A','I'))
  7  );

Table created.

SQL> INSERT INTO demo_customers (name, email)
  2  VALUES ('Sato Ichiro','s.ichiro@example.com');

1 row created.

SQL> set lin 1000
SQL> col name for a15
SQL> col email for a25
SQL> select * from demo_customers;

        ID NAME            EMAIL                     JOINED_ON S
---------- --------------- ------------------------- --------- -
         1 Sato Ichiro     s.ichiro@example.com      23-AUG-25 A

FAQ

  • Q: If I ROLLBACK, does the number “go back”? → No. Numbers may be gapped due to rollbacks/concurrency/failures.
  • Q: Change the next starting value? → Alter the column:
ALTER TABLE demo_customers MODIFY
  (id GENERATED BY DEFAULT AS IDENTITY START WITH 1000);
  • Q: Can I set the value manually? → Only when using BY DEFAULT.

Pros / Cautions

  • No extra object to manage; simple.
  • Expect gaps (that’s normal).

SEQUENCE (more control, production-oriented)

What it is: An independent number generator. Use NEXTVAL for the next number and CURRVAL for the most recently generated number in the session.

Create and use

-- Create
CREATE SEQUENCE demo_customers_seq START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;

-- Table using the sequence
CREATE TABLE demo_customers_seqver (
  id         NUMBER PRIMARY KEY,
  name       VARCHAR2(50) NOT NULL,
  created_at DATE DEFAULT SYSDATE
);

-- Use NEXTVAL on insert
INSERT INTO demo_customers_seqver (id, name)
VALUES (demo_customers_seq.NEXTVAL, 'Tanaka Hanako');

-- In the same session, CURRVAL returns the last number
SELECT demo_customers_seq.CURRVAL FROM dual;

Command execution example

SQL> CREATE SEQUENCE demo_customers_seq START WITH 1 INCREMENT BY 1 CACHE 20 NOCYCLE;

Sequence created.

SQL> CREATE TABLE demo_customers_seqver (
  2  id NUMBER PRIMARY KEY,
  3  name VARCHAR2(50) NOT NULL,
  4  created_at DATE DEFAULT SYSDATE
  5  );

Table created.

SQL> INSERT INTO demo_customers_seqver (id, name)
  2  VALUES (demo_customers_seq.NEXTVAL, 'Tanaka Hanako');

1 row created.

SQL> select * from demo_customers_seqver;

        ID NAME            CREATED_A
---------- --------------- ---------
         1 Tanaka Hanako   23-AUG-25

SQL> SELECT demo_customers_seq.CURRVAL FROM dual;

   CURRVAL
----------
         1

Common options

  • START WITH n — starting value
  • INCREMENT BY n — step
  • CACHE n / NOCACHE — in-memory cache for speed (gaps can occur on failures)
  • CYCLE / NOCYCLE — wrap to min after max or not

Pros / Cautions

  • Highly configurable; reusable across tables.
  • CURRVAL is available after you call NEXTVAL in the session.
  • Gaps are normal (cache/rollback/parallel insert, etc.).

IDENTITY vs. SEQUENCE (quick sketch)

┌──────────────┬─────────────────────────────────────────────┐
│ IDENTITY     │ Column-level auto-number; part of the table │
├──────────────┼─────────────────────────────────────────────┤
│ SEQUENCE     │ Independent object; use NEXTVAL/CURRVAL     │
├──────────────┼─────────────────────────────────────────────┤
│ Easy setup   │ ◎ (very simple)                             │
│ Flexibility  │ △ (limited)                                 │
│              │ ◎ (start/step/cache/cycle tunable)          │
└──────────────┴─────────────────────────────────────────────┘

Get the Generated Value Immediately: The RETURNING Clause

RETURNING ... INTO gives your program column values right after an INSERT/UPDATE/DELETE. It’s commonly used for single-row operations.

Why it’s handy

  • Immediately fetch the newly generated ID (primary key).
  • Fetch defaulted values (like SYSDATE) or generated columns in one round trip.

Note (SQL*Plus): The VARIABLE command does not support DATE. When you need to receive a date/time, declare a VARCHAR2 variable and use TO_CHAR on the RETURNING side.

How to use (SQL*Plus/SQLcl)

VARIABLE v_new_id NUMBER
VARIABLE v_when   VARCHAR2(32)

INSERT INTO demo_customers (name, email)
VALUES ('Yamada Taro','yamada.taro@example.com')
RETURNING id, TO_CHAR(joined_on,'YYYY-MM-DD HH24:MI:SS')
INTO :v_new_id, :v_when;

PRINT v_new_id
PRINT v_when

Command execution example

SQL> VARIABLE v_new_id NUMBER
SQL> VARIABLE v_when   VARCHAR2(32)
SQL> INSERT INTO demo_customers (name, email)
  2  VALUES ('Yamada Taro','yamada.taro@example.com')
  3  RETURNING id, TO_CHAR(joined_on,'YYYY-MM-DD HH24:MI:SS')
  4  INTO :v_new_id, :v_when;

1 row created.

SQL> select * from demo_customers;

        ID NAME            EMAIL                     JOINED_ON S
---------- --------------- ------------------------- --------- -
         1 Sato Ichiro     s.ichiro@example.com      23-AUG-25 A
      1000 Yamada Taro     yamada.taro@example.com   23-AUG-25 A

SQL> PRINT v_new_id

  V_NEW_ID
----------
      1000

SQL> PRINT v_when

V_WHEN
---------------------------------
2025-08-23 00:59:52

If joined_on is a TIMESTAMP column

RETURNING id, TO_CHAR(joined_on,'YYYY-MM-DD HH24:MI:SS.FF3')
INTO :v_new_id, :v_when;

Common patterns

  • Combine with IDENTITY to capture the generated id.
  • With SEQUENCE, you know the id via NEXTVAL, but RETURNING is still useful to fetch other columns (e.g., values assigned by defaults).

Also works with UPDATE/DELETE (reference)

-- Receive the primary key of the row updated
VARIABLE v_id NUMBER
UPDATE demo_customers SET status='I' WHERE email='s.ichiro@example.com'
RETURNING id INTO :v_id;

-- Receive the primary key of the row deleted
VARIABLE v_del NUMBER
DELETE FROM demo_customers WHERE id=1
RETURNING id INTO :v_del;

Command execution example

SQL> VARIABLE v_id NUMBER
SQL> UPDATE demo_customers SET status='I' WHERE email='s.ichiro@example.com'
  2  RETURNING id INTO :v_id;

1 row updated.

SQL> select * from demo_customers;

        ID NAME            EMAIL                     JOINED_ON S
---------- --------------- ------------------------- --------- -
         1 Sato Ichiro     s.ichiro@example.com      23-AUG-25 I
      1000 Yamada Taro     yamada.taro@example.com   23-AUG-25 A

SQL> PRINT v_id

      V_ID
----------
         1

SQL> VARIABLE v_del NUMBER
SQL> DELETE FROM demo_customers WHERE id=1
  2  RETURNING id INTO :v_del;

1 row deleted.

SQL> select * from demo_customers;

        ID NAME            EMAIL                     JOINED_ON S
---------- --------------- ------------------------- --------- -
      1000 Yamada Taro     yamada.taro@example.com   23-AUG-25 A

SQL> PRINT v_del

     V_DEL
----------
         1

Notes

  • Primarily for single-row DML. For multi-row, consider PL/SQL’s RETURNING BULK COLLECT INTO (advanced).
  • In applications (e.g., JDBC), you can also use getGeneratedKeys instead of SQL RETURNING.

Common Errors and Fixes (quick reference)

-- Duplicate (e.g., same email)
ORA-00001: unique constraint (….) violated
→ Pre-check duplicates; enforce uniqueness with constraints and indexes.

-- NOT NULL violation (required column is NULL)
ORA-01400: cannot insert NULL into ("OWNER"."T"."COL")
→ Add DEFAULTs, validate on the app side, or revise column list.

-- Foreign key violation (child inserted without a parent)
ORA-02291: integrity constraint (….) violated - parent key not found
→ Insert the parent first; fix the loading order.

Transaction Basics (commit = confirm / rollback = cancel)

An INSERT is not permanent until COMMIT. Use ROLLBACK to undo.

SAVEPOINT sv1;  -- checkpoint
INSERT INTO demo_customers (name) VALUES ('Temp User');
ROLLBACK TO sv1;  -- go back to the checkpoint
COMMIT;           -- finalize changes

Step-by-Step Execution (verified on 19c / Oracle Linux 7.9)

These are runnable minimal steps. Adjust tablespace and password policies to fit your environment.

1) Create a user (schema) — run as SYSDBA

-- In a CDB, switch to the target PDB first
-- sqlplus / as sysdba
CREATE USER appuser IDENTIFIED BY "Str0ng#Pass"
  DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE SEQUENCE TO appuser;

2) Log in and create a table — run as APPUSER

-- sqlplus appuser/Str0ng#Pass
CREATE TABLE customers (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name       VARCHAR2(50)  NOT NULL,
  email      VARCHAR2(255) UNIQUE,
  joined_on  DATE          DEFAULT SYSDATE,
  status     CHAR(1)       DEFAULT 'A' CHECK (status IN ('A','I'))
);

3) Insert a single row (explicit column list)

INSERT INTO customers (name, email, status)
VALUES ('Suzuki Jiro','suzuki.jiro@example.com','A');

-- Omitted columns get DEFAULTs (joined_on = SYSDATE)
INSERT INTO customers (name, email)
VALUES ('Kato Miki','kato.miki@example.com');

4) Insert dates and times safely

INSERT INTO customers (name, email, joined_on)
VALUES (
  'Shimizu Kana',
  'shimizu.kana@example.com',
  TO_DATE('2025-08-23','YYYY-MM-DD')
);

5) Retrieve the new ID on the spot

VARIABLE new_id NUMBER
INSERT INTO customers (name) VALUES ('Nagasawa Mei')
  RETURNING id INTO :new_id;
PRINT :new_id;
COMMIT;  -- confirm

6) Verification SQL

-- Row count
SELECT COUNT(*) FROM customers;

-- Most recent rows
SELECT * FROM customers ORDER BY id DESC FETCH FIRST 5 ROWS ONLY;

-- Unique index on email
SELECT index_name, uniqueness
  FROM user_indexes
 WHERE table_name = 'CUSTOMERS';

Practical Tips (what to watch for)

  • Always specify column names: INSERT INTO T VALUES(...) breaks easily when columns are added later.
  • DEFAULT vs. NULL: They are not the same; analytics/auditing may treat them differently.
  • Explicit date/time formats: Use TO_DATE / TO_TIMESTAMP.
  • Minimize mixed numbering strategies: Avoid mixing manual IDs with IDENTITY/SEQUENCE.
  • Plan commit intervals: For bulk inserts, too frequent commits hurt consistency; too few inflate UNDO.

INSERT ... SELECT will be covered in a separate article

  • Used to copy data between tables or ingest query results in bulk.
  • With large volumes, plan for execution plans, indexes, and locking. We’ll cover runnable examples and best practices in a dedicated post.

Summary

  • Explicit column lists make INSERT safer and more maintainable.
  • IDENTITY is simple; SEQUENCE offers control. Choose based on your requirements.
  • RETURNING lets you grab generated IDs immediately.
  • Explicit date/time formats avoid surprises from implicit conversions.
  • Design commit intervals, constraints, and indexes as part of an end-to-end operation plan.

This article targets Oracle Database 19c (other versions may differ in screens and defaults).


[reference]
INSERT

コメント

Copied title and URL