SQL & PL/SQL

I was attempting to create code for maintaining a Temporal Validity table. ( SCD Type 2 )

that is: AS OF PERIOD FOR view with an INSTEAD OF trigger

The code compiles. But I get “ORA-00904: <col> invalid identifier” when I try to use the code.

Questions

• do I have a typo?
• am I missing something?
• is this an actual bug?

Tested on

• 19.26.0.1.0 - ATP Free Tier
• 23.5.0.24.07 - VirtualBox

CODE

drop table all_ppi_data;
create table all_ppi_data (
  ppi_history_id  int generated always as identity,
  user_id         int not null,
  DOB             date not null,
  display_name    varchar(50 char),
  valid_from      date default on null sysdate,
  valid_to        date,
  period for current_test_data_period (valid_from, valid_to),
  constraint all_test_data_pk primary key (ppi_history_id)
);


create or replace view current_ppi_data
as
select ppi_history_id, user_id, DOB, display_name
from all_ppi_data
  as of period for current_test_data_period(sysdate);


insert into all_ppi_data (user_id, dob, display_name)
values ( 1, date '1984-1-1', 'George Orwells' ),
       ( 2, sysdate, 'New Row' );
commit;

create or replace
package ppi_data_api
as
  procedure update_rec( new_rec all_ppi_data%rowtype, old_rec all_ppi_data%rowtype);
end;
/

create or replace
package body ppi_data_api
as
  -- NOTE : uses 23ai syntax
  procedure update_rec( new_rec all_ppi_data%rowtype, old_rec all_ppi_data%rowtype)
  as
    null_id all_ppi_data.ppi_history_id%type;
  begin
    merge into all_ppi_data a
    using ( select null_id ppi_history_id,  -- PK
                   new_rec.user_id,         -- FK not null
                   new_rec.DOB,             -- date
                   new_rec.display_name     -- varchar2
            union all
            select old_rec.ppi_history_id,
                  cast(null as int) user_id,
                  cast(null as date) DOB,
                  cast(null as varchar(50 char) ) display_name
     ) b
    on (a.ppi_history_id = b.ppi_history_id)
    when matched then update set a.valid_to = sysdate
    when not matched then insert (user_id, dob, display_name )
      values (b.user_id,
              b.dob,
              b.display_name
            )
    --   where b.player_id is not null
      ;
  end update_rec;
end ppi_data_api;
/

declare
  rcd all_ppi_data%rowtype;
begin
  ppi_data_api.update_rec( rcd, rcd );
end;
/