Oracle DBMS_EDITIONS_UTILITIES
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Utilities to enhance working with databases making use of Edition Based Redefinition.
AUTHID CURRENT_USER
Dependencies
ALL_ALL_TABLES KU$_10_1_PHTABLE_VIEW KU$_M_VIEW_VIEW
ALL_EDITIONING_VIEWS_AE KU$_HTABLE_VIEW KU$_ZONEMAP_H_VIEW
ALL_OBJECTS KU$_M_VIEW_FH_VIEW KU$_ZONEMAP_PHVIEW
ALL_VIEWS_AE KU$_M_VIEW_H_VIEW KU$_PHTABLE_VIEW
DBMS_ASSERT KU$_M_VIEW_IOT_VIEW KU$PRIM_COLUMN_VIEW
DBMS_EDITIONS_UTILITIES_LIB KU$_M_VIEW_LOG_H_VIEW KU$_ZM_VIEW_H_VIEW
DBMS_OUTPUT KU$_M_VIEW_LOG_PH_VIEW KU$_ZM_VIEW_PH_ VIEW
DBMS_PRIV_CAPTURE KU$_M_VIEW_PFH_VIEW USABLE_EDITIONS
DBMS_SQL KU$_M_VIEW_PH_VIEW USER_SYS_PRIVS
KU$_10_1_HTABLE_VIEW KU$_M_VIEW_PIOT_VIEW  
Documented Yes
Exceptions
Error Code Reason
ORA-00942 Missing Table
ORA-38802 Edition does not exist
ORA-38803 Edition is unusable
ORA-38804 Not a legal edition name
ORA-38805 Edition is in use
ORA-38806 Edition is being altered or dropped
ORA-38807 Implementation restriction: an edition can have only one child
ORA-38808 Edition has a usable parent and a usable child
ORA-38809 Edition must be unusable
ORA-38810 Implementation restriction: can not drop an edition that has a parent and a child
ORA-38811 Need CASCADE option to drop edition that has actual objects
ORA-38812 Maximum number of editions reached
ORA-38813 editions not supported for schema <schema_name>
ORA-38814 ALTER SESSION SET EDITION must be first statement of transaction
ORA-38815 ALTER SESSION SET EDITION must be a top-level SQL statement
ORA-38816 Edition has a child that inherits objects from the edition
ORA-38817 Insufficient Privileges
ORA-54002 Only pure functions can be specified in a virtual column expression
First Available 11gR1
Security Model Owned by SYS with EXECUTE granted to PUBLIC
Source {ORACLE_HOME}/rdbms/admin/dbmsedu.sql
Subprograms
 
COMPARE_EDITION (new 12.1)
Compares two given editions to determine their parent/child relation dbms_editions_utilities.compare_edition(
conn sys@pdborcl as sysdba

CREATE EDITION 'demo$ed';

SELECT dbms_editions_utilities.compare_edition('ORA$BASE', 'DEMO$ED')
FROM dual;

DROP EDITION demo$ed;
 
SET_EDITIONING_VIEWS_READ_ONLY
Given the table name, set all the Editioning views in all editions to read-only or read write dbms_editions_utilities.set_editioning_views_read_only(
table_name IN VARCHAR2,
owner      IN VARCHAR2 DEFAULT NULL,
read_only  IN BOOLEAN  DEFAULT TRUE);
SELECT *
FROM dba_editions;

desc dba_views

SELECT view_name, editioning_view, read_only
FROM dba_views
WHERE owner = 'SH';

set long 1000000

SELECT text
FROM dba_views
WHERE owner = 'SH'
AND view_name = 'PROFITS';

exec dbms_editions_utilities.set_editioning_views_read_only('SALES', 'SH', TRUE);
 
SET_NULL_COLUMN_VALUES_TO_EXPR (new 12.1)
Replaces null values in a replacement column with the value of an expression. The expression evaluation cost is deferred to future updates and queries. dbms_editions_utilities.set_null_column_values_to_expr(
table_name  IN VARCHAR2,
column_name IN VARCHAR2,
expression  IN VARCHAR2);
conn sys@pdbdev as sysdba

ALTER USER uwclass ENABLE EDITIONS;

conn uwclass/uwclass@pdbdev

CREATE TABLE test_t (
sal        NUMBER,
comm       NUMBER,
total_comp NUMBER);

CREATE OR REPLACE EDITIONING VIEW test AS
SELECT * FROM test_t;

INSERT INTO test (sal, comm) VALUES (1, 1);
INSERT INTO test (sal, comm) VALUES (2, 2);
INSERT INTO test (sal, comm) VALUES (3, 3);
COMMIT;

DECLARE
 cNULLStr CONSTANT VARCHAR2(30) := 'SAL + COMM';
BEGIN
  dbms_editions_utilities.set_null_column_values_to_expr('TEST_T', 'TOTAL_COMP', cNULLStr);
END;
/

SELECT * FROM test;

SELECT * FROM test_t;

Related Topics
CrossEdition Triggers
Editions
Editioning Views
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved