Oracle 11g introduced Application Editions, a tool that can be used to make changes to an existing schema, without actually changing the source schema by creating a child edition of the base edition. Every 11g database has a base edition, by default named ORA$BASE from which child editions can be created. A base edition can only have one child edition so you can’t keep creating child editions of the same base (which is good as it keeps confusion to a minimum). With application editions developers can build a new edition of the current schema and make wholesale changes without affecting the base edition data or tables. To actually populate tables/views in the new edition a cross edition trigger is required, which can be created and invoked from the new edition. Testing can be accomplished in the new edition and when all looks good the new edition can be made the current edition in the database, which will implement all of the edition changes into the UAT for user testing and approval.
To explain how this works look at the following example involving one table in a base edition (in this case ORA$BASE) and the corresponding objects in the child edition (named Post_Upgrade). In preparation for using editions the user accounts involved need to be granted the ‘CREATE ANY EDITION’ privilege and must have EDITIONS enabled, otherwise the editioning tools are not available. The following statements executed by a DBA-privileged account allow the user BING to create and use editions:
grant create any edition to bing;
alter user bing enable editions;
The user is now ready to create and use editions.
Since a new edition hasn’t been created in the database, the default base edition is used; the first steps are to create the table to be upgraded, populate that table and prepare it for use by declaring a primary key and creating a sequence to populate that key:
SQL>
SQL> -- 1.Create table:
SQL>
SQL> CREATE TABLE Contacts(
2 ID NUMBER(6,0) CONSTRAINT Contacts_PK PRIMARY KEY,
3 Name VARCHAR2(47),
4 Phone_Number VARCHAR2(20)
5 );
Table created.
SQL>
SQL> -- 2.Populate table
SQL>
SQL> insert all
2 into contacts
3 values(174, 'Abel, Ellen', '011.44.1644.429267')
4 into contacts
5 values(166, 'Ande, Sundar', '011.44.1346.629268')
6 into contacts
7 values(130, 'Atkinson, Mozhe', '650.124.6234')
8 into contacts
9 values(105, 'Austin, David', '590.423.4569')
10 into contacts
11 values(204, 'Baer, Hermann', '515.123.8888')
12 into contacts
13 values(116, 'Baida, Shelli', '515.127.4563')
14 into contacts
15 values(167, 'Banda, Amit', '011.44.1346.729268')
16 into contacts
17 values(172, 'Bates, Elizabeth', '011.44.1343.529268')
18 into contacts
19 values(192, 'Bell, Sarah', '650.501.1876')
20 into contacts
21 values(151, 'Bernstein, David', '011.44.1344.345268')
22 into contacts
23 values(129, 'Bissot, Laura', '650.124.5234')
24 into contacts
25 values(169, 'Bloom, Harrison', '011.44.1343.829268')
26 into contacts
27 values(185, 'Bull, Alexis', '650.509.2876')
28 into contacts
29 values(187, 'Cabrio, Anthony', '650.509.4876')
30 into contacts
31 values(148, 'Cambrault, Gerald', '011.44.1344.619268')
32 into contacts
33 values(154, 'Cambrault, Nanette', '011.44.1344.987668')
34 into contacts
35 values(110, 'Chen, John', '515.124.4269')
36 into contacts
37 values(120, 'Weiss, Matthew', '650.123.1234')
38 into contacts
39 values(200, 'Whalen, Jennifer', '515.123.4444')
40 into contacts
41 values(149, 'Zlotkey, Eleni', '011.44.1344.429018')
42 select * from dual;
20 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> -- 3.Declare primary key and create sequence
SQL>
SQL> ALTER TABLE Contacts ENABLE VALIDATE CONSTRAINT Contacts_PK;
Table altered.
SQL>
SQL> DECLARE
2 Max_ID INTEGER;
3 BEGIN
4 SELECT MAX(ID) INTO Max_ID FROM Contacts;
5 EXECUTE IMMEDIATE '
6 CREATE SEQUENCE Contacts_Seq
7 START WITH '||To_Char(Max_ID + 1);
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- 4. View current data
SQL>
SQL> SELECT * FROM Contacts
2 ORDER BY Name;
ID NAME PHONE_NUMBER
---- ------------------------------ --------------------
174 Abel, Ellen 011.44.1644.429267
166 Ande, Sundar 011.44.1346.629268
130 Atkinson, Mozhe 650.124.6234
105 Austin, David 590.423.4569
204 Baer, Hermann 515.123.8888
116 Baida, Shelli 515.127.4563
167 Banda, Amit 011.44.1346.729268
172 Bates, Elizabeth 011.44.1343.529268
192 Bell, Sarah 650.501.1876
151 Bernstein, David 011.44.1344.345268
129 Bissot, Laura 650.124.5234
ID NAME PHONE_NUMBER
---- ------------------------------ --------------------
169 Bloom, Harrison 011.44.1343.829268
185 Bull, Alexis 650.509.2876
187 Cabrio, Anthony 650.509.4876
148 Cambrault, Gerald 011.44.1344.619268
154 Cambrault, Nanette 011.44.1344.987668
110 Chen, John 515.124.4269
120 Weiss, Matthew 650.123.1234
200 Whalen, Jennifer 515.123.4444
149 Zlotkey, Eleni 011.44.1344.429018
20 rows selected.
SQL>
The data looks good but it could be in a better format; the table is renamed so that the editioning view can have the original table name. An editionable object is required to make changes in an edition, and tables are not editionable objects, however views are, thus an editioning view is created to handle the changes applied to the edition. The following steps are executed:
SQL>
SQL> -- 5.Give table a new name (to give its current name to editioning view):
SQL>
SQL> ALTER TABLE Contacts RENAME TO Contacts_Table;
Table altered.
SQL>
SQL> -- 6.(Optional) Give columns of table new names:
SQL>
SQL> ALTER TABLE Contacts_Table
2 RENAME column Name TO Name_1;
Table altered.
SQL>
SQL> ALTER TABLE Contacts_Table
2 RENAME column Phone_Number TO Phone_Number_1;
Table altered.
SQL>
SQL> -- 7.Create editioning view:
SQL>
SQL> CREATE OR REPLACE EDITIONING VIEW Contacts AS
2 SELECT
3 ID ID,
4 Name_1 Name,
5 Phone_Number_1 Phone_Number
6 FROM Contacts_Table;
View created.
SQL>
SQL> -- 8.Create trigger on editioning view
SQL>
SQL> CREATE TRIGGER Contacts_BI
2 BEFORE INSERT ON Contacts FOR EACH ROW
3 BEGIN
4 :NEW.ID := Contacts_Seq.NEXTVAL;
5 END;
6 /
Trigger created.
SQL>
SQL> -- 9.Create new edition:
SQL>
SQL> CREATE EDITION Post_Upgrade AS CHILD OF Ora$Base;
Edition created.
SQL>
SQL> -- 10.Make new edition the session edition:
SQL>
SQL> ALTER SESSION SET EDITION = Post_Upgrade;
Session altered.
SQL>
SQL> -- 11.Check session edition:
SQL>
SQL> SELECT
2 SYS_CONTEXT('Userenv', 'Current_Edition_Name') "Current_Edition"
3 FROM DUAL;
Current_Edition
-------------------------
POST_UPGRADE
SQL>
The editioning view is in place and the current edition is no longer the default; it’s now time to start making changes in the edition. Since the data in the original table could be better defined, new columns are added to the physical table — these will be populated with the forward cross-edition trigger created later in the process:
SQL>
SQL> -- 12.Add new columns to physical table:
SQL>
SQL> ALTER TABLE Contacts_Table ADD (
2 First_Name_2 varchar2(20),
3 Last_Name_2 varchar2(25),
4 Country_Code_2 varchar2(20),
5 Phone_Number_2 varchar2(20)
6 );
Table altered.
SQL>
SQL> -- 13.Recompile invalidated trigger:
SQL>
SQL> ALTER TRIGGER Contacts_BI COMPILE REUSE SETTINGS;
Trigger altered.
SQL>
SQL> -- 14.Replace editioning view so that it selects replacement columns with their desired logical names:
SQL>
SQL> CREATE OR REPLACE EDITIONING VIEW Contacts AS
2 SELECT
3 ID ID,
4 First_Name_2 First_Name,
5 Last_Name_2 Last_Name,
6 Country_Code_2 Country_Code,
7 Phone_Number_2 Phone_Number
8 FROM Contacts_Table;
View created.
SQL>
The next step is to create the forward cross-edition trigger procedures — the first reformats the original Name column into first and last name components and the second separates the phone number into country code and number fields to make processing those values easier in the upgraded version of the table. Finally the forward and reverse cross-edition triggers are created:
SQL>
SQL> -- 15.Create first procedure that forward crossedition trigger uses:
SQL>
SQL> CREATE OR REPLACE PROCEDURE Set_First_And_Last_Name (
2 Name IN VARCHAR2,
3 First_Name OUT VARCHAR2,
4 Last_Name OUT VARCHAR2)
5 IS
6 Comma_Pos NUMBER := INSTR(Name, ',');
7 BEGIN
8 IF Comma_Pos IS NULL OR Comma_Pos < 2 THEN
9 RAISE Program_Error;
10 END IF;
11
12 Last_Name := SUBSTR(Name, 1, Comma_Pos-1);
13 Last_Name := RTRIM(Ltrim(Last_Name));
14
15 First_Name := SUBSTR(Name, Comma_Pos+1);
16 First_Name := RTRIM(LTRIM(First_Name));
17 END Set_First_And_Last_Name;
18 /
Procedure created.
SQL>
SQL> -- 16.Create second procedure that forward crossedition trigger uses:
SQL>
SQL> CREATE OR REPLACE PROCEDURE Set_Country_Code_And_Phone_No (
2 Phone_Number IN VARCHAR2,
3 Country_Code OUT VARCHAR2,
4 Phone_Number_V2 OUT VARCHAR2)
5 IS
6 Char_To_Number_Error EXCEPTION;
7 PRAGMA EXCEPTION_INIT(Char_To_Number_Error, -06502);
8 Bad_Phone_Number EXCEPTION;
9 Nmbr VARCHAR2(30) := REPLACE(Phone_Number, '.', '-');
10
11 FUNCTION Is_US_Number(Nmbr IN VARCHAR2)
12 RETURN BOOLEAN
13 IS
14 Len NUMBER := LENGTH(Nmbr);
15 Dash_Pos NUMBER := INSTR(Nmbr, '-');
16 n PLS_INTEGER;
17 BEGIN
18 IF Len IS NULL OR Len <> 12 THEN
19 RETURN FALSE;
20 END IF;
21 IF Dash_Pos IS NULL OR Dash_Pos <> 4 THEN
22 RETURN FALSE;
23 END IF;
24 BEGIN
25 n := TO_NUMBER(SUBSTR(Nmbr, 1, 3));
26 EXCEPTION WHEN Char_To_Number_Error THEN
27 RETURN FALSE;
28 END;
29
30 Dash_Pos := INSTR(Nmbr, '-', 5);
31
32 IF Dash_Pos IS NULL OR Dash_Pos <> 8 THEN
33 RETURN FALSE;
34 END IF;
35
36 BEGIN
37 n := TO_NUMBER(SUBSTR(Nmbr, 5, 3));
38 EXCEPTION WHEN Char_To_Number_Error THEN
39 RETURN FALSE;
40 END;
41
42 BEGIN
43 n := TO_NUMBER(SUBSTR(Nmbr, 9));
44 EXCEPTION WHEN Char_To_Number_Error THEN
45 RETURN FALSE;
46 END;
47
48 RETURN TRUE;
49 END Is_US_Number;
50
51 BEGIN
52 IF Nmbr LIKE '011-%' THEN
53 DECLARE
54 Dash_Pos NUMBER := INSTR(Nmbr, '-', 5);
55 BEGIN
56 Country_Code := '+'|| TO_NUMBER(SUBSTR(Nmbr, 5, Dash_Pos-5));
57 Phone_Number_V2 := SUBSTR(Nmbr, Dash_Pos+1);
58 EXCEPTION WHEN Char_To_Number_Error THEN
59 raise Bad_Phone_Number;
60 END;
61 ELSIF Is_US_Number(Nmbr) THEN
62 Country_Code := '+1';
63 Phone_Number_V2 := Nmbr;
64 ELSE
65 RAISE Bad_Phone_Number;
66 END IF;
67 EXCEPTION WHEN Bad_Phone_Number THEN
68 Country_Code := '+0';
69 Phone_Number_V2 := '000-000-0000';
70 END Set_Country_Code_And_Phone_No;
71 /
Procedure created.
SQL> -- 16.Create forward crossedition trigger in disabled state:
SQL>
SQL> CREATE OR REPLACE TRIGGER Contacts_Fwd_Xed
2 BEFORE INSERT OR UPDATE ON Contacts_Table
3 FOR EACH ROW
4 FORWARD CROSSEDITION
5 DISABLE
6 BEGIN
7 Set_First_And_Last_Name(
8 :NEW.Name_1,
9 :NEW.First_Name_2,
10 :NEW.Last_Name_2
11 );
12 Set_Country_Code_And_Phone_No(
13 :NEW.Phone_Number_1,
14 :NEW.Country_Code_2,
15 :NEW.Phone_Number_2
16 );
17 END Contacts_Fwd_Xed;
18 /
Trigger created.
SQL>
SQL> -- 17.Enable forward crossedition trigger:
SQL>
SQL> ALTER TRIGGER Contacts_Fwd_Xed ENABLE;
Trigger altered.
SQL>
SQL> -- 18.Create reverse crossedition trigger in disabled state:
SQL>
SQL> CREATE OR REPLACE TRIGGER Contacts_Rvrs_Xed
2 BEFORE INSERT OR UPDATE ON Contacts_Table
3 FOR EACH ROW
4 REVERSE CROSSEDITION
5 DISABLE
6 BEGIN
7 :NEW.Name_1 := :NEW.Last_Name_2||', '||:NEW.First_Name_2;
8 :NEW.Phone_Number_1 :=
9 CASE :New.Country_Code_2
10 WHEN '+1' THEN
11 REPLACE(:NEW.Phone_Number_2, '-', '.')
12 ELSE
13 '011.'||LTRIM(:NEW.Country_Code_2, '+')||'.'||
14 REPLACE(:NEW.Phone_Number_2, '-', '.')
15 END;
16 END Contacts_Rvrs_Xed;
17 /
Trigger created.
SQL>
SQL> -- 19.Enable reverse crossedition trigger:
SQL>
SQL> ALTER TRIGGER Contacts_Rvrs_Xed ENABLE;
Trigger altered.
SQL>
The forward cross-edition trigger splits the data into separate fields (first and last name, country code, number); the reverse cross-edition trigger puts those pieces back together into the starting format should the edition changes need to be undone. To create the reformatted data in the new edition it is necessary to wait for all pending DML to complete (either through a commit or a rollback) and that is done with a block of PL/SQL code like the one shown below:
SQL>
SQL> -- 20.Wait until pending changes are either committed or rolled back:
SQL>
SQL> DECLARE
2 scn NUMBER := NULL;
3 timeout CONSTANT INTEGER := NULL;
4 BEGIN
5 IF NOT DBMS_UTILITY.WAIT_ON_PENDING_DML(Tables => 'Contacts_Table',
6 timeout => timeout,
7 scn => scn)
8 THEN
9 RAISE_APPLICATION_ERROR(-20000,
10 'Wait_On_Pending_DML() timed out. CETs were enabled before SCN: '||SCN);
11 END IF;
12 END;
13 /
PL/SQL procedure successfully completed.
SQL>
Once the waiting is over the cross edition trigger can be fired using an update statement that doesn’t really modify anything; the code below, using the DBMS_SQL package to parse and execute the required DML statement and execute the forward cross-edition trigger written in a prior step, populates the new table:
SQL>
SQL> -- 21.Execute cross-edition trigger to populate the new table
SQL>
SQL> DECLARE
2 c NUMBER := DBMS_SQL.OPEN_CURSOR();
3 x NUMBER;
4 BEGIN
5 DBMS_SQL.PARSE(
6 c => c,
7 Language_Flag => DBMS_SQL.NATIVE,
8 Statement => 'UPDATE Contacts_Table SET ID = ID',
9 Apply_Crossedition_Trigger => 'Contacts_Fwd_Xed'
10 );
11 x := DBMS_SQL.EXECUTE(c);
12 DBMS_SQL.CLOSE_CURSOR(c);
13 COMMIT;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
It’s time to check the data in the new table to verify the required transforms are completed successfully:
SQL>
SQL> -- 22.Format columns for readability:
SQL>
SQL> column ID format 999
SQL> column Last_Name format A15
SQL> column First_Name format A15
SQL> column Country_Code format A12
SQL> column Phone_Number format A12
SQL>
SQL> -- 23.Query:
SQL>
SQL> SELECT * FROM Contacts
2 ORDER BY Last_Name;
ID FIRST_NAME LAST_NAME COUNTRY_CODE PHONE_NUMBER
---- --------------- --------------- ------------ ------------
174 Ellen Abel +44 1644-429267
166 Sundar Ande +44 1346-629268
130 Mozhe Atkinson +1 650-124-6234
105 David Austin +1 590-423-4569
204 Hermann Baer +1 515-123-8888
116 Shelli Baida +1 515-127-4563
167 Amit Banda +44 1346-729268
172 Elizabeth Bates +44 1343-529268
192 Sarah Bell +1 650-501-1876
151 David Bernstein +44 1344-345268
129 Laura Bissot +1 650-124-5234
ID FIRST_NAME LAST_NAME COUNTRY_CODE PHONE_NUMBER
---- --------------- --------------- ------------ ------------
169 Harrison Bloom +44 1343-829268
185 Alexis Bull +1 650-509-2876
187 Anthony Cabrio +1 650-509-4876
148 Gerald Cambrault +44 1344-619268
154 Nanette Cambrault +44 1344-987668
110 John Chen +1 515-124-4269
120 Matthew Weiss +1 650-123-1234
200 Jennifer Whalen +1 515-123-4444
149 Eleni Zlotkey +44 1344-429018
20 rows selected.
SQL>
The data transformations successfully executed and the table in the new edition is much more readable than the original data loaded at the beginning of this example. Until the new edition is made the default database edition, the original data and table are still in use; only the sessions modified to use the new edition can ‘see’ the reformatted data.
Application editions are a very useful addition to an already world-class database, allowing developers to make changes to existing tables and schemas without affecting current users in the testing environment. With application editions there is no need to inconvenience testers by replacing existing schemas with new versions, which may not have been fully exorcised of bugs; the development process becomes easier to manage both by isolating changes to a new edition and by allowing changes to be tracked against the current working edition. It may seem like a bit more work to use application editions but once the learning curve is traversed the process, at least to me, seems more logical and straightforward, which is what the development cycle should be to begin with. Oracle, with the application edition toolset, has given development a new and fairly easy-to-use way to effect change without affecting existing users, making this a win-win proposition.
