Introduction
In this section, we are trying to explain the Handling of table data in R12.2 online patching using oracle edition based redefinition(forward cross edition triggers)
Suppose the patch has to modify the column Description from Mixed Case to Upper case
Table A has the Existing Description column has values of (red, ORANGE…)
The requirement from the patch is
Description column has upper case values of (RED, ORANGE…)
Steps did in Online Patching
•Create a new column Description#1 in the table when you patch the table
•Create patch Editioning View Maps:
RUN – Description => Description
PATCH – Description => Description#1
Now Next step is to populate Description#1 from Description# which is achieved using forward cross edition triggers
What is Forward Cross edition triggers?
(1)Forward cross edition triggers are database triggers that work across editions.
(2)Forward cross edition triggers are used to sync data as part of the online patching process. A forward cross edition 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.
(3)When the patch is applied, it syncs up the data from mixed case to upper case. Now the data changes done from patching to cutover time are populated to a new column by forward cross edition triggers
(4)They Provide the logic to synchronize and transform data between run and patch editions storage columns
(5)In summary these are used to upgrade both existing data and ongoing changes that occur while the run edition is in use.
(6)It Allows the Run Edition code to signal that a data upgrade is required and Fires in response to an insert, update of the table
(7)In this particular case it upgrades the column “Description” from mixed to upper case
Running Application still sees the column data as mixed case
Patched Application sees the column data as upper case
Trigger Definition would be like this
CREATE OR REPLACE EMPLOYEE_FET BEFORE INSERT OR UPDATE ON EMPLOYEE FOR EACH ROW FORWARD CROSSEDITION DISABLE BEGIN :NEW.description#1 := Upper(:NEW.description); END EMPLOYEE_FET;
Some points for about Forward edition triggers
- they are Crossedition triggers are temporary—you drop them after you have made the restructured tables available to all users.
- 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 or a native dynamic SQL statement
- A crossedition trigger is forward unless you specify REVERSE. (Specifying FORWARD is optional.)
Hope you understand the logic in this . Please let me know your feedback
Related Articles
how to check triggers on a table in oracle : Check out How to check Trigger status in Oracle, how to enable/disable trigger, how to find the trigger definition, how to find all the triggers
Oracle EBS Auditing : How to setup Oracle EBS Auditing, how to add tables in audit group, how to select column for auditing, audit trail update concurrent request
oracle EBS password expiration : Useful Queries on oracle EBS password expiration , How to Stop the Force Password Reset on Creation of User Account in Oracle EBS
Oracle R12.2 Architecture : This page contains Oracle R12.2 Architecture information, technology stack information, an important difference from previous releases
https://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_editions.htm#ADFNS99912
