A DBA’s Journey to APEX, Part 3: Give Them a Lever, and They Will Move the World

    By: James Czuprynski on Feb 27, 2019

    VEVO 3.png

    By Jim Czuprynski | Edited by April Sims 

    The frenetic pace of application development in modern IT organizations means it’s not unusual to demand an application be built with minimal requirements gathering – literally, from a napkin-based sketch – to a working first draft of the app within extremely short time frames – even a weekend! – with production deployment to follow just a few days later. This article – the third in this ongoing series – demonstrates how simple it is to improve a basic prototype of the existing APEX application as well as construct a simple yet functional application for volunteer canvassers to connect with registered voters in a huge northwest suburban Chicago voting district, right from their mobile devices. Missed Part 2? Catch up here first.

    Our Story So Far

    As I mentioned in my previous article, I’d recently volunteered to help out with the political campaign for my U.S. Congressional district. I’d already demonstrated some progress toward that goal:

    • I constructed a new APEX workspace within the PDBVEVO pluggable database and populated it with several sample APEX applications for reference and experimentation.
    • I built a working prototype of a master-detail data entry page for reviewing and capturing voter information during canvassing.
    • I created some simple reports and forms that describe the campaign’s organizational structure, volunteer canvassing activity progress, and the extent of canvassing efforts still remaining.

    Before I deploy the desktop version of the VEVO application, I’ll focus my efforts on getting these application pages ready for prime-time usage.

    Improving the Campaign Organization Tree

    The campaign organization hierarchy tree view was functional for describing the campaign staff hierarchy, but I’ve now expanded its usefulness with some real information about the activities of staffers at all three levels of the hierarchy –  the “boots on the ground” volunteers, the campaign organizers they report to, and the committee leads responsible for managing teams of campaign organizers.

    For each level in the hierarchy, I’ve added a new application page that shows different information depending on which person in the campaign hierarchy is selected:

    Volunteers: Keeping It Simple

    For the volunteer level, I’ve added a page that uses a classic report object to display how many registered voters each Volunteer has interacted with so far, as well as the average values for voter responsiveness to the candidate. Figure 1 shows an example for volunteer Joseph Martinez.


    Figure 1. Canvassing Volunteer Activity Report

    Campaign Organizers: Summarizing Volunteer Activity

    For the role of campaign organizer, however, a different viewpoint is appropriate. A simple chart object displays a bar graph with a horizontal orientation that summarizes how many voters have been canvassed within months of the campaign. I’ve also built a new report using the classic report model that analyzes how many voters each volunteer has canvassed so far and how successful those canvassing efforts have actually been. Figure 2 shows an example for campaign organizer Priscilla Champion.


    Figure 2. Campaign Organizer Level Volunteer Activity Summary

    Committee Leads: Summarizing Campaign Organizer Progress

    Finally, the committee lead role has the greatest responsibility within the hierarchy for managing voter outreach, so I’ve built a new application page containing a classic report object that summarizes how each set of campaign organizers reporting to the committee lead has performed voter outreach.

    I’ve also added a simple chart object that displays a pie chart, which summarizes voter outreach progress within each type of canvassing activity – for example, directly talking with a voter during a neighborhood visit versus a telephone call, text message, or other outreach activity – for all volunteers who report directly to campaign organizers who correspondingly report to the selected committee lead. Figure 3 illustrates what a committee lead (in this case, Charles Barnes) would see when viewing this page.


    Figure 3. Committee Lead Canvassing Progress Analysis

    I’ve already demonstrated in the previous article how simple it is to create new application pages and then add reports and forms. Building the campaign organizer and committee leads detail pages gave me the opportunity to learn how to add multiple regions – one each for graph object and report object – and that’s extremely simple to do with APEX’s application page wizards, as Figure 4 shows.

    The trickiest part of this implementation was getting the tree list to respond to clicks at the different levels of the hierarchy and then routing the selected key values as bind variables values to each ancillary application page. That took quite a bit of experimentation and reading about how to use the APEX_UTIL.PREPARE_URL function to build an appropriate URL to pass control to the corresponding detail application page, as well as transfer the bind variable values. To demystify that, be sure to review the code in Listing 1 for the SQL statement behind the campaign organization hierarchy page, especially for the derivation of the LINK attribute for each row in the hierarchy.


    Figure 4. Adding Multiple Regions to an Application Page 

    Note: For some excellent examples of how to leverage the power of the tree object, be sure to take a look at the Sample Trees sample application that’s included with APEX 5.1. The example code for the sample Project Tracking application demonstrates some interesting techniques for generating the appropriate URL link to each data entry page depending on the hierarchy level of each entry. Also, check out the Managing Trees topic in the Oracle Application Express App Builder User’s Guide for detailed information on how to create, manage, and utilize tree views.

    Voter Canvassing Page Improvements

    I made some minor improvements to the Voter Canvassing master-detail page, updating the column headings for the Voter master region to reflect their content instead of their column names, as shown in Figure 5.


    Figure 5. Voter Canvassing Page – Master Region

    I also improved the canvassing detail region to include all of the columns in the T_CANVASSING table. I decoded the canvassing attribute values via static lists so that meaningful information would be displayed instead of just the codes for those attributes. See Figure 6 for a sample presentation of voter canvassing detail that highlights these enhancements.


    Figure 6. Voter Canvassing Page – Detail Region

    MobileVEVO: Prototyping A Mobile Device-Ready Canvassing Application

    The VEVO application is coming along nicely as a primary desktop application for managing several aspects of the campaign and its organization; however, one sorely needed set of requirements hasn’t yet been fulfilled.  Volunteers who are performing canvassing duties need a way to capture and record pertinent information about registered voters’ predilection toward their candidate and identify potential opportunities to follow up with promising voters – for example, to request additional campaign donations, or transform a voter to a campaign volunteer.

    This set of requirements boils down to three key features:

    • Canvasser Identification. A canvasser needs to identify themselves to the application so that they can begin to record information.
    • Workload Assignment. Next, the campaign needs to assign each canvasser a different set of voters so there’s little chance of duplication of effort, and focus needs to be placed on voters who’ve not yet been contacted.
    • Recording Canvassing Results. The most obvious need is to provide a method for a canvasser to record the results of her canvassing efforts after successfully reaching out to a voter.

    One final requirement for this application is that it must be able to be run from a mobile device – a hand-held tablet or smart phone – just as well as from a PC desktop.

    First, I created a new application named MobileVEVO using the same methods I used to create the VEVO application in my previous article; just as in that example, APEX automatically created a simple access page requiring a login and password. (For the purposes of rapid prototyping, I’ll leverage that access method for now; I’ll definitely change that when I take up implementing more robust security methods in a future article.)

    I created the main application page as a List View object because this type of display yields nicely to desktop and mobile device access (Figure 7). Note that I set the maximum number of entries initially displayed to just the first three volunteers in alphabetical order. I also activated the Search capability for this object so a canvasser can search for her name with just a few keystrokes. The SQL statement that initially populates this page in shown in Listing 2.


    Figure 7. Volunteer Self-Selection Page

    Once a canvasser has chosen their name from the provided list, control passes to a page that displays five as-yet-uncontacted registered voters, chosen via a simple randomizing algorithm, to populate the list. I used a Reflow Report object type for this list because it’s also a good choice for desktop and mobile applications, as we’ll see a bit later. Figure 8 shows an example of this page in desktop display mode, populated for volunteer Alexander. You can view the SQL statement that initially populates this page in Listing 3.


    Figure 8. Registered Voters Selection Page

    When a volunteer clicks on the NGP VAN ID link for one of the displayed registered voters, the application passes control to a third page that uses a Single-Row Form object to collect information about that voter’s attitudes toward the candidate, the possibility of future volunteerism or campaign contribution, a “do not contact” indicator, and even a space for ad hoc voter responses (Figure 9). Note that I used either radio buttons or drop-down lists to let the volunteer choose from a specific set of values; APEX also allows me to set a default value for each field’s initial display.


    Figure 9. Canvassing Results Page: Before Commit

    Figure 10 shows how the Canvassing Results page appears after I’ve completed recording information for Voter ID # 20124. Once I click the Apply button, control returns to the previous page. Note that because new canvassing details have been recorded for one of the originally-listed voters, that voter no longer shows up in the list of potential voters to be canvassed (Figure 11).


    Figure 10. Canvassing Results Page: After Changes to Data


    Figure 11. Registered Voters Selection Page: After Commit of New Canvassing Data

    So where did I specify the appropriate data manipulation language (DML) to complete the insertion of a new row in the T_CANVASSING table? That’s part of the magic of APEX: Because the application page definition inserts a single row into T_CANVASSING, APEX automatically generates the constraints, non-NULL validations, and the INSERT DML statement after the Apply button is clicked.


    Figure 12. Restricting SQL Statement Generation for Canvassing Data Insertion

    Testing Application Deployment in Desktop vs. Mobile Versions

    Since this is supposed to be a mobile application, how will I know that the MobileVEVO application functions properly when it’s run from a mobile device? The good news is that it’s relatively simple to simulate deploying on just about any intended platform with any internet browser.

    For example, Mozilla FireFox offers the capability to operate my internet browser in Responsive Design Mode, which simulates how the MobileVEVO application will look when displayed on a smart phone (see Figure 13). This makes short work of testing the application as if it was being executed in a number of display formats – including Apple devices using iOS, or tablets and other devices using Android OS – without actually having to test directly on each device.


    Figure 13. Responsive Design Mode

    Note: If your browser of choice is Google Chrome, you can select its similar Responsive Design Mode functionality through the Ctrl+Shift+I key combination, or selecting Developer Tools from the More tools … browser configuration panel.

    Figure 14 shows how the initial MobileVEVO page would display on a typical smart phone. Note that the List View object I chose to display a searchable list of Canvassers is essentially unchanged except for sizing of the entries displayed.


    Figure 14. Volunteer Self-Selection Page: Mobile Mode

    Figure 15 and Figure 16 show how the Voter Assignment and Canvassing Results pages would display on a typical smart phone, respectively. Note that the choice for Reflow Report object type for the Voter Assignment page now makes excellent sense because the report’s display literally “reflows” on a mobile device’s display screen.


    Figure 15. Voter Selection Page: Mobile Phone Deployment Mode


    Figure 16. Canvassing Results Page: Mobile Phone Deployment Mode

    APEX: Minimizing Development Complexity and Challenges

    From my perspective as a relatively new DevOps developer, this is one of the truly powerful aspects of using APEX for developing the MobileVEVO application: Since the same set of application pages can be used on any device, there’s no need to create multiple sets of presentation layer objects specific to multiple platforms. This certainly minimizes the possibility of any unexpected behaviors once it’s deployed; it also means that there’s less debugging to do when any issues are discovered.

    Deploying VEVO and MobileVEVO for User Review

    Now that I’ve got both the desktop and mobile versions of my VEVO applications in relatively stable state, I’m ready to deploy these applications so that my QA team can evaluate if the applications have covered all requested requirements and perhaps even perform some usability and simple, non-formal unit testing. The good news is that even if I don’t yet have a separate APEX/Oracle Database environment ready for deployment, I can temporarily leverage the existing development environment as a sufficient platform.

    Note: As an experienced developer and evolving DevOps DBA, I would never advocate using the development environment for anything but just that – development.

    In the next article in this series, I’ll show how to construct separate staging and production environments to demonstrate how to deploy these applications in both Cloud and non-Cloud environment through APEX’s Export and Import utilities. These are part of the application build methodologies that are intrinsic to every APEX application development environment.

    To deploy the Desktop VEVO application, all I need to do is supply the URL for the application server I’m already using for application development. Since APEX has already assigned a URL for my application development environment, all I need to do is copy that URL excluding the application session ID and publish that to my QA and user community teams.

    As Figure 17 shows, accessing this URL opens the standard VEVO login request page for the VEVO application. (For now, I’ve obscured the IP address for purposes of anonymity.)


    Figure 17. Deploying Desktop VEVO Application URL

    Likewise, I deployed the URL for the MobileVEVO application, as Figure 18 shows.


    Figure 18. Deploying MobileVEVO Application URL

    Next Time: Mapping Campaign Progress – Literally and Figuratively

    Once again, we’ve delved a bit deeper into the myriad capabilities of APEX while delivering several new updates to the VEVO application family:

    • The application’s pages, forms and reports have been enhanced for better functionality and readability, including the addition of different types of graphs to visually highlight particular aspects of canvassing activity.
    • A desktop-ready version of the application has been deployed for user evaluation and testing.
    • A mobile version of the application has been deployed so that canvassers in the field can record data about voters they’ve contacted.

    But there’s still quite a bit to explore! The next article in this ongoing series will tackle:

    • Implementing social login capability to improve application security and ease of access
    • Easing future application development efforts through #SmartDB methodology and techniques
    • Deploying both the desktop and mobile VEVO applications to a new application server and database environment for formal QA testing and production usage


    Listing 1. Campaign Hierarchy Tree View SQL Query

    Listing 2. Volunteer Self-Selection Page SQL Query

    Listing 3. Registered Voters Selection Page SQL Query


    About the Author

    Jim Czuprynski.jpgJim Czuprynski has nearly four decades of professional experience in his career in information technology. He has served diverse roles at several Fortune 1000 companies - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle DBA in 2001. Jim was awarded the status of Oracle ACE Director in March 2014 and is a sought-after public speaker on Oracle Database technology features, presenting often at Oracle OpenWorld, IOUG COLLABORATE, ODTUG Kaleidoscope, Oracle Development Community tours, and Oracle User Group conferences around the world.

    Jim has authored over 100 articles focused on facets of Oracle Database administration to his credit since 2003 at databasejournal.com and IOUG SELECT. He has also co-authored four books on Oracle database technology.

    Jim’s blog, Generally … It Depends, contains his regular observations on all things Oracle.

    Released: February 27, 2019, 9:45 am | Updated: April 23, 2019, 11:08 am
    Keywords: Feature | SELECT Journal | SELECT | SELECT Journal

    Copyright © 2019 Communication Center. All Rights Reserved
    All material, files, logos and trademarks within this site are properties of their respective organizations.
    Terms of Service - Privacy Policy - Contact

    Independent Oracle Users Group
    330 N. Wabash Ave., Suite 2000, Chicago, IL 60611
    phone: 312-245-1579 | email: ioug@ioug.org

    IOUG Logo

    Copyright © 1993-2019 by the Independent Oracle Users Group
    Terms of Use | Privacy Policy