Execute a step in a different edition

by Granville Bonyata on August 13, 2016

With Oracle’s EBR we have different editions for our different application releases with each edition containing a different version of your application code (packages, views, synonyms, etc). By default, all sessions will run in the Default Edition and see the objects in that edition. We have the option of changing the edition, either with:

Or if you want to do it inside PL/SQL, you can use DBMS_SESSION.set_edition_deferred (Oracle docs here). The catch is that this does not take effect until after the current PL/SQL call finishes. This is perfect for a logon trigger where you want users to point to a different edition for the duration of their session.


Neither of these options helps when you want to, within a stored procedure, execute a statement in another edition and then continue working in the same edition you started in.

To do that, we take advantage of the feature in DBMS_SQL where we can pass a parameter telling it which edition to use. This uses the “edition” parameter in dbms_sql.parse (doc).

Here is an example where you can drop a trigger from the ORA$BASE edition:


l_cursor       NUMBER := dbms_sql.open_cursor();
l_sql          VARCHAR2(4000);
l_edition      VARCHAR2(30) := 'ORA$BASE';


   l_sql := 'DROP TRIGGER scott.test_trogger';

   dbms_sql.parse(c => l_cursor,
                  statement => l_sql,
                  language_flag => dbms_sql.native,
                  edition => l_edition);


Note that we don’t need to execute the statement. When using dbms_sql for DDL, the parse call also execute the statement.


Previous post:

Next post: