Oracle DBMS_EDITIONS_UTILITIES
Version 12.2.0.1

General Information
Library Note Morgan's Library Page Header
"I spent my entire adult life looking out for the well-being, the training, the equipping of the troops for whom I was responsible. I will not be lectured about what our military needs by a five-deferment draft dodger,"
~ Sen. Tammy Duckworth
Purpose Utilities to enhance working with databases making use of Edition Based Redefinition.
AUTHID CURRENT_USER
Dependencies
ALL_ALL_TABLES KU$_HTABLE_VIEW KU$_P2TPARTCOL_VIEW
ALL_EDITIONING_VIEWS_AE KU$_IOTABLE_VIEW KU$_PARTITION_VIEW
ALL_OBJECTS KU$_M_VIEW_FH_VIEW KU$_PCOLUMN_VIEW
ALL_VIEWS_AE KU$_M_VIEW_H_VIEW KU$_PFHTABLE_VIEW
DBMS_ASSERT KU$_M_VIEW_IOT_VIEW KU$_PHTABLE_VIEW
DBMS_EDITIONS_UTILITIES_LIB KU$_M_VIEW_LOG_FH_VIEW KU$_PIOTABLE_VIEW
DBMS_OUTPUT KU$_M_VIEW_LOG_H_VIEW KU$_PRIM_COLUMN_VIEW
DBMS_PRIV_CAPTURE KU$_M_VIEW_LOG_PFH_VIEW KU$_QTAB_STORAGE_VIEW
DBMS_SQL KU$_M_VIEW_LOG_PH_VIEW KU$_QUEUE_TABLE_VIEW
DBMS_STANDARD KU$_M_VIEW_PFH_VIEW KU$_SP2TCOLUMN_VIEW
KU$_10_1_FHTABLE_VIEW KU$_M_VIEW_PH_VIEW KU$_SP2TPARTCOL_VIEW
KU$_10_1_HTABLE_VIEW KU$_M_VIEW_PIOT_VIEW KU$_SUBPARTITION_VIEW
KU$_10_1_IOTABLE_VIEW KU$_M_VIEW_VIEW KU$_VIEW_VIEW
KU$_10_1_PFHTABLE_VIEW KU$_M_ZONEMAP_FH_VIEW KU$_ZM_VIEW_FH_VIEW
KU$_10_1_PHTABLE_VIEW KU$_M_ZONEMAP_H_VIEW KU$_ZM_VIEW_H_VIEW
KU$_10_1_PIOTABLE_VIEW KU$_M_ZONEMAP_IOT_VIEW KU$_ZM_VIEW_IOT_VIEW
KU$_10_2_FHTABLE_VIEW KU$_M_ZONEMAP_PFH_VIEW KU$_ZM_VIEW_PFH_VIEW
KU$_11_2_VIEW_VIEW KU$_M_ZONEMAP_PH_VIEW KU$_ZM_VIEW_PH_VIEW
KU$_ACPTABLE_VIEW KU$_M_ZONEMAP_PIOT_VIEW KU$_ZM_VIEW_PIOT_VIEW
KU$_CLUSTER_VIEW KU$_NT_PARENT_VIEW USABLE_EDITIONS
KU$_COLUMN_VIEW KU$_P2TCOLUMN_VIEW USER_SYS_PRIVS
KU$_FHTABLE_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
Compares two given editions to determine their parent/child relation dbms_editions_utilities.compare_edition(
ed1objn IN NUMBER,
ed2objn IN NUMBER)
RETURN NUMBER;
conn sys@pdbdev 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
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
What's New In 12cR1
What's New In 12cR2

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-2017 Daniel A. Morgan All Rights Reserved