Modeling for Edition Based Redefinition in Oracle 11g

[Former Member]

Definition : Edition-based redefinition allows to have multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time

I'm looking for any hint or tips for using PD to model for Edition Based Redefinitioning in Oracle 11G r2 and beyond.

How are you managing the editions in the model ? In our situation we maintain branches for each edition. The first branch contains basic edition. This edition contains triggers. The subsequent editions are created from the basic edition.

Tim Graham maintain views of the same name but code differ from edition to edition. How do you manage this ? These views use different columns names, tables, etc.

What do you do about cross edition triggers?

How do you manage PD models for Edition ?

Examples about Editions :

CREATE EDITION e1

CREATE OR REPLACE PROCEDURE hello IS

  BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello, edition 1.');

  END hello;

/

If we want use EDITION "e1"

ALTER SESSION SET EDITION = e1;

Invoke procedure in parent edition:

BEGIN hello(); END; / 

Result:

Hello, edition 1.  PL/SQL procedure successfully completed. 

                                                                                **********************************************

CREATE EDITION e2

CREATE OR REPLACE PROCEDURE hello IS

  BEGIN

  DBMS_OUTPUT.PUT_LINE('Hello, edition 2.');

  END hello;

/

if we want use EDITION "e2"

ALTER SESSION SET EDITION = e2;

Invoke procedure:

BEGIN hello(); END; / 

Child invokes its own copy, the actual procedure:

Hello, edition 2.  PL/SQL procedure successfully completed. 

                                                                                ************************************

In the following example we add a new column to a base table EMPLOYEE, The base table has column NAME and contain FIRST and LAST NAME as content

To switch to edition "release_v2" and test that.

Remark : Table employees definition has only column NAME

ALTER SESSION SET EDITION = release_v2;

SELECT * FROM employees;

EMPLOYEE_ID NAME DATE_OF_B POSTCODE

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

2 Peter Parker 01-JAN-10

We switch to edition "release_v3" and test that.

Remark : Table employees definition has columns FIRST_NAME and LAST_NAME

SELECT * FROM employees;

EMPLOYEE_ID FIRST_NAME LAST_NAME DATE_OF_B POSTCODE

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

  2 Peter Parker 01-JAN-10

thank you

[Former Member]

This is a tricky one....

Today, I think the best way to handle this is through branches, each branch representing the new edition.  As changes are made in "lower" editions they can be integrated to the "higher" ones, while differences made in these other editions can be maintained as separate instances.

However, a native "editions" support for SAP PowerDesigner could be interesting.  While not planned at the moment, I am interested in how others think of this capability, and how they are using it today, to see what kind of capability we could consider for a future release.

[Former Member]

Hi,

My suggestion

Given edition-based redefinition allows having multiple versions of PL/SQL objects, views and synonyms in a single schema, which makes it possible to perform upgrades of database applications with zero down time.

Edition-based redefinition allow to redefine the copied objects in isolation. The changes do not affect users of the application. One database must have at least one edition.

Rules

Created or upgraded Oracle Database starts with one edition named ora$base. The parent of the first edition created with a CREATE EDITION statement is ora$base. The statement CREATE EDITION e2 creates the edition e2 as the child of ora$base:

An editioned object is a schema object that has both an editionable type and an editions-enabled owner. An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition. All objects editionable.

• SYNONYM - Exception SYNONYM is an editionable type, a public synonym is a noneditioned object.
• VIEW

  All PL/SQL object types: 

• FUNCTION
• LIBRARY
• PACKAGE and PACKAGEBODY
• PROCEDURE
• TRIGGER
• TYPE and TYPEBODY

A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions

Example: Table

It would be a natural fit to add a new field at the model level. I would call this new property "Edition". This property would contains a List of Values (Edition names). I should be able to select one of the editions already present or create a new one.

Depending on the edition chosen by default at model level we would have access to this edition objects and all ora$base objects - not edited - and all object that have noneditionable type - as Table.

It would have when creating an new edition object that I am able to achieve an import or copy / paste from an object of a previous edition. The imported object should serve me as template, inspiration, and source.

Finally I should be able to take cognizance the previous editions éditionable objects by selecting a LOV property at object level.