Exporting APEX Applications using SQLcl

Introduction

When I started working with APEX, I regularly exported my applications before making any changes. Even after several years of experience, I still export before making significant or complex changes. Although APEX Builder offers a simple way to export applications, SQLcl can also export your APEX app.

Oracle SQL Developer Command Line (SQLcl) is a modern terminal interface many developers use to interact with Oracle databases. As an alternative to SQL*Plus, it offers several new features that can be useful for day-to-day work. In addition to standard SQL and PL\SQL options, such as running queries, creating and modifying database objects, and executing scripts, many commands allow you to export an APEX application via SQLcl.

Moreover, Oracle regularly upgrades and adds new features to SQLcl. The latest version 23.1 (April 4, 2023), is available for download and requires only a minimum JDK of 11.0 to run. The recent upgrade also introduces enhancements for Liquibase and APEX commands.

In this blog, I will present a few examples of how to use the APEX export command and encourage you to explore more.

Setting up connections

If you have not used SQLcl before, the first thing you should do is add an Environment Variable to your system.

In my example, I indicate the path to "C:\sqlcl\bin," where the download file has been extracted. This makes it easier to open SQLcl from anywhere using cmd, and it can be useful to create a batch file to perform commands in the database.

To present some examples, I will connect to my OCI Always Free environment, where you can create your own instance of a DB. To initiate connections, I must download the wallet for my OCI instance and use it during connections.

The standard command is:

sql username/password@hostname:port/servicename

However, to connect to an OCI Cloud instance, SQLcl requires you to set the path to the downloaded wallet first by following these steps:

1. Start up SQLcl without logging in. If the Environmental Variable is not defined, then providing the full path to the sqlcl/bin directory where you unzipped the installation file is required.
2. Indicate your wallet location to set up a cloud configuration file (it may ask for the wallet password the first time).
3. Connect to your cloud database instance using username@service and password. 

After successfully connecting, you can move on to the next step, which is to export your APEX application.

Exporting a list of applications for a selected workspace

Once you are logged in, the first command worth looking at is "APEX help." It displays a list of available commands designed for APEX and returns a brief description of each. The latest release introduces enhancements to the broken-down APEX command to provide better control and flexibility. The list below can help you understand the syntax for exporting a workspace, applications, individual components, and much more.

The first option is the "APEX list" command, which returns all installed applications the logged-in user has access to. The output provides information about the workspace where applications are located, their ID, name, build status and the date when the application was last updated. If you don't know what applications are available, this command is useful for getting information about them.

SQLcl allows you to export all apps from a workspace or filter them by extra parameters like last update (-changesSince 2023-04-05) or last user (-changesBy username) who performed updates on applications in a particular workspace. The example below shows that only two applications have been changed since 2023-04-05. This helps me decide which one I need to export.

When a developer wants to export all applications in a given workspace with default export parameters, nothing is simpler than the following command:

APEX export -workspaceid 1254109XXXXXXXXXX -split

As you can see, an additional parameter, "-split," has been added, which divides the export into multiple folders and files per application. This is a quick and easy way to export all applications for the selected workspace ID that your logged-in user has access to. Additionally, by adding the "-expworkspace" option, you can export relevant workspace metadata, such as groups, grants, user interface defaults, and so on.

APEX export -expworkspace -workspaceid 1254109XXXXXXXXXX

Export individual applications with preferences

APEX Application Builder allows developers to export applications by navigating to App Builder -> Export -> Application. The example below shows a list of preferences that developers may want to include in the export.

In my example, I would like to export almost all of them and save them into a single file. I intend to export public and private reports, subscriptions, developer comments and translations. All of those selected options will be included in the file.

SQLcl allows you to perform the same export using a single command line and overwriting default parameters if provided by the user:

APEX export -applicationid 107 -expSupportingObjects I -expPubReports -expSavedReports -expComments -expTranslations 

By calling the above command, developers can indicate which application, reports, comments, and translations they want to include. This is a much simpler and quicker way to export applications, especially if it happens regularly, rather than navigating to Export via Application Builder and selecting options.

Export selected application components

SQLcl also allows you to list and export selected components from the application. The "-list" parameter can be used to select a list of components within an application. For example, to list all components changed since 2023-04-05:

It could be beneficial to know which pages have been recently changed, but if that information is not required and the developer knows exactly which components need to be exported, just call the command below.

In the results, the created file contains only page 3 for application 107. If you would like to export many components, for example, page 2 and page 3, then that list must be quoted like “PAGE:2 PAGE:3”.

Summary

In conclusion, exporting APEX applications via SQLcl is a straightforward process and can help develop applications as a part of continuous integration. After exporting the application and having the files on local/server directories, you can easily commit changes to the GIT repository. The key advantage is the possibility to build a batch file or any other script to simplify the export process without navigating via APEX Builder. A flexible combination of available commands and parameters could help in daily work activities. To explore more parameters and options available to perform exports, please refer to the Oracle Documentation here.

For more information, check out our APEX services, and if you liked this blog, check out our other APEX blogs here.