Web Tutorial: Oracle APEX Mailing Registration Form (Part 3/4)

We have data, and now we want to be able to update it. I hope you were paying attention to the last part of this Web Tutorial, because we will be repeating many of the steps we took previously.
We'll first define the Login process. Go to Page 1. Make sure Security section's Authentication setting is "Page Is Public". 

Next, create a Form by dragging it into the page the same way we did for Registration. Set the Table, and all the fields in MAILING_LIST should appear. We only want EMAIL and PASSWORD, so delete the rest. The fields would appear as P1_EMAIL and P1_PASSWORD by default, but in this case, because the values will carry on to other pages via a session object, let's keep it clean and just use "EMAIL" and "PASSWORD".

Add a validation for EMAIL.

This is the code we want. We're trying to make sure that there is such an email/password combination in the MAILING_LIST table. Don't forget that we need to hash the password value before comparison with the table's value.

:EMAIL in (SELECT EMAIL FROM MAILING_LIST WHERE upper(EMAIL) = upper(:EMAIL) AND PASSWORD = DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => UTL_RAW.CAST_TO_RAW (:PASSWORD)))

Add two buttons, LOGIN and REGISTER. The LOGIN button should submit the Form.

Now if the validation for EMAIL is cleared, we want the page to redirect to Page 3, which is Update. For this, we Create a Branch and do the neccessary.

The Register button should redirect to Page 2, Registration.

Let's try running this page. Try submitting with an obviously incorrect password. You should see the error message right there.

We won't try with a correct password just yet. Let's work on the Update page first. Go to Page 3. Add a Form and set it to the MAILING_LIST table. Remove both EMAIL and PASSWORD from the fields, because we won't be updating those fields in this page. Make sure that the settings for the remaining fields are the same as the Registration page.

Add a button, UPDATE. Also add a button, OPENPASSWORDMODAL. We will open with the second button later. Right now, just have the UPDATE button submit the Form. You'll notice that I dragged the UPDATE button in the Create section while I dragged the OPENPASSWORDMODAL button into the Form itself, right under the P3_DAYS field. Visually, this is correct because the OPENPASSWORDMODAL button is more like one of the fields rather than a Form control.

In the Processing tab in the left panel, Create A Process. This will run an SQL statement that will update the table. At the same time, in the Success Message setting, provide a message.

This is the code. It basically takes the values from the fields and updates the MAILING_LIST table for the single record that has the value EMAIL.

UPDATE MAILING_LIST SETFIRST_NAME = :P3_FIRST_NAME, LAST_NAME = :P3_LAST_NAME, DOB = :P3_DOB, GENDER = :P3_GENDER, DAYS = :P3_DAYS WHERE upper(EMAIL) = upper(:EMAIL)

We should also Create a Process in the Preprocessing section on the left panel.

This is the code. It uses the EMAIL value to search the database. If there's no such user, it redirects back to Login. If there is such a user, it populates this Form's fields with the correct data. Remember that this page is set to "Page Is Public"? Well, this handles the case if a user attempts to access this page without the appropriate session values. It's not perfect, but it will do.

IF (:EMAIL IS NULL) THEN     apex_util.REDIRECT_URL(1); END IF;
SELECT FIRST_NAME, LAST_NAME, DOB, GENDER, DAYS INTO :P3_FIRST_NAME, :P3_LAST_NAME, :P3_DOB, :P3_GENDER, :P3_DAYS FROM MAILING_LIST WHERE EMAIL = :EMAIL;

The code for the Update page is basically done at this point, but I want to bring you through a cool feature of Oracle APEX - Shared Components. For this, we need to go back to the main page, where we access Shared Components from the middle option in the row of big buttons.

From here, we want to go to Lists. You should have three there. It's the default setting.

We actually don't want to use these three. What we want to do is create a new List. You can do that by clicking on the green button Create, at the right side of the screen. In this window, we name the List.

Then we add in the list items. These are the links we want to provide to users of this app, which they will use as a quick navigation to other pages.

Here, we will determine what Page these links direct to. Since we've already created those pages, this will be easy.

Once that's done, you click Create List in the next panel.

We have a new List!

Now go back to the Update page, where we will access the Top Navigation region. In the bottom row, selected List from the Items tab and drag it into the region. Then change the Identification value in the left panel to "What would you like to update?".

Save! And then run the application. Now we can try logging in again, this time with a correct password. And there you can see how the Shared Component looks like. And you can see that the data is populated. The validations for Update should behave the same for Registration. Let me try changing the Days...

... success! You can see the success message on the top right corner of the screen.

And take a look at the database. Yep, updated! DAYS has been changed to 30.

Now that we've done this, it's a good time to bring out the next trick in the APEX arsenal, Modals. Remember we need a separate button to access the Change Password feature? Well, here it is. Set the OPENPASSWORDMODAL button to go to Page 8.

And now let's work on Page 8, Change Password. The following steps you will be familiar with by now...- - Drag a Form into the page. Set the table to MAILING_LIST, and all the fields should appear.
- Delete everything but PASSWORD (now known as P8_PASSWORD). Then add the CONFIRMPASSWORD field the same way you did for Registration. Validation should be the same. 
- Add a button, UPDATEPASSWORD. This will be the button used to submit the Form.
- set the page to "Page is Public".

Just as in the Update page, we should Create a Process under Preprocessing.

This is the code. Unlike the Update page, we do not populate the password field.

IF (:EMAIL IS NULL) THEN apex_util.REDIRECT_URL(1); END IF;

In the right panel, under the Appearance section, the Page Mode setting should be "Modal Dialog".

This is the process used to update.

This is the code. We basically just update the password (hashing it first) for the record that corresponds to the session's EMAIL value.

UPDATE MAILING_LIST SET password = DBMS_OBFUSCATION_TOOLKIT.MD5(INPUT => UTL_RAW.CAST_TO_RAW (:P8_PASSWORD)) WHERE upper(EMAIL) = upper(:EMAIL)

And then we set the page to redirect back to Update upon processing.

Try it now! What happens when you go to the Update page and click on Change Password? The modal opens!

Try with an incorrect match, or with all validations passed! You should see the Success Message in the top right corner.

Finally, we'll work on the Logout function. Go to Page 9, which is Logout. On the left panel, Create a Process under Pre-Rendering. On the right panel, under Settings, change Type to "Clear Current Session".

Then add some static HTML with a message. Create a button which leads back to Login page.

Try clicking on the Logout button now. There you go! If you click the Back button now, you should get redirected to the Login page because the session has already been cleared.

In this part of the web tutorial, I walked you through more Forms, Modals, Shared Components and the Logout function. These will be reused in the next part!

Next

Updating MAILING_LIST_TERMS.