Session IDs

Building session capability into a web application is problematic because browser requests are anonymous. There is no built-in way for a server to know whether two browser requests are part of the same session. APEX solves this problem by assigning an identifier to each session. A browser request indicates its session by adding the session’s ID to the request.

Recall that an APEX URL consists of the f?p= string followed by a colon-delimited string of values. The first two values are the application ID and page ID; the third value is the session ID. To see session IDs in action, log in to APEX and browse its screens. Look at the URL associated with each screen you visit. You should discover that every URL has the same session ID value. Now log out of APEX. When you log back in, APEX will assign a new session ID to the interaction. The URL of each screen you visit will now have this new session ID.

When the page loads, the URL will contain a new session ID.

Submit vs. Redirect

Suppose that you modify the value of an item on a web page, perhaps by selecting a radio button or typing into a text field. Your browser knows how to display this change directly, without having to ask the server to re-render the page. Consequently, the server will know nothing about the change you made to the item and thus cannot use the new value.

If you want your application to know about the values of the items on a page, you must first upload those values to the server. This action is called submit . The server holds a variable for each item of your application. When you submit a page, the value of each item on that page is saved in its corresponding variable. In effect, the submit action synchronizes the session state with the current page.

Recall that the action of a button is either submit or redirect. Both actions cause the APEX server to render a target page and send it to the browser. For redirect, you specify the target page as part of the redirect action. For submit, you specify the target page via a separate branch component—if no branches are specified, the current page is reloaded. Branches are discussed in Chapter 9, so for now you can assume that a submit operation always reloads the current page.

The primary difference between submit and redirect is that submit uploads the page’s item values to the server, whereas redirect does not. If a user changes the value of some items on a page and then does a redirect, those changes will be lost—even if the redirect is to the same page.

To see an example of this difference, consider the Item Sampler demo page from Chapter 5, as shown in Figure 5-2. Recall from Chapter 5 that the initial value of an item is determined by its Source properties. The default is to use the session state value if it is not null. This means that if you assign a non-null value to an item and submit, then that value will become the item’s initial value when the page is reloaded. You can verify this fact by changing some of the values on the page and clicking the Cancel button. Because that button does a redirect, your changes are lost. Now modify those values again and click the Submit button. The item values remain as chosen because the submit operation wrote those changes to the session state and then retrieved them when the page was reloaded. If you navigate to a different page and come back, the values will still be there.

Recall that when you run a page from within APEX, the developer toolbar appears on the bottom of the screen. The toolbar button labeled Session shows you the current session state. Clicking it opens a window that displays all items and their current session values. This window is very useful when debugging pages. In the example, open the session window and look at the session state. Then change some items and submit. Open the session window again, and verify that the item values have changed.

It is important to understand that specifying an initial value of an item will affect the value of the item in the browser, but will never, ever change its value in the session state. You can run some experiments on the Item Sampler page to verify this fact. Go to the property editor, set the source of the item P8_JOB to be the static expression CLERK, and have it be used Always, replacing any existing value in session state. Run the page, change the item’s value to ANALYST, and click the Submit button, checking the session state at each step. The session state value stays blank until the page is submitted, at which point it becomes ANALYST. The browser, however, will show CLERK when the page is rendered. Now go back to the property editor and change the Source Used property of the item to be Only when current value in session state is null and repeat the experiment. (Start by setting the value of the item to blank and clicking the Submit button to clear the session state.) You should observe that the session state values behave the same as before. The browser, however, will display CLERK until you submit a new value, after which it will display that value.

Using an Item to Submit a Page

The primary way to submit a page is to use a button. However, certain item types can also perform a submit. The Settings section of a text field or password field has the property Submit when Enter pressed. Turning on this property causes the item to submit the page when the user presses the Enter key.

The Settings section of a select list or radio group has the property Page Action on Selection. This property is shown in Figure 6-1 for a select list. The default value of the property is None. If you set it to Submit Page, then the page will be submitted each time a user changes the value of the item.

The figure also shows three other values for this property. These values correspond to different forms of redirect. The Redirect and Set Value option saves the value of that item in the session state and then redirects to the same page. That is, it behaves like submit, but for just one item instead of all items on the page.

