| 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 sys as sys@pdbdev as sysdba
GRANT create sequence TO ebradmin;
GRANT create trigger TO ebradmin;
GRANT execute ON dbms_editions_utilities TO ebradmin;
conn ebradmin/ebradmin@pdbdev
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@pdbdev
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@pdbdev
-- 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@pdbdev
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@pdbdev
-- 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@pdbdev
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@pdbdev
GRANT use ON EDITION demo_ed TO uwclass;
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;
sho user
sho edition
SELECT * FROM ebradmin.person;
exec ebradmin.person_proc;
SELECT * FROM ebradmin.person; |
conn ebradmin/ebradmin@pdbdev
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. |
|
