Editioning a stored procedure

Oracle 11.2 introduced a new feature Editioning-Based Redefinition. I began to notice it only a few months ago when I looked into EBS 12.2, which claims to have a game-changer feature “online patching”, which essentially means EBS has to be taken off-line for a very short period of time to install patches. It certainly is very appealing to users. 

The technique for installing patches containing only files is very easy: EBS 12.2 has two file systems, one is the running file system, and the other is the patching file system. Patch files are installed into the patch file system. The running and the patching file systems are switched back and forth. 

The technique for installing patches that change database is entirely based on the Editioning-Based Redefinition of Oracle 11.2. As always, Tom explains this technique with a level of clarity that only someone very intelligent, very knowledge can manage: 

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

But this time, I found it hard to wrap my mind around it, it seems a rather confusing technique to get used to – maybe that is why I haven’t saw usages of this technique before, and only begun to know it recently. I followed the instructions step by step, and drawn a few diagrams to help me understand it.

This blog is to show how to editioning a stored procedure. The next blog will be about editioning a table. 

The big picture 

Starting Oracle 11.2, there is a default edition called ORA$BASE. You can create a child edition of ORA$BASE, a child edition starts its life as an exact copy of ORA$BASE.

 

In this example, my_procedures uses my_procedure. Version2 is a child version of ORA$BASE. Within version2, my_procedure is changed, my_procedure2 is not, so my_procedure2 is

the same as its base version. Because the change to my_procedure happens inside version2, the user my_procedure2 in ORA$BASE is not affected. So in ORA$BASE, my_procedure2 continues to use the old version of my_procedure; in version2, my_procedure2 uses the new version of my_procedure.

 Create demo user

 --use sys account

create user demo identified by demo;

grant create table,  create view,  create sequence, create trigger, create procedure, create session to demo;

 

Create edition

 

--use sys account

create edition version2 as child of ora$base;  

--demo account is edition-enabled,  meaning, it can change objects within the edition

alter user demo enable editions;   

grant use on edition version2 to demo;

--make user scott able to use version2

grant use on edition version2 to scott;

Create procedures in the base edition

 

--use demo account

set serveroutput on

        

create or replace procedure my_procedure

     as

       begin

           dbms_output.put_line

          ( 'I am version 1.0' );

        end;

       /

create or replace procedure my_procedure2

        as

        begin

           my_procedure;

        end;

        /

Change my_procedure in version2

--use demo account

--switch to version 2

alter session set edition = version2;    

            

create or replace procedure my_procedure

        as

        begin

           dbms_output.put_line

           ( 'I am version 2.0' );

        end;

        /

Now we are in the state of the diagram:

 

We can check out that my procedure2 works in both editions:

SQL> alter session set edition=version2;

Session altered.

SQL> exec my_procedure2();

I am version 2.0

SQL> alter session set edition = ORA$Base;

Session altered.

SQL> exec my_procedure2();

I am version 1.0

--check the current edition

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') AS edition FROM dual;

EDITION

-----------

ORA$BASE

What scott can see?

 

Recall scott can use version2 thanks to this statement:

--make user scott able to use version2

grant use on edition version2 to scott;

let us first ask user demo to grant scott the privilege to use my_procedure2:

 

--use demo account

--switch to version 2

alter session set edition = version2;

grant execute on my_procedure2 to scott;

After this change, the status now becomes:

 

MY_PROCEDURE2 has a different version in version2 now. You can check this out with via user_objects, to check out all user objects, use user_objects_AE:

SQL> select object_name, edition_name from user_objects where object_name like 'MY_%';

OBJECT_NAME   EDITION_NAME

------------------------------

MY_PROCEDURE   VERSION2

MY_PROCEDURE2  VERSION2

SQL> select object_name, edition_name from user_objects_AE where object_name lik

e 'MY_%';

OBJECT_NAME   EDITION_NAME

------------------------------

MY_PROCEDURE ORA$BASE

MY_PROCEDURE VERSION2

MY_PROCEDURE2 ORA$BASE

MY_PROCEDURE2 VERSION2

 

Let us log on as scott:

 

sqlplus scott/scott

SQL> set serveroutput on;

--scott is not able to see demo.my_procedure2();in base version

SQL> exec demo.my_procedure2();

BEGIN demo.my_procedure2(); END;

      *

ERROR at line 1:

ORA-06550: line 1, column 7:

PLS-00201: identifier 'DEMO.MY_PROCEDURE2' must be declared

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

SQL> alter session set edition = version2;

Session altered.

--scott is able to see my_procedure2 in version2

SQL> exec demo.my_procedure2();

I am version 2.0

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)

But what about tables?