Oracle 11gR2: Editions and SQL Developer

I know the correct term is Edition Based Redefinition, but that would make the title of this blog post a bit too long.
Over the last weekend Oracle 11gR2 was released on the Windows platform, good news for me. On my laptop I run Windows so I could finally upgrade my demo database from Release 1 to Release 2. I had been doing demo's with a virtual machine, which was ok. Like the one I did last year during the Planboard Symposium
While I was preparing for that presentation, the support for Edition Based Redefinition was added to SQL Developer 2.1, at least that's what the New Features stated. But since March 1st there is SQL Developer 2.1.1 which does have support for Edition Based Redefinition.
This blogpost is not going to explain Edition Based Redefinition in detail, but will show you the support that SQL Developer has for Edition Based Redefinition.

When you connect with SQL Developer to an Oracle 11g Release 2 database, there are a number of extra folders in the browser.

Following the demo I used for the Planboard Symposium, I created a few Editions, which would result in a tree structure.



With a simple Right-click on the Edition of your choice, you can change your current Edition, like changing to the Base Edition.



And changing the Editions also changes the Editioning Views. Note that the underlying tables are none editionable objects.


While playing around with the Editions I noticed something weird though.
First change to another edition named "Release_1":

And create a new Editioning View, using SQL*Plus:

And because this Editioning View includes a new column, Language, we also need a Forward Cross Edition Trigger to be compatible with older Editions:

The SQL Developer browser has a folder for the Cross Edition Triggers which now includes the newly created trigger:

But now the strange bit; when you try to edit the Cross Edition Trigger code in SQL Developer, it doesn't show any code.

At least not in the place where you would expect it, the Cross Edition Trigger code does show up in context of the underlying Base Table, named EMP_BASE:



And here's the testscript that I used:

conn alex/alex@orcl11

set echo on
cl scr

select *
  from v$version
/

pause

cl scr

set echo off
prompt 
prompt [== Setup ==]
prompt 
set echo on

create table emp
as
select *
  from scott.emp
/

pause

set echo off
prompt 
prompt [== Editions ==]
prompt 
set echo on

create edition base as child of ora$base
/

create edition release_1 as child of base
/

create edition release_2 as child of release_1
/

pause

cl scr

set echo off
prompt 
prompt [== Rename Base Table ==]
prompt 
set echo on

alter table emp rename to emp_base
/

pause

set echo off
prompt 
prompt [== Change Session Edition ==]
prompt 
set echo on

alter session set edition = base
/

pause

select sys_context('userenv', 'current_edition_name') "Current_Edition"
  from dual
/


pause
cl scr

