Tuesday, October 11, 2022
Sometimes you expect to see some data in your Oracle APEX application and it's not there... in this post I will describe one use case I see more often and how to solve it.
We have a report which shows all the employees (coming from the EMP table):
Clicking the pencil icon opens up the form of that employee:
We added two extra buttons:
Let's first start to see what is behind our Print button. We use the APEX Office Print (AOP) Dynamic Action to generate a document, but when opening the document it's empty.
Behind our Bug button we have a Dynamic Action that fires a process and sets the value of an item when P2_EMPNO is not empty, but for some reason the commission is not being filled in.
In both cases, we don't get the data that we expect to see...
Whenever you have an issue like this, I turn APEX debug on (see APEX developer toolbar and click Debug > App Trace), click the button again and check my debug output (Debug > View Debug).
This is what I saw in the debug output after clicking the Bug icon:
We can see that the EMPNO item is empty, so our IF statement in our code is returning false, and the commission is not being calculated.
By default, APEX puts the primary key column as a hidden item on the page. The following two settings of the item are important: Maintain Session State is set to "Per Request (Memory Only), and Session State Protection is set to "Checksum Required - Session Level".
The error in the debug output was "Access to undefined Per Request (Memory Only) variable P2_EMPNO", which doesn't allow the value of the item to be known in dynamic actions (AJAX requests). One way to solve this is to change the "Maintain Session State" to "Per Session (Disk)".
And now, on the debug output, we see the value of P2_EMPNO being filled in, and the commission is being calculated.
Also, when we Print the document, we now see that the employee is being filled in.
Yay! We now see our data! 😀
You might wonder why did this solve my issue? When session state is maintained "Per Session" the value is stored in the database and available across requests, whereas when "Per Request" is set, the value is only available during page load.
An alternative to the above solution is to define in your dynamic action the item you need the value of in "Items to Submit". But with session state protected items, depending on the use case, this might not always work, and you get a checksum error.
No comments yet, be the first one to let us know what you think of this article!