How to Integrate Oracle APEX and Version Control
This blog post will shed light on how your development team can use version control to bring their Oracle APEX development and deployment to the next level.
The Version Control System (VCS), basically, is just a directory with files in it. The catch is: these files are “tracked”. This means that each time a team member changes them, the old file is overwritten. But, we can always go back to the old version. This is useful if we encounter a critical bug with the new functionality in production and need to roll back to an older version.
Now, let’s dive deeper into VCS and Oracle APEX.
Which VCS should you choose?
The first question you must ask is: What Version Control System (VCS) do you want to use? There is a plethora of different systems available. From the dated “Concurrent Version System” (Please, don’t use this…), to Apache Subversion, to the industry standards Git and Mercurial. Most of the time, you will want to use Git because it is well-tested and the most widespread one. Though, for large-scale systems (read Google, Meta, …) Mercurial has some compelling advantages in terms of performance.
APEX Working Copies
Oracle introduced working copies as a branching feature for APEX applications in 23.2. This enables developers to create a working copy of an application and edit this copy independently from the main application. Once the development (and, hopefully, testing) is done, the changes can be merged back into the main application. This resembles a traditional branching approach in version control systems but is completely represented within APEX. Internally, APEX will create applications with different IDs that you could export but, unfortunately, you cannot import them again, without overwriting the existing main application. Maybe this will be a feature in future APEX versions but for now, we have to ignore them in our VCS integration.
Command-Line Exports
Historically, you could export your APEX application either manually (not recommended) or using a tool called “APEXExport Utility”. The latter was a Java based command-line tool with various options to tailor the export to your needs. Nowadays, SQLcl is the export utility of choice and offers even more options, including but not limited to: Exporting entire workspaces, ORDS modules and many others. You can download SQLcl as a standalone tool or, alternatively, use the docker image provided by Oracle on the Oracle Container Registry.
With SQLcl you can export APEX applications with the apex command. Here is a sample of how that might look:
apex export -applicationid 106This would then export application 106 to a single .sql file.
If you look at the file (see the small excerpt above), you will quickly come to notice that this can only be a starting point. There are changing IDs and timestamps in the file, and the file is basically just one large PL/SQL script. This is really bad for version control. We will have to diff the entire file for every new version because each change will be reflected in just this one place. To make matters worse, for every export we perform, the IDs and timestamps will change, creating unnecessary differences in the new export.
We can fix this by providing a few more options when exporting the application:
apex export -applicationid 106 -split -skipExportDate -expOriginalIds -expSupportingObjects Y -expType APPLICATION_SOURCE,READABLE_YAMLLet’s walk through the parameters to see what they are doing for us:
- Split: This will split the export into multiple files so we can localize changes more easily. It will split the application into individual pages but also the shared components and user interface setting.
- SkipExportDate: This will omit the export date in the files to avoid unnecessary changes in unchanged parts of the application
- ExpOriginalIds: This will export the original IDs and make sure that they don’t change, no matter how often and from which environment you are exporting the application.
- ExpSupportingObjects: This is optional, and you only need it if you are working with supporting objects in APEX. It will include the supporting objects in their own directory.
- ExpType: The important export types we are using are APPLICATION_SOURCE (includes the application pages, shared components, …) and READABLE_YAML. The “readable” files contain the same information as the standard .sql files but are in a more readable format for developers. You can diff them with previous versions and quickly see the changes made in the current iteration.
We have to include both types because we want to use the readable format for diffs in our code review, but we have to use the .sql files to import the application again. Oracle does not provide a way to directly import .yaml files (yet).
This will create a source tree of the following structure:
To reinstall the application, you can just execute the “install.sql” script in your deploying schema. This includes references to all other files included in the export.
Checking Changes into Version Control
Now that you’ve got the full export, we can add this to our repository. How can you do that? Well, usually there are database objects and other dependencies included in your APEX projects. These should live in the same repository since they are crucial for your application. At Apps Associates, we promote a project structure as the following:
You can see the word “changelog” hinting at our use of Liquibase, so stay tuned for our blog post on that topic! Your application export would go into “apex/application1”. This gives us the opportunity to include multiple apps in our project without changing the directory structure.
If we were to develop a new feature into our application, we can simply run the export again and put it into our apex directory. Now we can see a diff of the changes made and can perform a code review. This could look like this, for a simple change:
Of course, you should automate this process and integrate the code review into your existing deployment pipelines. We will share our process on this blog within the next months!
Conclusion
In this blog post, we have looked at how to integrate your APEX development with version control. This integration gives us much more confidence in the reproducibility of our deployments and enables a much smoother code review than before. With working copies, the APEX team provides a great feature that needs just a bit more export support, to be a very useful addition to this approach.
One caveat remains: We are still waiting for .yaml file imports. At the moment, we have to include both the original .sql source files, as well as the more readable .yaml files to ensure smooth code review and easy deployments at the same time.
If you are looking for a partner to help you strategize and execute on initiatives such as this, look no further than Apps Associates. We have more than 20 years of experience with enterprise technology implementation and specialize in Oracle applications and integrations. Contact Apps today.
