Search notes:

Oracle: identity columns - RETURNING clause

create table tab_with_id (
   txt  varchar2(20),
   id   integer generated always as identity
);

declare

   procedure ins(t varchar2) is
      inserted_id pls_integer;
   begin
      insert into tab_with_id (txt) values (t) returning id into inserted_id;
      dbms_output.put_line(t || ' was inserted with id ' || inserted_id);
   end ins;

begin
   ins('foo');
   ins('bar');
   ins('baz');
end;
/

drop table tab_with_id;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/columns/identity/returning.sql

RETURNING clause in an INSERT … SELECT FROM statement

It seems that it is not possible to combine an insert statement with a returning into. The demonstration below produces an ORA-00933: SQL command not properly ended error.

create table tq84_identity (
    id   integer generated always as identity,
    txt  varchar2(100)
);

declare
   i  integer;
begin
   insert into tq84_identity(txt)
   select dummy from dual
   returning id into i;
   
   dbms_output.put_line('id = ' || i);
end;
/

Oracle: identity columns - RETURNING clause

RETURNING clause in an INSERT … SELECT FROM statement

See also