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:
ALTER SESSION SET EDITION = NEW_EDITION_NAME
/
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:
DECLARE
l_cursor NUMBER := dbms_sql.open_cursor();
l_sql VARCHAR2(4000);
l_edition VARCHAR2(30) := 'ORA$BASE';
BEGIN
l_sql := 'DROP TRIGGER scott.test_trogger';
dbms_sql.parse(c => l_cursor,
statement => l_sql,
language_flag => dbms_sql.native,
edition => l_edition);
dbms_sql.close_cursor(l_cursor);
END;
/
Note that we don’t need to execute the statement. When using dbms_sql for DDL, the parse call also execute the statement.