Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted March 14, 2012

Using Application Editions To Make Upgrades Less Painful

By David Fitzjarrell

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.

See all articles by David Fitzjarrell



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date