Move rows between tables using single statement in Oracle

Question

Due to stupid legacy system limitations I am trying to write the following query using one single statement:

insert into dbo.mytable_archive 
  select * 
    from dbo.mytable 
   where date < trunc(sysdate) - 14;

delete from dbo.mytable 
 where date < trunc(sysdate) - 14;

Using the power of Google I find that this seems possible in many other databases using the RETURNING clause i Postgres or OUTPUT clause in SQLServer but I am unable to find an equivalent solution for Oracle (V12).

Any idea for a workaround?

Answer 1

In case your statement runs around midnight and may take longer than 1 second you should better do this:

create or replace procedure move_to_arch as
   theDate DATE := trunc(sysdate) - 14;
begin
insert into dbo.mytable_archive 
  select * 
    from dbo.mytable 
   where date < theDate ;

delete from dbo.mytable 
 where date < theDate ;
commit;
end;
/