| 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@pdbdev
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@pdbdev
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@pdbdev
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@pdbdev
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 sys@pdbdev 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@pdbdev
sho edition
conn uwclass/uwclass@pdbdev
sho edition
conn sys@pdbdev as sysdba
ALTER DATABASE DEFAULT EDITION = ORA$BASE;
sho edition
conn sys@pdbdev 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 sys@pdbdev 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. |
|