The Redirect to Page and Redirect to URL options have different intents. The Redirect to Page action causes the item to act as a navigation list—its values are interpreted as page numbers, and selecting a value causes APEX to redirect to that page. For example, consider a select list having the static values of Figure 6-2 and whose action is Redirect to Page. This item displays the three options (Home, Employee Reports, Charts); choosing an option redirects to the corresponding page number.

The Redirect to URL option is similar, except that the item values are assumed to be URLs. Neither of these two options changes the session state.

Redirect Can Set Session State

Consider the redirect action of a button. Even though that action does not submit the page, it nevertheless can modify the session state.

When you set the action of a button to Redirect to Page in this Application, the Target property appears in the property editor. Clicking the property’s input box brings up the Link Builder wizard page shown in Figure 6-3.

In addition to setting the Page property to the desired target, you can use the Set Items section to set the value of as many items as you want. Enter the item name in the Name box and its desired session state value in the Value box. Each time you fill up a row, another row appears, thus allowing you to enter arbitrarily many name/value pairs.

For example, consider again the Item Sampler page of Figure 5-2. Suppose that you want the Cancel button to set the value of the items P8_EMPNO to 9999 and P8_JOB to CLERK and to clear the value in P8_SALARY. Then the Set Items section of the link builder would look like Figure 6-4.

Suppose that you want the Cancel button to clear the session state of all items in the page. APEX calls this clearing the cache and provides the Clear Cache property in the link builder to do so. For example, in Figure 6-3, typing 3,4,8 into the Clear Cache input box will clear the session state for all items on pages 3, 4, and 8. If you have also specified items in the Set Items section, APEX will assign values to those items after clearing the cache.

Note that this way of modifying the session state is totally unlike the submit action, because it does not (and cannot) use values displayed in the browser. That is, a redirect from a button can set the session state value of an item to a constant or computed value, but it cannot set it to a browser value entered by the user.

Referring to Session State Variables

Now that you know how to set the value of a session state variable, you need to know how to get a page to access that value. There are two primary techniques. If you want to reference the variable from within SQL code, prepend a colon in front of the item name. Such a reference is called a bind variable . If you want to reference an item from within an HTML expression, prepend an ampersand in front of the item name and append a period at its end. This latter reference, which is called a substitution string , was introduced in Chapter 3.

For example, consider the item P8_EMPNO on the Item Sampler page. To refer to it as a substitution string, write “&P8_EMPNO.”. To refer to it as a bind variable, write “:P8_EMPNO”.

In this book, you have seen several uses for SQL queries, such as the source of a report or chart, the initial value of an item, and the definition of the values of a list-based item. You have also seen several uses for HTML expressions, such as the title of a page or region and the label of an item. The remaining sections of this chapter give examples of how you can greatly increase the functionality of your pages by using item references within these SQL queries and HTML expressions.

Before building any of the pages in this chapter, you might want to go to the List Details page for the DeskTop Navigation Menu (as shown in Figure 4-21), and add a root entry named Session State. Then, each time you create a page, you can assign the parent of its navigation menu entry to be Session State.

Customized Reports

A customized report is a report whose contents are affected by the values of one or more items. A user enters values for some items on the page and clicks a Submit button . The page then displays a report based on those input values. In this section, you will build three customized report pages to illustrate this technique.

Filter by Job and Department Page

The first page, which is titled Filter by Job and Department , is page 9 of the demo and appears in Figure 6-5. The idea is that a user chooses values for the Job and Dept items and clicks the Submit button. The report then shows the employees who have the selected job and are in the selected department. For example, Figure 6-5 shows the clerks who work in the research department.

This page has two regions: Select Values and Matching Employees. The Select Values region is a static content region that contains two select lists, P9_JOB and P9_DEPTNO, both of whose values are defined by SQL queries. The list-of-values query for P9_JOB is as follows:

select distinct Job as DisplayVal, Job as ResultVal

from EMP

order by DisplayVal

The list-of-values query for P9_DEPTNO is as follows:

select DName, DeptNo

from DEPT

order by DName

The action of the button is Submit and its position is Edit.

The Matching Employees region is a report region. For the report to display the EMP records having the selected job and department number, its source query needs to reference the value of the two items. The following query does the trick; note how it uses bind variable syntax to refer to the two items.

select EmpNo, EName, Job, Sal, DeptNo

from EMP

where Job = :P9_JOB  and  DeptNo = :P9_DEPTNO

order by EName

You can improve the functionality of this region by allowing a user to select a null value for P9_JOB or P9_DEPTNO. The intent is that a null value for P9_JOB (or P9_DEPTNO) should mean that the query will not use that item for filtering. For example, if both items were null, the report would display all employees. To enable this functionality, revise the source query to explicitly test for null item values, as follows:

select EmpNo, EName, Job, Sal, DeptNo

from EMP

where (:P9_JOB is null  or  Job = :P9_JOB)

and   (:P9_DEPTNO is null  or  DeptNo = :P9_DEPTNO)

order by EName

I would like to point out that there was no particular need to use two regions on this page. In fact, it is easy to modify this page to use a single region. First, go to the Layout section for the item P9_JOB and change its Region property to Matching Employees. Then do the same thing for P9_DEPTNO and the Submit button. Finally, go to the Appearance section of Matching Employees and set its Item Display Position property to Above Content, so that the two items appear above the report. (You can also delete the Select Values region if you like.) Now run the page. You should discover that its behavior has not changed. That is, the decision to use one or two (or even three!) regions is based purely on aesthetics.

Filter by Possible Department Page

The second custom report page, titled Filter by Possible Department , is page 10 of the demo and appears in Figure 6-6. This page has a structure similar to the previous one. The top region is used for input—the user selects departments from a checkbox item and clicks the Submit button. The bottom region displays a report of the employees in the selected departments. For example, the ACCOUNTING and RESEARCH boxes are checked in Figure 6-6, which means that the report lists those employees in either the accounting or research departments. If no boxes are checked, the report will display no records.

The Select Some Departments region is a static content region containing a checkbox item and a button. The checkbox item is named P10_DEPTNO, its template is Hidden, and its values are defined by the query

select DName, DeptNo

from DEPT

order by DName

Recall that the value of a multi-value item is a string containing the selected checkbox values separated by colons. For example, the value of P10_DEPTNO in Figure 6-5 is 10:30. If all four departments were checked, the value would be 10:40:20:30. (The order of the selected values in the string is based on the sort order of the item’s list-of-values query, which in this case is by department name.)

The Matching Employees region is a classic report. The task is to come up with a source query for the report that will filter the records based on this item value. One approach is to test each EMP record to see if its DeptNo value is a substring of P10_DEPTNO. This approach leads to the following query, which almost works:

select EmpNo, EName, Job, Sal, DeptNo

from EMP

where InStr(:P10_DEPTNO,  DeptNo) > 0

order by EName

The InStr function tests whether the second argument is a substring of the first one. If so, it returns the character position of the match; otherwise, it returns 0. Thus, the query returns those EMP records whose DeptNo value appears somewhere in the item. This query is almost what you want, but not quite. For example, suppose that the value of the item is 10:30. The EMP records having a DeptNo value of 10 and 30 will match, and those having a value of 20 and 40 will not match. This is correct, but look at what happens if a new department is created with the department number 3. The employees in this department will match, which is incorrect.

The solution is to not only test for the DeptNo value but to also test for the colons on either side of it. You should add a colon to each end of the item value, so that all components of the value are surrounded by colons. The query thus becomes this:

select EmpNo, EName, Job, Sal, DeptNo

from EMP

where InStr(':' || :P10_DEPTNO || ':',':' || DeptNo || ':')  >  0

order by EName

Note

Many people (including me) have difficulty coming to terms with this query; if you feel the same, try working out an example. In particular, note the two different uses of the colon: the colon outside of quotes denotes a bind variable, whereas a colon inside the quotes is a delimiter character.

Filter by Salary Range Page

