Wednesday, June 18, 2014

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