Automating Remote Deployment of APEX application using Oracle Visual Builder Studio

Photo by Brandi Redd on Unsplash

The following document shows the steps to migrate an APEX application from one instance to another using Oracle Visual Builder Studio and SQLcl.

About Visual Builder Studio:

Oracle Visual Builder Studio (VB Studio) is a powerful application development platform that enables your team to organize and manage their work successfully at all phases of the app development lifecycle. VB Studio can also be linked to source control (Git) so that developers can keep track of changes, apply version control best practices, and collaborate with their teammates to develop applications.

More details: https://www.oracle.com/in/application-development/visual-builder-studio/

About SQLcl:

Oracle SQL Developer Command Line (SQLcl) is a free command line interface for Oracle Database. It allows you to interactively or batch execute SQL and PL/SQL. SQLcl provides in-line editing, statement completion, and command recall for a feature-rich experience, all while also supporting your previously written SQL*Plus scripts.

More details: https://www.oracle.com/in/database/sqldeveloper/technologies/sqlcl/#:~:text=It%20allows%20you%20to%20interactively,previously%20written%20SQL*Plus%20scripts.

SQLcl has a set of commands which can be used to export/import and install APEX applications.

More details: https://docs.oracle.com/en/database/oracle/apex/23.1/aeadm/exporting-and-importing-using-sqlcl.html#GUID-54ED6670-EAEA-45CF-BB0B-F2CB81EB8CFF

The Setup

Step 1: Create New Project

To create a new project, go to the Organization tab in VB Studio and click Create.

Create a new project by entering the Project Name and selecting ‘Initial Repository’ as the template.

Select Initial Repository

Select Repository

Step 2: Create Build Executor templates

Go to Organization -> Build Executor templates -> Create template — and Create a new Oracle Linux template

Click on Configure Software and add SQLcl to the list of softwares

Step 3: Create a Build executor

Go to Organization -> Build Executor -> Create VM and create a new VM based on build executor template you just created.

Step 4: Upload ADB wallets to the Git repo

Using Git commands and SSH, upload the ADB wallets (downloaded from ADB instances of ICS1 and ICS2) to the Git repository

Step 5: Create a Build job to export the application

Go to Builds -> Create Job -> Enter the job name and pick the VM template you just created.

On the Git tab, configure Git by picking the repository and the branch you want to store the APEX application export file in.

Go to Steps -> Configure Steps -> Add Step -> SQLcl

Add the ADB connection details, wallet path (where your APEX application is stored) and add the below command to export the application using SQLcl

apex export -expWorkspace -workspaceId
apex export -applicationid APPLICATIONID

Use the After Build -> Git Publisher step to push the export file to Git repository

Step 6: Create a Build job to import the application

Go to Builds -> Create Job -> Enter the job name and pick the VM template you just created. On the Git -> Configure Git tab, configure Git by picking the repository and the branch you want to pick the APEX application file from.

Go to Steps -> Configure Steps -> Add Step -> SQLcl

Add the ADB connection details, wallet path and add the below command to import the application using SQLcl

Install Workspace:

@WORKSPACEFILE.sql

Install APEX application:

set serveroutput on
begin
apex_application_install.set_workspace_id(WORKSPACEID);
apex_application_install.set_auto_install_sup_obj( p_auto_install_sup_obj => true );
end;
/
@APPLICATIONFILE.sql

Setup pipelines in VB Studio and run the build job.