In a previous post I introduced a situation where a column was added to a table and view and then a few seconds later a procedure that inserted records into the view was recreated. It all sounds harmless but during that “few seconds” anyone running the procedure would get an error. In this post I’ll show the first step in avoiding that using Edition Based Redefinition (EBR). Let’s jump right into the code shall we…
-- First up we'll add a column to the table. At this point the editioning view in the ORA$BASE Edition will not see -- the new column so no associated code will be invalidated. ALTER TABLE demo ADD ( new_col NUMBER ); -- Then we'll create a brand new edition for the view and make it aware of the new column SQL> CREATE EDITION new_column; Edition created. -- change the current session to use the new edition SQL> ALTER SESSION SET EDITION = new_column; Session altered. -- create a new editioning view that will see the new column - note that the ORA$BASE edition remains -- unaware of the change SQL> CREATE OR REPLACE EDITIONING VIEW demo_view AS 2 SELECT * 3 FROM demo; View created. -- !!! note that without the editions there would be a period of badness here because the procedure -- !!! recreated below would be marked invalid -- re-create the procedure to insert into the view with the new column - this version of ths stored -- procedure is only used in the NEW_COLUMN edition. SQL> CREATE OR REPLACE PROCEDURE p AS 2 BEGIN 3 INSERT INTO demo_view 4 VALUES(systimestamp, 5 1); 6 END; 7 / Procedure created. -- check the views structure (note the new column) SQL> DESC demo_view Name Null? Type ------------------------ --------------- TS NOT NULL TIMESTAMP(6) NEW_COL NUMBER -- execute the procedure SQL> BEGIN 2 p; 3 END; 4 / PL/SQL procedure successfully completed. SQL> COMMIT; Commit complete. -- Verify the row created by the prior procedure run by checking the new column SQL> select count(*) 2 from demo 3 where new_col = 1 4 / COUNT(*) ---------- 1 1 row selected. -- check how the job is doing. Its still running in the ORA$BASE edition. -- all looks good - no failures SQL> select run_count, 2 failure_count 3 from user_scheduler_jobs 4 / RUN_COUNT FAILURE_COUNT ---------- ------------- 5549 0 1 row selected.
The view and procedure that are aware of the new column only execute in this edition (NEW_COLUMN)- thus they work fine. The view and code running in the ORA$BASE edition continues to run fine as well. At this point we have two working editions of code. But sooner or later we have to commit to the new one. In the next blog I’ll discuss options to do just that.
Thanks for reading!