Wednesday, 7 December 2016

Edition-Based Redefinition Enhancements in Oracle Database 12c

  • 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.
  • 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>