Proposed wrapper for APEX_UTIL.set_session_state
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.
ADDENDUM:
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 p_commit to 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.
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.