07 April 2010

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
/

3 comments: