Oracle Edition Based Redefinition Demo 2
Version 11.2.0.3
 

Edition Basics 5: Create Application Table And API Procedure
This demo is a follow on to Editioning Demo 1 and assumes that the resources and grants created in Demo 1 exist. If you have not run Demo 1 already perform Step 1 from that demo.

The first step is in this demo is to create a table with attributes similar to those one might find in a production application and to grant appropriate privileges to the application user.
conn / as sysdba

GRANT create sequence TO ebradmin;
GRANT create trigger TO ebradmin;
GRANT execute ON dbms_editions_utilities TO ebradmin;

conn ebradmin/ebradmin

CREATE TABLE person (
per_id    NUMBER(5),
full_name VARCHAR2(20));

CREATE SEQUENCE per_id_seq;

CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.per_id := per_id_seq.NEXTVAL;
  ELSIF UPDATING THEN
    :NEW.per_id := per_id_seq.NEXTVAL+100;
  END IF;
END bi_person;
/

INSERT INTO person (full_name) VALUES ('Tom Kyte');
INSERT INTO person (full_name) VALUES ('Mogens Norgaard');
INSERT INTO person (full_name) VALUES ('Connor McDonald');
INSERT INTO person (full_name) VALUES ('Julian Dyke');
INSERT INTO person (full_name) VALUES ('Richard Foote');
COMMIT;

SELECT * FROM person;

CREATE OR REPLACE PROCEDURE person_proc AUTHID DEFINER IS
BEGIN
  UPDATE person
  SET per_id = per_id + 100;
  COMMIT;
END person_proc;
/

GRANT select ON person TO uwclass;
GRANT execute ON person_proc TO uwclass;

-- log on as the application user and test the application
conn uwclass/uwclass

sho user
sho edition

SELECT * FROM ebradmin.person;

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;
 
Edition Basics 6: Prepare Application For Upgrade
In preparation for an editioning upgrade rename the person table to person_tab, create the editioning view reflecting pre-upgrae, and recreate the original table-level before trigger on the editioning view.
conn ebradmin/ebradmin

-- rename the base table
RENAME person TO person_tab;

SELECT * FROM person_tab;

-- replace direct table access with an editioning view API
CREATE OR REPLACE EDITIONING VIEW person AS
SELECT per_id, full_name
FROM person_tab;

desc user_views

SELECT view_name, view_type, editioning_view, read_only
FROM user_views;

SELECT * FROM user_editioning_views;

SELECT * FROM user_editioning_views_ae;

SELECT view_name, view_column_name, table_column_name
FROM user_editioning_view_cols;

col view_name format a20
col edition_name format a12

SELECT view_name, view_column_name, table_column_name, edition_name
FROM user_editioning_view_cols_ae
ORDER BY 4,1,2;

SELECT object_name, object_type, namespace, edition_name, status
FROM user_objects_ae
ORDER BY 4,2,1;

-- due to fine grained dependency tracking the procedure should be valid: if not
ALTER PROCEDURE person_proc COMPILE REUSE SETTINGS;

-- drop and recreate the trigger with the same DDL
SELECT trigger_name, table_name FROM user_triggers;

SELECT dbms_metadata.get_ddl('TRIGGER','BI_PERSON') FROM dual;

DROP TRIGGER bi_person;

CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.per_id := per_id_seq.NEXTVAL;
  ELSIF UPDATING THEN
    :NEW.per_id := per_id_seq.NEXTVAL+100;
  END IF;
END bi_person;
/

SELECT trigger_name, table_name FROM user_triggers;

-- log on as the application user and retest the application
conn uwclass/uwclass

SELECT * FROM ebradmin.person;

-- the editioning view looks like the original table
-- test the procedure to verify the execute grant is valid

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;
 
Edition Basics 7: Application Upgrade
To upgrade the application we are going to add first_name, last_name, and chg_date columns to the table. These will be populated, for existing rows, later without application downtime using a DML statement. In the third demo crossedition triggers will be used to accomplish this real-time.
conn ebradmin/ebradmin

