Rapid development and beyond with Oracle APEX

Over the years, the requirements of software and its development have changed a lot. On the one hand there is an increasing customer demand for web applications which can be accessed easily via a web browser and can handle huge amounts of data. On the other hand there is growing demand on the part of software developers for faster development including faster prototyping. The well-known database manufacturer Oracle has found a way to combine both demands.

Whenever it is possible and reasonable, users prefer web applications to desktop applications because of their easy accessibility in web browsers on desktop or mobile devices without the installation of any additional software. Furthermore, the amount of data in companies has increased over the years. At all times database systems are the preferred ways to store and handle data efficiently. The database manufacturer Oracle [1, 2] is well-known for its relational database system “Oracle Database” which provides many efficient features to read and write large amounts of data. To cope with the growing demand of developing web applications very fast, Oracle has created the online development environment “Oracle APEX” [3] which comes as a no-cost plugin for “Oracle Database” and is already included in “Oracle XE”. “APEX” stands for “APPLICATION EXPRESS” and that is precisely what it is.

Combined development environment and database

Oracle APEX is fully supported through and available for Oracle Database. After its installation Oracle APEX provides a powerful development environment which is accessible online via web browsers. Oracle APEX is independent of the operating system underneath. Just a web browser is required.

Due to the fact that Oracle APEX is installed on Oracle Database, the corresponding database can be accessed directly through the online development environment shown in figure 1.

Every part of an Oracle Database, e.g. tables, views, triggers, etc., can be accessed in this way by using the SQL Workshop. Thus no persistence layer is needed to exchange data between the developed application and the database.

However, this means similarly that Oracle Database is required to use Oracle APEX. In view of the operating costs of Oracle Database, which can be very high depending on the preferred license model, Oracle APEX is more suitable for companies that already operate Oracle Database.

Figure 1: Oracle APEX development environment

Programming directly within the database

Oracle Database is a powerful relational database system which can handle huge amounts of data and supports tables, views, sequences and triggers etc. In addition to those database items, Oracle Database provides packages and functions to enable developers to extend their databases by writing source code with PL/SQL. PL/SQL was invented by Oracle and extends the ordinary SQL functionality with features known from other programming languages. Thus “PL/SQL” stands for “Procedural Language/Structured Query Language” and makes it feasible to use, for example, variables, arrays, if-queries and loops directly in an Oracle Database. Even object orientation can be applied.

As mentioned before, no persistence layer is needed to gain direct access via the online development environment to the database underneath. In the same way PL/SQL does not require such a persistence layer to gain access to tables or views. In packages and functions PL/SQL can be mixed up with ordinary SQL. This enables a very easy, fast and lightweight way to write powerful PL/SQL scripts. It is feasible to use prepared statements as well.

No additional programming language

Except for PL/SQL, which extends the ordinary SQL with powerful features known from programming languages, there is no additional programming or scripting language to learn. Oracle APEX is an online development environment which supports developers to create web applications. Thus all the established technologies in web development, e.g. HTML, CSS, JavaScript or jQuery, can be used to design web applications and make them do what should be done.

Furthermore, other technologies such as Java or Jasper Reports can be applied as well.

In addition to the off-the-shelf set of items like buttons, text fields or select lists, Oracle APEX can be extended with other powerful third-party items in the form of plugins. One of the most common plugin items is the “Select2 APEX plugin”
[4] which is based on “Select2” [5] and improves the functionality as well as the user-friendliness of ordinary Oracle APEX select lists. Furthermore, it is also very simple to create own APEX plugins.

Rapid GUI prototypes

Besides the advantages mentioned, regarding software development itself, Oracle APEX supports developers in an earlier stage as well. As a default layout theme for the graphical user interface (consecutively GUI) Oracle APEX comes with the Universal Theme and the Theme Roller as an easy-to-use tool to adapt the Universal Theme individually. There is no need to spend time on the GUI at the very beginning. Thus the developer can directly start with implementing the business logic.

This is the reason why Oracle APEX is feasible to create rapid GUI-Prototypes without logic. Thus prospective customers can get an idea of how their future application will look.

One of the most efficient features of Oracle APEX is tabular reports of data which are used in so called Master-Detail-Pages. Master-Detail-Pages consist of a tabular master page and an item-based detail page. The master page provides an overview over the corresponding data in form of data sets while the detail page provides the possibility to create and edit a single data set.

To create such a tabular report just an ordinary SQL SELECT statement is sufficient. According to the selected columns Oracle APEX generates a tabular report including the same columns. The appearance of this report is based on the current theme used for the regarding application. Figure 2 shows an SQL SELECT statement as well as the corresponding tabular report.

Figure 2: SQL SELECT statement and corresponding tabular report

Internationalisation

The world is getting more and more connected. Thus the demand for multilingual applications is growing rapidly as well. Oracle has taken this fact into consideration and has equipped Oracle APEX with powerful translation utilities to translate whole applications.

The language in which the APEX application is developed at the very beginning serves as the default language. All the labels, buttons, region titles, main and sub menu entries can be translated with no additional effort. There is no need to think about translating the application at the very beginning. Each APEX application can be translated rapidly at any time.

If an application is to be translated, Oracle APEX will store all translatable texts in a translation repository. This repository can be exported as an XLIFF file. “XLIFF” stands for “XML Localisation Interchange File Format” [7] and is an XML-based format to store and exchange translation data. Once an XLIFF file is created it can be easily extended with the translated texts and imported into the translation repository. As a final step the updated translation repository must be published before the translated application can be used.

