Delete Employee Region

The button is named Delete, and its action is Submit. The idea is that a user chooses an employee from the list and clicks the Delete button to submit the page; during the submit, the APEX server will execute a process to delete that employee’s record. The following steps explain how to create this process and associate it with the button.

First, go to the Processing tab of the page designer, right-click the Processes node, and choose Create Process. Give the new process a name, such as DeleteEmp.

Third, change the When Button Pressed property in the process’s Server-side Condition section, as shown in Figure 7-6. Doing so specifies that the process will execute only when the Delete button is pressed. Otherwise, the process would execute each time a submit action occurs, regardless of how the submit occurred. This would be a problem for the Employee Data Entry page because it has four buttons, all doing a submit. You want the DeleteEmp process to execute only when the Delete button is pressed, which is the purpose of the When Button Pressed property.

Insert Employee Region

The Insert Employee region contains five items and a button. The items correspond to columns of the EMP table and have the names P17_INSERT_ENAME, P17_INSERT_JOB, and so on. Item P17_INSERT_ENAME is a text field and P17_INSERT_SAL is a number field. The other three items are select lists, whose values are defined via the following list-of-values queries.

At this point, you might want to test your page as it stands. Insert a record and make sure it appears correctly in the report. Then delete it, and watch the report return to its original state.

Update Employee Region

The Update Employee region has four items and two buttons. The items are named P17_UPDATE_EMPNO, P17_UPDATE_JOB, and so on. The select list values are defined the same as in the other regions. The buttons are named GetInfo and Update, and both have the action Submit.

Two steps are required to use this region. First, a user selects the desired employee from P17_UPDATE_EMPNO and clicks the GetInfo button. The process associated with this button populates the items P17_UPDATE_JOB, P17_UPDATE_SAL, and P17_UPDATE_DEPTNO with the corresponding values from that employee’s EMP record. The user then changes those items as desired. When the user clicks the Update button, the process associated with that button updates the record with the new values.

The critical part of the query is the clause beginning with the keyword into. This clause indicates that the query’s three output values will be placed into the three specified items. In other words, the into clause turns an SQL query into a PL/SQL statement that extracts values from the database and assigns them to items. Note that the into clause makes sense only if the SQL query returns exactly one row. A process that executes a multi-row SQL query needs to do something totally different with the query output, as you will see later in the next section.

Local Variables

A PL/SQL block can have a variable declaration section prior to the begin keyword. The declaration section starts with the keyword declare and is followed by a series of variable declarations. The code of Listing 7-5 declares four local variables: v_amount, v_saleCommission, v_prevCommission, and v_newCommission. The syntax for each declaration is similar to the syntax for declaring table columns in SQL, and the possible variable types are also the same. For example, the last three variables have the type number(7,2), which is the same type as the column Comm in the EMP table.

Local variables and item references can be used interchangeably in a PL/SQL block. The difference is that local variables are referenced as is, whereas an item reference requires a prepended colon. For example, the SQL update command in step 3 references both the local variable v_newCommission and the item P18_SOLO_SALESMAN. Because items are referenced using bind variable syntax, they are easy to spot. However, local variable references in SQL commands are hard to distinguish from column names or function calls. To avoid confusion, you should always name local variables so that people know they are variables. A common convention, which is adopted here, is to begin each local variable name with v_.

Comments

As in SQL, PL/SQL denotes comments with two consecutive minus signs. All characters following the two minus signs are ignored until the end of the line.

Assignment Statements

Assignment statements in PL/SQL use := as the assignment operator. Assignment can also be used in the declare section to assign an initial value to a local variable.

Conditional Statements

PL/SQL supports several forms of a conditional statement; the form appearing in step 1 of Listing 7-5 is good for general-purpose use. Note that the keywords act as section boundaries: the conditional expression appears between if and then, the statements to be executed when the condition is true appear between then and else, and the statements to be executed when the condition is false appear between else and end if. As usual, if there are no statements between the else and end if keywords, the else keyword can be omitted.

Lost Update Problem

One frequently encountered situation is called the lost update problem . It arises when two users update the same database record at approximately the same time, and the timing of the updates is such that the database system loses one of them. Here are two scenarios that illustrate the problem.

For the first scenario, consider the Employee Data Entry page of Figure 7-5. Assume that two users use the Update Employee region to update Allen’s record—user A wants to change the salary to 1800, and user B wants to change the job to ANALYST. If both users click the GetInfo button at approximately the same time, then they both will retrieve the same record from the EMP table, which says that Allen is a salesman who has a salary of 1600. When user A changes the salary and clicks the Update button, that record will be replaced by one saying that Allen is a salesman who has a salary of 1800. User B, however, doesn’t see the updated salary. When user B changes the job and clicks the Update button, the record will be replaced by one saying that Allen is an analyst making 1600.

