19 Edition-Based Redefinition

Editioned and Noneditioned Objects

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

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.

An editioned object is uniquely identified by its OBJECT_NAME, OWNER, and EDITION_NAME. A noneditioned object is uniquely identified by its OBJECT_NAME and OWNER—its EDITION_NAME is NULL. (Strictly speaking, the NAMESPACE of an object is also required to uniquely identify the object, but you can ignore this fact, because any statement that references the object implicitly or explicitly specifies its NAMESPACE.)

You can display the OBJECT_NAME, OWNER, and EDITION_NAME of an object with the static data dictionary views *_OBJECTS and *_OBJECTS_AE (described in Table 19-1).

You need not know the EDITION_NAME of an object to refer to that object (and if you do know it, you cannot specify it). The context of the reference implicitly specifies the edition. If the context is a data definition language (DDL) statement, the edition is the current edition of the session that issued the command (for information about the current edition, see "Current Edition and Session Edition"). If the context is source code, the edition is the one in which the object is actual (see "Inherited and Actual Objects").

Topics:

• Editionable and Noneditionable Schema Object Types

• Rules for Editioned Objects

• Enabling Editions for a User

ALTER USER user_name ENABLE EDITIONS FORCE;

Creating an Edition

To create an edition, use the SQL statement CREATE EDITION.

You must create the edition as the child of an existing edition. The parent of the first edition created with a CREATE EDITION statement is ora$base. This statement creates the edition e2 as the child of ora$base:

CREATE EDITION e2

(Example 19-1 and others use the preceding statement.)

At Oracle Database 11g Release 2, an edition can have at most one child.

The descendents of an edition are its child, its child's child, and so on. The ancestors of an edition are its parent, its parent's parent, and so on. The root edition has no parent, and a leaf edition has no child.

Inherited and Actual Objects

Each database session uses exactly one edition at a time. Upon creation, a child edition inherits from its parent edition all editioned objects in the database that are visible in the parent edition. Each inherited object is visible in the child edition.

An inherited object is copied on change or actualized; that is, when a user of the child edition references an inherited object in a DDL statement (other than DROP), the inherited object is copied and the DDL statement affects only the copy—the actual object. The unchanged object in the parent edition is no longer visible in the child edition.

Example 19-1 creates a procedure named hello in the edition ora$base, and then creates the edition e2 as a child of ora$base. When e2 invokes hello, it invokes the inherited procedure. Then e2 changes hello, actualizing it. The procedure hello in the edition ora$base remains unchanged, and is no longer visible in e2. Now when e2 invokes hello, it invokes the actual procedure.

Topics:

• Dropping Inherited Objects

• Actualizing Referenced Objects

Making an Edition Available to Some Users

As the creator of the edition, you automatically have the USE privilege WITH GRANT OPTION on it. To grant the USE privilege on the edition to other users, use the SQL statement GRANT USE ON EDITION. For information about the GRANT statement, see Oracle Database SQL Language Reference.

Making an Edition Available to All Users

To make an edition available to all users, either:

• Grant the USE privilege on the edition to PUBLIC:

  GRANT USE ON EDITION edition_name TO PUBLIC
  

  For information about the GRANT statement, see Oracle Database SQL Language Reference.

• Make the edition the database default edition:

  ALTER DATABASE DEFAULT EDITION = edition_name
  

  This has the side effect of granting the USE privilege on edition_name to PUBLIC.

  For information about the ALTER DATABASE statement, see Oracle Database SQL Language Reference.

Current Edition and Session Edition

Each database session uses exactly one edition at a time. The edition that a database session is using at any one time is called its current edition. When a database session begins, its current edition is its session edition, which is the edition in which it begins. If you change the session edition, the current edition changes to the same thing. However, there are situations in which the current edition and session edition differ.

Topics:

• Your Initial Session Edition

• Changing Your Session Edition

• Displaying the Names of the Current and Session Editions

• When the Current Edition Might Differ from the Session Edition

SELECT EDITION_NAME FROM ALL_EDITIONS;

ALTER SESSION SET EDITION = e2
...
ALTER SESSION SET EDITION = ora$base

SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_NAME') FROM DUAL;

SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_NAME') FROM DUAL;

Retiring an Edition

After making a new edition (an upgraded application) available to all users, you want to retire the old edition (the original application), so that no user except SYS can use the retired edition.

ALTER DATABASE DEFAULT EDITION = edition_name