set echo off
prompt 
prompt [== Editioning View Base Release ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , ename
     , deptno
     , job
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause


desc emp

pause

select *
  from emp
/

pause

cl scr
set echo off
prompt 
prompt [== Change Session Edition ==]
prompt 
set echo on

alter session set edition = release_1
/

pause

select sys_context('userenv', 'current_edition_name') "Current_Edition" 
from dual
/

pause
set echo off
prompt 
prompt [== Add additional column to Table ==]
prompt 
set echo on

alter table emp_base
add (language varchar2(2) null)
/

pause

desc emp_base

pause
set echo off
prompt 
prompt [== Editioning View Emp inherited ==]
prompt 
set echo on

desc emp

pause
cl scr

set echo off
prompt 
prompt [== Editioning View Release 1 ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , ename
     , deptno
     , job
     , language
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause

desc emp

pause

select *
  from emp
/

pause
cl scr
set echo off
prompt 
prompt [== Function Derive Language Value ==]
prompt 
set echo on

create or replace
function get_language (p_job in varchar2)
   return varchar2
is
begin
  return case p_job 
         when 'MANAGER' then 'fr' 
         else 'en'
         end;
end get_language;
/

pause


set echo off
prompt 
prompt [== Forward Cross Edition Trigger        ==]
prompt [== DML on earlier editions fire trigger ==]
prompt 
set echo on


create or replace trigger EMP_1_2_Fwd_Xed
before insert or update on emp_base
for each row
forward crossedition
disable
begin 
   :new.language := get_language(:new.job);
end EMP_1_2_Fwd_Xed; 
/

pause
set echo off
prompt 
prompt [== Enable Trigger ==]
prompt 
set echo on

alter trigger EMP_1_2_Fwd_Xed enable
/


pause
cl scr
set echo off
prompt 
prompt [== Test Trigger from Base Edition ==]
prompt 
set echo on

alter session set edition = base
/

pause

update emp
   set job = 'PA'
 where job = 'CLERK'
   and deptno = 10
/

pause

select *  
  from emp
 where job = 'PA'
   and deptno = 10
/

pause

select *
  from emp_base
 where job = 'PA'
   and deptno = 10
/

commit
/

pause
cl scr
set echo off
prompt 
prompt [== Change to Release_1 Edition ==]
prompt 
set echo on

alter session set edition = release_1
/

select ename
     , job
     , language
  from emp
/

pause

set echo off
prompt 
prompt [== Use Forward Crossedition Trigger     ==]
prompt [== to upgrade existing records in table ==]
prompt [== Consider: DBMS_PARALLEL_EXECUTE      ==]
prompt 
set echo on

declare 
  c number := dbms_sql.open_cursor(); 
  x number; 
begin 
   dbms_sql.parse
   ( c => c
   , Language_Flag => dbms_sql.native
   , Statement => 'UPDATE EMP_BASE
                   SET EMPNO = EMPNO'
   , Apply_Crossedition_Trigger => 'EMP_1_2_Fwd_Xed'
   );
   x := dbms_sql.execute(c); 
   dbms_sql.close_cursor(c); 
   commit; 
end; 
/

pause

select ename
     , job
     , language
  from emp
/

pause

cl scr

set echo off
prompt 
prompt [== Change to Release_2 Edition ==]
prompt 
set echo on

alter session set edition = release_2
/

set echo off
prompt 
prompt [== Change to Mandatory Column ==]
prompt 
set echo on

alter table emp_base
modify 
( language varchar2(2)  not null 
)
/

pause
set echo off
prompt 
prompt [== Add extra columns ==]
prompt 
set echo on

alter table emp_base
add 
( first_name varchar2(25)
, last_name  varchar2(25)
)
/

pause

set echo off
prompt 
prompt [== Editioning View Emp Release_2 ==]
prompt 
set echo on

create or replace editioning view emp
as
select empno
     , first_name
     , last_name
     , deptno
     , job
     , language
     , hiredate
     , sal
     , comm
     , mgr
  from emp_base
/

pause
cl scr
set lines 140

select *
  from emp
/


pause
set echo off
prompt 
prompt [== Forward Crossedition Trigger ==]
prompt [== Derive First and Last name   ==]
prompt 
set echo on

create or replace trigger EMP_1_3_Fwd_Xed
before insert or update of ename on emp_base
for each row
forward crossedition
disable
begin 
  :new.last_name := :new.ename;
end EMP_1_3_Fwd_Xed; 
/

alter trigger EMP_1_3_Fwd_Xed enable
/

pause
cl scr
set echo off
prompt 
prompt [== Apply Forward Crossedition Trigger ==]
prompt 
set echo on

declare 
  c number := dbms_sql.open_cursor(); 
  x number; 
begin 
  dbms_sql.parse
  ( c => c
  , Language_Flag => dbms_sql.native
  , Statement => 'UPDATE EMP_BASE
                  SET ENAME = ENAME'
  , Apply_Crossedition_Trigger => 'EMP_1_3_Fwd_Xed'
  );
  x := dbms_sql.execute(c); 
  dbms_sql.close_cursor(c); 
  commit; 
end; 
/

pause

select * 
  from emp
/

pause
cl scr
set echo off
prompt 
prompt [== Reverse Crossedition Trigger ==]
prompt [== Set ename based on LastName  ==]
prompt 
set echo on

create or replace trigger EMP_3_2_Rve_Xed
before insert or update of  last_name on emp_base
for each row
reverse crossedition
disable
begin 
  :new.ename := substr(:new.last_name, 1, 10);
end EMP_3_2_Rve_Xed; 
/


alter trigger EMP_3_2_Rve_Xed enable
/


pause

select sys_context ('userenv', 'current_edition_name') "Current_Edition"
  from dual
/

pause

set echo off
prompt 
prompt [== Change Lastname from Release_2 Edition ==]
prompt 
set echo on

update emp
   set last_name = 'MARTINA'
 where last_name = 'MARTIN'
/

commit
/

pause

select ename
     , last_name
  from emp_base
/

pause

alter session set edition = release_1
/

select ename
  from emp
/
pause

update emp
   set ename = 'MARTIN'
 where ename = 'MARTINA'
/

pause

select ename
     , last_name
  from emp_base
/

commit
/

pause

cl scr

set echo off
prompt
prompt [== Clean up==]
prompt
set echo on


alter session set edition = ora$base
/

pause

set echo off
prompt
prompt [== Make Editions Unusable ==]
prompt
set echo on

alter edition release_2 unusable
/
alter edition release_1 unusable
/
alter edition base unusable
/

pause

set echo off
prompt
prompt [== Drop Editions ==]
prompt
set echo on

drop edition release_2 cascade
/
drop edition release_1 cascade
/
drop edition base cascade
/
pause

set echo off
prompt
prompt [== Remove Base Table ==]
prompt
set echo on

drop table emp_base purge
/

drop view emp
/