This scenario is problematic, regardless of who writes first. If user A modifies first, user B will overwrite the new salary with its previous value; if user B modifies first, user A will overwrite the new job with its previous value. No matter what happens, the database system will lose one of the updates.

You can verify this scenario for yourself. Open the Employee Data Entry page in two private browser windows (using private windows keeps APEX from realizing that they both belong to you). Select the same employee in each window and click the GetInfo button. Then do an update in each window and click their Update button. Now look at the modified record in the report—you will see that your first update was lost.

For the second scenario, consider the Sales Commission page of Figure 7-7. Suppose that Allen made solo sales of $100 and $200, earning commissions of $15 and $25, respectively. Users A and B each enter one of the sales into the Solo Commission region of the page and click its Submit button concurrently. Suppose that the execution of their PL/SQL processes both hit step 2 of Listing 7-5 at roughly the same time, meaning that both processes would see a current commission of $300. User A’s process would then calculate a new commission of $315 in step 3, and user B’s process would calculate a new commission of $325. Whichever process updates last would overwrite the commission of the process updating first. The overwritten update would be lost.

This statement causes the process to sleep for 10 seconds before beginning step 3. Now repeat the experiment. If you click the two Submit buttons within 10 seconds of each other, the problem scenario will occur and the first commission you submitted will be lost.

In both of these scenarios, the occurrence of a lost update depends entirely on the timing of the user requests. This dependence on timing means that lost updates tend to occur at seemingly random times. For example, it is quite possible for an application to run for months (or years) without a lost update and then have several in short succession.

Lost updates are clearly a very bad thing. A lost update might not be detected until long after it occurred, at which point it might be too late. The application developer must ensure that they never occur. Their unpredictable nature makes it hard to discover the problem via testing. Consequently, any PL/SQL process that updates the database must be written specifically to avoid lost updates.

Avoiding Lost Updates

Lost updates occur when two concurrent processes read the same data and then make conflicting updates to it. The way to avoid this problem is to force the conflicting processes to interact sequentially, so that the second process reads the data written by the first one.

The Oracle database system uses write locks to ensure this sequentiality. You specify write locks by adding the clause for update to an SQL query. When your process executes a query having that clause, the database system locks the query’s output records so that they cannot be read or modified by any other process. Your process can then modify these records without fear of conflict. The database system releases the locks when your process completes.

If another process tries to access this record while it is locked, the database system will delay that process until your process completes. Consequently, step 3 of your process can modify the record, secure in the knowledge that it will not overwrite the modification of another process.

Go to page 18 of your Employee Demo application and make this modification to the process for the Solo Commission region. Then use two private browser windows to verify that it works, as described in the previous section. The page should never lose an updated commission, even if you add the sleep statement before step 3 of the process.

Now consider the first scenario, in which two users concurrently update an employee record. In this scenario, the activity of updating a record requires two processes: the GetEmpInfo process reads values from a specified record, and the UpdateEmp process modifies that record. It is not sufficient for GetEmpInfo to lock its records, because the locks will be released when GetEmpInfo finishes (and therefore, before UpdateEmp begins). Instead, explicit cooperation between these two processes is needed. Here is a good strategy.

The GetEmpInfo process reads the values of the selected record, displays them on the page, and saves the original values for future reference. The UpdateEmp process rereads the values from the database and compares them with the saved values. If they match, then the process can perform the update. If not, then another process must have updated the record, which means that UpdateEmp must refuse to perform the update.

Page 19 of the Employee Demo application, named Revised Employee Data Entry, contains the implementation of this strategy. It looks the same as page 17 and has the same behavior. The only difference (for the moment) is that its GetEmpInfo and UpdateEmp processes have been revised so that they cannot lose updates.

Because this page is substantially the same as page 17, the best way to implement it is to begin with a copy of page 17. To do so, go to the page designer for any page and find the insert icon along its top row (the icon labeled +). Then choose its Page as Copy menu item as shown in Figure 7-8.

This item will bring up the APEX page copy wizard. Its first screen asks you where the to-be-copied page is; select Page in this application. The second screen asks you to specify the source and destination pages; Figure 7-9 shows how I filled it in. On the third screen, you should set up a navigation menu entry, in the usual way. The fourth screen tells you whether there were difficulties copying the page components; ignore that screen and click the Create button.

