Oracle 26ai insert into select returning

In the 21c SQL Language Reference the following can be found regarding the INSERT statement:

But in the 26ai SQL Language Reference that becomes

and that small change opens a world of opportunity, reducing the number of context switches in certain cases, as shown below. But there are also some ceveats!

The classic set-up:

Suppose you have a table with an identity column as primary key and another table with a foreign key pointing to it. Further, suppose you need PL/SQL to insert records in both that parent and child table. In that case, you’ll need to to use insert … returning in orer to get the PK-value created during that insert in order to use it for the subsequent insert(s) into the child table.

Before 26ai, the retunring_clause was only possible when using the values_clause. If you wanted to use a subquery (an “insert-select”), there was no way to also have a retunring clause.

For example, when using collections in Oracle Apex, it is common to use PL/SQL to select from apex_collections to get the data stored in collections into records in tables.

Before 26ai, this required

1. select …
   from apex_collection
   into :plsql_variables_or_records
   where …

2. insert into destination_parent_table (…)
   values (:plsql_variables_or_records, …)
   returning new ID as :variable_for_parent_key

3. insert into destination_child_table (…)
   values (:variable_for_parent_key, …)

So, that are 3 SQL calls in PL/SQL and therefore also 3 context switches…

In 26ai this can be reduced to

1. insert into destination_parent_table (…) values (
   select … from apex_collection where …
   ) returning new ID as :variable_for_parent_key

2. insert into destination_child_table (…)
   select :variable_for_parent_key, …
   from apex_collections
   where …

So that is

• 1 query less

• 1 context switch less

• less data in PL/SQL => less memory usage

Some code to illustrate this and expose some issues:

drop table if exists src purge;
create table src (id number, val varchar2(30));

-- 1) Multi record insert
   -- Very nice for creating test data or (initial) data loading.
   -- Probably less usefull in application development.
   -- Here it is used to populate a source table (to select from)
insert into src (id, val) values
   (1, 'One'),
   (2, 'Two'),
   (3, 'Three');
commit;

/*
The new way of using subqueries when inserting:
There has always been
   insert into dest (col1, col2)
   select col_a, col_b from src;
It was previously not possible to add a returning clause onto this,
probably since that select potentially returns multiple records.
That fact results in having to do 2 sql-calls, in PL/SQL:
   1 to select the values into variables
   2 to do the insert using the "insert values"-way or working:
         select col_a, col_b
            into :param1, :param2
            from src
            where id = 1;
         insert into dest (col1, col2)
            values (:param1, :param2)
            returning id into :id;
But now, the values(...) can contain a select
*/

-- Testcase for the new functionality:
drop table if exists dest purge;
create table dest (id number GENERATED ALWAYS AS IDENTITY, src_id number, val varchar2(30));

insert into dest (src_id, val) values (
   select id, val from src where id = 1
);
-- Note that it has to be a single row query (or "ORA-01427: single-row subquery returns more than one row" is raised)
-- If you want to insert multiple rows (implying you don't need "returning") just use the old style insert-select.
-- This really only adds value when using "returning into":

set serveroutput on
declare
   dst_id number;
begin
   insert into dest (src_id, val) values (
      select id, val from src where id = 3
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 2
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 1
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);

   insert into dest (src_id, val) values (
      select id, val from src where id = 42 --does not exist
   )
   returning new id into dst_id;
   dbms_output.put_line('New dest.id is: '||dst_id);
end;
/

select * from dest;

The caveat is that this returns

So, also when the subquery returns no records, the insert is performed without raising errors…

(Note that in the PL/SQL sample above, the returned PK-value is not used to insert a child record. This part has been intentionally left out since it is not relevent to the new 26ai functionality of the insert statement.)