RETURNING INTO Enhancements in Oracle 23ai

The RETURNING INTO clause has been in Oracle for a long time — it lets you capture column values from DML statements (INSERT, UPDATE, DELETE) without doing a separate SELECT. Oracle 23ai extends this in two meaningful ways: it now works with multi-row DML, and it supports the OLD and NEW keywords to capture values before and after the change.

Classic single-row RETURNING (has always worked):

DECLARE
    v_new_id NUMBER;
BEGIN
    INSERT INTO orders (customer_id, status)
    VALUES (42, 'PENDING')
    RETURNING order_id INTO v_new_id;

    DBMS_OUTPUT.PUT_LINE('Created order: ' || v_new_id);
END;

New: OLD and NEW qualifiers:

DECLARE
    v_old_status VARCHAR2(20);
    v_new_status VARCHAR2(20);
BEGIN
    UPDATE orders
    SET    status = 'SHIPPED'
    WHERE  order_id = 1001
    RETURNING OLD status, NEW status
    INTO v_old_status, v_new_status;

    DBMS_OUTPUT.PUT_LINE('Changed: ' || v_old_status || ' -> ' || v_new_status);
END;

Bulk collect with multi-row DML:

DECLARE
    TYPE t_ids IS TABLE OF NUMBER;
    v_ids t_ids;
BEGIN
    DELETE FROM temp_orders
    WHERE  processed = 'Y'
    RETURNING order_id BULK COLLECT INTO v_ids;

    DBMS_OUTPUT.PUT_LINE('Deleted ' || v_ids.COUNT || ' orders');
END;

Why this matters: Audit logging, event sourcing, and change tracking patterns all benefit from capturing old/new values at the DML layer. Previously you had to use triggers or issue a SELECT before and after the DML. Now you can do it inline, atomically, with less code.

Author: matheusdba