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
View original post 1 more word