EBR – Fix The Old Data

EBR is great for implementing application and data structures upgraded with no downtime. But what about 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.

Old Data From The Old Edition

This is the data created before the new edition was even added. This blog entry shows how to handle that.

Fixing The Old Data

The data that was created before the new edition (NEW_COL) was even introduced needs to be updated to be synchronized with new records arriving. This ensures all records will have data in the newly added column. The simplest way to do this is with DBMS_SQL using the APPLY_CROSSEDITION_TRIGGER parameter as shown below.

Remember the cross edition trigger we defined fires for updates.

We’ll run this in the ORA$BASE edition.

 2     v_curs NUMBER;
 3     v_ret_val NUMBER;
 4   BEGIN
 5     v_curs := DBMS_SQL.OPEN_CURSOR;
 6     DBMS_SQL.PARSE( c => v_curs,
 7                     language_flag => DBMS_SQL.NATIVE,
 8                     statement => 'UPDATE demo SET ts = ts where new_col is null',
 9                     apply_crossedition_trigger => 'set_new_col' );
 10    v_ret_val := DBMS_SQL.EXECUTE(v_curs);
 11    DBMS_OUTPUT.PUT_LINE('Updated ' || v_ret_val || ' Rows');
 12    DBMS_SQL.CLOSE_CURSOR(v_curs);
 13  END;
 14 /
Updated 3572 Rows
PL/SQL procedure successfully completed.

Commit complete.
 2     FROM demo
 3    WHERE new_col IS NULL;

The APPLY_CROSSEDITION_TRIGGER parameter made DBMS_SQL fire the cross edition trigger (SET_NEW_COL) for every record returned by the SQL statement provided. This gets the old data in sync with the new!

Thanks for reading.