To retire an edition, you must revoke the USE privilege on the edition from every grantee. To list the grantees, use this query, where :e is a placeholder for the name of the edition to be dropped:

SELECT GRANTEE, PRIVILEGE
FROM DBA_TAB_PRIVS
WHERE TABLE_NAME = :e
/

For information about the REVOKE statement, see Oracle Database SQL Language Reference.

Dropping an Edition

To drop an edition, use the DROP EDITION statement, described in Oracle Database SQL Language Reference. If the edition has actual objects, you must specify the CASCADE clause, which drops the actual objects.

If a DROP EDITION edition CASCADE statement is interrupted before finishing normally (from a power failure, for example), the static data dictionary view *_EDITIONS shows that the value of USABLE for edition is NO. The only operation that you can perform on such an unusable edition is DROP EDITION CASCADE.

You drop an edition in these situations:

• You want to roll back the application upgrade.

• (Optional) You have retired the edition.

You can drop an edition only if all of these statements are true:

• The edition is either the root edition or a leaf edition.

• If the edition is the root, it has no objects that its descendents inherit. (That is, each object inherited from the root edition was either actualized or dropped.)

• The edition is not in use (that is, it is not the current edition or session edition of a session).

• The edition is not the database default edition.

To explicitly actualize an inherited object in the child edition:

1. Make the child edition your session edition.

   For instructions, see "Changing Your Session Edition".

2. Recompile the object, using the appropriate ALTER statement with COMPILE. For a PL/SQL object, also specify REUSE SETTINGS.

   For example, this statement actualizes the procedure p1:

   ALTER PROCEDURE p1 COMPILE REUSE SETTINGS
   

   For information about the ALTER statements for PL/SQL objects, see Oracle Database PL/SQL Language Reference.

   For information about the ALTER statements for SQL objects, see Oracle Database SQL Language Reference.

Creating an Editioning View

To create an editioning view, use the SQL statement CREATE VIEW with the keyword EDITIONING. To make the editioning view read-only, specify WITH READ ONLY; to make it read-write, omit WITH READ ONLY.

If an editioning view is read-only, users of the unchanged application can see the data in the base table, but cannot change it. The base table has semi-availability. Semi-availability is acceptable for applications such as online dictionaries, which users read but do not change. Make the editioning view read-only if you do not define crossedition triggers on the base table.

If an editioning view is read-write, users of the unchanged application can both see and change the data in the base table. The base table has maximum availability. Maximum availability is required for applications such as online stores, where users submit purchase orders. If you define crossedition triggers on the base table, make the editioning view read-write.

Because an editioning view must do no more than select a subset of the columns from the base table and provide aliases for them, the CREATE VIEW statement that creates an editioning view has restrictions. Violating the restrictions causes the creation of the view to fail, even if you specify FORCE.

Partition-Extended Editioning View Names

An editioning view defined on a partitioned table can have a partition-extended name, with partition and subpartition names that refer to the partitions and subpartitions of the base table.

The data manipulation language (DML) statements that support partition-extended table names also support partition-extended editioning view names. These statements are:

• DELETE

• INSERT

• SELECT

• UPDATE

Changing the 'Write-ability' of an Editioning View

To change an existing editioning view from read-only to read-write, use the SQL statement ALTER VIEW READ WRITE. To change an existing editioning view from read-write to read-only, use the SQL statement ALTER VIEW READ ONLY.

Replacing an Editioning View

To replace an editioning view, use the SQL statement CREATE VIEW with the OR REPLACE clause and the keyword EDITIONING.

You can replace an editioning view only with another editioning view. Any triggers defined on the replaced editioning view are retained.

Dropping or Renaming the Base Table

If you drop or rename the base table on which an editioning view is defined, the editioning view is not dropped, but the editioning view and its dependents become invalid. However, any triggers defined on the editioning view remain.

Adding Indexes and Constraints to the Base Table

If your upgraded application requires new indexes or constraints, you must add them to the base table. You cannot add them to the editioning view.

If the new indexes might negatively impact the old edition (the original application), make them invisible. In the crossedition triggers that must use the new indexes, specify them in INDEX hints.

When all users are using only the upgraded application:

• If the new indexes were used only by the crossedition triggers, drop them.

• If the new indexes are helpful in the upgraded application, make them visible.

SQL Optimizer Index Hints

SQL optimizer index hints are specified in terms of the logical names of the columns participating in the index. Any SQL optimizer index hints specified on an editioning view using logical column names must be mapped to an index on the corresponding physical column in the base table.

