Hello,
We are on Apex 5.1.4 (I know...) running on Oracle 18c.
We have been developing an application that was going to use Global Temp Tables to segregate data for each user.
We have multiple users that may log in to the application concurrently and use the same queries to populate the Temp Tables. These queries may return different data for different users and were written into the Global Temp Table.
However, we found that using Global Temp Tables is not suitable in Apex for this purpose, because the APEX_PUBLIC_USER is tied to the same Oracle Session ID, and all users logging in go through APEX_PUBLIC_USER. So we have had instances where one user overwrote data in the GTT for another user. The blog post that told us this said that is why Apex uses Collections.
We are really trying to avoid using Apex Collections for a number of reasons, based on the architecture of our application. We have considered two options:
1. Change the GTT tables to regular tables and include the APEX Session ID to segregate data being used by individual users and periodically purge the data in these tables by deleting data from APEX sessions that are gone.
2. Another possible option is using the Internal Security Setting called ENABLE REAL APPLICATION SECURITY. If we're reading the documentation correctly, it seems to suggest that Real Application Security will assign a unique Oracle Session ID to each user's Apex Session when they login. Since Global Temp Tables are tied to the user's Oracle Session ID, this may segregate the data as we want it to be.
Is that a correct assumption? And if so, would that make the use of Global Temporary Tables valid in Apex?
Or does anyone have another suggestion?
Thanks,
Bill