The code to abort the process requires explanation. The raise_application_error function causes APEX to abort the process and display an error message on the page. The function takes two arguments: the first argument is the error code, which can be any number between –20,000 and –20,999; the second argument is the error message.

Note that the query contains the for update clause. The process needs to lock the specified EMP record until it can execute the update statement, to ensure that the record cannot be modified in the meantime.

You can test this code as you did in the previous section, by running the page from two private browser windows. In each window, select the same employee and click the GetInfo button. Then modify the item values in each and click the Update buttons. The second update operation will not work; instead, APEX will display the error message shown in Figure 7-10.

Using a Hash Function

Although this revised code works, the technique does not scale well. The need to save and compare all the original values is tedious and will become more so as the number of saved values increases. An easier approach is to combine all the original values into a single value; you can then save and compare that one value. The preferred technique is to use a hash function , which transforms a collection of values into a single fixed-length value, called a checksum . A hash function also obfuscates its input, which makes it useful for encoding user passwords—as will be seen in Chapter 13.

The first line of the code declares a variable v_vals of type apex_t_varchar2 and assigns it the collection that gets created by the type’s constructor function (which is also called apex_t_varchar2). The arguments to the constructor function are the values 'CLERK' and 20. The second line of the code declares a variable v_result of type varchar2(1000) and assigns it the result of the apex_util.get_hash function. In other words, the variable v_result will contain the hash of the values ['CLERK', 20].

Although this code does what you want, it is somewhat clunky. This code will be revisited at the end of Chapter 8, where you will see how to improve it using validations.

Submitting via Items

Recall that items of certain types (i.e., radio groups, select lists, text fields, and password fields) can be configured to perform a submit action. Such items should be able to have an associated process. The problem is that the When Button Pressed property doesn’t give you the option of picking an item.

The solution to this conundrum is to forego the When Button Pressed property in the Server-side Condition section and instead use the property below it named Type (refer back to Figure 7-6). That property lets you explicitly specify a condition for when the process should execute. Its options are the same as for conditional rendering, described in Chapter 6.

For example, let’s change the Update Employee region of the Revised Employee Data Entry page so that it doesn’t use the GetInfo button. Figure 7-12 shows what the new region looks like. Now, simply choosing an employee from the select list is sufficient to cause the job, salary, and department of the selected employee to appear automatically.

The way to implement this behavior is to first modify the select list so that it submits the page when changed and then modify the GetEmpInfo process to execute when that submit occurs. For the first task, modify the action of select list P19_UPDATE_EMPNO to be Submit Page, as shown in Figure 7-13.

For the second task, change the Server-side Condition properties of the GetEmpInfo process as shown in Figure 7-14. Note that its SQL expression makes use of the APEX variable REQUEST, which holds the name of the item that performed the submit action. Because the expression is an SQL expression, it references the variable using bind variable notation. The expression compares the variable value to the string 'P19_UPDATE_EMPNO' (without a colon) because the expression is interested in the name of that item, not the value it holds.

When you are done, you can delete the GetInfo button from the page.

Multiple-Use Processes

Another situation that cannot make use of the When Button Pressed property is when a process is applicable to more than one button. This section contains two examples related to the Revised Employee Data Entry page.

For the first example, suppose that you want to log all changes made to the EMP table. In particular, each click of the Delete, Insert, or Update button should cause an appropriate record to be inserted into a table named EMPLOG.

This code refers to the built-in variables APP_USER and REQUEST, both of which you have seen before. The variable APP_USER holds the name of the logged in user, and REQUEST holds the name of the button or item that performed the submit action. Thus, the LogChanges process inserts a record into the EMPLOG table containing the source of the submit action, the user who performed it, and the date it occurred.

This process needs to execute each time the Insert, Delete, or Update button is clicked, but not when a value is chosen from the item P19_UPDATE_EMPNO. Clearly, the When Button Pressed property is of no use here because it lets you choose only a single button.

The solution is to set the server-side condition of the process to be the SQL expression shown in Figure 7-15. This expression ensures that the process will fire only when the button named Insert, Delete, or Update is clicked. Because each of these buttons also has its own dedicated process, clicking any of these buttons will cause two processes to execute. The processes will execute in order of their sequence number.

You might be thinking that you don’t need a separate process to do the logging; instead, you could append the logging code to each of the button-specific processes for these buttons. This strategy works, but is not advisable. The problem is that doing so would duplicate the logging code, which means that if you want to change the way the logging works (such as by adding a new column to the EMPLOG table or changing the RequestDate column to hold the time as well as the date), you will need to update it in three places. Moreover, if you decide to add a new button to the page, you must remember to extract this logging code from an existing button and add it to the process for the new button. It is much more elegant and practical to have a separate process that can be called by multiple buttons.

A second example of a process that cannot make use of the When Button Pressed property involves the sending of email. In particular, suppose that you want the Revised Employee Data Entry page to send an email to the database administrator when it detects a suspicious modification. Let’s define a suspicious modification as a deletion or update that occurs outside of normal 9 a.m. to 5 p.m. working hours. To implement this feature, you will need to do two things: create a process (call it SendSuspiciousEmail) to send the email, and specify a condition for when it will fire.

This code should execute when the Delete or Update button is pressed and the current time (as determined by the current_timestamp function) is earlier than 9 a.m. or later than 5 p.m. Figure 7-16 shows the SQL expression for this condition.

Executing a Process Without a Submit

You should set the type of the EmpNo column to be Link. To configure this link, click the column’s Target property to bring up the Link Builder wizard screen. Figure 7-18 shows the screen as I filled it in. It specifies that when a link is chosen, APEX will set an item named P20_EMPNO to the selected employee number and redirect to the same page. For this to be meaningful, you will of course need to create P20_EMPNO as a hidden item.

Now consider the Emp Info region . It should have four items and three buttons. The items are named P20_ENAME, P20_JOB, P20_SAL, and P20_DEPTNO. They all have the type Display Only so that they cannot be modified. The buttons are named ReportView, Previous, and Next. ReportView is positioned Above Region, and Previous and Next are positioned Below Region; all three buttons are aligned left.

The Previous and Next buttons use the same icons as the buttons used in the single row mode of an interactive report. I chose to use them in this region just to illustrate how it’s done. Figure 7-19 shows the Appearance properties for the Previous button.

To specify that the button should be displayed as an icon, you set the value of the Button Template property to Icon and the value of the Icon property to the name of the desired icon file. You can get this file name by clicking the quick-pick symbol to the right of the Icon property and choosing from the large number of icons displayed. Clicking the Template Options property lets you customize the button’s appearance. In particular, setting the Style property to Remove UI Decoration displays the icon without the rectangle that typically outlines the button. The property settings for the Next button are the same as for the Previous button, except that the icon is a right-facing arrow.

Although this process has the correct code, it has the wrong execution point. The Processing execution point means that the process executes when a submit action occurs. But the Employees region does not perform a submit; instead, it invokes a link, and links perform a redirect. What you need is for the process to execute after the redirection occurs—that is, during page rendering.

Click the node for FetchRow in the page designer and look at its Execution Options section. The Point property specifies the execution point of the process. Figure 7-20 shows you the possible values.

If a process has the execution point New Session, it will execute only when a session begins. The execution points from Before Header to After Footer denote the six stages of page rendering; a process assigned to one of these points will execute during the specified rendering stage. The execution points After Submit and Processing are two stages of a submit action. You assign the process to the After Submit point if you want it to execute before the validation stage and to Processing if you want it to execute after validation.

Although these options can be useful for advanced applications, the differences between many of the execution points are subtle and in most cases irrelevant. For our purposes, the only interesting distinction is between processes that execute during submit and those that execute during rendering. In particular, all processes in this book have the execution point After Header when they execute during page rendering, and Processing when they execute during submit.

Turn your attention back to the FetchRow process, and change its execution point to After Header. You should observe that the node for the process will vanish from the processing tree and reappear in the rendering tree, as shown in Figure 7-21. This makes sense, because the rendering tree contains everything that occurs during page rendering, which includes processes. This figure also shows that the After Header execution point is a pre-rendering point —that is, it occurs before the regions are rendered. This is important, as it ensures that FetchRow will assign values to the page items before they are rendered.

FetchRow will get called each time the page is rendered. But it doesn’t make sense for it to go to the database unless the user has selected a row—that is, it should execute its SQL statement only when P20_EMPNO is non-null. That is the purpose of the if-statement in Listing 7-15. (An equally good equivalent solution is to remove the if-statement and change the Server-side Condition properties of the process so that it executes only when P20_EMPNO is not null.)

Instead of creating FetchRow as a submit process and then changing its execution point, it is easier to create it directly as a rendering process. You simply need to right-click the After Header node in the rendering tree and select Create Process.

Moving Between Regions

At this point in your application, you should verify that clicking a link on the Employees report will correctly place the values for the selected employee into the items of Emp Info. The next issue is to work out a plan for how the regions can “take turns” rendering themselves.

