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 second in this ongoing series – focuses on how easy it is to leverage Oracle APEX to build the first of several components of a sufficiently-robust application for election canvassers to identify, classify, and inform voters in a huge northwest suburban Chicago voting district.  Missed part 1? 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 US Congressional district. I immediately realized that the campaign was in more than mild disarray in terms of its IT strategy, especially for voter outreach in the diverse communities that comprise our heavily-gerrymandered district in northeastern Illinois. I’d agreed to focus my donated IT skills on perhaps its most crucial need: the ability to identify, track, and canvass potential swing voters among over the projected 280,000 registered voters who’d be participating in the election before November 6, 2018. I’ve already demonstrated some progress toward that goal:

• The initial database design in a matter of minutes using SQL Developer Data Modeling toolset.
• Utilizing a small Oracle 18c Database in the Oracle Public Cloud, implementing a new PDB named PDBVEVO and creating the VEVO schema to contain sufficient test data.
• Using SQL Developer, SQL*Loader, and PL/SQL, I populated sufficiently representative test data in the VEVO schema.

As I started the next phase of development efforts, there was a need to make some minor adjustments to the sample data already loaded into the VEVO schema:

• I regenerated new data for the T_VOTERS table using the DataGenerator tool I described in Part 1 of this series. I increased the total number of registered voter entries to 282,000 (the standard number of voters in each US Congressional district). I also further randomized the distribution of first and last names in the data set, as I’d been using a normalized distribution for those column values and it made sense to reduce the number of duplicated name pairs.
• I also tweaked the PKG_LOAD_GENERATOR PL/SQL package so that it now generates voter participation data for the T_VOTING_RESULTS table in a bit more randomized fashion. Likewise, I reduced the population of canvassed voters in the T_CANVASSING table to only 10% of all registered voters to provide a more realistic starting point for canvassing efforts early in the campaign. See Listing 1 for that package specification and body.
• Finally, to reflect a sudden growth of new campaign volunteers – a problem that many political campaigns only wish they have! – The number of campaign staff was expanded from just under 30 to almost 250 people, and added a new staffing role in the hierarchy – Canvassing Organizer – so that the burgeoning staff of volunteers could be managed more efficiently.

All of the sample data as well as the SQL*Loader control files for reloading this data are available for download. Now I’m ready to allow my application development team to begin building the prototype application and its required features.

Preparing the Oracle 18c Database’s APEX Repository for Application Development

Before writing any code, let’s explore the environment that was used to compose the APEX application. As I mentioned in Part 1 of this series, the application was developed using an OCI-resident Oracle 18c database, which allows us to leverage APEX almost immediately after a little housekeeping.

Accessing APEX

Access the database APEX repository using an internet browser – simply the public IP address of the OCI database plus the string /ords/pdbvevo, which identifies the PDB in which the repository resides (Figure 1).

Configuring a New APEX Workspace

Next, I’ll create a new APEX workspace (Figure 2) with a new user account, VEVODEV (Figure 3).

Once I confirm the workspace’s creation (Figures 4 and 5), I’m ready to add an application to my workspace.

Adding a New APEX Application

It’s time to create the VEVO application based on the VEVO schema. Note that the main APEX development environment for my workspace (Figure 6) already acknowledges there are five tables ready for use. So the next step is to create a new application based on that schema (Figure 7).

Note to keep the application extremely simple at this point by choosing the Universal (42) theme and a similar clean application style (Vita-Slate) . You can always augment its appearance later. In the same vein, I’ll create a blank home page (Figure 8). (Note that you could have also chosen to copy relevant application features and capabilities from other applications, as shown in Figure 9.)

Finally, I’ll define some standard interface attributes and presentation formats for my application, including consistent display of dates and timestamps (Figure 10).

Once I confirm the application’s configuration (Figure 11), APEX creates an application development shell that’s fully ready for my next set of development efforts.

Leveraging Sample APEX Applications

Now that the APEX workspace is initialized, I’ll take advantage of the numerous APEX sample applications available to provide some solid, working examples for well-known patterns and to minimize reworking application objects. Figure 12 shows a brief list of the different sample applications that can be imported into the existing APEX workspace.

In order to manage some complex parent-child entity relationships in my application, I’ll load the Sample Master Detail application by clicking on its icon, which opens a modal window to confirm that sample application’s security authentication scheme (Figure 13). In a matter of seconds, the corresponding tables and database objects are included in the VEVODEV workspace (Figure 14).