Forward Crossedition Triggers

The DML changes that you make to the table in the post-upgrade edition are written only to new columns or new tables, never to columns that users of pre-upgrade (ancestor) editions might be reading or writing. However, if the user of an ancestor edition changes the table data, the editioning view that you see must accurately reflect these changes. This is accomplished with forward crossedition triggers.

A forward crossedition trigger defines a transform, which is a rule for transforming an old row to one or more new rows. An old row is a row of data in the pre-upgrade representation. A new row is a row of data in the post-upgrade representation. The name of the trigger refers to the trigger itself and to the transform that the trigger defines.

Reverse Crossedition Triggers

If the pre- and post-upgrade editions will be in ordinary use at the same time (hot rollover), use reverse crossedition triggers to ensure that when users of the post-upgrade edition make changes to the table data, the changes are accurately reflected in the pre-upgrade editions.

Crossedition Trigger Interaction with Editions

The most important difference between crossedition triggers and noncrossedition triggers is how they interact with editions.

In this topic, the current edition is the edition in which the triggering DML statement runs. The current edition might differ from the session edition (for details, see "When the Current Edition Might Differ from the Session Edition").

• Which Triggers Are Visible

• What Kind of Triggers Can Fire

• Firing Order

• Crossedition Trigger Execution

Creating a Crossedition Trigger

To create a crossedition trigger, you must be editions-enabled (for information about enabling editions for a user, see "Enabling Editions for a User").

Create a crossedition trigger with the SQL statement CREATE TRIGGER, observing these rules:

• A crossedition trigger must be defined on a table, not a view.

• A crossedition trigger must be a DML trigger (simple or compound).

  The DML statement in a crossedition trigger body can be either a static SQL statement (described in Oracle Database PL/SQL Language Reference) or a native dynamic SQL statement (described in Oracle Database PL/SQL Language Reference).

• A crossedition trigger is forward unless you specify REVERSE. (Specifying FORWARD is optional.)

• The FOLLOWS clause is allowed only when creating a forward crossedition trigger or a noncrossedition trigger. (The FOLLOWS clause indicates that the trigger being created is to fire after the specified triggers fire.)

• The PRECEDES clause is allowed only when creating a reverse crossedition trigger. (The PRECEDES clause indicates that the trigger being created is to fire before the specified triggers fire.)

• The triggers specified in the FOLLOWS or PRECEDES clause must exist, but need not be enabled or successfully compiled.

• Like a noncrossedition trigger, a crossedition trigger is created in the enabled state unless you specify DISABLE. (Specifying ENABLE is optional.)

  Tip:

  Create crossedition triggers in the disabled state, and enable them after you are sure that they compile successfully. If you create them in the enabled state, and they fail to compile, the failure affects users of the existing application.

• The operation in a crossedition trigger body must be idempotent (that is, performing the operation multiple times is redundant; it does not change the result).

CREATE TABLE table1 (key NUMBER, value VARCHAR2(20));

CREATE TABLE table2 (key NUMBER, value VARCHAR2(20), last_updated TIMESTAMP);
CREATE UNIQUE INDEX i2 on table2(key);

CREATE OR REPLACE TRIGGER trigger1
  BEFORE INSERT OR UPDATE ON table1
  FOR EACH ROW
  CROSSEDITION
DECLARE
  row_already_present  EXCEPTION;
  PRAGMA EXCEPTION_INIT(row_already_present, -38911);
BEGIN
  IF APPLYING_CROSSEDITION_TRIGGER THEN
    /* Trigger is running because of serendipitous change.
       Insert new row into table2 unless it is already there. */
    INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX(table2(key)) */
    INTO table2
    VALUES(:new.key, :new.value, to_date('1900-01-01', 'YYYY-MM-DD'));
  ELSE
    /* Trigger is running because you are applying transform.
       If tranform has not yet inserted new row in table2, insert new row;
       otherwise, update new row. */
    BEGIN
      INSERT /*+ CHANGE_DUPKEY_ERROR_INDEX(table2(key)) */
      INTO table2
      VALUES(:new.key, :new.value, SYSTIMESTAMP);
    EXCEPTION WHEN row_already_present THEN
      UPDATE table2
      SET value = :new.value, last_updated = SYSTIMESTAMP
      WHERE key = :new.key;
    END;
  END IF;
END;
/

Transforming Data from Pre- to Post-Upgrade Representation

