Enhanced DML RETURNING clause in Oracle 23ai/26ai

Posted on by

Oracle has supported the RETURNING clause in DML for years, but Oracle Database 23ai / 26ai significantly improves its usability by allowing direct access to OLD and NEW column values in INSERT UPDATE DELETE MERGE statements.

This makes it much easier to capture before-and-after data without triggers or extra queries.

Setup

DROP TABLE IF EXISTS test_tbl;

CREATE TABLE test_tbl
(
  user_id   NUMBER,
  user_name VARCHAR2(10)
);

Insert some test data:

INSERT INTO test_tbl(user_id, user_name)
VALUES
  (10, 'tomasz10'), 
  (20, 'tomasz20'),  
  (30, 'tomasz30'), 
  (40, 'tomasz40'); 

COMMIT;

SELECT * FROM test_tbl;

   USER_ID USER_NAME 
---------- ----------
        10 tomasz10  
        20 tomasz20  
        30 tomasz30  
        40 tomasz40

UPDATE with RETURNING OLD and NEW

Oracle 23ai introduces explicit OLD and NEW references directly in the RETURNING clause—previously available mainly in triggers.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_old_user_id  t_tbl;
  l_new_user_id  t_tbl;
BEGIN
  UPDATE test_tbl
     SET user_id = 7
  RETURNING
    OLD user_id,
    NEW user_id
  BULK COLLECT INTO
    l_old_user_id,
    l_new_user_id;

  FOR i IN 1 .. l_old_user_id.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Old user_id: ' || l_old_user_id(i) ||
      ', New user_id: ' || l_new_user_id(i)
    );
  END LOOP;

  ROLLBACK;
END;
/

, Old user_id: $10, New user_id: $7
, Old user_id: $20, New user_id: $7
, Old user_id: $30, New user_id: $7
, Old user_id: $40, New user_id: $7

PL/SQL procedure successfully completed.
  • No triggers required
  • No extra SELECT before or after DML
  • Perfect for auditing, logging, validation, and change tracking

DELETE with RETURNING OLD

For DELETE, the OLD keyword is optional, but Oracle 23ai allows it explicitly for clarity and consistency.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_old_user_id  t_tbl;
  l_old_user_id1 t_tbl;
BEGIN
  DELETE FROM test_tbl
  RETURNING
    OLD user_id,  -- explicit OLD
    user_id       -- implicit OLD
  BULK COLLECT INTO
    l_old_user_id,
    l_old_user_id1;

  FOR i IN 1 .. l_old_user_id.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(
      'Old user_id: ' || l_old_user_id(i) ||
      ', Old user_id: ' || l_old_user_id1(i)
    );
  END LOOP;

  ROLLBACK;
END;
/

, Old user_id: $10, Old user_id: $10
, Old user_id: $20, Old user_id: $20
, Old user_id: $30, Old user_id: $30
, Old user_id: $40, Old user_id: $40

PL/SQL procedure successfully completed.

Both columns return the same pre-delete values, but explicit OLD improves readability—especially in complex codebase

INSERT with RETURNING NEW

Nothing special here all you can do is just add NEW keyword

SET SERVEROUTPUT ON
DECLARE
  l_new_user_id  number;
  l_new_user_id1 number;
BEGIN
  INSERT INTO test_tbl(user_id, user_name)
VALUES (10, 'tomasz10') 
  RETURNING
    NEW user_id, --explicit NEW word
    user_id      --implicit NEW  
  INTO
    l_new_user_id,
    l_new_user_id1;
    
  -- Display the results
    DBMS_OUTPUT.PUT_LINE
    (
      ', New user_id: $' || l_new_user_id ||
      ', New user_id: $' || l_new_user_id1
    );
  
  ROLLBACK;
END;
/

, New user_id: $10
, New user_id: $10

PL/SQL procedure successfully completed.

Have a fun 🙂

  Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.