Edition Based Redefinition and USER_OBJECTS_AE

Last week the Oracle PL/SQL Programming (OPP) and APEXposed conference took place in Brussels, Belgium.

My session was on Edition Based Redefinition, the killer feature of the Oracle 11g Release 2 database. One of my demo's showed what a procedure looks like when you override it in a newer edition from the datadictionary standpoint.
In earlier releases of the Oracle database it was not possible to have two procedures (or any other object as a matter of fact) with the same name in the same database schema.
With Edition Based Redefinition you can have two procedures with the same name, as long as they are in different Edtions.
For this demo I will use the new datadictionary view USER_OBJECTS_AE.

Let's first start within the default edition (the one you get when you upgrade to or install an Oracle 11g Release 2 database) called ORA$BASE.

SQL> select sys_context('userenv'
  2                    ,'current_edition_name'
  3                    ) "Current_Edition"
  4    from dual
  5  /

Current_Edition
--------------------
ORA$BASE

Within this edition we will create a procedure called "hello" which simple shows "hello world".

SQL> create or replace
  2  procedure hello
  3  is
  4  begin
  5     dbms_output.put_line ('Hello World');
  6  end hello;
  7  /

Procedure created.

SQL> begin
  2     hello;
  3  end;
  4  /
Hello World

PL/SQL procedure successfully completed.

Querying the USER_OBJECT_AE datadictionary view results in a single record

SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- -----------------
HELLO                PROCEDURE           ORA$BASE

Introducing a new edition as a child of ORA$BASE, in which we will create a "newer" version of the "hello"-procedure.

SQL> create edition R1 as child of ora$base
  2  /

Edition created.

SQL> alter session set edition = R1
  2  /

Session altered.

When we call the "hello"-procedure in this new edition (named R1), we get the same result as before. The procedure is inherited from the parent edition (named ORA$BASE).

SQL> begin
  2     hello;
  3  end;
  4  /
Hello World

PL/SQL procedure successfully completed.

The datadictionary view USER_OBJECTS_AE shows just a single version of the "hello"-procedure. This more or less proofs that the procedure is not copied over to the new version (named R1). There is still only one version of the procedure.

SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------
HELLO                PROCEDURE           ORA$BASE

Creating a new version of the "hello"-procedure will -of course- produce a different result when executed.

SQL> create or replace
  2  procedure hello
  3  is
  4  begin
  5     dbms_output.put_line ('Hello Universe');
  6  end hello;
  7  /

Procedure created.

SQL> begin
  2     hello;
  3  end;
  4  /
Hello Universe

This new version of the procedure will override the inherited version of the ORA$BASE edition. This is also reflected in the USER_OBJECTS_AE view:

SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- ------------------
HELLO                PROCEDURE           ORA$BASE
HELLO                PROCEDURE           R1

Now you can see there are two versions of the procedure named "hello", one in each edition.

Drop the Procedure
When you create a new edition, all editionable objects are inherited into the latest edition. If you don't want an object to "be there" in the latest edition, simply drop it. It won't exist anymore in the latest edition. It will exist in the older edition though.
Instead of creating the newer procedure in the latest edition (named R1), dropping it will also lead to an entry in the USER_OBJECTS_AE view.

SQL> drop procedure hello
  2  /

Procedure dropped.

SQL> select object_name
  2       , object_type
  3       , edition_name
  4    from user_objects_ae
  5   where object_name = 'HELLO'
  6  /

OBJECT_NAME          OBJECT_TYPE         EDITION_NAME
-------------------- ------------------- -------------
HELLO                PROCEDURE           ORA$BASE
HELLO                NON-EXISTENT        R1

As you can see from the results, the procedure "hello" doesn't exist any more in the latest edition (named R1). The object_type "NON-EXISTENT" gives that away.