Using Oracle APEX

Problem Statement

Over the past several years there has been a growing use of Oracle APEX as a development platform for creating business applications within Harvard University. This has resulted in close to 140 APEX applications introduced into the environment with varying standards of build and deployment.

This advisory provides guidance to the product development communities in determining when Oracle APEX is fit for purpose and offer some best practices to follow in the creation of the resulting applications.

Executive Summary

There is an increasing business demand for web applications which can be easily accessed and can handle substantial amounts of data. There is also a growing demand for faster development including faster prototyping. Oracle APEX provides a platform that meets these demands.

Oracle APEX is a low-code, web application development framework that runs directly out of the Oracle Database and uses Oracle REST data services (ORDS) deployed in a WebLogic server for data access. The application definitions are stored in the database as meta data. ORDS, a mid-tier Java application, provides a database management REST API and the ability to publish RESTful web services for interacting with the data and stored procedures in an Oracle database.

Oracle APEX is included by default with all editions of the Oracle database. It can be used to build a wide variety of applications from the simplest application that converts a spreadsheet to a web page, to mission-critical applications which are used daily by tens of thousands of users.

This advisory provides some guidelines for the product and portfolio teams to enable them to determine whether or not Oracle APEX is an appropriate tool for a specific custom build.

Oracle APEX is appropriate for the creation of applications that are data driven and running on Oracle databases. Increasingly, with the use of REST APIs, the scope of use is being extended beyond Oracle databases. However, the following use cases have been identified as potentially good candidates for APEX at Harvard.

border="1" cellpadding="2" cellspacing="3" style="display:block; margin-left:0; margin-right:auto"
• Reporting and workflow applications
• Authorization applications
• Application extensions
• Custom business applications
• Testing prototypes
• Empowering tech-savvy users

The advisory also includes several scenarios where APEX is likely not a good fit for your application like:

• Native applications
• Client-server applications
• Applications needing to be used offline
• Single page applications
• Mobile applications

It is recommended that the teams review their current environment architecture and portfolios for use of Oracle and other development platforms, and their organizations for the skills required to create and maintain an APEX application. They should then consider the costs, benefits, risks, and complexity of introducing a specific Oracle APEX application into their organization and portfolio. They should also consider how well a custom build aligns with the strategic direction being taken by HUIT or a specific school.

Finally, this advisory provides some best practices for the development and DevOps teams for both the creation and deployment of APEX applications.

Intended Audience

This document is intended for IT Managers, product & portfolio owners, APEX developers and DevOps teams who are creating business applications to aid in their determination of whether Oracle APEX is the appropriate platform for a particular use case, and once developed, in the deployment of the application.

Summary & Recommendations

The assumption made in these recommendations is that there is no competitive product on the market that is available to the team, and a decision has already been made to build a custom application.

There are four areas of consideration in determining whether Oracle APEX is an appropriate for a custom build:

• the strategic vision of HUIT or a specific school as regards the technologies to be deployed,
• the use case before the product or development team,
• the team’s current environment in terms of skills and other technology in use, and
• the costs, risks and benefits of the custom solution being considered.

The use cases are discussed in section 5 of this advisory.

1. Strategic Vision

   In determining whether Oracle APEX is the best fit for a custom build, consider the strategic direction of HUIT or a specific school and how Oracle and specifically APEX aligns with that vision.

2. Analysis of Current Architecture and Team

   In analyzing your environment and team capabilities, ask the following questions:

   • Are there Oracle databases already in use in the environment?
     
     Having Oracle databases already in use indicates that a team has the licensing necessary to use APEX and ORDS. It is also likely then that they have the skills necessary to install and maintain the required environments.
   • Do you already have other Oracle APEX applications in the environment?
     
     If a team already has other APEX applications in their portfolio, then it is likely that they will already have both the setup, and skills and experience required to create and maintain a new one. A team that does not would have to invest a bit more time and money.
   • What other development platforms are currently being used?
     
     The development platforms in use are an indicator of the skills that exists within a team. If Oracle APEX is not part of the team’s skills set, then there would be a significant investment of time and money to acquire those skills.
   • How many other development platforms does the portfolio have?
     
     The more development platforms a team has in their application portfolio, the more complex the maintenance. It is recommended to keep that number low.
   • What are the data sources that will serve the custom application? Are they in Oracle databases?
     
     These questions give the teams an opportunity to consider the integrations and data exchange mechanisms that will be required.
   • Does the team have Oracle DBA skills? What is the level of skill?
     
     The teams should have access to Oracle database administrators particularly experience with data modeling, performance tuning and data protection will be necessary. This expertise does not have to exist within the development team. It can be part of say a DevOps team or if the environments are part of a hosted environment, then they could be part of the services offered.
   • Does the team have APEX developer skills? What is the level of skill?
     
     Oracle APEX developers should be proficient is several of the following areas:
     • Oracle Application Express
     • Data modeling
     • Oracle SQL and PL/SQL
     • Oracle database performance tuning
     • Data and Web security
     • HTML5
     • CSS
     • Web design
     • JavaScript
     • RESTful web services

   In summary, the use of APEX to create applications is more appropriate when there is existing APEX infrastructure and expertise. The expertise should include Oracle database administration and APEX development. As noted above, the expertise to stand up APEX infrastructure can be part of a DevOps team or if the environments are hosted then part of the services offered.

   Note that this does not exclude teams just starting out with no infrastructure or the required expertise. These teams might incur higher initial costs and have greater risks using APEX for an application build. The team would need to ensure that considering all the factors discussed, it still makes sense to proceed using Oracle APEX.

   In addition, the data sources that will serve your application should factor in your decision. The more of your data sources that exist in Oracle databases, the more appropriate it would be to use APEX to create a new custom application. However, an APEX application can still interact with data that is not in an Oracle database using RESTful APIs.

   Finally consider how many other development platforms your team will have to work with. Keeping that number reasonably low will reduce complexity in your environment.

3. Cost, Risks and Benefits Analysis

   The costs, complexities, risks, and benefits analysis will give the team the opportunity to weigh the following:

   • the costs, including development time and training costs, to build and maintain a custom Oracle APEX application
   • the complexities that a new custom APEX application will add to an environment
   • the risks introduced by adding a new application, and
   • the business benefits that will be derived from a new application.

   The higher the costs, complexities, and risks introduced for few or limited business benefits, the less appropriate Oracle APEX for a specific scenario.

Potential Use Cases

Below are some examples of use cases where Oracle APEX has been or could be used to create business applications in Harvard.

1. Reporting & Workflow Applications

   These are applications that replace spreadsheets, PDF forms, and email review and approval processes. They often provide organization-specific dashboards, improved workflows, or fill reporting gaps

   They also can provide interactive reporting and data visualization applications based on disparate data from across multiple systems.

2. Authorization Applications

   This category of applications provides application specific solutions for fine grain access control for other APEX and non-APEX applications. An example of this is Alseta.

   The applications define user-organization unit, user-object, and user-organization unit-object relationships. They are used to define and manage the ownership and allowed operations and actions.

3. Application Extensions

   Applications that extend ERPs and other enterprise software to avoid customizations within a vendor application or when a business application like EBS, GMAS, Peoplesoft or Center Stone have limitations in terms of how the data can be viewed of number of licenses in the case of Center Stone.

4. Custom Business Applications

   These are applications that fill business needs not covered by any of the existing enterprise applications or new business needs triggered by some unforeseen change like Covid-19.

   Often there is a need to fill the business need in days or weeks and not months or years. The development teams can easily modify to meet changing requirements and rapidly iterate to a production ready application. A key aspect of this use case is that often applications that are needed urgently and developed quickly end up having an operational life measure in years.

5. Test Prototypes Without Significant Investment

   When your organization has an idea for an enterprise application, you can quickly create and test a prototype to determine its viability.

6. Empower Tech-Savvy Users

   Your organization may have many tech-savvy workers that would benefit from being able to put together highly customized enterprise applications to support their work. These users can get what they need without taking up the development team’s time for most of the process. The use of APEX in this case helps to impose some standards and consistency in development.

Best Practices for Development

The goal of the best practices below is to ensure that good and consistent practices are followed in the development of APEX applications. These best practices should be considered when designing an application and managing the development process.

1. Create the Oracle Data Model

   A data model will provide the structure, definition, and format of the data. It is the foundation for any applications and a good application requires a good data model. It is essential not simply for storing data and implementing data related business rules but also for application performance.

2. Develop APEX Coding Practices

   Institute some common practices that a development team adheres to when creating APEX applications. Here are some recommended practices:

   • Keep all code in packages and avoid stand-alone procedures and functions.
   • Limit the code in a package to a page.
   • Use Page Comments to specify what is happening on the page or why specific options were used.
   • Make use of all the features in APEX and stay as close as possible to existing features within APEX.
   • Develop naming conventions for the applications items.
   • Create a LOV for every table holding data of a specific type to ease the life of users and ensure no invalid values are entered.
   • Create modules within your application
   • Never allow application pages to access a database table directly but use a view instead to provide abstraction over the underlying tables.
   • Consider enabling feedback about your application from within the application.
   • Review the application code with each introduction of a new APEX version.
   • Instrument heavily (automate).
   • Share knowledge within the teams by using Shared Components within APEX and the Publish/Subscribe method.

