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.
