Using and Operating an Oracle Global Temporary Table (Magic xpa 3.x)
There are two types of Oracle Global Temporary Tables:
-
Global Temporary Tables on commit delete rows: In this scenario all the data in the Global Temporary Tables is saved until the current transaction ends. The data can be used only by the current transaction.
-
Global Temporary Tables on commit preserve rows: In this scenario all the data in the Global Temporary Tables is saved until the current session ends. The data can be used only for the current open session.
-
A Physical transaction is more relevant in Magic xpa when using Global Temporary Tables than a Deferred transaction (as discussed in the RIA section below). As long as the transaction is open, a user can access Global Temporary Tables data in subtasks that are included within the current open transaction. This is true for both types of Global Temporary Tables.
-
To access Global Temporary Tables on commit preserve rows data within another transaction, Magic xpa will have to access the data using the same connection that created the Global Temporary Tables on commit preserve rows. In this case, because of the reuse connection mechanism in Magic xpa, it's difficult to know which connection Magic xpa will use to connect to the Global Temporary Table.
-
Since RIA uses only deferred transactions, the physical transaction on the database is made only according to the end of the transaction strategy.
"Before task prefix" – The physical transaction is made when exiting the task.
"Before record prefix" – The physical transaction is made when exiting a record.
-
If you want to work with Global Temporary Tables, you must do all data manipulation within the same transaction. Therefore, all of your data manipulation in RIA needs to be made under the same cache block.
-
The reason that it's not safe to work with Global Temporary Tables on commit preserve with the same session is because Magic xpa has a connection reuse mechanism. One context can create the session, make some data manipulation and while traveling between different transactions (that exist in other tasks), another context can reuse this session, get the first context data, while the first context will get a new session and will not see its data in the Global Temporary Tables.
