Oracle Editioning a table

In the last blog, I have traced the steps of Tom’s wonderful introductions to Oracle 11.2 new feature “Editioning-based redefinition” to edition a stored procedure. 

A Closer Look at the New Edition
Edition-Based Redefinition, Part 2
Looking at Edition-Based Redefinition, Part 3

All The following object types are editionable:

•  Synonyms
•  Views (including editioning views, which we’ll define below
• All PL/SQL object types (functions, procedures, packages, and so on)

Tables are not. But do not despair, there is a way to make tables editioned, it is just it can seem as a roundabout and confusing way. 

Big picture

 There is an employees table, containing a phone_number column. We want to use two columns (country and phone#) to store phone numbers.

Before	After
PHONE_NUMBER	COUNTRY_CODE	PHONE#
650.507.9876	+1	650.507.9876
011.44.1644.429262	+44	1644.429262

  

This change requires us to do:

1.  Add two columns (country_code and phone#) to table employee
2.  Handling existing data. For existing phone numbers stored in column phone_number, we need to figure out the country part and the regional part, and stored them in the two new columns.
3. Ensure new user code and old user code can co-exist for some time before all old user code can be converted. Old user code will continue to access phone_number column, new user code will use the two new columns. phone_number values created by the old user code will be handled to create values in the two new columns; values in the two new columns will be handled to create values in phone_number column. 

The final picture looks like this:

 

 

All code has to access the editioning view (employees) to access the real physical table (employees_rt). The original table employees is now renamed to employees_rt.The old code continues to use the phone_number column of the table; the new code uses the two new columns (country_code & phone#) of the table. When the old user code changes phone_number, a forward crossedition trigger carries the value to the two new columns country_code & phone#). When the new user code changes country_code & phone#, a backward crossedition trigger carries the value to phone_number. In this way, the old and new user code can co-exist for a long time until all old user code is ready to be shutdown. 

Create table and user code

--use sys account

grant all on  hr.employees to demo

alter user demo quota 100M on USERS;

grant unlimited tablespace to demo;

--use demo/demo

create table

     employees

     as

     select * from hr.employees;

create sequence emp_seq start with 500;

         

create or replace package emp_pkg

      as

        procedure show

        ( last_name_like in employees.last_name%type );

 

        function add

        ( FIRST_NAME in employees.FIRST_NAME%type := null,

          LAST_NAME in employees.LAST_NAME%type,

          EMAIL in employees.EMAIL%type,

         PHONE_NUMBER in employees.PHONE_NUMBER%type := null,

        HIRE_DATE in employees.HIRE_DATE%type,

        JOB_ID in employees.JOB_ID%type,

        SALARY in employees.SALARY%type := null,

         COMMISSION_PCT in employees.COMMISSION_PCT%type := null,

         MANAGER_ID in employees.MANAGER_ID%type := null,

         DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null  )

       return employees.employee_id%type;

     end;

     /

create or replace package body emp_pkg

      as

 

      procedure show

      ( last_name_like in employees.last_name%type )

      as

      begin

          for x in

          ( select first_name, last_name,

                  phone_number, email

             from employees

            where last_name like

                  show.last_name_like

            order by last_name )

         loop

             dbms_output.put_line

             ( rpad( x.first_name || ' ' ||

                       x.last_name, 40 ) ||

               rpad( nvl(x.phone_number, ' '), 20 ) ||

               x.email );

         end loop;

     end show;

     function add

     ( FIRST_NAME in employees.FIRST_NAME%type := null,

      LAST_NAME in employees.LAST_NAME%type,

       EMAIL in employees.EMAIL%type,

       PHONE_NUMBER in employees.PHONE_NUMBER%type := null,

       HIRE_DATE in employees.HIRE_DATE%type,

       JOB_ID in employees.JOB_ID%type,

       SALARY in employees.SALARY%type := null,

       COMMISSION_PCT in employees.COMMISSION_PCT%type := null,

       MANAGER_ID in employees.MANAGER_ID%type := null,

       DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null

     )

     return employees.employee_id%type

     is

         employee_id  employees.employee_id%type;

     begin

         insert into employees

         ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,

           EMAIL, PHONE_NUMBER, HIRE_DATE,

           JOB_ID, SALARY, COMMISSION_PCT,

           MANAGER_ID, DEPARTMENT_ID )

         values

         ( emp_seq.nextval, add.FIRST_NAME, add.LAST_NAME,

           add.EMAIL, add.PHONE_NUMBER, add.HIRE_DATE,

           add.JOB_ID, add.SALARY, add.COMMISSION_PCT,

           add.MANAGER_ID, add.DEPARTMENT_ID )

         returning employee_id into add.employee_id;

         return add.employee_id;

     end add;

     end;

     /

Create editioning view

This requires a one-time maintenance window for sliding in the editioning view. The general steps for the process are as follows: 

•  Rename the existing table, because the editioning view will be taking over the old name.
•  Create the editioning view, giving it the name of the original table.
• Drop any triggers on the existing table, and move them to the editioning view. This step is recommended because the CREATE TRIGGER statements you have refer to the table by name directly. You’ll want the triggers on the editioning view that now has the name of the original table.
•  Re-create the triggers on the editioning view.
•  Revoke privileges from the base table, and re-grant them on the editioning view.
•  Take other steps, such as moving a fine-grained access control policy from the base table to the editioning view.

In the future, as you develop your new applications, you can avoid this outage entirely by using ditioning views in your application code from the very beginning. This strategy will allow you to rename and reorder columns in your table in a very trivial fashion and will permit online application upgrades coupled with physical schema changes.

--use demo/demo

alter table employees rename to employees_rt;

create editioning view employees

      as

      select

        EMPLOYEE_ID, FIRST_NAME,

        LAST_NAME, EMAIL, PHONE_NUMBER,

        HIRE_DATE, JOB_ID, SALARY,

        COMMISSION_PCT, MANAGER_ID,

        DEPARTMENT_ID

      from employees_rt

    /

      

alter table employees_rt

      add

      ( country_code varchar2(3),

        phone# varchar2(20)

      )

      /

create index employees_phone#_idx

      on employees_rt(phone#)

      ONLINE INVISIBLE

      /

 

Create forward crossedition trigger

 

The forward crossedition needs to be created in version2, however, its firing is done by actions in the old version – confusing right? The forward crossedition trigger will analyze old values and figure out values for the two new columns.

alter session set edition = version2;

create or replace trigger employees_fwdxedition

     before insert or update of phone_number on employees_rt

      for each row

      forward crossedition

      declare

          first_dot  number;

          second_dot number;

      begin

          if :new.phone_number like '011.%'

        then

             first_dot

                := instr( :new.phone_number, '.' );

             second_dot

                := instr( :new.phone_number, '.', 1, 2 );

             :new.country_code

                := '+'||

                   substr( :new.phone_number,

                             first_dot+1,

                           second_dot-first_dot-1 );

             :new.phone#

                := substr( :new.phone_number,

                             second_dot+1 );

         else

             :new.country_code := '+1';

             :new.phone# := :new.phone_number;

         end if;

     end;

     /

Now let us change all the existing phone numbers in the phone_number column.

 

alter session set edition = ORA$BASE;   

update employees set phone_number = phone_number;

Upgrading user code

 

Now the physical schema has been changed (employees_rt table has two new columns), it is ready to upgrade the user code. To not upset existing running code, this will be done in version2.

alter session set edition = version2;

--the user code now starts to use the new columns

create or replace editioning view employees

      as

      select

        EMPLOYEE_ID, FIRST_NAME,

        LAST_NAME, EMAIL, COUNTRY_CODE, PHONE#,

        HIRE_DATE, JOB_ID, SALARY,

        COMMISSION_PCT, MANAGER_ID,

        DEPARTMENT_ID

      from employees_rt

    /

create or replace package emp_pkg

      as

        procedure show

        ( last_name_like in employees.last_name%type );

 

        function add

        ( FIRST_NAME in employees.FIRST_NAME%type := null,

          LAST_NAME in employees.LAST_NAME%type,

          EMAIL in employees.EMAIL%type,

          COUNTRY_CODE in employees.COUNTRY_CODE%type := null,

          PHONE# in employees.PHONE#%type := null,

        HIRE_DATE in employees.HIRE_DATE%type,

        JOB_ID in employees.JOB_ID%type,

        SALARY in employees.SALARY%type := null,

         COMMISSION_PCT in employees.COMMISSION_PCT%type := null,

         MANAGER_ID in employees.MANAGER_ID%type := null,

         DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null  )

       return employees.employee_id%type;

     end;

     /

create or replace package body emp_pkg

      as

 

      procedure show

      ( last_name_like in employees.last_name%type )

      as

      begin

          for x in

          ( select first_name, last_name,

                   country_code, phone#,  email

             from employees

            where last_name like

                  show.last_name_like

            order by last_name )

         loop

             dbms_output.put_line

             ( rpad( x.first_name || ' ' ||

                       x.last_name, 40 ) ||

                rpad( nvl(x.country_code, ' '), 5 ) ||

                rpad( nvl(x.phone#, ' '), 20 ) ||

               x.email );

         end loop;

     end show;

     function add

     ( FIRST_NAME in employees.FIRST_NAME%type := null,

      LAST_NAME in employees.LAST_NAME%type,

       EMAIL in employees.EMAIL%type,

       COUNTRY_CODE in employees.COUNTRY_CODE%type := null,

       PHONE# in employees.PHONE#%type := null,

       HIRE_DATE in employees.HIRE_DATE%type,

       JOB_ID in employees.JOB_ID%type,

       SALARY in employees.SALARY%type := null,

       COMMISSION_PCT in employees.COMMISSION_PCT%type := null,

       MANAGER_ID in employees.MANAGER_ID%type := null,

       DEPARTMENT_ID in employees.DEPARTMENT_ID%type := null

     )

     return employees.employee_id%type

     is

         employee_id  employees.employee_id%type;

     begin

         insert into employees

         ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,

           EMAIL, COUNTRY_CODE, PHONE#, HIRE_DATE,

           JOB_ID, SALARY, COMMISSION_PCT,

           MANAGER_ID, DEPARTMENT_ID )

         values

         ( emp_seq.nextval, add.FIRST_NAME, add.LAST_NAME,

           add.EMAIL, add.COUNTRY_CODE, add.PHONE#, add.HIRE_DATE,

           add.JOB_ID, add.SALARY, add.COMMISSION_PCT,

           add.MANAGER_ID, add.DEPARTMENT_ID )

         returning employee_id into add.employee_id;

         return add.employee_id;

     end add;

     end;

     /

Create backward crossedition trigger

 

The backward crossedition needs to be created in the base version, however, its firing is done by actions in the new version – confusing right? The backward crossedition trigger will analyze new values and figure out values for the old column.

alter session set edition = ORA$BASE;

create or replace trigger employees_revxedition

    before insert or update of country_code,phone# on employees_rt

    for each row

    reverse crossedition

    declare

        first_dot  number;

        second_dot number;

    begin

            if :new.country_code = '+1'

           then

              :new.phone_number :=

                 :new.phone#;

           else

             :new.phone_number :=

                 '011.' ||

                 substr( :new.country_code, 2 ) ||

                 '.' || :new.phone#;

          end if;

   end;

   /

 With the forward trigger and backward trigger, the new and old user code can co-exist. 

--the old use code

SQL> alter session set edition = ORA$BASE;

SQL> begin

            dbms_output.put_line

            ( emp_pkg.add

             ( first_name      => 'A1',

               last_name       => 'B1',

               email           => 'A1',

               phone_number    => '703.123.9999',

               hire_date       => sysdate,

               job_id          => 'IT_PROG' ) );

      end;

        /

--the new use code

SQL> alter session set edition = version2;

SQL> begin

     dbms_output.put_line

     ( emp_pkg.add

       ( first_name   => 'A2',

         last_name    => 'B2',

         email        => 'A2',

         country_code => '+44',

         phone#       => '703.123.4567',

         hire_date    => sysdate,

        job_id       => 'IT_PROG' ) );

   end;

   /

Make the new edition default for new sessions  

This can be done in an after logon trigger.

SQL> grant use on edition version2 to public;

SQL> create or

    replace trigger set_edition_on_logon

    after logon on database

    begin

            dbms_session.set_edition_deferred( 'VERSION2' );

    end;

    /

Clean up

When no one is using ORA$BASE anymore, we can:

• Dropping the forward and reverse crossedition triggers
• Optionally, dropping or setting as unused the PHONE_NUMBER column