3. Build to be secure

   • Use the documented Oracle APEX security best practices to protect the application and to avoid the introduction of SQL injection, URL tampering or cross site scripting.
   • Create a custom authentication that allows single sign-on (HarvardKey Login).
   • Create appropriate authorization schemes so persons see only what they are allowed to.
   • Use session state protection in shared components and bind variables wherever possible.

4. Create templates for applications

   Pick a theme and create a template and used by all developers within your organization for a consistent look and feel within and across applications. Embedded in the templates should also be the reusable components and security models.

5. Logging and debugging

   The ability to be able to turn on logging and debugging at any point to see what is happening behind the scenes is particularly important. Utilize all the appropriate Oracle provided features and recommended logging and debugging techniques.

6. Build for performance and scale

   • All code must be tested on performance. The documented Oracle database and APEX best practices such as using analytical functions instead of creating a custom PL/SQL routine should be followed.
   • Use external files on web servers to generate less hits on the database.
   • Use bind variables instead of substitution strings.
   • Where possible, build an application performance report into the applications so administrators or developers can easily see what is going on.

7. Create reusable components

   Build as many reusable components as possible such as PL/SQL packages to do error handling and if APEX needs to be extended, make use of plugins, or use the main building blocks of APEX like PL/SQL, jQuery, HTML5, CSS.

8. Workspaces

   Workspaces enable the separation of web applications and databases to ensure security and data integrity. Each workspace is associated with one or more database schemas (database users) which are used to store the database objects, such as tables, views, packages. Applications can only be associated with one workspace.

   There are several things to consider when creating workspaces such as security, users and development accounts, REST services and interactive reports.

   A workspace with access to multiple database schemas would be a concern for managing RESTful services because there is currently no way to specify a parsing schema. In this case, a one-to-one mapping of schema to workspace with the purpose of managing that schema’s RESTful services would be one way to go.

   In an organization with less experienced developers, giving each developer their own workspace and schema might be most appropriate. This would allow them the space to learn more about the tool without any chance of negatively impacting other business applications.

   In general, determine the required user and developer management scheme then plan out the appropriate APEX workspaces.

9. Documentation

   Below are a few best practices to follow on documentation:

   • Create conceptual design documents.
   • Within the PL/SQL packages
     • use code comments to guide users in understanding how to use (especially in the package spec) and maintain the code (especially in the package body), and
     • inside a program unit only use the line commenting technique.
   • For generated documentation,
     • if you use the JavaDoc syntax, then you can use plsql-md-doc to generate an easy-to-read document, or
     • alternatively, Oracle SQL Developer or PL/SQL Developer include documentation functionality based on a JavaDoc -like tagging.
   • Within the APEX applications, use the comment space in each component page, region, item, process, etc. to specify what is happening or to specify any technical options used.
   • Create end-user guides.

10. Build APIs

    It is still a common practice for teams to use database links to expose and share data between databases. Database links while a simple, straightforward, and useful method for transferring data, pose several concerns including security, distributed transactions and performance resulting from the coupling of databases and applications.

    ORDS provides all the requirements to create, manage, expose, and secure RESTful services. If you need to expose or share data from your APEX application, consider creating REST services against your database objects.

11. Inventory your applications

    An inventory would keep track of all the custom APEX applications within your environment. It would also provide valuable answers to important questions like

    • What business processes does the application support?
    • Are there dependencies between the applications?
    • Are there several applications supporting the same or similar business processes?
    • Are there applications that are unused or barely used?

Best Practices for Deployment

As with the development best practices above, consideration should be given to the deployment best practices below to ensure the application will operate properly and be supportable over time.

1. Use runtime only mode for production environments

   The runtime environment enables users to run a production application without supporting the ability to change or edit the application or the ability to run ad hoc queries from the SQL Workshop component.

   A runtime environment includes only the packages necessary to run your applications, making it a more hardened environment. It does not provide a web interface for administration.

2. Deployment standards

   Standard deployments are necessary. Create best practices and security guidelines for APEX deployments.

3. Shared infrastructure

   Review existing APEX environments with a goal of using shared infrastructure as much as possible.

