Understanding Oracle SQL INSERT RETURNING Syntax and Usage

Radu PARVU

5 months ago

Summary – Introduction

This article is meant to be in the continuation of the other DML related posts of this blog like:

The reason why I think it I it is worth spending time on studying various methods and use cases involving DML is that for the Oracle Clients I worked for O, DML statements—INSERT, UPDATE, DELETE, and MERGE—are what actually change and maintain data.

Therefore, most real-world database activity revolves around these operations, not only just querying (SELECT).

Almost every time, a DML operation connects to real business-critical scenarios like:

Data migrations
ETL processes
Application updates
User or application Error recovery

A series helps learners understand how data is truly managed in production systems. The goal is to validate the statement that Oracle provides the tools to get the job done efficiently!

introducing the current article, it is meant as a detailed, documentation-style article on Oracle SQL INSERT … RETURNING, with clear explanations, examples, and a few references to official Oracle documentation.

Guide

Overview

The RETURNING clause in Oracle SQL allows you to retrieve column values from rows affected by a DML statement (INSERT, UPDATE, or DELETE) without issuing a separate query.

When used with INSERT, it is especially useful for:

Getting auto-generated values (e.g., sequences, identity columns)
Returning values assigned by triggers; sometimes called by developers: application default values
Reducing round-trips between application and database

Syntax

			
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
RETURNING column_expression [,column_expression ...]
INTO bind_variable [, bind_variable ...];

Key Points

In Oracle SQL, INSERT ... RETURNING is used to capture values from the row you just inserted—without needing a separate SELECT.

This is especially useful for generated keys (like identity/sequence values) or computed columns.

RETURNING works only in PL/SQL or client programs with bind variables
It cannot directly return values to plain SQL output (like a SELECT)
Supports expressions, not just column names
Supports scalar and collection variables

Basic Example

Table Setup

			
CREATE TABLE emp (
    emp_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    name   VARCHAR2(100),
    salary NUMBER
);

		

Insert with RETURNING

			
DECLARE
v_emp_id emp.emp_id%TYPE;
BEGIN
INSERT INTO emp (name, salary)
VALUES ('John Doe', 5000)
RETURNING emp_id INTO v_emp_id;
DBMS_OUTPUT.PUT_LINE('Inserted ID: ' || v_emp_id);
END;

		

What Happens

In this simple case:

• A new row is inserted
Oracle generates emp_id
The value is returned immediately into v_emp_id, in this case is probably the first value in the sequence: number 1

Example: INSERT Returning Using Sequences

While we are talking about sequences and auto-generated identity columns, it is worth remembering that sequences are a legacy feature shipped with the Oracle database much earlier than identity columns. So without the identity columns were available, people would use something like:

			
CREATE SEQUENCE emp_seq START WITH 1;
DECLARE
    v_emp_id NUMBER;
BEGIN
    INSERT INTO employees (emp_id, name, salary)
    VALUES (emp_seq.NEXTVAL, 'Alice', 6000)
    RETURNING emp_id INTO v_emp_id;
    DBMS_OUTPUT.PUT_LINE('New ID: ' || v_emp_id);
END;

		

Example: Returning Multiple Columns

			
DECLARE
    v_id     NUMBER;
    v_name   VARCHAR2(100);
BEGIN
    INSERT INTO emp (name, salary)
    VALUES ('Bob', 7000)
    RETURNING emp_id, name INTO v_id, v_name;
    DBMS_OUTPUT.PUT_LINE(v_id || ' -->> ' || v_name);
END;

		

Example: Returning Expressions

You can return computed values:

			
DECLARE
    v_annual_salary NUMBER;
BEGIN
    INSERT INTO emp (name, salary)
    VALUES ('Carol', 8000)
    RETURNING salary * 12 INTO v_annual_salary;
    DBMS_OUTPUT.PUT_LINE('Annual Salary: ' || v_annual_salary);
END;

		

Performance Benefits

I observed performance benefits Using RETURNING in caseswhen we would need to:

Improve latency in OLTP systems
Avoid extra SELECT
Reduce network round-trips

Best Practices

Use RETURNING for single-row inserts when you need generated values
Use BULK COLLECT for batch operations
Prefer identity columns over sequences (modern Oracle)
Avoid using it in distributed (DB link) scenarios

Conclusion

The INSERT … RETURNING clause is a powerful Oracle feature that:

Eliminates unnecessary queries
Improves performance
Simplifies application logic

I found it essential for mission critical systems with modern Oracle development, especially in high-performance and transactional systems.

Oracle Documentation : the SQL reference manual is best recommended: Oracle Database SQL Language Reference: