Written by Jim Czuprynski |

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 fourth and final in this series – shows how to take advantage of APEX 18.2’s social login capabilities for improved user security. It also discusses how to leverage #SmartDB concepts to isolate SQL statements where they best belong: inside the database itself. Finally, it demonstrates how to complete a production deployment of the latest version of the APEX applications developed.

Our Story So Far

The previous article in this series showed how easy it was to build desktop and mobile applications in APEX 18.1 to assist my colleagues and fellow volunteers in their canvassing efforts during the recent U.S. Congressional election within our tightly-contested district. I’ve already demonstrated some progress toward that goal:

• I enhanced several of the application’s pages, forms, and reports, including placing graphs within report subregions to improve understanding of how canvassing was progressing at different organizational levels.
• I deployed a desktop-ready version of the original application for users to evaluate and test.
• I built a brand new mobile application for canvassers in the field to capture and record data about registered voters.

My final article will tackle some final enhancements to both the desktop and mobile applications, including the implementation of #SmartDB coding techniques to put SQL where it belongs – on the database server instead of within the application. I’ll also show how to deploy the applications in a separate APEX production environment so developers can continue to work on improvements while canvassers, campaign organizers, and other staffers leverage application tools to continue their voter outreach efforts.

#SmartDB: The Thoughtful Way to Proceed

I endeavored to implement # concepts and practices within my APEX applications, beginning with the use of editioning views for all access to the data within my database, as I showed in the second article in this series. Part of the beauty of leveraging APEX for application development is that any SQL statements added to an application page are really retained within the APEX application schema itself.

This is a tremendous advantage over embedding application code in a piecemeal fashion within an application, as many developers did back in the day of client-server application development. And since the code being executed is literally stored within the same database that I’m accessing, this essentially eliminates any round trips between database server and application client – one of the most common egregious issues with poor application performance, as Toon Koopelars’s numerous studies on # have proven over time. (See the references at the end of this article for extensive information on the concepts and implementation of #SmartDB.)

While most of my prototyping so far has implemented business logic through SQL statements coded directly within the applications’ pages, there’s one other way I can effectively push SQL even deeper into the database layer: PL/SQL Table Functions.

Using PL/SQL Table Functions to Implement #SmartDB

Implementing PL/SQL table functions from a valid working example is quite simple. Here are the steps I followed to tackle this for the query underlying the Uncovered Voters page for the desktop VEVO application:

• First, I built a PL/SQL OBJECT type (OBJ_VOTERS_TOUCHED) whose individual data elements reflect exactly what each row of the result set the current SELECT statement returns.
• Next, I built a PL/SQL TABLE type (TBL_VOTERS_TOUCHED) that contains individual “rows” of type OBJ_VOTERS_TOUCHED.
• I created a PL/SQL package (PKG_APEX_REPORTING) that contains a table function named RptVotersTouched that returns an instance of TBL_VOTERS_TOUCHED.
• Within the body of the packaged function, I recreated the same query that originally existed within the Uncovered Voters page. The query uses the SELECT … BULK COLLECT INTO method to copy the results of the retrieved data directly into the TBL_VOTERS_TOUCHED

To implement the PL/SQL table function within the Uncovered Voters page, I modified the original SQL Query to call the table function instead, as shown in Figure 1. Note that this required no other changes to the page itself because I retained the same column names for the classic report object.

In a similar fashion, I built another set of objects for the Canvassing Targets page of the MobileVEVO application. In this case, I needed to pass the value for the volunteer’s unique identifier (ST_SK_ID) to determine a random list of five registered voters that should be canvassed. Function GetCanvassCandidates now returns the list of identified voters, so it was easy to replace the SQL statement originally stored within the application page with a call to that table function, as shown in Figure 2.

Note that the algorithm that identifies the next five voters relies on the value of ST_SK_ID that’s passed into the GetCanvassCandidates function as the seed value for their randomized selection. This is a simple example of using a PL/SQL table function to shelter the actual mechanism employed – a valuable technique for obscuring proprietary data selection methods, business rules, or other equally sensitive intellectual capital.

#SmartDB: Trading Tediousness for Flexibility

I’m sure I’m not the first PL/SQL applications developer to admit the tedious attention to detail that’s required to implement PL/SQL Tables for #SmartDB. However, there are several advantages to coding calls to my database using this technique:

• All of the code lives deep within my Oracle database as a packaged function, which gives me complete control over the versions of code being deployed regardless of where it’s being deployed within applications.
• The access methods as well as the business logic that the code implements become much more reusable and consistent.
• The actual functionality that underlies the code is completely hidden. This is a crucial advantage when I need to preserve crucial intellectual property from prying eyes – for example, a proprietary algorithm or even business rules that might inadvertently reveal how the application’s business users manipulate or leverage data.

Production At Last: Deploying APEX Applications for End Users

At last, it’s time to deploy VEVO and MobileVEVO to an actual customer-facing environment. Here are the steps I undertook to complete their deployment.

Preparing a Production Environment

To prepare for the production deployment, I followed these steps:

• First, I built a new Oracle 18.3 database environment as a “classic” database within the Oracle Public Cloud. (You can see an excellent example of the necessary steps in this video from the Oracle Learning Library.)
• I named the database instance PRODVEVO to reflect its status as the new production environment for my application system. Note that for consistency’s sake, the corresponding PDB is still named PDBVEVO.
• I then created the required tablespaces, created the current VEVO schema, and loaded data into the T_CAMPAIGN_ORG, T_STAFF, and T_VOTERS I used a freshly-generated set of data for all registered voters, which now includes improved formatting of data for voter addresses, telephone numbers, and several other fields.
• I regenerated random data for the T_CANVASSING and T_VOTER_RESULTS tables using the procedure of package PKG_LOAD_GENERATOR.
• Finally, I gathered statistics and finished creating indexes and constraints for the schema’s tables.

Upgrading from APEX 18.1 to 18.2

As part of my production deployment efforts, I also decided to upgrade my existing development and new production APEX environment from its current 18.1 release to 18.2, the latest release available. I held off with upgrading until now because I had a horrendous initial experience when trying to upgrade to APEX 18.2; I was unable to figure out where APEX’s image files were supposed to be placed and how to handle creating the proper soft link to that directory. (After much research, I found out this was a common issue encountered by newbies during APEX upgrades, so operator error was to blame.)

The good news is that the documentation for upgrading to APEX 18.2 has gotten a boost recently; check out the steps to follow in My Oracle Support (MOS) Note 2487317.1, Step by Step Process of Upgrading APEX to 18.2 in Database Cloud Service, for a complete step-by-step checklist of how to upgrade an existing DBaaS environment from a prior APEX release to 18.2. The entire set of steps took only about 15 minutes per PDB to apply in both my development PDBVEVO environment as well as my production PRODVEVO environment. I also made sure to upgrade the PDB seed database (PDB$SEED) to APEX 18.2 at the same time so that any future PDBs I cloned from seed would be able to take immediate advantage of the latest APEX release.

See Listing 1 (attached below) for an abbreviated listing of all the steps I followed to complete the upgrade to APEX 18.2, including the creation of the required soft links to the appropriate image directory.

Deployment to Production

Now that my production environment is ready to accept the latest version of both the desktop VEVO and MobileVEVO applications, it’s actually quite simple to deploy them to production.

Exporting the Development Workspace

First, I exported the most recent APEX application code from my development environment with just a few mouse clicks.

• I connected to my development environment’s INTERNAL workspace using my APEX administrative credentials and chose the Export Workspace option from the Manage Workspaces tab (Figure 3).
• I then selected the VEVODEV workspace (Figure 4) and then specified a full export of that workspace (Figure 5).
• Finally, I saved the full export as sql to my local file system (Figure 6).

Importing the Workspace into Production

With just a few mouse clicks, it’s quite simple to import the most recent version of the APEX application code into my newly-created production environment:

• First, I connected to the PRODVEVO APEX repository and chose the Import Workspace option from the Manage Workspaces tab (Figure 7) and then selected the file I had previously created from the VEVODEV export process (Figure 8).
• After receiving confirmation of the successful import (Figure 9), I then confirmed that the VEVO schema was the one to use (Figures 10-12).
• Next, I installed the Desktop VEVO application into the workspace (Figures 13-17). Note that I allowed APEX to auto-assign a new application ID for this application.

In a similar fashion, I also exported and then imported the latest version of the MobileVEVO application into the PRODVEVO APEX repository.

Wrapping Up: APEX Lessons Learned

Since this is the final part of this article series, let me recap some of the lessons I’ve learned over the last few months as I’ve delved into using APEX to develop applications:

• First, APEX is extremely easy to learn, and there is a cornucopia of assistance to facilitate that learning process, including the documentation, various APEX community forums, and many blogs full of detailed knowledge from application developers in the field.
• Building APEX pages from scratch is simple to do, but there’s no shame in leveraging an existing page that contains an excellent example of how you’d like your page to perform or behave … and those examples are likely to be found in the fount of knowledge that the APEX sample applications already contain.
• While learning the elements of how to code application navigation, I found it helped to keep in mind the words of famed architect Mies van der Rohe: “Less is more.” As a past applications developer, I was used to having to code explicit instructions to make my application do what I wanted it to do, and I naturally followed that development pattern with APEX. Unfortunately, I found that I’d either overridden a default navigation behavior or caused the application to perform erratically; often simply removing those “extra” navigation directives was enough to overcome those eccentric behaviors.
• Since APEX stores the application’s codebase within its database schema, migrating the latest version of both of my applications to a production environment was relatively simple to complete.

Thanks for coming along for the ride but be assured it’s not the end of my APEX journey. I’m planning to continue to blog on and present numerous APEX topics in the coming months, including how to implement OAuth2 security and how to leverage geographic information system (GIS) capabilities built into APEX.

Listings

Listing 1. Upgrading APEX from 18.1 to 18.2: An Abbreviated Example (attached below)

References

Upgrading APEX to Release 18.2:

• MOS Doc ID #2487317.1: Step by Step Process of Upgrading Apex to 18.2 in Database Cloud Service

Leveraging PL/SQL Tables Within APEX:

• Steven Feuerstein, Writing SQL in Oracle Application Express. Oracle Magazine, March/April 2014
• Steven Feuerstein, Writing PL/SQL in Oracle Application Express. Oracle Magazine, March/April 2014

SmartDB Concepts, Deep Dives, and Practical Examples:

• NoPLSQL and Thick Database Approaches video
• Tom Kyte’s Edition-Based Redefinition (EBR) Article Series:
  • Part 1
  • Part 2
  • Part 3
• Additional EBR Documentation:
  • Oracle Documentation
  • Bryn Llewellyn’s White Paper

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, 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. He is currently employed as a Senior Enterprise Data Architect at Viscosity North America, an Oracle Platinum Partner based in Dallas, TX.

Additional Resources

COLLABORATE 20 will take place April 19-23, 2020 at the Mandala Bay Resort and Casino in Las Vegas, Nevada! Call for Presentations is now open. Don’t miss your chance to share your stories, insights, and advice with the Quest Oracle Community! Submit an abstract by October 11, 2019, to be considered to be a speaker at the conference.

Save the date for Quest Experience Week! This free, four-day event is jam-packed with digital education designed to provide both strategic insight and how-to-education for Oracle users of all levels. Each day is dedicated to a different product and begins with a keynote followed by afternoon breakout sessions. Join us November 12-15, 2019, for conference-quality education from the comfort of your desk!

• Cloud Day: November 12
• Database Day: November 13
• PeopleSoft Day: November 14
• JD Edwards Day: November 15