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

So far, we have been working on the MAILING_LIST table. Now, how do we update the MAILING_LIST_TERMS table? It is a one-to-many relationship, so many rows in the MAILING_LIST will refer to one row in the MAILING_LIST table. Interests and Descriptions are all part of the MAILING_LIST_TERMS table, differentiated by the TYPE column.

We will begin with Interests. Go to Page 4. Make sure that "Page is Public".

In it, go to the options at the bottom row and select a List from the Items tab of the bottom row. Drag it into the Top Navigation region. Set the Source as follows. This is the Shared Component we created in Part 2 of this tutorial.

After that, we Create a Process under Preprocessing, to ensure that this page does a check for the EMAIL session value.

This is the SQL.

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

Now select a List View from the Items tab of the bottom row. Drag it into the page. The List View will be based on MAILING_LIST_TERMS. In the Source section, the Type is "Table/View". The Table Name is "MAILING_LIST_TERMS". Also define the SQL.

This is the SQL. You search for the correct rows based on the value of EMAIL. The TYPE column's value must be "INTERESTS".

upper(EMAIL) = upper(:EMAIL) AND TYPE = 'INTERESTS'

There's more to be done with this, but leave it alone for now. We will get to it later.

What we will do next, is create a Form in the body like we did for some of the other pages. The source should be MAILING_LIST_TERMS. Once that is changed, you should see three fields appear in your Form. Remove EMAIL and TYPE. We only want to have TERM in the Form. Then create a Button. 

For TERM, Create a Validation. 

This is the SQL for the validation. We want to ensure that any new term of type "INTERESTS" under the current user email does not already exist in MAILING_LIST_TERMS.

upper(:P4_TERM) not in (SELECT upper(TERM) FROM MAILING_LIST_TERMS WHERE EMAIL = :EMAIL AND TYPE = 'INTERESTS')

The Button is simple - all it does is submit the Form.

In the Processing section on the left panel, Add a Process. This is what will be triggered when the Form is submitted

This is the SQL. We use this to add the value of P5_TERM as a new row, with EMAIL and "INTERESTS" as the other values.

INSERT INTO MAILING_LIST_TERMS (EMAIL, TYPE, TERM) VALUES (:EMAIL, 'INTERESTS', :P4_TERM);

Now let's get back to work on the List View! Thus far, in the right panel, we have been dealing with the Regions tab. Now go to the Attributes tab. In the Settings section, change Text to "TERM". This means that we only want the TERM column to be displayed. Set Link Target to "Page 6", which we will work on later. Then, under Messages, include a message to be displayed if no records are found.

When you set the Link Target to "Page 6", you should also add this in the parameters. This basically sets the P6_TERM field of that page to the value of TERM.

Let's test this! When you log into your application and go to Interests, you should see this message. That's because you have not yet defined any Interests. If you try to click the ADD NEW INTEREST button before entering any value, you'll see an error message.

Add this in the textbox and click the ADD NEW INTEREST button...

...and the Interest appears!

I'm going to add another one.

See?

And the database should show this now, after I add a few more.

Now we've created the functionality to add Interests. How to delete them? For this, we go to Page 6, which is Interest (note the singular). This is a Modal page. In it, add some static HTML to provide instructions, and a Form. 

Set the Form's source to be MAILING_LIST_TERMS. Delete all fields that appear in the Form except for TERM, which is now P6_TERM.

The Form should now contain a text field and a button. However, the text field should be set to Readonly. The Button should submit the Form.

Then Create a Process to delete that Interest. And maybe include a Success Message.

The SQL should be straightforward.

DELETE FROM MAILING_LIST_TERMS WHERE EMAIL = :EMAIL AND TYPE = 'INTERESTS' AND TERM = :P6_TERM;

I created a new Interest, "swimming", in the meantime. Now let's click on it. What happens? The Modal pops up!

Click on DELETE INTEREST. And now, no more "swimming"! You should see the Success Message.

I shouldn't have to elaborate further on the Descriptions module. It's basically more of the same. And if I'm gonna go through the trouble of producing screenshots, there should be at least some value in that exercise. Suffice to say, if you've been paying attention, you should be able to get that done without any further hand-holding.

Conclusion

This was my maiden effort with Low-Code platforms,  and there's probably a lot of other features I have not yet tapped into. In this tutorial, I have included not just the absolutely necessary, but also some nice labor-saving features that should hopefully be really useful.

Formal regards,
T___T

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.