8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Edition-Based Redefinition Enhancements in Oracle Database 12c Release 1 (12.1)
Oracle 11gR2 introduced Edition-Based Redefinition. Editionable object types include:
- FUNCTION
- LIBRARY
- PACKAGE and PACKAGE BODY
- PROCEDURE
- TRIGGER
- TYPE and TYPE BODY
- SYNONYM
- VIEW
In Oracle 11gR2, if editioning was enabled for a user and you created a new object that was an editionable object in that user's schema, the object you created was automatically editionable. It was not possible to create non-editionable objects of one of these types in an edition enabled schema.
Oracle 12c allows you to create non-editionable objects of these editionable types in an edition enabled schema.
Related articles.
CREATE object
Create a test user with editioning enabled.
CONN sys@pdb1 AS SYSDBA CREATE USER edition_test1 IDENTIFIED BY edition_test1 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE TO edition_test1; ALTER USER edition_test1 ENABLE EDITIONS;
The CREATE [OR REPLACE]
statement for editionable object types has been amended to allow the editionable status of the object to be controlled explicitly.
The following code creates three procedures. Notice that EDITIONABLE=Y
is the default when creating an editionable object.
CONN edition_test1/edition_test1@pdb1 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN NULL; END; / CREATE OR REPLACE EDITIONABLE PROCEDURE proc2 AS BEGIN NULL; END; / CREATE OR REPLACE NONEDITIONABLE PROCEDURE proc3 AS BEGIN NULL; END; / COLUMN object_name FORMAT A20 COLUMN editionable FORMAT A15 SELECT object_name, editionable FROM user_objects ORDER BY object_name; OBJECT_NAME EDITIONABLE -------------------- --------------- PROC1 Y PROC2 Y PROC3 N 3 rows selected. SQL>
ALTER object
The ALTER
statement for editionable object types have be amended to allow their editionable status to be changed. This is only possible prior to editioning being enabled on the user. Once editioning has been enabled, the editionable status of the object is fixed.
Create a test user that does not have editioning enabled.
CONN sys@pdb1 AS SYSDBA CREATE USER edition_test2 IDENTIFIED BY edition_test2 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; GRANT CREATE SESSION, CREATE PROCEDURE TO edition_test2;
Create the three test procedures in this schema.
CONN edition_test2/edition_test2@pdb1 CREATE OR REPLACE PROCEDURE proc1 AS BEGIN NULL; END; / CREATE OR REPLACE EDITIONABLE PROCEDURE proc2 AS BEGIN NULL; END; / CREATE OR REPLACE NONEDITIONABLE PROCEDURE proc3 AS BEGIN NULL; END; / COLUMN object_name FORMAT A20 COLUMN editionable FORMAT A15 SELECT object_name, editionable FROM user_objects ORDER BY object_name; OBJECT_NAME EDITIONABLE -------------------- --------------- PROC1 Y PROC2 Y PROC3 N 3 rows selected. SQL>
Switch the editionable status of prod2
and proc3
.
ALTER PROCEDURE proc2 NONEDITIONABLE; ALTER PROCEDURE proc3 EDITIONABLE; COLUMN object_name FORMAT A20 COLUMN editionable FORMAT A15 SELECT object_name, editionable FROM user_objects ORDER BY object_name; OBJECT_NAME EDITIONABLE -------------------- --------------- PROC1 Y PROC2 N PROC3 Y 3 rows selected. SQL>
Enable editioning for the user.
CONN sys@pdb1 AS SYSDBA ALTER USER edition_test2 ENABLE EDITIONS;
Trying to amend the editionable status of the objects now results in an error.
SQL> CONN edition_test2/edition_test2@pdb1 Connected. SQL> ALTER PROCEDURE proc2 EDITIONABLE; ALTER PROCEDURE proc2 EDITIONABLE * ERROR at line 1: ORA-38825: The EDITIONABLE property of an editioned object cannot be altered. SQL> ALTER PROCEDURE proc3 NONEDITIONABLE; ALTER PROCEDURE proc3 NONEDITIONABLE * ERROR at line 1: ORA-38825: The EDITIONABLE property of an editioned object cannot be altered. SQL>
For more information see:
Hope this helps. Regards Tim...