I was perplexed for quiet a while on how would Oracle handle TABLES in online patching as TABLE is a non-editonable object. This was a lingering question, but one I did not look into until we had a patch fail and I had to dig in to troubleshoot.
A Base Table is always defined in the product schema (say SCOTT for example). The base table (SCOTT.EMPLOYEE) is wrapped around by an Editioning View (EV). The EV will have the same name as the base table, but with a # suffixed to it (SCOTT.EMPLOYEE#). The APPS schema will have a SYNONYM with the same name as base table (APPS.EMPLOYEE), but it will point to the EV (SCOTT.EMPLOYEE#)
SCOTT.EMPLOYEE ----> SCOTT.EMPLOYEE# ---> APPS.EMPLOYEE (base_table) (editioning_view) (synonym)
When we run ADOP PREPARE phase, EBS will create a child edition in the database. Since TABLE is a non editionable object, there will be only one entity. On the other hand, an EV is an editionable object, and you will see two instance of this EV one in the run edition and other in the patch edition (child).
When we add a column to a table, the base table (SCOTT.EMPLOYEE#) gets modified. Then the AD_ZD_TABLE.UPGRADE procedure is called on the patch edition EV, which recreates the patch EV, which will now show the new columns as well. However, the run edition EV is not modified and the currently connected users will continue to see only the columns as existed before the addition.
At this point of time, if you connect to run edition and do a “select * from scott.employee#”, you will see 2 columns, while if you run the same query after connecting to patch edition, you will see 3 columns.
CREATE TABLE EMPLOYEE (id NUMBER name VARCHAR2(40) )
EV on RUN Edition
CREATE OR REPLACE EDITIONING VIEW SCOTT.EMPLOYEE# AS SELECT ID AS ID, NAME AS NAME FROM EMPLOYEE;
EV on PATCH Edition
CREATE OR REPLACE EDITIONING VIEW SCOTT.EMPLOYEE# AS SELECT ID AS ID, FIRST_NANE AS FIRST_NAME, LAST_NAME AS LAST_NAME FROM EMPLOYEE;
Forward Cross edition Triggers:
These are special triggers defined to handle the data that are entered in the run edition while an ADOP session is in progress. This trigger will have logic to compensate for the newly added column in the base table, which is not yet visible to the application and users.
Let’s assume that we are adding two columns first_name and last_name to the EMPLOYEE table such that name = first_name ||’ ‘||last_name.
The forward cross edition trigger will look like
CREATE OR REPLACE EMPLOYEE_F01 BEFORE INSERT OR UPDATE ON EMPLOYEE FOR EACH ROW FORWARD CROSSEDITION DISABLE BEGIN :NEW.first_name := TRIM(substr(:NEW.name,1,INSTR(:NEW.name,' ')-1)); :NEW.last_name := TRIM(substr(:NEW.name,INSTR(:NEW.name,' ')+1)); END EMPLOYEE_F01;
Reverse Cross Edition Triggers:
They do the exact opposite work as forward cross edition triggers do. They handle the data entered in the child edition while the ADOP session is in progress. If we had to create a reverse cross edition trigger for the above example, it would look like:
CREATE OR REPLACE EMPLOYEE_R01 BEFORE INSERT OR UPDATE ON EMPLOYEE FOR EACH ROW REVERSE CROSSEDITION DISABLE BEGIN :NEW.name := :NEW.first_name || ' ' || :NEW.last_name; END EMPLOYEE_R01;
After the patching, when we run CUTOVER, the APPS.EMPLOYEE is dropped and recreated, and it will not point to the old patch edition EV, which has the new columns visible.
The existing data can be handled in two ways. One is to write an data-fix which will comb through the data and split the name filed into two and update the first_name and last_name field. Wait, isn’t that what the forward cross edition trigger does? Absolutely, but it fires only on inserts or updates. What if we do a pseudo update forcing the trigger to fire?
UPDATE EMPOYEE SET ID = ID;