After redefining the database objects that comprise the application that you are upgrading (in the new edition), you must transform the application data from its pre-upgrade representation (in the old edition) to its post-upgrade representation (in the new edition). The rules for this transformation are called transforms, and they are defined by forward crossedition triggers. (For general information about forward crossedition triggers, see "Forward Crossedition Triggers".)

Some old rows might have been transformed to new rows by serendipitous changes; that is, by changes that users of the pre-upgrade application made, which fired forward crossedition triggers. However, any rows that were not transformed by serendipitous changes are still in their pre-upgrade representation. To ensure that all old rows are transformed to new rows, you must apply the transforms that you defined on the tables that store the application data.

There are two ways to apply a transform:

• Fire the trigger that defines the transform on every row of the table, one row at a time.

• Instead of firing the trigger, run a SQL statement that does what the trigger would do, but faster, and then fire any triggers that follow that trigger.

  This second way is recommended if you have replaced an entire table or created a new table.

For either way of applying the transform, invoke either the DBMS_SQL.PARSE procedure or the subprograms in the DBMS_PARALLEL_EXECUTE package. The latter is recommended if you have a lot of data. The subprograms enable you to incrementally update the data in a large table in parallel, in two high-level steps:

1. Group sets of rows in the table into smaller chunks.

2. Apply the desired UPDATE statement to the chunks in parallel, committing each time you have finished processing a chunk.

The advantages are:

• You lock only one set of rows at a time, for a relatively short time, instead of locking the entire table.

• You do not lose work that has been done if something fails before the entire operation finishes.

For both the DBMS_SQL.PARSE procedure and the DBMS_PARALLEL_EXECUTE subprograms, the actual parameter values for apply_crossedition_trigger, fire_apply_trigger, and sql_stmt are the same:

• For apply_crossedition_trigger, specify the name of the forward crossedition trigger that defines the transform to be applied.

• To fire the trigger on every row of the table, one row at a time:

  • For the value of fire_apply_trigger, specify TRUE.

  • For sql_stmt, supply a SQL statement whose only significant effect is to select the forward crossedition trigger to be fired; for example, an UPDATE statement that sets some column to its own existing value in each row.

• To run a SQL statement that does what the trigger would do, and then fire any triggers that follow that trigger:

  • For the value of fire_apply_trigger, specify FALSE.

  • For sql_stmt, supply a SQL statement that does what the forward crossedition trigger would do, but faster—for example, a PL/SQL anonymous block that calls one or more PL/SQL subprograms.

Dropping the Crossedition Triggers

To drop a crossedition trigger, use the DROP TRIGGER statement, described in Oracle Database PL/SQL Language Reference. Alternatively, you can drop crossedition triggers by dropping the edition in which they are actual, by using the DROP EDITION statement with the CASCADE clause. For information about dropping editions, see "Dropping an Edition".

You drop crossedition triggers in these situations:

• You are rolling back the application upgrade (dropping the post-upgrade edition).

  Before dropping the post-upgrade edition, you must disable or drop any constraints on the new columns.

• You have finished the application upgrade and made the post-upgrade edition available to all users.

  When all sessions are using the post-upgrade edition, you can drop the forward crossedition triggers. However, before dropping the reverse crossedition triggers, you must disable or drop any constraints on the old columns.

To disable or drop constraints, use the ALTER TABLE statement with the DISABLE CONSTRAINT or DROP CONSTRAINT clause. For information about the ALTER TABLE statement, see Oracle Database SQL Language Reference.

Preparing Your Application to Use Editioning Views

An application that uses one or more tables must cover each table with an editioning view. An editioning view covers a table when all of these statements are true:

• Every ordinary object in the application references the table only through the editioning view. (An ordinary object is any object except an editioning view or crossedition trigger. Editioning views and crossedition triggers must reference tables.)

• Application users are granted object privileges only on the editioning view, not on the table.

• Oracle Virtual Private Database (VPD) policies are attached only to the editioning view, not to the table. (Regular auditing and fine-grained auditing (FGA) policies are attached only to the table.)

When the editioning view is actualized, a copy of the VPD policy is attached to the actualized editioning view. (A policy is uniquely identified by its name and the object to which is it attached.) If the policy function is also actualized, the copy of the policy uses the actualized policy function; otherwise, it uses the original policy function.

The static data dictionary views *_POLICIES, which describe the VPD policies, can have different results in different editions.

If an existing application does not use editioning views, prepare it to use them by following this procedure for each table that it uses:

