Oracle Edition Based Redefinition Demo 1
Version 20c

General Information
Library Note [an error occurred while processing this directive]
Purpose The code listings on this page are designed to demonstrate basic editioning functionality rather than how the different pieces of EBR are brought together to address a business issue.

The demonstrations on this page are not intended to be fully inclusive of all available functionality: Student need to read Byrn's White Paper and review the Oracle docs to gain a comprehensive understanding. These demos are intended to illustrate specific aspects of value in gaining an understanding of this valuable technology.
 
Setup As SYS - Create Application Owner
Setup Notes The following statements, executed from an account with appropriate credentials is used to create the application schema owner, EBRADMIN, wit appropriate permissions for the demonstrations on this page. While this demo uses SYS, the user most likely to be used by students, it is recommended that a properly privileged DBA management account be used in any organization wishing to implement this technology. Privileges granted here for demonstration purposes, similarly, should not be granted in a non-training environment.
Create objects required for the four listings that follow conn sys@pdbdev as sysdba

-- view available tablespaces
SELECT tablespace_name, contents
FROM dba_tablespaces
ORDER BY 2,1;

SELECT file_name
FROM dba_data_files;

-- if a suitable tablespace does not exist then create a demo tablespace
CREATE TABLESPACE uwdata
DATAFILE 'c:\oracle\product\oradata\orabase\uwdata01.dbf' SIZE 100M
LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
DEFAULT NOCOMPRESS
SEGMENT SPACE MANAGEMENT AUTO;

-- create the application owner
CREATE USER ebradmin
IDENTIFIED BY ebradmin
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
PROFILE secdefault
QUOTA 20M ON uwdata;

-- grant system privileges
GRANT create session, create table, create procedure, create synonym, create view
TO ebradmin;
GRANT select ON dba_editions TO ebradmin;
GRANT select ON dba_objects_ae TO ebradmin;
GRANT select ON dba_source_ae TO ebradmin;
GRANT create any edition TO ebradmin;
GRANT drop any edition TO ebradmin;

-- enable editions for the application owner and user
ALTER USER ebradmin ENABLE EDITIONS;
ALTER USER uwclass ENABLE EDITIONS;

-- review data dictionary entries
SELECT username, account_status, created, editions_enabled
FROM dba_users
WHERE username IN ('EBRADMIN', 'UWCLASS');

USERNAME  ACCOUNT_STATUS CREATED              E
--------- -------------- -------------------- -
EBRADMIN  OPEN           04-MAY-2020 22:37:08 Y
UWCLASS   OPEN           25-JUN-2019 21:02:37 Y

SELECT *
FROM dba_sys_privs
WHERE grantee IN ('EBRADMIN', 'UWCLASS')
ORDER BY 1,2;

SELECT d.username, u.account_status
FROM dba_users_with_defpwd d, dba_users u
WHERE d.username = u.username
ORDER BY 2,1;
 
Edition Basics 1: CREATE and DROP EDITION
Drop Edition Notes This listing demonstrates the first step in mastering EBR. Creating an edition and navigating between editions. The string returned by the function demo_func is hardcoded to eliminate any misinterpretation of which function is visible to the user.
The listing at right demonstrates the basic syntaxes for creating and dropping editions.

Editions are cannot be altered as they have no intrinsic properties.		 conn ebradmin/ebradmin@pdbdev

-- view default edition
SELECT * FROM dba_editions;

SELECT property_value
FROM database_properties
WHERE property_name = 'DEFAULT_EDITION';

-- create and test a function under the default edition
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN sys_context('USERENV', 'CURRENT_EDITION_NAME');
END demo_func;
/

SELECT demo_func FROM dual;

-- examine data dictionary metadata
desc user_objects

SELECT object_name, object_type, edition_name
FROM user_objects;

-- create a child edition
CREATE EDITION demo_ed;
-- Oracle recommends that the "AS CHILD OF" syntax not be used at this time

SELECT * FROM dba_editions;

sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

conn sys@pdbdev as sysdba

SELECT owner, object_type, edition_name, COUNT(*)
FROM dba_objects_ae
WHERE edition_name IS NOT NULL
GROUP BY owner, object_type, edition_name;

conn ebradmin/ebradmin@pdbdev

sho edition

-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;

sho edition
SELECT sys_context('USERENV', 'CURRENT_EDITION_ID') FROM dual;
SELECT sys_context('USERENV', 'CURRENT_EDITION_NAME') FROM dual;

-- the editionable object was "copied" into the child edition
SELECT demo_func FROM dual;

-- drop the function from the child edition
DROP FUNCTION demo_func;

-- the pointer to demo_func in the child edition has been deleted
SELECT object_name, object_type, edition_name
FROM user_objects;

-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;

sho edition

-- dropping the child edition does not alter the object in the parent edition
SELECT object_name, object_type, edition_name
FROM user_objects;

-- commenting an edition
col comments format a50

SELECT * FROM all_edition_comments;

COMMENT ON EDITION demo_ed IS 'This is a demonstration edition';

SELECT * FROM all_edition_comments;

DROP EDITION demo_ed;
 
Edition Basics 2: Editioning A PL/SQL Object
PL/SQL Object Editioning notes This listing demonstrates the second step in mastering EBR. Creating an editionable object, in this case a simple function written in PL/SQL and observing how both objects exist and function in the same schema at the same time: Their independence maintained by editioning.
Segments cannot be editioned ... many code object types can be. This listing shows how to use editioning to have different objects, even different object types, with the same name defined within the same schema. conn ebradmin/ebradmin@pdbdev

-- create a child edition
CREATE EDITION demo_ed;

-- make the child edition current
ALTER SESSION SET EDITION=demo_ed;

