This is part 12 of a post series about Oracle Edition-Based Redefinition.

Visit the index page for all the parts of the series

Introduction

In the previous part of this series I wrote about using the Database Default Edition as a way for exposing new editions when using EBR for online application upgrades. As I wrote there, this is a simple method, but in my opinion it is also an impractical method in most cases. From my experience, the best way to expose new editions is by using services. This gives us high levels of both flexibility and control.

When we create or modify an Oracle service, we can specify the session edition for subsequent database connections using this service.
If our new edition includes changes that require code changes in the client-side (for example, when we make API changes), then we’ll expose the new edition by using a new service.
If the new edition includes only changes that are transparent to the client-side (for example, changes in package bodies only), then we don’t have to create a new service. Instead, we can modify the latest service to use the new edition from now on.

Examples

Let’s see some examples. I’ll repeat the upgrade use-cases from part 3 and part 9, but this time we’ll use services.

Baseline

We start our example when ORA$BASE is the only edition in the database (and therefore also the database default edition).
The initial implementation of our sample application was created in the ORA$BASE edition.
Let’s create a service that will be used by the client sessions:

SQL> begin
  2      dbms_service.create_service(
  3          service_name => 'S1',
  4          network_name => 'S1'
  5      );
  6      dbms_service.start_service(
  7          service_name => 'S1'
  8      );
  9  end;
 10  /

PL/SQL procedure successfully completed.

A client session connects to the database using the S1 service. Note that we didn’t specify the edition parameter when we created this service. This means that the session will be connected to the database default edition (ORA$BASE).

-- client session #1
C:\> sqlplus demo_ebr/ebr@localhost/S1
ORA$BASE>

Exposing a New Edition with No API Changes

Let’s now create the new edition V1:

ORA$BASE> create edition V1;

Edition created.

ORA$BASE> grant USE on edition V1 to demo_ebr;

Grant succeeded.

Let’s move to edition V1 in the developer session:

-- the developer session
ORA$BASE> conn demo_ebr/ebr
Connected.

ORA$BASE> @set_edition V1

Session altered.

V1>

And let’s compile the new version of the PEOPLE_DL package body:

V1> create or replace package body people_dl as
  2      procedure add
  3      (
  4          i_id           in people.id%type,
  5          i_first_name   in people.first_name%type,
  6          i_last_name    in people.last_name%type,
  7          i_phone_number in people.phone_number%type
  8      ) is
  9      begin
 10          insert into people (id,first_name,last_name,phone_number)
 11          values (i_id,i_first_name,i_last_name,i_phone_number);
 12          --
 13          dbms_output.new_line;
 14          dbms_output.put_line('wow! this is not the base edition anymore');
 15          --
 16      end add;
 17
 18      procedure remove(i_id in people.id%type) is
 19      begin
 20          delete people where id = i_id;
 21      end remove;
 22  end people_dl;
 23  /

Package body created.

Now, let’s expose the new edition V1 to the clients. Since the only change is in the package body there is no need to create a new service. We can simply change the edition of the S1 service:

SQL> begin
  2      dbms_service.modify_service(
  3          service_name   => 'S1',
  4          edition        => 'V1',
  5          modify_edition => true
  6      );
  7  end;
  8  /

PL/SQL procedure successfully completed.

There is something unusual with the way dbms_service.modify_service lets us modify the service’s edition. It is not enough to pass a value to the edition parameter. We have also to pass true to the modify_edition parameter. The edition parameter is completely ignored unless we specify modify_edition=>true.

So when we open a new client session (still using the S1 service) the session edition is automatically V1:

-- client session #2
C:\> sqlplus demo_ebr/ebr@localhost/S1
V1> 

And we can see that it is using the new implementation of people_dl:

-- client session #2
V1> exec app_mgr.do_something

wow! this is not the base edition anymore

PL/SQL procedure successfully completed.

The first client session had been opened before we exposed the new edition, so it is still using the pre-upgrade edition, and if we execute the same procedure from that session we see it’s indeed using the old implementation:

-- client session #1
ORA$BASE> exec app_mgr.do_something

person added using the BASE version

PL/SQL procedure successfully completed.

Exposing a New Edition with API Changes

Now let’s re-run the upgrade we’ve done in part 9 – adding the EYE_COLOR column to the PEOPLE$0 table, adding it to the PEOPLE editioning view, and making the corresponding changes in the packages.
We’ve done all of this in a new edition V3, and we now want to expose this edition to the clients.
Since we’ve changed the API (of the people_dl.add procedure, for example), the clients should be aware to the fact they are working with a new version of the application.
Therefore, in this case we’ll create a new service (S2), associate it with the new edition (V3), and let the clients know they should connect to this new service.

SQL> begin
  2      dbms_service.create_service(
  3          service_name => 'S2',
  4          network_name => 'S2',
  5          edition      => 'V3'
  6      );
  7      dbms_service.start_service(
  8          service_name => 'S2'
  9      );
 10  end;
 11  /

PL/SQL procedure successfully completed.

So when we open a new client session, using the S2 service, the session edition is automatically V3:

-- client session #3
C:\> sqlplus demo_ebr/ebr@localhost/S2
V3> 

And we can use the new features that were added in the latest edition:

-- client session #3
V3> exec people_dl.add(1,'John','Lennon',null,'Brown')

this is the version that introduced eye colors

PL/SQL procedure successfully completed.

V3> commit;

Commit complete.

V3> select * from people;

        ID FIRST_NAME LAST_NAME  PHONE_NUMBER EYE_COLOR
---------- ---------- ---------- ------------ --------------------
         1 John       Lennon                  Brown

For other parts of the EBR series, please visit the index page