1. Give the table a new name (so that you can give its current name to its editioning view).

   Oracle recommends choosing a new name that is related to the original name and reflects the change history. For example, if the original table name is Data, the new table name might be Data_1.

2. (Optional) Give each column of the table a new name.

   Again, Oracle recommends choosing new names that are related to the original names and reflect the change history. For example, Name and Number might be changed to Name_1 and Number_1.

   Any triggers that depend on renamed columns are now invalid. For details, see the entry for ALTER TABLE table RENAME column in Table 18-2.

3. Create the editioning view, giving it the original name of the table.

   For instructions, see "Creating an Editioning View".

   Because the editioning view has the name that the table had, objects that reference that name now reference the editioning view.

4. If triggers are defined on the table, drop them, and rerun the code that created them.

   Now the triggers that were defined on the table are defined on the editioning view.

5. If VPD policies are attached to the table, drop the policies and policy functions and rerun the code that created them.

   Now the VPD policies that were attached to the table are attached to the editioning view.

6. Revoke all object privileges on the table from all application users.

   To see which application users have which object privileges on the table, use this query:

   SELECT GRANTEE, PRIVILEGE
   FROM DBA_TAB_PRIVS
   WHERE TABLE_NAME='table_name';
   

7. For every privilege revoked in step 6, grant the same privilege on the editioning view.

8. Enable editions for users who own private synonyms that refer to the table (for instructions, see "Enabling Editions for a User") and notify those users that they must re-create those synonyms.

Procedure for Edition-Based Redefinition Using Only Editions

Use this procedure only if the type of every object that you will redefine is editionable (as defined in "Editionable and Noneditionable Schema Object Types"). Table is not an editionable type.

1. Create a new edition.

   For instructions, see "Creating an Edition".

2. Make the new edition your session edition.

   For instructions, see "Changing Your Session Edition".

3. Make the necessary changes to the editioned objects of the application.

4. Ensure that all objects are valid.

   Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

5. Check that the changes work as intended.

   If so, go to step 6.

   If not, either make further changes (return to step 3) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

6. Make the new edition (the upgraded application) available to all users.

   For instructions, see "Making an Edition Available to All Users".

7. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

   For instructions, see "Retiring an Edition".

Example 19-6 shows how to use the preceding procedure to change a very simple PL/SQL procedure.

Procedure for Edition-Based Redefinition Using Editioning Views

Use this procedure only if you will change the structure of one or more tables, and while you are doing so, other users do not need to be able to change data in those tables.

1. Create a new edition.

   For instructions, see "Creating an Edition".

2. Make the new edition your session edition.

   For instructions, see "Changing Your Session Edition".

3. In the new edition, if the editioning views are read-only, make them read-write.

   For instructions, see "Changing the 'Write-ability' of an Editioning View".

4. In every edition except the new edition, make the editioning views read-only.

5. Make the necessary changes to the objects of the application.

6. Ensure that all objects are valid.

   Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

7. Check that the changes work as intended.

   If so, go to step 8.

   If not, either make further changes (return to step 5) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

8. Make the upgraded application available to all users.

   For instructions, see "Making an Edition Available to All Users".

9. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

   For instructions, see "Retiring an Edition".

Procedure for Edition-Based Redefinition Using Crossedition Triggers

Use this procedure only if you will change the structure of one or more tables, and while you are doing so, other users must be able to change data in those tables.

1. Create a new edition.

   For instructions, see "Creating an Edition".

2. Make the new edition your session edition.

   For instructions, see "Changing Your Session Edition".

3. Make the permanent changes to the objects of the application.

   For example, add new columns to the tables and create any new permanent subprograms.

   Objects that depend on objects that you changed might now be invalid. For more information, see Table 18-2.

4. Ensure that all objects are valid.

   Query the static data dictionary *_OBJECTS_AE, which describes every actual object in the database, in every edition. If invalid objects remain, recompile them, using any UTL_RECOMP subprogram (described in Oracle Database PL/SQL Packages and Types Reference).

5. Create the temporary objects—the crossedition triggers (in the disabled state) and any subprograms that they need.

   For instructions, see "Creating a Crossedition Trigger".

   You need reverse crossedition triggers only if you do step 10, which is optional.

6. When the crossedition triggers compile successfully, enable them.

   Use the ALTER TRIGGER statement with the ENABLE option. For information about this statement, see Oracle Database PL/SQL Language Reference.