sho edition

-- editionable object was copied into the new edition
SELECT demo_func FROM dual;

-- recreate and test a function with the same name but different return value
CREATE OR REPLACE FUNCTION demo_func RETURN VARCHAR2 AUTHID CURRENT_USER IS
BEGIN
  RETURN 'DEMO_ED';
END demo_func;
/

SELECT * FROM user_errors;

SELECT * FROM user_errors_ae;


-- test the new function
SELECT demo_func FROM dual;

-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects
ORDER BY 1;
-- user_objects shows only the current edition

-- user_objects_ae and user_source_ae show All Editions (thus the AE)
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;

col text format a60

SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;

-- drop the function in the child edition
DROP FUNCTION demo_func;

-- replace the function with a procedure of the same name and test it
CREATE OR REPLACE PROCEDURE demo_func AUTHID CURRENT_USER IS
BEGIN
  dbms_output.put_line('DEMO_ED');
END demo_func;
/

set serveroutput on

exec demo_func;

-- review the data dictionary presentation
SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1;

SELECT edition_name, text
FROM user_source_ae
WHERE name = 'DEMO_FUNC'
ORDER BY edition_name, line;

-- return to the parent edition
ALTER SESSION SET EDITION=ORA$BASE;

show edition

-- attempt to drop the edition
DROP EDITION demo_ed;

-- learn from the exception then drop the edition
DROP EDITION demo_ed CASCADE;
 
Edition Basics 3: NON-EXISTENT Objects
NON-EXISTENT Object notes It can be a bit of a shock seeing a "NON-EXISTENT" object. So see it now and understand what it is (or isn't).
Working with editions will likely be a first exposure to non-existent objects listed in the data dictionary. conn ebradmin/ebradmin@pdbdev

DROP FUNCTION demo_func;

SELECT object_name, object_type
FROM user_objects
ORDER BY 1;

SELECT object_name, object_type, edition_name
FROM user_objects_ae
ORDER BY 1,2;
 
Edition Basics 4: Edition Navigation
Edition Navigation notes In this final demo we start with a stored procedure that inserts 5 records into a table. Execute on the procedure and select on the table are granted to a second, application client, user. This demo is intended to show how the grant of EXECUTE by the application to the client is edition independent.
The value of EBR is the result of editioning one or more of the schemas within a database.

DBAs, to implement editioning, must understand edition navigation skills: how to "switch editions".		 conn ebradmin/ebradmin@pdbdev

-- create the application objects
CREATE TABLE ebr_tab (
testcol VARCHAR2(20));

CREATE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'rgan','gens') testcol
FROM ebr_tab;

CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab;

CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  FOR i IN 1 .. 5 LOOP
    INSERT INTO ebr_tab (testcol) VALUES (instr);
  END LOOP;
  COMMIT;
END test_proc;
/

-- grant privileges to the end-user
GRANT execute ON test_proc TO uwclass;
GRANT select ON ebr_tab TO uwclass;
GRANT select ON ebr_tab_view TO uwclass;
 
-- test the application
conn uwclass/uwclass

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;

conn ebradmin/ebradmin@pdbdev

-- clean up the test data
TRUNCATE TABLE ebr_tab;

-- create the child edition and the child's version of the procedure
CREATE EDITION demo_ed;

ALTER SESSION SET EDITION=demo_ed;

SELECT object_name, object_type, edition_name
FROM user_objects_ae;

CREATE OR REPLACE PROCEDURE test_proc(instr IN VARCHAR2) AUTHID DEFINER IS
BEGIN
  FOR i IN 1 .. 10 LOOP
    INSERT INTO ebr_tab (testcol) VALUES (REVERSE(instr));
  END LOOP;
  COMMIT;
END test_proc;
/

CREATE OR REPLACE VIEW ebr_tab_view AS
SELECT TRANSLATE(testcol, 'aeiou','uoiea') testcol
FROM ebr_tab;

CREATE OR REPLACE SYNONYM ebr_syn FOR ebr_tab_view;

SELECT object_name, object_type, edition_name
FROM user_objects_ae;

-- test the application

conn uwclass/uwclass@pdbdev

-- who and where are we?
sho user
sho edition

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;

ALTER SESSION SET EDITION=demo_ed;

-- learn from the exception
-- how does uwclass get access to the new edition?

-- return to the application administrator and grant the required privilege
conn ebradmin/ebradmin

GRANT use ON EDITION demo_ed TO uwclass;

-- retest the application
conn uwclass/uwclass@pdbdev

-- who and where are we?
sho user
sho edition

ALTER SESSION SET EDITION=demo_ed;

sho edition

exec ebradmin.test_proc('Morgan');

SELECT * FROM ebradmin.ebr_tab;

SELECT * FROM ebradmin.ebr_tab_view;

SELECT * FROM ebradmin.ebr_syn;

-- remove the client's access to the edition
conn ebradmin/ebradmin@pdbdev

REVOKE use ON EDITION demo_ed FROM uwclass;

DROP EDITION demo_ed CASCADE;
 
Wrap-Up
Additional EBR demo notes This concludes Demo 1. The second demo, which is linked below extends the scope to include Editioning Views.

Related Topics
Database Security
CrossEdition Triggers
DBMS_EDITIONS_UTILITIES
DBMS_PARALLEL_EXECUTE
Editioning Demo 2
Editioning Demo 3
Editioning Demo 4
Editioning Demo 5
Editioning Demo 6
Editioning Demo 7
Editioning Views
Editions
Invisible Indexes
Object Privileges
System Privileges
User
Built-in Functions
Built-in Packages
What's New In 19c
What's New In 20c