In a similar fashion, I imported the Sample Reporting, Sample Charts, and Sample REST Services applications into the VEVODEV workspace; Figure 15 shows the results of the import activities.

Implementing the SmartDB Paradigm

Last but not least, I fully intend to implement the SmartDB paradigm within my new application. I’ll admit that I’ve become an ardent supporter of SmartDB after first hearing about it at Hotsos Symposium 2016, and it’s been interesting to see its gradual spread due to the valiant advocacy of my colleagues Bryn Llewelyn and Toon Koppelaars at Oracle.

Therefore, I’ve granted the VEVO schema the ability to leveraging application editioning and created five base editioning views for each of the VEVO schema’s tables. My APEX application development activities will leverage those editioning views to give me maximum flexibility when it’s time to make changes to the underlying table structures.  The SQL commands and DDL to implement editioning for the VEVO schema are in Listing 2.

SmartDB: Whenever Possible, Do It In the Database

In essence, SmartDB isn’t really a new concept; it’s more about returning to the methods we used in the late 1990s to build well-performing, scalable systems before n-tier architecture enabled code to be placed at the application server layer and even within the application itself. Put simply, SmartDB advocates leveraging the sophistication of existing Oracle Database technology to centralize the execution of SQL and PL/SQL where it best belongs: within the confines of the database itself. SmartDB also focuses on building application systems for resiliency, tunability, security, and elimination of application downtime.

Here’s a great starting point for getting acquainted with SmartDB’s key concepts. It has links to key videos, blogs, presentations, and white papers that explain the requisite details of SmartDB, including Edition-Based Redefinition (EBR) and Code-Based Access Control (CBAC).

Viewing Canvassing Activity

Perhaps the single most important aspect of any political campaign is voter outreach – in other words, getting voters to consider voting for the campaign’s candidate. Fortunately, at this (simulated) stage of the campaign, that’s already started, with about 10% of the 282,000 registered voters having been canvassed at least once. But right now my teams are flying blind – they only have a general idea which voter(s) have already been contacted. So the first priority is to build the capability of at least viewing this canvassing information, with the promise of eventually being able to update any voter’s canvass status without having to load data via Excel spreadsheets or CSV file dumps.

To facilitate this high-priority requirement, I’ll build a new master-detail relationship page with the VEVO application. I’ll start by adding a new page to the existing application (Figure 16) and then selecting Single-Page Master Detail as the Form Type (Figure 17):

Next, I’ll name my new page and add basic navigation via breadcrumbs (Figure 18) and a new navigation menu entry (Figure 19).

On to the meat of the matter! I’ll next build the master form with just a few mouse clicks (Figure 20). Note that I’m using the VOTERS editioning view, not the T_VOTERS table, for the source of my form’s contents. I’ve specified the appropriate column (V_UNIQUE_SK) as the primary key that’ll be used to uniquely identify rows in this form.

Here’s how I built the detail form (Figure 21) based on the contents of the CANVASSING editioning view, once again constructed with just a few mouse clicks. Note that I’ve specified the appropriate columns to identify the linkage between these two views (V_UNIQUE_SK and CV_UNIQUE_SK).

Make no mistake: This is an active, virtually production-ready version of one of the trickier design patterns to implement within any application. As I’ll demonstrate shortly, it’s completely flexible in terms of what I can search for within the list of master entries and with just a few tweaks it can also be used for data entry of both new master and detail entries. Before I became an official Oracle DBA, I was a reasonably skilled PowerBuilder developer, and my last application development project involved using version 7.0 during a conversion of a mission-critical application from Sybase to Oracle. If I had been attempting to develop this simple window using that technology – admittedly, a few years ago, but still in this millennium! – I know that it would have taken me at least a day or so to construct this level of capability within an application, and probably the better part of the next day to get the master and detail data display sets synchronized. I have to admit that I was thoroughly surprised at how simple it was to implement this design pattern in an APEX application environment via literally just a few mouse clicks.

Testing Application Functionality and Navigation

Now that my initial application framework is created, I’ll access the APEX repository and attempt to run it for the very first time. Returning to the APEX home page for the VEVO application (Figure 22), I’ve clicked on the breadcrumb for the Voter Canvassing page; this displays the development interface for that page (Figure 23).