The third customized report page, titled Filter by Salary Range , is page 11 of the demo and appears in Figure 6-7. A user enters the minimum and maximum salary values and clicks the Submit button. The page then customizes an employees-per-job chart for those employees in the specified salary range.

This page contains a single region, which is of type chart. The region’s Item Display Position property (in its Appearance section) has the value Above Contents, which is why the two items and Submit button are located above the chart. The two items, named P11_MINSAL and P11_MAXSAL, are number fields having a Width value of 5. The button’s position is Edit.

Of course, this page customizes a chart, not a report, but the technique is exactly the same. Recall from Chapter 3 that the source query for this chart will have two output columns: a label, which here is the value of Job, and the series value, which here is the count of records for that job. To customize the chart to consider only employees in the specified salary range, simply add that restriction to the where clause of the query. The source query becomes this:

select Job, count(*) as EmpCount

from EMP

where (:P11_MINSAL is null  or  :P11_MINSAL <= Sal)

and   (:P11_MAXSAL is null  or  :P11_MAXSAL >= Sal)

group by Job

order by EmpCount

As with the Filter by Job and Department page , this query ensures that a null item value will not contribute to the filter.

It should also be clear to you that the chart’s source query would be the same if you decided to move the two items and button to a separate region. If not, try it and see.

Master-Detail Reports

A master-detail report is a set of two reports. These reports are linked, in the sense that each row of the detail report has an associated row in the master report. A user interacts with the reports by choosing one of the master rows; the detail report responds by displaying just the detail rows associated with the chosen master record.

In this section, you will build a master-detail page titled Employees by Department, which is page 12 of the Employee Demo application. Its master report contains one row for each department. Clicking a department row causes the detail report to display the list of employees in that department. Figure 6-8 shows the page after clicking the link for the accounting department.

Consider how to implement this page. The main issue is that the master report needs to let the detail report know which row was selected. The standard technique is to use a hidden item, whose value is the key of the selected row. Let’s name this item P12_DEPTNO. The master report will assign to P12_DEPTNO the DeptNo value of the selected row. The detail report will then use this value to customize itself.

The hard part is implementing the master report. You have three questions to answer: What is the source query of the report? How do you get its fourth column to display a link? And how do you assign the appropriate value to P12_DEPTNO?

Chapter 3 described how to specify a link for a report column. To review, go to the page designer and click the DEPTNO column in the rendering tree. Set the type of the column to Link. Find the Link section in the property editor (refer to Figure 3-35 in Chapter 3) and set the link text to Click for details. Clicking the Target box will take you to the Link Builder page, in which you can set the target to page 12 (the current page).

Wait a minute. A redirect to the current page doesn’t do anything, so what is the point of the link? Its real purpose is to change the session state. Recall that the Link Builder page also has a Set Items section, in which you can set the hidden item P12_DEPTNO to the selected department number. Recall from Chapter 3 that you refer to the value of column X in the current row by writing #X#. Thus, you should set P12_DEPTNO to #DEPTNO#, as shown in Figure 6-9.

Finally, you have to actually create the item P12_DEPTNO. You know that it should be a hidden type, but where should it be placed? Because the item is hidden, it doesn’t really matter which region on the page it goes in. I chose to place it in the master region because that is where its value is assigned.

Chart Drill Down

The next page, Employees by Job, is page 13 of the Employee Demo application and is shown in Figure 6-10. The page has two regions: a pie chart and an employee report. The chart gives the employee count per job. Clicking a pie segment customizes the report so that it displays the employees having that job. Figure 6-10 shows the page after clicking the MANAGER slice.

This technique of clicking a chart to see more detail is called chart drill down. Here, clicking the chart selects a category value, which is then used to customize the report.

The only remaining issue is how to specify the chart’s behavior when it is clicked. This behavior is determined by the Link properties of the chart’s series; see Figure 6-11.

The Type property has the value Link to Page in this Application. Choosing it exposes the Target property, from which you can use the Link Builder screen to specify the target page. Figure 6-12 shows that screen as it was configured for the chart. You use the same technique that you used for the master report of Figure 6-9: choose the target to be the current page, and set the value of the hidden item. The only difference is how you refer to the selected value. When that value comes from column X of a report, you write #X#. When it comes from column X of a chart, you write &X.—that is, you reference the column name as a substitution string.