However, the translation utilities of Oracle APEX just cumulate all translatable texts without any duplicate checks. This means, that e.g. every OK-Button of the application appears a number of times within the XLIFF file. Due to this, some texts have to be translated more than once. This confession must be made to keep the flexibility of translating an application at any time during the development process.

“APEX has allowed us to migrate several disparate Excel and MS Access applications to a consistent, secure, web based environment. The speed and concurrency offered by APEX have been exceptionally valuable.”

(Eric Brandenburg, Senior Applicants Architect, Brunswick Corporate [6])

 

Authentication and authorisation

Although web applications can be accessed very easily by anyone using a web browser, it is not always intended that everyone can read or edit every data within the application. Different users should get different permissions to access data. Oracle has thus developed a comparably powerful and easy-to-use approach to secure web applications developed with Oracle APEX.

To allow or deny users to read or edit data, the application has to know the identity of the user which requests access. This process is called authentication. Oracle APEX provides the possibility to define and apply different authentication schemes. Particular authentication servers can also be used to adapt SSO, which stands for “Single-Sign-On”. An authentication scheme needs to know where to find authentication information of the users and what to do with new users. This can be achieved with PL/SQL. Once a web application contains more than one authentication scheme, it is very easy to switch. Even for the APEX workspace users of the development environment there is a built-in authentication theme available off-the-shelf.

As soon as a user is authenticated successfully the next process, called authorisation, comes into play. According to the authentication schemes mentioned Oracle APEX provides authorisation schemes to manage what permissions a user has. Such an authorisation scheme needs to know where to find the permissions the users have. This can be achieved with PL/SQL as well. The following example works with a table “USER” containing the user identity, a table “ROLE” containing the existing application roles and a table “USER_ROLE” containing roles the users have. Figure 3 shows a PL/SQL function, located in the package “APP_SEC”, which returns a value whether or not the committed user has administrator permissions or not. In addition the figure shows the corresponding content of the authorisation scheme which calls the PL/SQL function. Almost everything in Oracle APEX (e.g. pages, regions, items, buttons, validations, processes etc.) can be restricted with an authorisation scheme.

In addition to authentication and authorisation, Oracle has provided an additional functionality called Oracle VPD. VPD stands for “Virtual Private Database” and offers the possibility to implement multi-client capability into APEX web applications. With Oracle VPD and PL/SQL special columns of tables can be declared as conditions to separate data between different clients. An active Oracle VPD automatically adds an SQL WHERE clause to an SQL SELECT statement. This WHERE clause contains the declared columns and thus delivers only data sets that match (row level security).

“For close to 20 years, the Université du Québec à Trois-Rivières (UQTR) has used the Oracle PL/SQL technology to develop most of its internal and public systems on the Web platform (for example, the student portal). Moreover, we have integrated Oracle Application Express (APEX) to our development, and we are completely satisfied with it. Oracle Application Express is a quick, powerful, and mature development tool that allowed us to improve our productivity level.” 

(Georges-Martin Caron – IT and Technology Project Manager – Coordinator of the Information Systems, Université du Québec à Trois-Rivières [6])

Conclusion

Oracle has created a powerful development environment in the form of a no-cost plugin for Oracle Databases called Oracle APEX. It can be used for both rapid development and rapid prototyping. Oracle APEX provides easy-to-use and at the same time powerful support for authentication, authorisation and internationalisation. In addition, with PL/SQL it is feasible to create efficient, multilingual, secure and future-proof web applications which are independent of their dimensions.

The German IT service provider EPOS CAT GmbH has been working with Oracle Databases and Oracle APEX for more than 10 years. Since 2005 more than 80 web applications for over 170,000 users have been developed. This shows the great success of Oracle APEX.

Figure 3: PL/SQL function and calling authorisation scheme

Refrences

  1. Oracle (07.03.2014). About Oracle. Called on 13.10.2018 from https://www.oracle.com/corporate/#infoOracle:
  2. Oracle Fact Sheet – The Complete Cloud and Next-Generation Platform For Business. September 2017.
  3. Oracle (03.10.2018). Oracle APEX. Called on 13.10.2018 from https://apex.oracle.com/en/
  4. APEX-PLUGIN.COM (12.08.2013). Select2. Called on 13.10.2018 from http://www.apex-plugin.com/oracle-apex-plugins/item- plugin/select2_344.html
  5. SELECT2 (2015).Select2. Called on 13.10.2018 from https://select2.org/
  6. Oracle (03.11.2015). Oracle Application Express Customer Quotes. Called on 13.10.2018 from https://www.oracle.com/technetwork/developer-tools/apex/learnmore/apex-quotes-1863317.html
  7. OASIS (01.02.2008). XLIFF Version 1.2. Called on 13.10.2018 from http://docs.oasis-open.org/xliff/v1.2/os/xliff-core.html
  8. #orclapex (26.11.2015). apeks.png. Called on 13.10.2018 from https://blogs.oracle.com/academy-dach/oracle-apex-programming-competition-2016-fr-studenten-aus-nrw
  9. Logok (18.11.2014).Oracle_logo-880×660.png. Called on 30.01.2019 from http://logok.org/oracle-logo/