Christoph's 2 Cents

A Backup for My Brain!

APEXDevOpsJavaScriptLiquibaseOracle Cloud InfrastructrureOracle Tools

Bulk Install APEX Applications with SQLcl Liquibase

The following code uses a JavaScript that can install multiple exported APEX applications, by looping over the export directories and running the install.sql script in each.

In this example I have a number of (split) exported APEX applications in my project directory:

├── f100
│   ├── application
│   └── install.sql
└── f101
    ├── application
    └── install.sql

This JavaScript can be run by itself from SQLcl, however, the XML changelog allows you to include the script in a Liquibase update.

var File = Java.type("")
var FilenameFilter = Java.type("")
var workingDir = new".").getCanonicalPath(); //should be project root
var apexDir = java.nio.file.FileSystems.getDefault().getPath(workingDir, 'myproject', 'apex').toFile();
var allFiles = apexDir.listFiles();
for(file in allFiles ){
  if (allFiles[file].isDirectory()) {
    fileName = allFiles[file].getName(); //e.g. f100
    filePath = allFiles[file].getCanonicalPath(); //e.g /Users/foo/myproject/apex/f100
    instFile = java.nio.file.FileSystems.getDefault().getPath(filePath, 'install.sql').toFile();
    if (instFile.isFile()) {  //check if install.sql exists
      instScript=instFile.getCanonicalPath()  //Set instScript full path of install.sql
      ctx.write('Installing '+instScript + '\n')
      sqlcl.setStmt('@ "' + instScript + '"'); //format the SQL statement. i.e. /Users/foo/myproject/apex/f100/install.sql; //execute the SQL statement

Create a changeset of type runOracleScript and reference the above script:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="" xmlns:xsi="" xmlns:n0="" xsi:schemaLocation="">
    <changeSet id="1" author="me" runOnChange="true" runAlways="true">
        <n0:runOracleScript objectName="apex" ownerName="scott" sourceType="FILE">

The import_apex.sql and the apex.xml are both stored in my project directory: /Users/foo/myproject.

To run the code, log into SQLcl and perform a Liquibase update referencing the apex.xml changelog.

SQLcl: Release 22.4 Production on Thu Dec 15 12:25:36 2022

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 15 2022 12:25:38 -06:00

Connected to:
Oracle Database 19c Enterprise Edition Release - Production

SQL: > lb update -changelog-file apex.xml
--Starting Liquibase at 12:26:08 (version 4.17.0 #0 built at 2022-11-02 21:48+0000)

-- Loaded 1 change(s)
Running Changeset: apex.xml::1::me
Installing /Users/foo/myproject/apex/f101/install.sql
Installing /Users/foo/myproject/apex/f100/install.sql

Operation completed successfully.

SQL: > 

Note that you don’t get the detailed install logs this way.