Oracle 26ai: RETURNING OLD and NEW Values

Oracle 26ai adds the OLD keyword to the existing RETURNING clause — complementing the NEW keyword that was already there, and letting you capture both the before and after state of a row in a single atomic DML operation, with no extra SELECT required.

The problem

Audit logging, change tracking, and before/after comparisons all need both the old and new value of a column. Before 26ai, the only way to get both was two separate statements:

			
DECLARE
  v_old_salary  employees.salary%TYPE;
  v_new_salary  employees.salary%TYPE;
BEGIN
  -- Capture salary before the update
  SELECT salary
  INTO   v_old_salary
  FROM   employees
  WHERE  employee_id = 100;
 
  -- Then apply the change
  UPDATE employees
  SET    salary = salary * 1.15
  WHERE  employee_id = 100;
 
  -- v_old_salary now holds the pre-update value
  -- but the SELECT and UPDATE are two separate round trips
END;

		

The SELECT and UPDATE are not atomic. A concurrent transaction could modify the row between the two statements, meaning the value you captured may not be the value you actually changed.

What RETURNING already gave us

The RETURNING clause has existed for some time and lets you capture the post-DML state in a single statement:

			
UPDATE employees
SET    salary = salary * 1.15
WHERE  employee_id = 100
RETURNING salary INTO v_new_salary;

Useful — but it only ever returned the new value. To get the old one too, you were back to a separate SELECT.

The 26ai solution

RETURNING OLD and RETURNING NEW let you capture both states in one atomic operation.

			
DECLARE
  v_old_salary  employees.salary%TYPE;
  v_new_salary  employees.salary%TYPE;
BEGIN
  -- Capture before and after salary in one atomic DML operation
  UPDATE employees
  SET    salary = salary * 1.15
  WHERE  employee_id = 100
  RETURNING OLD salary, NEW salary
  INTO      v_old_salary, v_new_salary;
 
  DBMS_OUTPUT.PUT_LINE(
    'King salary: ' || v_old_salary || ' -> ' || v_new_salary
  );
END;

		

One statement. One round trip. Both values are guaranteed to reflect the same row state, the value before and immediately after the update.

Bulk operations with BULK COLLECT

OLD and NEW compose naturally with BULK COLLECT for multi-row DML. Each index position in the two collections corresponds to the same row:

			
-- Bulk example: capture old and new salaries for an entire department raise
DECLARE
  TYPE t_sal_tab IS TABLE OF employees.salary%TYPE;
  v_old_sals t_sal_tab;
  v_new_sals t_sal_tab;
BEGIN
  UPDATE employees
  SET    salary = salary * 1.10
  WHERE  department_id = 60
  RETURNING OLD salary, NEW salary
  BULK COLLECT INTO v_old_sals, v_new_sals;
 
  FOR i IN 1 .. v_old_sals.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_old_sals(i) || ' -> ' || v_new_sals(i));
  END LOOP;
END;

		

DELETE: only OLD is valid

After a delete there is no new state — OLD is the only meaningful keyword. This makes it a clean way to log or archive rows as they are removed:

			
DECLARE
  v_emp_id    employees.employee_id%TYPE;
  v_last_name employees.last_name%TYPE;
  v_salary    employees.salary%TYPE;
BEGIN
  DELETE FROM employees
  WHERE  employee_id = 206
  RETURNING OLD employee_id, OLD last_name, OLD salary
  INTO      v_emp_id, v_last_name, v_salary;
 
  DBMS_OUTPUT.PUT_LINE(
    'Deleted: ' || v_emp_id || ' ' || v_last_name || ' (' || v_salary || ')'
  );
END;

		

INSERT: only NEW is valid

Before an insert there is no old state — NEW is the only meaningful keyword. Useful for capturing sequence-generated primary keys or default column values assigned by the database:

			
DECLARE
  v_new_id  employees.employee_id%TYPE;
BEGIN
  INSERT INTO employees (employee_id, first_name, last_name, email,
                         hire_date, job_id, salary, department_id)
  VALUES (employees_seq.NEXTVAL, 'Jane', 'Smith', 'JSMITH',
          SYSDATE, 'IT_PROG', 7000, 60)
  RETURNING NEW employee_id INTO v_new_id;
 
  DBMS_OUTPUT.PUT_LINE('Inserted employee_id: ' || v_new_id);
END;

		

When to use this

The primary use cases are audit logging, change-data capture pipelines, and before/after reporting anywhere you need both states of a row without a separate pre-query. Because the capture is part of the DML statement itself, the old and new values are always consistent with each other regardless of concurrent activity.

iancarpenter.dev

Sharing what I learn