Customized Titles and Labels

In Chapter 3, you saw how to use HTML tags to format a region title. In fact, you can use HTML tags to format any property whose value is displayed on the page, such as page and region titles, item labels, and button text. Moreover, these properties can also be customized by item values.

For example, looking back at the master-detail and chart drill-down pages, I can see a small problem: the detail reports do not indicate which master record (or chart slice) was selected. A good solution to this problem is to customize the title of the detail region. For example, Figure 6-13 shows the revised detail region for the Employees by Department page. Note that the region title contains the selected department name.

Similarly, Figure 6-14 shows the revised detail region for the Employees by Job page. Its title bar shows the selected job.

That's it! You're done. Note that you use the substitution string notation because the title is an HTML expression.

There is just one glitch to this expression: the page currently has no item named P12_DNAME. For this expression to make sense, you have to create the item and assign the selected department name to it. Fortunately, you already know how to do these things. First, create P12_DNAME as a hidden item. Then, go back to the master report, enter the link builder for the DeptNo column, and add a row to the Set Items property. See Figure 6-15.

Cascading Lists

The next task is to build a page that enables a user to choose an employee name from a select list and then displays a report containing information about that employee. This page, titled Cascading Lists, is page 14 of the Employee Demo application and appears in Figure 6-16. The page has two regions, each of which gives a solution to this task.

The top region has no button. Instead, the select list has the action Redirect and Set Value, which means that choosing an employee name from the select list causes the item’s value to be changed in the session state.

If you try out this region, you should discover that it is easy to scan through the list of names, due to the small number of employees. Suppose, however, that the EMP table were large, with thousands of employees to pick from. Scrolling through the select list would be awkward and time-consuming, even if the names were sorted. A common solution to this problem is to let the user choose the name in steps, via a series of select lists.

The bottom region of Figure 6-16 uses two select lists to choose an employee. The first select list displays the first letter of each employee name, and the second one displays the names of employees whose names begin with that letter.

This technique is called a cascading list, in which the display values of the second select list change according to the value selected for the first one. This technique can be extended to multiple select lists—the values of each select list except the first change according to the values chosen for the select lists previous to it. The advantage of this approach is that a user can scroll through several small select lists instead of one large one.

The two select lists in the bottom region are named P14_FIRST_LETTER and P14_EMPNO_CASCADE. They are defined as follows.

The source of the bottom report is exactly the same as the top report, except that P14_EMPNO is replaced by P14_EMPNO_CASCADE.

If you set the action of both these select lists to Redirect and Set Value, then choosing a first letter causes the list of names to be updated, and choosing a name causes the report to be updated.

The concept of a cascaded list is common enough that APEX has a specific property to handle it, called Parent Item(s), which is in the Cascading List of Values section of a select list. Figure 6-17 shows the specification for the P14_EMPNO_CASCADE list.

This figure specifies that P14_EMPNO_CASCADE depends on the value of P14_FIRST_LETTER; that is, it monitors the behavior of P14_FIRST_LETTER and refreshes itself when the value of P14_FIRST_LETTER changes. Consequently, the action of P14_FIRST_LETTER should now be None. The action of P14_EMPNO_CASCADE should be Redirect and Set Value, as before. The Cascading List of Values property enables APEX to use JavaScript and Ajax to refresh the select list without having the re-render the entire page. It is the most efficient way to implement cascading.

Conditional Rendering

Throughout your use of the app builder, you have come across numerous situations in which choosing a particular value in the property editor caused the editor to suddenly display other properties. The Link Builder page shown in Figure 6-15 is such an example—if you choose the target type URL, then the Set Items properties suddenly disappear, and if you change its value back to Page in this application, then those items reappear. What’s going on here?

Although it looks like the items are getting created and deleted on the spot, they are not; instead, those items always exist. When you are in the link builder and select the value Page in this application, APEX displays the additional items; when you select the value URL, APEX hides them. This ability to dynamically show or hide an item is called conditional rendering.

