SYS_CONTEXT and APEX

I

I have to admit I found this the hard way when users started to complaining about weird things (in production). As a big fan of SYS_CONTEXT I used it among other things also for auditing columns (created_by, updated_by...) in procedures and triggers.

I used Application Processes, set processing point to Before header and created application contexts for specific page and user (via package). Some people even use processes on Global page (page zero). Well, that was terrible idea.

It worked fine whenever I tested it. But it didn't when users came. First I thought it was a user_id/client_id problem with DBMS_SESSION.SET_CONTEXT, but even if I matched the values in PL/SQL to values from APEX, it didn't helped. It still sometimes used values from someone else session. I couldn't find it anywhere, but I had a suspicion about sessions management. I knew that APEX sessions are not the same thing as database sessions, but I somehow expected dedicated db session for each APEX session and that's certainly not the case.

There is no guarantee that even code on your single page will be executed in same database session.

There is a better way

1. go to Shared Components – Security Attributes – Database Session
2. in Initialization PL/SQL Code set your init procedure, mine is: "sess.update_session();"
3. in Cleanup PL/SQL Code set your cleanup procedure, mine is: "sess.clear_session();"

I also do this:

1. go to Shared Components – Application Processes – Create
2. set Process Point to After Authentication
3. in Code set your after auth procedure, mine is: "sess.create_session(:APP_USER);"

You can set this up in Authentication Schemes but I like it in Application Processes more, because then it is used no matter what authentication method you use (and I am sometimes switching them).

You can read more about this in APEX documentation in Database Sessions section. And for better security you should prefix all procedures with "#OWNER#." to make sure you are running procedures from your schema.

So now I have 3 procedures.

• sess.create_session to create record in sessions table (and to create APEX session when called from SQL Developer, SQL Plus) so I know when user started his session, I can retrieve and set his items from previous session...
• sess.update_session to check few things, store current page items and to track recent activity
• sess.clear_session to cleanup at the end of APEX request

You can check current SESS package on my Git. There are some other useful functions there like: get_user_id, get_app_id, get_page_id, get_session_id... I use these functions in views, procedures, triggers, everywhere I need them and I know that I can trust the values.

For application specific contexts (like country_id, department_id, processing_date) I use Application Items (under Shared Components). I prefix them with "G_" and I access them thru APEX package.

• apex.set_item to set app items or page items
• apex.get_item to get value of app item or page item

Ofter I wrap these app specific items into app package so I don't have to hardcode names or use constants and I can also provide proper data type conversion (for number and dates).

So yeah, if you are still reading, I don't use SYS_CONTEXT in APEX anymore, at least not directly and not for application contexts.

I will write more about application contexts in the future. You can explore article from Tim Hall about VPD, which rely on contexts and article about DBMS_SESSION in the mean time.