UPDATE RETURN Clause Enhancements - A new feature in 23ai

Starting from Oracle 23ai, Oracle has added new enhancements for INSERT, UPDATE, and DELETE statements.  This helps developers to obtain values before and after statement execution. These values are valid only for UPDATE statements. INSERT statements do not report old values and DELETE statements do not report new values. Let us demontsrate the same with some examples.

Oracle 19c: Let's create a table in Oracle 19c database. SQL> select banner from v$version; BANNER ------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production #Now let's create a table in Oracle 19c database and insert few values.  SQL> create table employee (id number, name varchar2(20)); Table created. SQL> insert into employee values(1,'RUPESH'); 1 row created. SQL> insert into employee values(2,'SAGAR'); 1 row created. SQL> insert into employee values(3,'PD'); 1 row created. SQL> commit; Commit complete. SQL> select * from employee;   ID NAME ---- --------    1 RUPESH    2 SAGAR    3 PD #Declare bind variables to hold value. SQL> var v_id number SQL> var v_name varchar2(20) #Assign the value to above declared bind variable. SQL> exec :v_id:=1; PL/SQL procedure successfully completed. #Now update the table with RETURNING clause to display old and new values. SQL> update employee set name='JR' where id=:v_id returning old name into :v_name; update employee set name='JR' where id=:v_id returning old name into :v_name                                                            * ERROR at line 1: ORA-00925: missing INTO keyword From the above error message, it is clear that it is not possible to use UPDATE RETURN feature enhancements in Oracle 19c database. Let's try the same example in Oracle 23ai database. Oracle 23ai: Now let's try the same examples in Oracle 23ai database. SQL> select banner from v$version; BANNER --------------------------------------------------------- Oracle Database 23ai Free Release 23.0.0.0.0 - Production #Now let's create a table in Oracle 23ai database and insert few values.  SQL> create table employee (id number, name varchar2(20)); Table created. SQL> insert into employee values(1,'RUPESH'); 1 row created. SQL> insert into employee values(2,'SAGAR'); 1 row created. SQL> insert into employee values(3,'PD'); 1 row created. SQL> commit; Commit complete. SQL> select * from employee;   ID NAME ---- -------    1 RUPESH    2 SAGAR    3 PD #Declare bind variables to hold value. SQL> var v_id number SQL> var v_name varchar2(20) #Assign the value to above declared bind variable. SQL> exec :v_id:=1; PL/SQL procedure successfully completed. #Now update the table with RETURNING clause to display old. SQL> update employee set name='JR' where id=:v_id returning old name into :v_name; 1 row updated. #Now execute PRINT statement to display old values. SQL> print   V_ID ------      1 V_NAME --------- RUPESH   SQL> commit; Commit complete. SQL> print   V_ID ------      1 V_NAME -------- RUPESH #Query the table to display new values. SQL> select * from employee;   ID NAME ---- ------    1 JR    2 SAGAR    3 PD

Visit "Oracle Database 23ai Free - All in one page" to access all blog posts on Oracle 23ai in one page.

Thanks for reading this post ! Please comment if you like this post ! Click FOLLOW to get future blog updates !