Oracle context issues in APEX. Solving user overlapping problem

As it is well known, contexts offer a great possibility for a user to apply fine-grained access control, as the applications can be associated with different function-based security policies. Application contexts allow flexible, parameter-based access control using attributes of interest to an application.

Therefore, contexts are important if a developer wants to base predicates on context values, set and access user attributes, use context values within attributes as bind values.

Being ORACLE objects, the contexts can be used in APEX as well. If you have a view that filters the records based on a context, you can display the query results if in the APEX page that queries the view you set the context first, with a value that you ask.

This article describes the way contexts work in APEX and the way they should be used in a multi-user application and how the user overlapping issues can be solved.

For example, let’s consider a product modeling application, where a user enter can access the application by using a user account and a password, in order to choose a product and  different options for the product that they want to buy. If you want to view different statistics regarding the sales for a given period or for a certain product, then the best way to do this is by creating a view that has a context call in it. Therefore in the APEX page that a user accesses the start and end dates of the period that the statistics are displayed for need to be set, as well as the product. After setting these values and the corresponding contexts (in APEX processes) the view will display the requested info. The user sets the context and views the information according to the set value. But this is a static case when the user sets the value of the needed context to display the needed information. And for the case when information is needed for display in the same page where the context is set, APEX does exactly what is needed.

However there is another situation where contexts might generate problems. In the same modeling application let’s consider the pages where the user has to fill in the information for the chosen product. After choosing the product an order id should be set for data that the user will enter while modeling the chosen product. Normally a call should be made to set a context with the current order id. After filling in information to order the product the user might see that the product has different settings than the ones that he set. This is because he always sees the information about the current order that is always set by setting a context value. Therefore this means that the context was changed. Who could have done this? Answer: Any other user that starts modeling another product order. When another user starts modeling another product order he changes the context to its own order. Therefore in the most-likely case that multiple users use the application simultaneously all of them will see the same data that the last user set, because contexts are not session-dependant. This is obviously wrong and it represents an APEX minus.

The solution for this inconvenience is to connect the current order id to the current user session id. APEX always sets a different value for each different access to the application. An order is always defined during the current session of work, therefore all that is needed would be to set the value if the current session id, instead of the order id. How can this solve the problem as it seems to generate the same problem, only that now we are not dealing with the context problem on the order id, but on the session id? If you ask yourselves that then you actually understood the way contexts work in an APEX page.

The answer is that the problem would remain if you defined the context in the APEX page. The session id context must not be set in the APEX page, but in another place, that is in the Shared Components-Security Attributes area. The Virtual Private Database area that represents the calls to security contexts should contain among other context calls something like this:


that sets the context of the session id to the value of the user application session value. CTX_APEX_PROC is a procedure that was created like this:

create or replace procedure ctx_apex_proc( p_name in varchar2, p_val in varchar2 )



   dbms_session.set_context( 'ctx_apex', p_name, p_val );



While CTX_APEX is a context created like this:

create or replace context ctx_apex using ctx_apex_proc;

After doing this the sessionid context will be set to the value of the current session on all the APEX pages and any Oracle object that is contains a call to this context will receive the right value no matter how many users are connected to the same page of the application and with as many work sessions.

Leave a Reply