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:

/Users/foo/myproject/apex
├── 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.

script
var File = Java.type("java.io.File")
var FilenameFilter = Java.type("java.io.FilenameFilter")
var workingDir = new java.io.File(".").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
      sqlcl.run(); //execute the SQL statement
      ctx.write('\n');
    }
 
  }
}
/

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

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:n0="http://www.oracle.com/xml/ns/dbchangelog-ext" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.9.xsd">
    <changeSet id="1" author="me" runOnChange="true" runAlways="true">
        <n0:runOracleScript objectName="apex" ownerName="scott" sourceType="FILE">
            <n0:source><![CDATA[apex_import.sql]]></n0:source>
        </n0:runOracleScript>
    </changeSet>
</databaseChangeLog>

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 19.0.0.0.0 - Production
Version 19.18.0.1.0

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.