Oracle APEX is an powerful tool for creating reports based on SQL queries. However, some functionalities that are very important in the development process are missing, for example the possibility to connect to a Git repository. In this post I want to present how our team handles this problems.
In April 2018, almost exactly one and a half year ago, I wrote an article about Authorization in Oracle APEX with LDAP groups. Around that time we introduced APEX at my working place. In the beginning there were only a handful of reports, but since then the use has increased considerably and many different evaluations are now implemented with the application.
For this reason we have decided to try to establish a real development process in APEX. This is not yet perfect and is only in its infancy. If you see potential for improvement, please let me know in the comments!
We use a single APEX instance on a server. In this instance there is a sandbox workspace where various new things can be tested. The real reports are in a different workspace. Here again we have created two applications: Development and production. As the designations already show, the evaluations are developed or revised in one app and then transferred to the other.
To capture changes on both stages, we use the command line tools provided by APEX. The packaged download file contains two Java .class files called APEXExport and APEXExportSplitter. This allows the APEX application to be exported first. The result is a very large PL/SQL file that can then be subdivided into individual components such as pages. Unfortunately, the second step is not very helpful when later transferring a page from one application to another, as there is an import error. However, a git repository makes it easier to understand the structure of the application. How exactly these two tools can be used can be seen in the following article: Automated export and import of APEX applications via command line. Unfortunately I did not find an English version.
We have created two scripts for development and two for production that use these tools and each export the application to a specific folder. In these folders we created a Git repository with the two branches master and development. This gives us the possibility to see who made which changes.
Staging in APEX
In order to transfer pages, we also use the possibilities that APEX provides. The interface offers the possibility to create a new page as a copy of a page from another application. So a new page is created in production as a copy from development. If it is not a new page, but only changes have been made, the page must first be deleted in production and then recreated, overwriting is not possible.
- We use an APEX server, on which we dial up via SSH, execute the scripts there and push them into the repository. Because not every developer has his own working copy, the possibilities of Git are not really used and problems can occur during simultaneous development.
- Changes cannot be rolled back directly by Git, but the different states of the large export file can be restored and a certain old state completely imported. This solves some of the problem, but it is still a bit annoying.