-- wait up to 30 seconds for existing transactions to complete
ALTER SESSION SET ddl_lock_timeout = 30;

ALTER TABLE person_tab
ADD (first_name VARCHAR2(15), last_name VARCHAR2(15), chg_date DATE);

desc person_tab

SELECT * FROM person_tab;

desc person

SELECT * FROM person;

CREATE INDEX iix_person_last_name
ON person_tab(last_name)
INVISIBLE
ONLINE;
 
Edition Basics 8: Change Edition and Create Upgrade Editionable Objects
In this step we create the new edition and the post-upgrade editioning view. This view shows the per_id and new columns but does not provide visibility to the pre-upgrade full_name column. In addition we will create a new table-level trigger attached to the post-upgrade editioning view. Finally we will recompile the stored procedure to actualize it in the post-upgrade edition.
conn ebradmin/ebradmin

sho edition

-- examine what we have
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

-- create a child edition and make it current
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

sho user
sho edition

-- examine what we have
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

desc person

-- create a new editioning view showing the way the table will look
CREATE OR REPLACE EDITIONING VIEW person AS
SELECT per_id, first_name, last_name, chg_date
FROM person_tab;

desc person

SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

-- create a new trigger in the child edition actualizing the trigger
CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    :NEW.per_id := per_id_seq.NEXTVAL;
  ELSIF UPDATING THEN
    :NEW.per_id := per_id_seq.NEXTVAL+1000;
  END IF;
END bi_person;
/

SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

-- actualize the existing procedure in demo_ed
ALTER PROCEDURE person_proc COMPILE REUSE SETTINGS;

SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 3,2;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 3,2;

-- test as the application owner
desc person_tab

SELECT * FROM person_tab;

desc person

SELECT * FROM person;

SELECT * FROM person_tab;

-- manually fill in the new columns (not necessary when using crossedition triggers)
UPDATE person_tab
SET first_name = TRIM(SUBSTR(full_name, 1, INSTR(full_name,' ',1,1))),
    last_name = TRIM(SUBSTR(full_name, INSTR(full_name,' ',1,1))),
    chg_date = SYSDATE;

SELECT * FROM person_tab;

SELECT * FROM person;

INSERT INTO person (first_name, last_name, chg_date) VALUES ('Dan','Morgan', SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Hans','Forbrich',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Jonathan','Lewis',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Bryn','Llewellyn',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Cary','Millsap',SYSDATE);
INSERT INTO person (first_name, last_name, chg_date) VALUES ('Caleb','Small',SYSDATE);
COMMIT;

SELECT * FROM person_tab;

SELECT * FROM person;
 
Edition Basics 9: Test Both Application Versions
In this final step we will test the application in pre and post upgrade editions and demonstrate the use of DBMS_EDITIONS_UTILITIES to set editioning views to READ ONLY.
conn ebradmin/ebradmin

GRANT use ON EDITION demo_ed TO uwclass;

conn uwclass/uwclass

sho user
sho edition

SELECT * FROM ebradmin.person;

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;

ALTER SESSION SET EDITION=demo_ed;

sho user
sho edition

SELECT * FROM ebradmin.person;

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;

conn ebradmin/ebradmin

SELECT view_name, table_name
FROM user_editioning_views;

SELECT view_name, editioning_view, read_only
FROM user_views;

exec dbms_editions_utilities.set_editioning_views_read_only('PERSON_TAB', 'EBRADMIN', TRUE);

SELECT view_name, editioning_view, read_only
FROM user_views;

exec dbms_editions_utilities.set_editioning_views_read_only('PERSON_TAB', 'EBRADMIN', FALSE);

SELECT view_name, editioning_view, read_only
FROM user_views;

conn / as sysdba

SELECT dbms_metadata_util.get_editionid('ORA$BASE');
SELECT dbms_metadata_util.get_editionid('DEMO_ED');

DROP EDITION demo_ed CASCADE;
This concludes Demo 2. The third demo, which will be linked below when testing is completed, will extend the scope to include Cross-Edition Triggers.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved