Enhancements in Oracle 23c: Introducing the New/Old Returning Clause

Introduction:

Oracle 23c brings a host of new features aimed at simplifying application development. One standout feature is the enhanced Returning Clause for Data Manipulation Language (DML) operations, which now includes the ability to work with both NEW and OLD values. This addition empowers developers to streamline their code, boost productivity, and enhance performance.

Understanding the Returning Clause:

The RETURNING INTO clause for INSERT, UPDATE, and DELETE statements has undergone significant improvements. It now provides a comprehensive report on the OLD and NEW values affected by the respective statement. This means that developers can employ the same logic for all DML types to retrieve values before and after the execution of a statement. It’s important to note that old and new values are only applicable to UPDATE statements. INSERT statements exclusively return new values, while DELETE statements only report old values.

The Evolution from the Previous Norm:

Previously, Oracle’s returning clause primarily focused on providing new values. For example, in cases where an application inserted data into a table, it would return an auto-generated ID for the row. In the context of updates, developers could return the ID along with the updated row and other relevant columns. While it was possible to retrieve old column values for updates through some workarounds, it was by no means straightforward. Oracle’s commitment to simplifying the process for application developers led to the introduction of this feature as a SQL clause.

Utilizing the New Returning Clause:

This newly enhanced feature allows developers to retrieve either a single row/value or multiple rows/values, offering a flexible and dynamic approach to data manipulation. Let’s delve into a example:

set serveroutput on
DROP table if exists TEST_TAB purge;
CREATE TABLE TEST_TAB (columnA VARCHAR2(10), columnB number);
INSERT INTO TEST_TAB(columnA, columnB) VALUES ('Test', 10);
INSERT INTO TEST_TAB(columnA, columnB) VALUES ('Row 2', 20);
CREATE TABLE IF not exists  audit_table1(col_new VARCHAR2(10),col_old VARCHAR2(10), columnB number);

DECLARE
  TYPE columnA_TYPE IS TABLE OF TEST_TAB.columnA%TYPE;
  OLD_columnA columnA_TYPE;
  NEW_columnA columnA_TYPE;
BEGIN
  UPDATE TEST_TAB AA
     SET AA.columnA = 'NEW_VALUE'
   WHERE AA.columnB < 30
  RETURNING OLD columnA, NEW columnA
  BULK COLLECT INTO OLD_columnA, NEW_columnA;

  FOR I IN 1 .. OLD_columnA.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE('Old.columnA = ' || OLD_columnA(I));
    DBMS_OUTPUT.PUT_LINE('New.columnA = ' || NEW_columnA(I));
  END LOOP;

END;

Output

Old.columnA = Test
New.columnA = NEW_VALUE
Old.columnA = Row 2
New.columnA = NEW_VALUE

For more examples, feel free to refer to this link.

Oracle 23c’s enhanced RETURNING clause can come in handy in application auditing, Application level Change Data Capture, and many application-driven logic. This powerful feature promises to be a game-changer for developers seeking code efficiency, precision, and performance in their applications.