This is part 9 of a post series about Oracle Edition-Based Redefinition.
Visit the index page for all the parts of the series
Introduction
In part 7 (“Editioning Views”) I introduced our third development use case – adding a new column that represents a new business logic.
I emphasize the fact it’s a new business logic, because sometimes we add new columns that come instead of existing columns, for replacing an existing business logic. As we’ll see in future posts, such cases require some additional handling in online upgrades.
Now, after part 8 (“The Last Planned Downtime”), in which we converted our schema to use editioning views and made it fully ready for online upgrades, we can implement the requirement in an online way.
The Task
We want to add a new attribute – Eye Color – to the Person entity.
For that we need to add a new column – EYE_COLOR – to the PEOPLE table, add make the corresponding changes in the PEOPLE_DL and APP_MGR packages (adding an input parameter – i_eye_color – to the people_dl.add procedure and changing the implementation of people_dl.add and app_mgr.do_something accordingly).
The Current State
At this point, V2 is our latest edition and it is exposed to the production clients. When connected to V2, we see the following objects:
V2> break on object_type on object_name on status on edition_name skip 1
V2>
V2> select o.object_type,
2 o.object_name,
3 o.status,
4 o.edition_name
5 from user_objects o
6 order by nvl2(o.edition_name, 2, 1),
7 o.object_type,
8 o.object_name;
OBJECT_TYPE OBJECT_NAME STATUS EDITION_NAME
------------ ------------ ------- ------------
INDEX PEOPLE_PK VALID
TABLE PEOPLE$0 VALID
PACKAGE APP_MGR VALID ORA$BASE
PEOPLE_DL VALID V2
PACKAGE BODY APP_MGR VALID V2
PEOPLE_DL VALID V2
VIEW PEOPLE VALID V2
7 rows selected.
The PEOPLE editioning view projects all the columns of the PEOPLE$0 table:
V2> desc people$0 Name Null? Type ----------------------- -------- ---------------- ID NOT NULL NUMBER(9) FIRST_NAME NOT NULL VARCHAR2(15) LAST_NAME NOT NULL VARCHAR2(20) PHONE_NUMBER VARCHAR2(20) V2> desc people Name Null? Type ----------------------- -------- ---------------- ID NOT NULL NUMBER(9) FIRST_NAME NOT NULL VARCHAR2(15) LAST_NAME NOT NULL VARCHAR2(20) PHONE_NUMBER VARCHAR2(20)
Performing the Upgrade
Creating a New Edition
As in any online upgrade, we start by creating a new edition.
Let’s create edition V3 and grant use on it to DEMO_EBR:
-- connected as system ORA$BASE> create edition v3; Edition created. ORA$BASE> grant use on edition v3 to demo_ebr; Grant succeeded.
In the developer session, let’s change the session edition to V3:
-- the developer session
V2> @set_edition v3
Session altered.
V3>
Adding the Column to the Table
Let’s add the new column to the table. This is an online operation, and, as explained in part 7, since no object depends on the table except for the editioning view, no object is invalidated.
V3> alter table people$0 add (eye_color varchar2(20));
Table altered.
Adding the Column to the Editioning View
Now we can add the new column to the editioning view. We do it in the privacy of the new (and unexposed) edition, and, as a result, a new actual object of PEOPLE is instantiated.
V3> create or replace editioning view people as
2 select * from people$0;
V3> desc people
Name Null? Type
----------------------- -------- ----------------
ID NOT NULL NUMBER(9)
FIRST_NAME NOT NULL VARCHAR2(15)
LAST_NAME NOT NULL VARCHAR2(20)
PHONE_NUMBER VARCHAR2(20)
EYE_COLOR VARCHAR2(20)
The clients, that are connected to the V2 edition, still see the pre-upgrade version of PEOPLE:
-- the client session
V2> desc people
Name Null? Type
----------------------- -------- ----------------
ID NOT NULL NUMBER(9)
FIRST_NAME NOT NULL VARCHAR2(15)
LAST_NAME NOT NULL VARCHAR2(20)
PHONE_NUMBER VARCHAR2(20)
So although tables are non-editionable and we have only one instance with one structure of the table, the view allows us to get the feeling that the table has different structures in different editions.
Changing the Code
In the privacy of V3 we make all the necessary changes in the packages:
V3> create or replace package 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 i_eye_color in people.eye_color%type
9 );
10 procedure remove
11 (
12 i_id in people.id%type
13 );
14 end people_dl;
15 /
Package created.
V3> 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 i_eye_color in people.eye_color%type
9 ) is
10 begin
11 insert into people (id,first_name,last_name,phone_number,eye_color)
12 values (i_id,i_first_name,i_last_name,i_phone_number,i_eye_color);
13 --
14 dbms_output.new_line;
15 dbms_output.put_line('this is the version that introduced eye colors');
16 --
17 end add;
18
19 procedure remove(i_id in people.id%type) is
20 begin
21 delete people where id = i_id;
22 end remove;
23 end people_dl;
24 /
Package body created.
V3> create or replace package body app_mgr as
2 procedure do_something is
3 begin
4 people_dl.add(123, 'John', 'Doe', NULL, 'Brown');
5 people_dl.remove(123);
6 commit;
7 end do_something;
8 end app_mgr;
9 /
Package body created.
Revalidation
And finally, before exposing the new edition to the clients, we make sure all the objects are valid and we explicitly actualize dependent objects if needed, as explained in part 5 (“Explicit Actualization of Dependent Objects”):
V3> @revalidate
PL/SQL procedure successfully completed.
V3> select o.object_type,
2 o.object_name,
3 o.status,
4 o.edition_name,
5 nullif(
6 d.referenced_name || ' [' || lower(d.referenced_type) || ']',
7 ' []'
8 ) depends_on
9 from user_objects o,
10 user_dependencies d
11 where d.name(+) = o.object_name
12 and d.type(+) = o.object_type
13 and d.referenced_owner(+) = user
14 order by nvl2(o.edition_name, 2, 1),
15 o.object_type,
16 o.object_name,
17 d.referenced_name;
OBJECT_TYPE OBJECT_NAME STATUS EDITION_NAME DEPENDS_ON
------------ ------------ ------- ------------ --------------------
INDEX PEOPLE_PK VALID
TABLE PEOPLE$0 VALID
PACKAGE APP_MGR VALID ORA$BASE
PEOPLE_DL VALID V3 PEOPLE [view]
PACKAGE BODY APP_MGR VALID V3 APP_MGR [package]
PEOPLE_DL [package]
PEOPLE_DL VALID V3 PEOPLE [view]
PEOPLE_DL [package]
VIEW PEOPLE VALID V3 PEOPLE$0 [table]
9 rows selected.
Exposing the New Edition
And now let’s simulate exposing of the post-upgrade version to the client, simply by setting its session edition to V3, and let it use the people_dl.add procedure for inserting our first person into the table:
-- the client session
V2> @set_edition v3
Session altered.
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.