Conditional rendering can be defined for items, buttons, and regions. Each one has a Server-side Condition section in its property editor, which lets you choose a condition type from a select list. Although there are many condition types, the focus here is on the type SQL expression. The value of this type is an SQL expression that returns true/false. The value of the expression determines whether the item, button, or region is rendered.

For an example, let’s return to the Employees by Department page shown in Figure 6-8. Note that the detail report region will be empty until a value from the master report has been chosen. If you don’t want the page to display an empty report region, then you can use conditional rendering, displaying the region only when the item P12_DEPTNO is not null. The region’s conditional SQL expression is shown in Figure 6-18.

For another example, consider the Display Table page shown in Figure 6-19, which will be page 15 of the Employee Demo application. The Select Table region contains two checkboxes and a button. Clicking the button causes the checked tables to be displayed.

Implementing the Select Table region is straightforward. Its checkbox item is named P15_CHOICE, and it contains the two static values shown in Figure 6-20. The button performs a Submit action.

The page contains two other regions, both of which are of type Classic Report. The Employee Info region has the following source:

select * from EMP

• And the Department Info region has the following source:

  select * from DEPT

• Both of these regions are displayed conditionally, depending on the value of P15_CHOICE. The condition for Employee Info is the following SQL expression:

  InStr(:P15_CHOICE, 'Employee') > 0

• And the condition for Department Info is the following SQL expression:

  InStr(:P15_CHOICE, 'Department') > 0

• When the button is clicked, APEX creates a colon-delimited string containing the values of the checked boxes and saves it in the session state. When the page is then re-rendered, APEX uses the session state value to initialize P15_CHOICE and evaluates each conditional expression to determine which regions to display. Consequently, it is important that the Source properties of P15_CHOICE be specified as in Figure 6-21.

Region Display Selectors

Recall from Chapter 2 (and Figure 2-6) that the Application Properties screen lets you manage application-wide properties, such as the application logo and the location of the navigation menu. Figure 6-22 shows the top part of that screen.

Note in particular the tab bar under the title, with tabs labeled Show All, Name, Properties, and so on. This bar is a region display selector. If you click the Show All tab, the page displays all its regions; if you click any of the other tabs, the page displays only the chosen region.

You can add a region display selector to your application. Let’s build a page similar to the Display Table page of Figure 6-19, but using a region display selector instead of a radio group. This page, titled Select Region, will be page 16 of the Employee Demo application. Figure 6-23 shows the page with the Department Information tab selected.

This page consists of two classic report regions, one for each of the EMP and DEPT tables. Above it is a Region Display Selector region . To create the display selector region, create a region in the page designer and then change its type to Region Display Selector. Unlike every other region you have encountered, this region has no source; its sole purpose is to display a tab bar for the regions of the page.

The region display selector in Figure 6-23 is placed in the Content Body position with a small sequence number so that it appears first. Its template is Buttons Container, which formats the region without a title.

By default, the region display selector displays a tab for every region on the page (provided that there is more than one region to display). If you want to exclude a region, use its property Region Display Selector, which is a switch in the Advanced section. Turning the property off excludes that region from having a tab in the display selector.

Summary

The APEX server maintains data about each user session; this data is called the session state. In this chapter, you saw how the session state gets modified and examined ways that an application developer can make use of it.

The session state has a variable for each item in the application. These variable values can get modified as the result of a submit or redirect action. When a page is submitted, the values of all items on the page will be updated in the session state. When a page redirects to a target page, the redirect operation can assign values to specific items. In particular, when you configure a button, report link, or chart to redirect, the property editor will display the Link Builder wizard, which has a section for specifying session state values.

One common use of session state is to enable users to customize reports. The idea is that a user specifies some values, and those values affect which of the report records are displayed. You saw examples of several ways that a user could specify those values:

This chapter also considered additional uses of session state, such as cascading lists, in which the value chosen for one select list determines the values displayed by another, and conditional rendering , in which the value of an item determines whether a region should be visible or hidden.

Session state is an immensely important aspect of APEX. Most of the examples in the rest of the book will make use of the techniques mentioned in this chapter.