4. Consider using RDS

   Using Amazon’s managed database service (RDS) means AWS takes over many of the difficult and tedious management tasks of a relational database like installations, upgrades, patching, backups, automatic failure detection, and recovery.

5. Lower the barrier for entry for non-prod environments

   Create a self-service development environment where developers and other tech-savvy users can create prototypes and proofs of concept for business applications.

6. Create best practices and templates for variable URLs

   Create naming conventions and templates for the use of variable URLS.

7. Acquire a common license for AOP plugin

   The various product teams should consider consolidating the AOP Plugin licenses into a common license.

8. Production support organizations

   Ensure support organizations such as DevOps are prepared and trained to support the APEX applications.

Scenarios Where APEX is NOT a Good Fit

The situations below would warrant that a team look at alternatives to APEX.

It is important to note that the APEX platform is constantly evolving and some of the scenarios below may get addressed in future releases. The Oracle APEX roadmap can be found here https://apex.oracle.com/en/learn/resources/roadmap/.

1. Native applications

   These are applications built to be used on a specific device or OS e.g., navigation apps on mobile phones. These applications require native low-level access to a device’s peripherals.

2. Client/Server applications

   In general Oracle APEX is most appropriate for data centric web-based applications. If the desired solution is a rich client application, then it is not an appropriate platform.

3. Offline applications

   The application can execute offline without network/internet connection to supporting application and database servers. APEX developed applications require access to the database to work.

4. Single page applications

   A single page application is a web application that interacts with the user by dynamically rewriting the current page, rather than loading entire new pages from the server.

   Oracle APEX is a multi-page application (MPA) framework. It is possible to achieve single page applications using custom code or by integrating a JavaScript framework like Oracle Jet, ReactJS, AngularJS etc. However, these options would likely involve more effort and result in a more complex application.

5. Mobile applications

   Oracle APEX allows for the creation of web applications which responsively work on any mobile device; however, it does not natively allow for the creation of applications that can be installed on a mobile device. To solve this problem, applications would need to be implemented as a Progressive Web Applications (PWA). Even then, the application would still require a connection to the Oracle database to work.

6. Version control requirements

   For large scale development efforts, an effective framework that allows multiple developers to work concurrently is necessary. The ability to manage multiple versions of the application through its development life cycle is also critical.

   Unlike file-based development languages, APEX stores the application definitions as metadata inside an Oracle database. It has an export feature that generates a file with many randomized values which makes it challenging to compare two copies of the same application.

   Therefore, key aspects of version control like tracking changes, tagging, and merging code, and rolling back changes have to be handled differently in APEX. For example, to mitigate the inability to track changes, it is recommended that as much code as possible is stored in packages. These packages can be stored as regular files and as such it would then be simple to do a diff comparison.

   At an application level, tools like APEX Diff can be used to export an APEX application in a JSON format. Diff Comparisons using the JSON format exports can then be done on different versions of an APEX application.

7. Other scenarios

   • relies heavily on JavaScript functionality,
   • needs to analyze unstructured data like Twitter feeds,
   • is required to manipulate OS files or
   • to create complex and static reports then APEX is likely not a good fit.

Additional Considerations

The scope of this advisory is limited to APEX design, development, and operation. Within Harvard there are several other development platforms in use, and considerations that go beyond the use of APEX.

1. Other platforms

   This advisory does not offer a comparison between Oracle APEX and any other development platforms.

2. Other advisories

   The following are additional resources on the Enterprise Architecture website that would be helpful when creating or implementing any software solution.

   • API Principles and Practices: Use of the API Gateway and Portal
   • API Principles Checklist
   • Application Architecture Checklist
   • Data Exchange Mechanisms and Considerations

References

• https://docs.oracle.com/database/apex-5.1/HTMDB/understanding-developer-...
• https://www.neooug.org/gloc/Presentations/2019/SpendoliniAPEX%20Security...
• https://vmorneau.me/apex-pwa-part1/
• https://www.gartner.com/document/4005973?ref=solrAll&refval=313130009
• https://insum-labs.github.io/plsql-and-sql-coding-guidelines/v1.1/3-codi...
• https://www.youtube.com/watch?v=OIC2Edl9i-c

Appendix - Examples of APEX applications at Harvard

TABLE 1: CUSTOM BUSINESS APPLICATIONS

TABLE 2: REPORTING AND WORKFLOW APPLICATIONS

TABLE 3 : APPLICATION EXTENSIONS

TABLE 4: AUTHORIZATION APPLICATIONS