jcon.no: Oracle blog

This is an update on the previous article “Strange behavior with EBR“.
To try to understand a little more I have tested some more. The result suggest that what’s inherited in my latest edition is actually not what I first thought (read last article). Let’s have a look.

I’m using the same TST schema as created in the previous article.

To be able to delete the editions which I’m testing with I create some new editions (with no existing objects). The following is ran as a user with DBA privileges:

-- -----------------------------------------------------------------------
-- Note! Before dropping an edition the TST user must switch to some other 
-- edition first. For instance RELEASE_2024_3
-- Run as TST: alter session set edition=RELEASE_2024_3;
-- -----------------------------------------------------------------------

-- drop edition RELEASE_2024_6 cascade; -- Uncomment if need for new tests
-- drop edition RELEASE_2024_5 cascade; -- Uncomment if need for new tests
-- drop edition RELEASE_2024_4 cascade; -- Uncomment if need for new tests
create edition RELEASE_2024_4 as child of RELEASE_2024_3;
create edition RELEASE_2024_5 as child of RELEASE_2024_4;
create edition RELEASE_2024_6 as child of RELEASE_2024_5;
GRANT USE ON EDITION RELEASE_2024_4 TO tst;
GRANT USE ON EDITION RELEASE_2024_5 TO tst;
GRANT USE ON EDITION RELEASE_2024_6 TO tst;

Then I create the first version of my FOO package (spec and body) as the TST user:

alter session set edition=RELEASE_2024_4;
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;
/

Everything looks fine yet.

SQL> col object_name for a10
SQL> col edition_name for a15
SQL> select object_name, object_type, status, edition_name 
  2  from user_objects_ae e
  3  where 1=1
  4    and object_name='FOO'
  5    and edition_name is not null order by 4;

OBJECT_NAM OBJECT_TYPE             STATUS  EDITION_NAME   
---------- ----------------------- ------- ---------------
FOO        PACKAGE                 VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_4

Then I switch to edition RELEASE_2024_5, and first recreate the package body with a minor change (just to be able to verify that this is the edition 5 version:

SQL> alter session set edition=RELEASE_2024_5;

Session altered.

SQL> create or replace package body foo as
  2    procedure p1 as
  3    begin
  4      dbms_output.put_line('==> Ran P1 - RELEASE 5');
  5    end;
  6  end;
  7  /

Package Body FOO compiled

When I now check the USER_OBJECTS_AE everything still looks OK (Valid).

SQL> col object_name for a10
SQL> col edition_name for a15
SQL> select object_name, object_type, status, edition_name
  2  from user_objects_ae e
  3  where 1=1
  4    and object_name='FOO'
  5    and edition_name is not null order by 4;

OBJECT_NAM OBJECT_TYPE             STATUS  EDITION_NAME   
---------- ----------------------- ------- ---------------
FOO        PACKAGE                 VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_5

Now I introduce the new version of the specification – still in RELEASE_2024_5:

SQL> alter session set edition=RELEASE_2024_5;

Session altered.

SQL> create or replace package foo as
  2    procedure p1;
  3    procedure p2;
  4  end;
  5  /

Package FOO compiled

Now the PACKAGE BODY in edition 5 (RELEASE_2024_5) becomes INVALID (as expected).
Note! See query against USER_OBJECTS_AE above.

OBJECT_NAM OBJECT_TYPE             STATUS  EDITION_NAME   
---------- ----------------------- ------- ---------------
FOO        PACKAGE                 VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_4 
FOO        PACKAGE                 VALID   RELEASE_2024_5 
FOO        PACKAGE BODY            INVALID RELEASE_2024_5

So then we also recreate the PACKAGE BODY in edition 5.

SQL> alter session set edition=RELEASE_2024_5;

Session altered.

SQL> create or replace package body foo as
  2    procedure p1 as
  3    begin
  4      dbms_output.put_line('==> Ran P1');
  5    end;
  6 
  7    procedure p2 as
  8    begin
  9      dbms_output.put_line('==> Ran P2');
 10    end;
 11  end;
 12  /

Package Body FOO compiled

Now the package body becomes VALID in edition RELEASE_2024_5, but INVALID in edition RELEASE_2024_6. Seems a little strange (but as we also noticed in the previous blog post).
Note! See query against USER_OBJECTS_AE above.

OBJECT_NAM OBJECT_TYPE             STATUS  EDITION_NAME   
---------- ----------------------- ------- ---------------
FOO        PACKAGE                 VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_4 
FOO        PACKAGE                 VALID   RELEASE_2024_5 
FOO        PACKAGE BODY            VALID   RELEASE_2024_5 
FOO        PACKAGE BODY            INVALID RELEASE_2024_6

So now let’s look at the USER_SOURCE in edition RELEASE_2024_6.

SQL> alter session set edition=RELEASE_2024_6;

Session altered.

SQL> 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 - RELEASE 5');
   end;

end;

7 rows selected.

So … the next edition has not inherited from edition RELEASE_2024_4 (as we thought in the previous blog post), but rather inherited the previous version of the package body in RELEASE_2024_5.
Still very strange??? Or is this expected behavior???

What if I delete only edition RELEASE_2024_6, and then recreate it:

-- RAN as DBA (after switching TST back to an earlier edition)
SQL> drop edition RELEASE_2024_6 cascade;

Edition RELEASE_2024_6 dropped.

SQL> create edition RELEASE_2024_6 as child of RELEASE_2024_5;

Edition RELEASE_2024_6 created.

SQL> GRANT USE ON EDITION RELEASE_2024_6 TO tst;

Grant succeeded.

If I now check USER_OBJECTS_AE, everything looks fine again. But it’s shouldn’t be necessary.
Note! See query against USER_OBJECTS_AE above.

OBJECT_NAM OBJECT_TYPE             STATUS  EDITION_NAME   
---------- ----------------------- ------- --------------- 
FOO        PACKAGE                 VALID   RELEASE_2024_4 
FOO        PACKAGE BODY            VALID   RELEASE_2024_4 
FOO        PACKAGE                 VALID   RELEASE_2024_5 
FOO        PACKAGE BODY            VALID   RELEASE_2024_5

The last is not really surprising, but I expect the inheritance to work also with an existing edition.