The key idea is to realize that a non-null value for P20_EMPNO indicates that a row has been selected. So to “un-select” a row, it follows that you should put a null value into P20_EMPNO. The two regions can thus use the value of that item to determine their conditional rendering—the Employees region should be visible when the item value is null, and the Emp Info region should be visible when the value is not null. In other words, the value of P20_EMPNO determines whether the page is in report mode or single row mode.

This reasoning also leads to a natural way to implement the ReportView button. It should simply redirect to the same page, setting P20_EMPNO to null. Make these changes, and test your page again. Clicking a row of the report should display the Emp Info region for that employee, and clicking the Report View button should display the report again.

Finding Previous and Next Rows

The final issue is how to implement the Previous and Next buttons so that clicking them causes the region to display the previous (or next) row of the report. This section considers two different ways to implement this functionality. The first approach is easier to understand, but it has some limitations. The second approach is a bit harder to understand but is much more elegant.

Approach 2: A Unified Previous/Next Process

Approach 1 suffers from the fact the Previous and Next buttons don’t have any visible effect when they get to the first or last record; to the user, they seem to have stopped working. An alternative idea is to hide the buttons when they are not applicable. To do this, the page needs to “read ahead.” That is, in addition to keeping track of the current employee number, the page also needs to keep track of the previous and next values. This way, it can hide the Previous button if the previous value is null and similarly for the Next button.

To implement this approach, create the hidden items P20_PREV and P20_NEXT to hold the previous and next employee numbers. Then, whenever a record is selected, a process can “read ahead” and assign the appropriate values to these items. This process, called FindPreviousNext, appears in Listing 7-18.

begin

  if :P20_EMPNO is not null then

    select PrevEmp, NextEmp

    into :P20_PREV, :P20_NEXT

    from (select EmpNo, lag(EmpNo)  over (order by EName) as PrevEmp,

                        lead(EmpNo) over (order by EName) as NextEmp

          from EMP)

    where EmpNo = :P20_EMPNO;

  end if;

end;

Listing 7-18

PL/SQL Code to Find Previous and Next Values

This code is similar to the code in Listing 7-17, but differs in that it is not specific to any particular button. In fact, it must execute whenever a row gets selected, regardless of whether the selection is via a link from the report or via one of the Previous or Next buttons. In other words, the FindPreviousNext process should execute during page rendering, just like the FetchRow process.

As a consequence, the Previous and Next buttons can be configured much more simply. Consider the Previous button (the Next button is similar). Because P20_PREV holds the EmpNo of the previous row, you can configure the button to redirect when clicked, placing the value of P20_PREV into P20_EMPNO. See Figure 7-22.

You can also use P20_PREV and P20_NEXT to customize when the Previous and Next buttons get displayed. In particular, the Previous button should be displayed when P20_PREV is not null, and the Next button should be displayed when P20_NEXT is not null. These conditions can be expressed using conditional rendering. For example, Figure 7-23 shows the Server-side Condition section for the Previous button.

To summarize, approach 2 implements previous/next functionality as follows. You first create hidden items P20_PREV and P20_NEXT. You then create the process FindPreviousNext, which calculates the value of these two items using the code of Listing 7-18 and executes during page rendering.

Finally, you configure the Previous and Next buttons to redirect to the current page, assigning a value to P20_EMPNO. The Previous button uses the value of P20_PREV, and the Next button uses the value of P20_NEXT. Each button uses conditional rendering, so that it displays only when its associated item value is not null.

As a result, the entire page consists of two relatively small processes: one that fetches the values of the selected row and one that calculates the employee number of the previous and next records. Both processes execute during page rendering. A user will select a row by clicking either a report link or a Previous/Next button. In each case, the page will determine the employee number of the selected record, save it to the item P20_EMPNO, and redirect, thereby causing the two processes to execute. Everything works without any submit operations.

Implementing the Report Region

The implementation of the report region is essentially the same as in the Single Row View page of Figure 7-17. One difference is that it also has a hidden item, called P21_MODE, to distinguish between the three modes. The item will have the values Report, Insert, and Update, as appropriate, with a null value indicating report mode.

Implementing the Insert Region

The CancelInsert button redirects, setting P21_MODE to Report.

Implementing the Update/Delete Region

To summarize, the Single Row Update page has four processes. Three of the processes execute during submit; their responsibilities are to run their respective insert, delete, and update SQL statements. The fourth process is responsible for running an SQL query to initialize a region’s items; it executes during page rendering.