EBR – Fix The New Data

All of the previous blog entries in this series have focused on getting the application and data structures upgraded with no downtime. Now its time to look at the data itself. There are two main areas where data requires fixing:

New Data From The Old Edition

This is the data created in the old edition (ORA$BASE in these blog entries) that is not aware of the data structure and code changes in the new edition (NEW_COL in these blog entries). The code running in ORA$BASE is happily creating records in the DEMO table but it is not putting anything the the NEW_COL column because it is not even aware it exists. This blog entry shows how to handle that.

Old Data From The Old Edition

This is the data created before the new edition was even added. I’ll explain how to handle that in the next blog entry.

Fixing The New Data

At this point one record has been created in the NEW_COL edition that puts a value in the NEW_COL column.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
         1

But the record count getting created in the ORA$BASE edition without a value is steadily increasing.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
         9

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col IS NULL;
COUNT(*)
----------
        11

We need a way to populate the NEW_COL column even though the ORA$BASE edition is not fully aware of it. This is done using a cross edition trigger as shown below.

-- ensure we are working with the ORA$BASE edition
ALTER SESSION SET EDITION = ORA$BASE;

-- create a trigger to populate the NEW_COL column
CREATE OR REPLACE TRIGGER set_new_col
BEFORE INSERT OR UPDATE ON demo
FOR EACH ROW
REVERSE CROSSEDITION
ENABLE
BEGIN
 :NEW.new_col := 1;
END;

Why did I include UPDATE in the trigger? Check the next blog entry for an explanation.

Now we’ll see the number of records with a value in NEW_COL start to increase.

SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
         7
SQL> SELECT COUNT(*)
 2     FROM demo
 3    WHERE new_col = 1;
COUNT(*)
----------
        11

Thanks for reading.