Today I was asked to implement change detection in an Apex page. This page used a custom PL/SQL process instead of the standard Apex transaction processing. In this setup users can overwrite each others transaction. This can be fixed by calculating a checksum over the queried items and comparing that to a checksum on the same items before applying the changes. If the checksums are equal, no one else has changed these items, otherwise the current user should be warned.

I decided to create a checksum item P200_CHECKSUM at the top of the page. The page contained a dynamic form that was generated in a PL/SQL region. In order to preserve performance I added the checksum calculation to the PL/SQL region. The resulting checksum was written to :P200_CHECKSUM in session state. With PL/SQL code at the beginning of the save process the checksum was checked.

But it did not work! The checksum was calculated and I could see the value with Show session. But debugging showed that the checksum used in the save process was empty. It took me a while to figure out what was happening.

It was all about rendering order:
– at first the P200_CHECKSUM item was rendered with value null
– then the checksum was calculated and put into session state

At submit the value of P200_CHECKSUM in session state was updated with the value from the page (which was null).
This is why the value of P200_CHECKSUM was empty in the save process.

Once I understood this the solution was easy: just put the P200_CHECKSUM item in a region that is rendered after the calculation.

An alternative solution is to use a PL/SQL Dynamic Action with the P200_CHECKSUM in the Page items to return. The PL/SQL code can be null;. The DA fills the value of the page item P200_CHECKSUM with the value in session state. Drawbacks of this solution are extra network traffic and a slight delay in the setting of the value.

Experiment

The above led me to set up an experiment on session state and items rendered. The session state is set at several process points, with items showing the result on the page. Session state is set by either using bind variable notation or with apex_util.set_session_state.
The image below comprises the result after the page is run.

Several conclusions can be drawn:
– there is no difference between using bind variables and set_session_state
– from the normal Apex process points only the value set After Region is not rendered
– as expected from my experience the item set in the PL/SQL procedure is only rendered after the procedure has run
– the session state set in a display item is also available in previous items. The debug listing shows that the query for populating the display item is executed before the regions.

Happy apexing!

Source Article from http://dickdral.blogspot.com/2016/11/wrestling-with-session-state.html

Leave a Reply