Oracle 23ai/26ai: The New RETURNING Clause for the MERGE Statement

This blog is part of a series about aspects and features of Oracle 26 and Autonomous Database. 

The SQL MERGE statement was introduced in Oracle version 9i, allowing what is sometimes called UPSERT logic: a single SQL statement that conditionally inserts or updates rows.  However, one limitation remained.  Unlike INSERT, UPDATE, and DELETE, the MERGE statement did not support the RETURNING clause.  Oracle 23ai/26ai removes this restriction. Developers can now use the RETURNING clause directly in MERGE statements to retrieve values of affected rows. 

The Problem Before Oracle 23

Before Oracle 23, I would have to code a query loop capturing the values that were going to be updated and then update them in separate statements within the loop with additional exception handling as required.

…
  l_rows_processed := FALSE;

  FOR s IN (
    SELECT a.activity_id
    ,      listagg(DISTINCT ma.name,', ') WITHIN GROUP (ORDER BY ma.area_level, ma.name) area_list
    FROM   activities a
      INNER JOIN activity_areas aa ON a.activity_id = aa.activity_id
      INNER JOIN my_areas ma ON ma.area_code = aa.area_code and ma.area_number = aa.area_number
    WHERE a.activity_id = p_activity_id
    AND a.processing_status = 4
    And ma.matchable = 1
    GROUP BY a.activity_id;
  ) LOOP
    l_rows_processed := TRUE;

    UPDATE activities u
    SET    u.area_list = s.area_list
    WHERE  u.activity_id = s.activity_id

    update_activity_description(l_new_area_list,l_description);
  END LOOP;

  IF NOT l_rows_processed THEN 
    RAISE e_activity_not_found;
  END IF;
…

New Syntax in Oracle 23/26

Alternatively, I can use the MERGE statement to write a single SQL statement to generate the new value for a column and then update it in one go.  Now, the return clause also captures that new value in a variable that can be passed to another procedure.

MERGE INTO activities u
  USING (
    SELECT a.activity_id
    ,      listagg(DISTINCT ma.name,', ') WITHIN GROUP (ORDER BY ma.area_level, ma.name) area_list
    FROM   activities a
      INNER JOIN activity_areas aa on a.activity_id = aa.activity_id
      INNER JOIN my_areas ma on ma.area_code = aa.area_code and ma.area_number = aa.area_number
    WHERE a.activity_id = p_activity_id
    AND a.processing_status = 4
    AND ma.matchable = 1
    GROUP BY a.activity_id
  ) S 
  ON (s.activity_id = u.activity_id)
  WHEN MATCHED THEN UPDATE 
  SET u.area_list = s.area_list
  RETURNING new area_list INTO l_new_area_list; --new in Oracle 23
  
  IF SQL%ROWCOUNT = 0 THEN 
    RAISE e_activity_not_found;
  ELSE
    update_activity_description(l_new_area_list,l_description);
  END IF;

The benefits are

• Less and simpler code, which ought therefore to be easier to test and maintain, requiring less additional logic and exception handling.
• Fewer SQL statements and therefore fewer context switches between PL/SQL and SQL.

Just like the return clause on UPDATE and DELETE, it is also possible to

• Reference new and/or old column values,
• Single values into a scalar (single value) variable
• Bulk collect multiple rows into an array variable
• Aggregate multiple rows into a scalar variable

I am far from the first to blog about this feature, but it deserves to be better known.

See also:

• Andrej Pashchenko: MERGE and DML RETURNING clause in Oracle 23ai.
• Connor McDonald: MERGE in 23ai – so much more than RETURNING. This blog goes much further, also looking at bulk collecting multiple rows into an array.
• Tim Hall: DML RETURNING Clause Enhancements in Oracle Database 23ai/26ai.