When I click into the Run icon in the top right-hand corner of the page, APEX displays a Login screen for my application (Figure 24). That’s because I selected Application Express Accounts for my application’s authentication scheme. I’ll supply my APEX workspace credentials for the VEVODEV user to connect to the application repository, and then the APEX repository itself makes the connection to the PDBVEVO PDB as the APEX_PUBLIC_USER user account. (Note that this database connectivity method is only in use for development purposes right now; I’ll look more closely at application security, including how to use secondary account authentication methods, in an upcoming article.)

The master-detail form that is displayed will retrieve every single voter in the T_VOTERS table. But a neat feature of this ready-built form is that filtering capabilities are already built in, so it’s a simple matter to select a small subset of voters and then display information about related canvassing activity. As Figure 25 shows, I filtered all voters based on a value of Czupryna for last name and then displayed canvassing information for just the last voter in the filtered list.

I’ll definitely be returning to this page in future articles in this series to demonstrate how to customize data entry, filtering, and data display; for now, however, the simplicity of building the framework of such a complex application object should be evident.

Staff Hierarchy Reporting

Perhaps the single most important aspect of any political campaign is voter outreach – in other words, getting voters to consider voting for the campaign’s candidate. It’s therefore crucial that my application assists the mid-level campaign staff organizers keep a close eye on the dozens of volunteers who are responsible for actually interacting with potential voters via canvassing activity. It’s equally important to watch out for volunteer fatigue because people aren’t getting paid to canvass, and it’s easy for discouragement to set in if the same volunteers are constantly performing those tasks. (From personal experience, there’s nothing more frustrating than talking to a potential voter who tells you they absolutely hate the same candidate you’re absolutely enthusiastic about.)

I’ve built a simple SQL query that shows the campaign organization’s hierarchy (Listing 2). Instead of using a simple tabular report, this time I’ll deploy that hierarchical query as the basis for a tree view of the organization staff, as shown in Figure 26.

Figure 26. Creating a Tree View of Campaign Staff HierarchyAnd Figure 27 shows what the finished tree view displays when I execute it. Note the cool feature that allows me to roll over any entry in the tree view to display the campaign staffer’s role in the campaign (e.g. Volunteer).

Later in this series, I’ll return to this page and add details on which canvassers have been doing the lion’s share of the work within each campaign organizer’s group so that campaign organizers can determine which volunteer(s) could use a break as well as which ones could be asked to step up to increase canvassing activities on each team.

Summarizing Canvassing Activity

As a canvasser, I’d be pretty pleased with the details I can see about fellow constituents, but if I’m responsible for managing the entire campaign, there would be an entirely different set of questions that I need answers. For example, are canvassers adequately covering our assigned precincts or are we in serious danger of falling behind by Election Day?

Summary: Precinct-Level Coverage

The first query captures how many voters have already been canvassed at least once versus those that haven’t been contacted at all.  (see Listing 3). I then used that query as the basis for building a simple APEX report that summarizes this information within each locale, postal code, and voting precincts, as shown in Figure 28. The completed report is shown in Figure 29.

In a future article, I’ll return to this report to improve it further, making it the basis for a real-time summary that will be the first thing key campaign organizers see whenever they log into the VEVO application.

Next Time: Responding to User Demands, From Either Desktop or Mobile Device

This article delivered several new updates to the VEVO application:

• A new APEX workspace within the PDBVEVO pluggable database was built and populated with several sample applications for future development efforts.
• With just a few mouse clicks, the initial data entry portal framework was created for reviewing and capturing voter information from our canvassers in the field as they encounter voters.
• The application now offers some basic reporting on campaign organizational structure, the current extent of volunteer canvassing activity, and how much more canvassing remains to be done among registered voters.

But we’re not done yet! The next article in this ongoing series will tackle:

• Improving the application’s pages, forms, and reports for better functionality and readability
• Deploying a desktop-ready version of the application
• Deploying a minimal mobile version of the application so that canvassers in the field can record data about voters they’ve contacted

About the Author

Jim 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, Hotsos Symposium, 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.

Listing 1. Revised PKG_LOAD_GENERATOR Package Body

Listing 2. SQL Commands and DDL for SmartDB Implementation

Listing 3. SQL Query for Campaign Hierarchical Display

Listing 4. SQL Query for Campaign Voter Coverage Reporting