Understanding Oracle APEX Session State Part 1

Session state is one of those things in APEX which can be hard to understand for beginning or sometimes even seasoned APEX developers. In this article I will explain what it is and the basic usage in APEX. In part 2 I will be showing some more advanced usage.

What is session state?

This is how the Oracle documentation explains it:

• What is Session State?
  Session state enables developers to store and retrieve values for a user as the user navigates between different application pages.

• About Session IDs A session ID is a unique number assigned to a specific user for the duration of that user's visit (session)

• What Is a Session? A session is a logical construct that establishes persistence (or stateful behavior) across page views. Each session is assigned a unique identifier. The Application Express engine uses this identifier (or session ID) to store and retrieve an application's working set of data (or session state) before and after each page view.

What does that actually mean in practice? First we have to understand there is a big difference between the data we see on our page in the browser (client-side) and the data on the database (server-side). They can potentially be completely different. Take a look at the following picture of the APEX architecture, as you can see there is a separation between browser and database.

When a user logs on to an APEX application the user is assigned a "session" on the database with its own unique ID (session with session ID as seen in the Oracle documentation). Think of it as your own data bubble on the database. Within this session the user can make his own data manipulations without touching anyone else's data. That is, until the user decides to commit his changes to the database. At that point the changes are stored in the database and becomes available to all other users.

How is session state used in APEX?

Let's say you made a nice report page to show data from a table. The report uses a query to fetch data from the table. So, when you open the page, the request is sent to the apex instance, which fetches all the metadata to create the page and runs the query to fetch the data for the report. This is then sent back to the browser and the page is rendered in the browser with all the data from the table.

But what if we want to filter that data with a value we fill in a page item? We create a page item to filter the data and use a search button with dynamic action to refresh our report. We also implement a where clause into the query and use the page item as a substitution like so:

select name
from   projects
where  status = :P1_STATUS;

We enter a value into the page item and click the search button which triggers a refresh on the report, the query will run, but now the question, how does the database "know" what value you entered in the page item? Somehow the value on your screen must be transferred to the database. The easiest solution is to set an option on our report page, which instructs the page to first submit the value in the page item to the database side (inside your session) when the request to the APEX instance is made, namely we set the region to submit the value to the database before running the query:

Now the database knows what the value of P1_STATUS is, because the item value is now in session state (your data bubble I mentioned earlier) on the database, and runs the query with that value substituted in your query.

Note that item values are automatically set to session state when you submit the whole page, for instance when you press a save button with submit action. The attribute above is especially relevant when doing a "partial page refresh", like in the example when we filter and refresh only the report without submitting the whole page.

Links and substitutes

Links in our reports work in the same way. When we want to edit a row of data in our report, we can create a form page and create a link to it:

Here we instruct the page to set the value of the page item P2_ROWID with that of the ROWID of the selected row. So this value is not saved in the database, but stored as a value inside your session, so that the database can fetch the data for the selected row. This is called stateful behavior.

But what if I want to set the value of another item on page 2 with the value of an item on page 1? Let's find out:

I create a test item on page 2 (the form) and want to fill it with the value from in entered in P1_STATUS, so I enter this in the link as well:

I run my report page and lets see what happens... I fill in "Open" in P1_STATUS and click enter which will submit the page (the value is set to session state and the query for my report uses the session value). The page is reloaded and the page filtered the data in my report as expected:

Now I click the link to open the form page:

I can see that the value from P1_STATUS is transferred to P2_TEST. So, that works.

Now let's change the value in P1_STATUS on the report page and click the link in the report again (so we do not press Enter this time).

Hmm, that didn't work as expected! Since I submitted the first value "Open" to filter the report, this value is stored in session. But when I change the value, and click the link in the report again (without submitting the report first), the value is not moved to page 2!

So why is that? The values in the report links are substituted on page load (so before rendering the page) on the database. Since the database in this case knows only the "old" session state, that is what it uses to render the page. So be aware when values are set! When you need to transfer changing values, a workaround is to create a custom link with the prepare_url function. This will dynamically create the correct link for you. But that is something for part 2 where I will explain this and other uses of session state!

That's it for part 1. Hope this helps you understanding what session state is.

Pieter van den Broek

Senior Oracle Consultant and APEX enthusiast