jcon.no: Oracle blog

I got some strange behavior with the Edition Based Redefinition feature this other day. I have 4 editions (RELEASE_1, RELEASE_2, RELEASE_3 and RELEASE_4). I create a package FOO with one procedure P1 in RELEASE_1. Then i recreate the package in RELEASE_2 adding a second procedure P2. Now I expect RELEASE_3 (and RELEASE_4) to have inherited the version from RELEASE_2. The strange thing is that it seems like the specification is inherited from RELASE_2 (with both procedures P1 and P2), but the body is “inherited” from RELEASE_1 (marked as INVALID in USER_OBJECTS_AE). The following article shows the commands to reproduce the issue.
(Note! I’m on a Oracle Exadata (running on Linux 7) with Oracle Database version 19.23.0.0.0.)

The behavior happend in a project at work, but I have created a test case which also shows the behavior.

-- Run as user with DBA privilege
drop user tst cascade;
create user tst identified by tst;
alter user tst ENABLE EDITIONS;

alter user tst default tablespace users;
alter user tst quota unlimited on users;

grant create session, create table, create view, create sequence, create procedure, create type, 
      create trigger, create synonym, create any context, create job to tst;

create edition RELEASE_1 as child of ORA$BASE;
create edition RELEASE_2 as child of RELEASE_1;
create edition RELEASE_3 as child of RELEASE_2;
create edition RELEASE_4 as child of RELEASE_3;

GRANT USE ON EDITION ORA$BASE to tst;
GRANT USE ON EDITION RELEASE_1 TO tst;
GRANT USE ON EDITION RELEASE_2 TO tst;
GRANT USE ON EDITION RELEASE_3 TO tst;
GRANT USE ON EDITION RELEASE_4 TO tst;

Then logon as the TST user and run the following:

alter session set edition=RELEASE_1;

create or replace package foo as
   procedure p1;
end;
/

create or replace package body foo as
   procedure p1 as
   begin
      dbms_output.put_line('==> Ran P1');
   end;
end;
/

alter session set edition=RELEASE_2;

create or replace package foo as
   procedure p1;
   procedure p2;
end;
/

create or replace package body foo as
   procedure p1 as
   begin
      dbms_output.put_line('==> Ran P1');
   end;
   
   procedure p2 as
   begin
      dbms_output.put_line('==> Ran P2');
   end;
end;
/

So far so good … .

But let’s look at our metadata.

select object_name, object_type, status, edition_name 
from user_objects_ae 
where 1=1
  and object_name='FOO'
  and edition_name is not null order by 4;

OBJECT_NAME     OBJECT_TYPE          STATUS     EDITION_NAME             
--------------- -------------------- ---------- -------------------------
FOO             PACKAGE              VALID      RELEASE_1           
FOO             PACKAGE BODY         VALID      RELEASE_1           
FOO             PACKAGE              VALID      RELEASE_2           
FOO             PACKAGE BODY         VALID      RELEASE_2           
FOO             PACKAGE BODY         INVALID    RELEASE_3 

Wait a second. Why is the package body in edition RELEASE_3 invalidated? Let’s take a look at the source.

alter session set edition=RELEASE_3;
select text from user_source where name='FOO' and type = 'PACKAGE' order by line;

TEXT
------------------
package foo as
   procedure p1;
   procedure p2;
end;

select text from user_source where name='FOO' and type = 'PACKAGE BODY' order by line;

TEXT
-------------------------------------------
package body foo as
   procedure p1 as
   begin
      dbms_output.put_line('==> Ran P1');
   end;
end;

What??? The specification has correctly inherited from RELEASE_2, but it seems like the body now is a copy of RELEASE_1. How can this be?