7. Wait until pending changes are either committed or rolled back.

   Use the procedure DBMS_UTILITY.WAIT_ON_PENDING_DML, described in Oracle Database PL/SQL Packages and Types Reference.

8. Apply the transforms.

   For instructions, see "Transforming Data from Pre- to Post-Upgrade Representation".

   Note:

   It is impossible to predict whether this step visits an existing row before a user of an ancestor edition updates, inserts, or deletes data from that row.

9. Check that the changes work as intended.

   If so, go to step 10.

   If not, either make further changes (return to step 3) or roll back the application upgrade (for instructions, see "Rolling Back the Application Upgrade").

10. (Optional) Grant the USE privilege on your session edition to the early users of the upgraded application.

    For instructions, see "Making an Edition Available to Some Users".

11. Make the upgraded application available to all users.

    For instructions, see "Making an Edition Available to All Users".

12. Disable or drop the constraints and then drop the crossedition triggers.

    For instructions, see "Dropping the Crossedition Triggers".

13. Retire the old edition (the original application), so that all users except SYS use only the upgraded application.

    For instructions, see "Retiring an Edition".

Rolling Back the Application Upgrade

To roll back the application upgrade:

1. Change your session edition to something other than the new edition that you created for the upgrade.

   For instructions, see "Changing Your Session Edition".

2. Drop the new edition that you created for the upgrade.

   For instructions, see "Dropping an Edition".

3. If you created new table columns during the upgrade, reclaim the space that they occupy (for instructions, see "Reclaiming Space Occupied by Unused Table Columns").

Reclaiming Space Occupied by Unused Table Columns

If you roll back an upgrade for which you created new table columns,

To reclaim the space that unused columns occupy:

1. Set the values of the unused columns to NULL.

   To avoid locking out other users while doing this operation, use the DBMS_PARALLEL_EXECUTE procedure (described in Oracle Database PL/SQL Packages and Types Reference).

2. Set the unused columns to UNUSED.

   Use the ALTER TABLE statement (described in Oracle Database SQL Language Reference) with the SET UNUSED clause (described in Oracle Database SQL Language Reference).

3. Shrink the table.

   Use the ALTER TABLE statement (described in Oracle Database SQL Language Reference) with the SHRINK SPACE clause (described in Oracle Database SQL Language Reference).

Example: Using Edition-Based Redefinition to Upgrade an Application

This example uses an edition, an editioning view, a forward crossedition trigger, and a reverse crossedition trigger.

Topics:

• Existing Application

• Preparing the Application to Use Editioning Views

• Using Edition-Based Redefinition to Upgrade the Application

SELECT * FROM Contacts
ORDER BY Name;
 

ID NAME                                            PHONE_NUMBER
---------- ----------------------------------------------- --------------------
       174 Abel, Ellen                                     011.44.1644.429267
       166 Ande, Sundar                                    011.44.1346.629268
       130 Atkinson, Mozhe                                 650.124.6234
       105 Austin, David                                   590.423.4569
       204 Baer, Hermann                                   515.123.8888
       116 Baida, Shelli                                   515.127.4563
       167 Banda, Amit                                     011.44.1346.729268
       172 Bates, Elizabeth                                011.44.1343.529268
       192 Bell, Sarah                                     650.501.1876
       151 Bernstein, David                                011.44.1344.345268
       129 Bissot, Laura                                   650.124.5234
       169 Bloom, Harrison                                 011.44.1343.829268
       185 Bull, Alexis                                    650.509.2876
       187 Cabrio, Anthony                                 650.509.4876
       148 Cambrault, Gerald                               011.44.1344.619268
       154 Cambrault, Nanette                              011.44.1344.987668
       110 Chen, John                                      515.124.4269
       ...
       120 Weiss, Matthew                                  650.123.1234
       200 Whalen, Jennifer                                515.123.4444
       149 Zlotkey, Eleni                                  011.44.1344.429018

107 rows selected.

DECLARE
  c NUMBER := DBMS_SQL.OPEN_CURSOR();
  x NUMBER;
BEGIN
  DBMS_SQL.PARSE(
    c                          => c,
    Language_Flag              => DBMS_SQL.NATIVE,
    Statement                  => 'UPDATE Contacts_Table SET ID = ID',
    Apply_Crossedition_Trigger => 'Contacts_Fwd_Xed'
  );
  x := DBMS_SQL.EXECUTE(c);
  DBMS_SQL.CLOSE_CURSOR(c);
  COMMIT;
END;
/