NOTE: update for APEX 5 (see below)
I decided to try using a wrapper procedure to isolate calls to APEX_UTIL.set_session_state in an autonomous transaction. I’m currently using it in a project and seeing how it goes in terms of performance.
DISCLAIMER: Don’t just throw this into your mission-critical system without at least testing it thoroughly first.
Since I had Morten Braten’s Alexandria library handy, I simply modified his APEX_UTIL_PKG. If you’re not using this library you can create your own wrapper quite simply:
create or replace procedure sv (p_name in varchar2 ,p_value in varchar2 := NULL) as PRAGMA AUTONOMOUS_TRANSACTION; begin APEX_UTIL.set_session_state (p_name => p_name ,p_value => p_value); COMMIT; end sv;
Since my system has many schemas (one for each application), I would compile this in a “common” schema and then grant execute on it to the schemas that need it, and create local synonyms in each one so that my applications just need to call sv.
As Joel Kallman rightly points out, putting set_session_state in an autonomous transaction means that the new value will not be visible to the rest of the calling code, so for example the call to v() will not return ‘Joe’ here:
sv('P1_NAME', 'Joe'); x := v('P1_NAME'); -- will not be 'Joe'
Therefore, it is intended that sv() be used as the final step in any procedure, e.g.:
PROCEDURE p1_controller IS p1_name VARCHAR2(100); BEGIN p1_name := v('P1_NAME'); sv('P1_NAME', p1_name); END;
UPDATE for APEX 5
As of Oracle APEX 5.0, APEX_UTIL.set_session_state supports a new optional parameter,
p_commit (documentation). It is defaulted to
true which preserves the old behaviour (i.e. it might or might not commit).
If you set
false, the procedure will not issue any commit. This removes the need for the autonomous transaction, and leaves the responsibility for committing to the developer; if it’s called from an APEX page process, it will be committed automatically.