Oracle Edition Based Redefinition Demo 3
Version 11.2.0.3
 
Edition Basics 10: Create Application Table And API Procedure with Editioning View
This demo is a follow on to Editioning Demo 2 and assumes that the resources and grants created in Demo 1 and objects created in Demo 2 exist. If you have not run Demo 1 already perform Step 1 from that demo.

The first step is in this demo is to truncate the person_tab table and rebuild the infrastructure just to assure ourselves that everything is the way it was during the final step of Demo 2. We will then test it and compare the results between the parent edition ORA$BASE and its leaf edition DEMO_ED.
conn ebradmin/ebradmin

sho edition

TRUNCATE TABLE person_tab;

-- recreate the ORA$BASE objects to make sure they are in place
CREATE OR REPLACE EDITIONING VIEW person AS
SELECT per_id, full_name
FROM person_tab;

CREATE OR REPLACE TRIGGER bi_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
  dbms_output.put_line('Firing BI_PERSON in edition ' || sys_context('USERENV', 'CURRENT_EDITION_NAME'));
  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;
/

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

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 the leaf edition
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

-- 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;

-- 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
  dbms_output.put_line('Firing BI_PERSON in edition ' || sys_context('USERENV', 'CURRENT_EDITION_NAME'));
  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;
/

-- 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;

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;

GRANT use ON EDITION demo_ed 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;

ALTER SESSION SET EDITION=demo_ed;

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;

SELECT * FROM ebradmin.person_tab;
 
Edition Basics 11: Create Crossedition Triggers
In Step 10, above, we rebuilt the application and tested it using the UWCLASS end user. What we did not do, as we did in Demo 2, was use an UPDATE statement to perform a one-time fix that moved the pre-upgrade data into the post-upgrade columns. Now we will build two CROSSEDITION triggers. A FORWARD trigger that will populate pre-upgrade data to the post-upgrade columns and a REVERSE to populate post-upgrade data to the pre-upgrade column.
conn ebradmin/ebradmin

sho edition

ALTER SESSION SET EDITION=demo_ed;

sho edition

CREATE OR REPLACE TRIGGER person_fwd_xed
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
FORWARD CROSSEDITION
DISABLE
BEGIN
  :NEW.first_name := TRIM(SUBSTR(:NEW.full_name,1,INSTR(:NEW.full_name,' ',1,1)));
  :NEW.last_name := TRIM(SUBSTR(:NEW.full_name,INSTR(:NEW.full_name,' ',1,1)));
  :NEW.chg_date := SYSDATE;
  dbms_output.put_line('Forward Crossedition Trigger Fired');
END person_fwd_xed;
/

CREATE OR REPLACE TRIGGER person_rev_xed
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
REVERSE CROSSEDITION
DISABLE
BEGIN
  :NEW.full_name := :NEW.first_name || ' ' || :NEW.last_name;
  dbms_output.put_line('Reverse Crossedition Trigger Fired');
END person_rev_xed;
/

desc user_triggers

col trigger_name format a15
col triggering_event format a17

SELECT trigger_name, trigger_type, triggering_event, base_object_type, action_type, crossedition
FROM user_triggers;

SELECT trigger_name, crossedition, before_statement, before_row, after_statement, after_row, fire_once
FROM user_triggers;

desc all_trigger_ordering

desc dba_objects_ae

SELECT object_name, object_type, status, namespace, edition_name
FROM user_objects_ae;

-- enable the triggers
ALTER TRIGGER person_fwd_xed ENABLE;
ALTER TRIGGER person_rev_xed ENABLE;

SELECT trigger_name, status
FROM user_triggers;

ALTER SESSION SET EDITION=ora$base;

sho edition

set serveroutput on

SELECT * FROM person_tab;

INSERT INTO person (full_name) VALUES ('Joze Senegacnik');
-- fires forward trigger

SELECT * FROM person_tab;

-- generates an important error to learn
ALTER SESSION SET EDITION=demo_ed;

COMMIT;

ALTER SESSION SET EDITION=demo_ed;

INSERT INTO person (first_name, last_name, chg_date) VALUES ('Safra', 'Catz', SYSDATE);
-- fires reverse trigger
-- note also that the table triggers, attached to the editioning view fire before the crossedition triggers


COMMIT;

SELECT * FROM person_tab;

CREATE OR REPLACE TRIGGER follows_test
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
FORWARD CROSSEDITION
FOLLOWS ebradmin.person_fwd_xed

ENABLE
BEGIN
  dbms_output.put_line('FOLLOWS_TEST');
END person_fwd_xed;
/

CREATE OR REPLACE TRIGGER precedes_test
BEFORE INSERT OR UPDATE ON person_tab
FOR EACH ROW
REVERSE CROSSEDITION
PRECEDES ebradmin.person_rev_xed

ENABLE
BEGIN
  dbms_output.put_line('PRECEDES_TEST');
END person_rev_xed;
/

col trigger_owner format a15
col trigger_name format a15

SELECT * FROM all_trigger_ordering;
 
Edition Basics 12: Application Upgrade
Time to test our application from the application user's perspective
conn uwclass/uwclass

sho edition

set serveroutput on

SELECT * FROM ebradmin.person_tab;

INSERT INTO ebradmin.person (full_name) VALUES ('Ronan Miles');

SELECT * FROM ebradmin.person;
SELECT * FROM ebradmin.person_tab;

COMMIT;

ALTER SESSION SET EDITION=demo_ed;

INSERT INTO ebradmin.person
(first_name, last_name, chg_date)
VALUES
('Debra', 'Lilley', SYSDATE);

COMMIT;

SELECT * FROM ebradmin.person;
SELECT * FROM ebradmin.person_tab;

exec ebradmin.person_proc;

SELECT * FROM ebradmin.person;
SELECT * FROM ebradmin.person_tab;
 
Edition Basics 13: Change Default Edition
Up to now ORA$BASE has been the default edition: Something we will first verify. So that new connections go directly to the leaf edition we will set it as the default and test it then set the default edition back to ORA$BASE.
conn / as sysdba

SELECT * FROM dba_editions;

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

ALTER DATABASE DEFAULT EDITION = DEMO_ED;

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

sho edition

conn ebradmin/ebradmin

sho edition

conn uwclass/uwclass

sho edition

conn / as sysdba

ALTER DATABASE DEFAULT EDITION = ORA$BASE;

sho edition

conn / as sysdba

sho edition
 
Edition Basics 14: Remove Infrastructure
Simply put ... we drop the edition and all objects in the child edition ... then drop the ebradmin user and cascade to include all owned objects.
conn / as sysdba

DROP EDITION demo_ed CASCADE;

DROP USER ebradmin CASCADE;
This concludes Demo 3. The fourth demo, which will be linked below when testing is completed, will extend the scope to include the DBMS_SQL package.
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: © 2012 Daniel A. Morgan All Rights Reserved