Replacing PL/SQL in a busy database is risky: sessions using the old code fail or wait while it changes. Edition-based redefinition lets two versions of the same code exist at once, in different editions. Sessions keep using the old edition while the new one is installed and tested, then switch over.
Code for This Guide
The main examples are in the examples/pkg-metadata folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
alter user schema_name enable editions; create edition new_edition as child of parent_edition; alter session set edition = new_edition; -- create or replace the changed code here alter database default edition = new_edition; -- switch everyone, when ready
Every database starts with the edition ORA$BASE. Enabling editions for a user cannot be undone, and editionable objects include PL/SQL units, views, synonyms, and types.
Two Versions of One Function
Run this as a DBA in the pluggable database. It creates a separate schema, EBR_DEMO, so nothing in NIMBUS changes.
Example:
create user ebr_demo no authentication; -- a schema-only account
alter user ebr_demo enable editions; -- cannot be undone
grant create procedure to ebr_demo;
create or replace function ebr_demo.fare_tax (p_fare number) return number is
begin
return round(p_fare * 0.05, 2); -- release 1: 5 %
end;
/
create edition release_2 as child of ora$base;
alter session set edition = release_2;
create or replace function ebr_demo.fare_tax (p_fare number) return number is
begin
return round(p_fare * 0.07, 2); -- release 2: 7 %
end;
/
select sys_context('USERENV', 'CURRENT_EDITION_NAME') as edition,
ebr_demo.fare_tax(100) as tax from dual;
alter session set edition = ora$base;
select sys_context('USERENV', 'CURRENT_EDITION_NAME') as edition,
ebr_demo.fare_tax(100) as tax from dual;
select object_name, edition_name, status from dba_objects_ae
where owner = 'EBR_DEMO' order by edition_name;Output:
User EBR_DEMO created. User EBR_DEMO altered. Grant succeeded. Function EBR_DEMO.FARE_TAX compiled Edition RELEASE_2 created. Session altered. Function EBR_DEMO.FARE_TAX compiled EDITION TAX ____________ ______ RELEASE_2 7 Session altered. EDITION TAX ___________ ______ ORA$BASE 5 OBJECT_NAME EDITION_NAME STATUS ______________ _______________ _________ FARE_TAX ORA$BASE VALID FARE_TAX RELEASE_2 VALID
In RELEASE_2 the tax is 7 percent, and in ORA$BASE still 5, for the same function name at the same time. DBA_OBJECTS_AE lists FARE_TAX once per edition. The example drops the user and the edition afterward.
Things to Know
- Tables are not editioned; editioning views and crossedition triggers handle table changes during an upgrade.
- Sessions choose an edition when they connect, or with ALTER SESSION; services can have a default edition.
- Remove old editions when no session uses them anymore.
Related Guides
Conclusion
Edition-based redefinition keeps old and new versions of PL/SQL side by side in different editions, so applications can be upgraded online. Enable editions for the schema, create a child edition, install the new code there, and switch sessions when it is ready.
