Export/import APEX workspace and application with SQLcl

One of the reason why I prefer SQLcl to SQL*Plus is that SQLcl has built-in "apex" commands. APEX workspace and application can be easily export / import with SQLcl.

Note: If you have multiple versions of APEX schemas created in the database, you have to log into database as current version of APEX schema to run the commands. If you log in as other privileged users (i.e. SYS, SYSTEM), you have to set current schema before running apex command with following SQL,

  alter session set current_schema=<current-apex-schema>;

For example, if APEX 19.1 is installed

alter session set current_schema=APEX_190100;

1. Export / Import APEX workspace

Workspace can be exported with following command

  apex -workspaceid <workspace_id> <-expWorkspace | -expMinimal | -expFiles>

The command will create a SQL script file under current directory with name w<workspace_id>.sql, it can be run with one of following options

      -expWorkspace:  Export workspace identified by -workspaceid

      -expMinimal:    Only export workspace definition, users, and groups

      -expFiles:      Export all workspace files identified by -workspaceid

And <workspace id> can be listed/found with following SQL

  select workspace_id,workspace from apex_workspaces;

For example

apex_190100@ddb01> select workspace_id,workspace from apex_workspaces;

      WORKSPACE_ID WORKSPACE
  ---------------- --------------------------
                11 COM.ORACLE.APEX.REPOSITORY
                12 COM.ORACLE.CUST.REPOSITORY
                10 INTERNAL
  4482715458638925 SALES
  2104419987831886 FINANCE
  6822807081190901 HR

If export workspace SALES,

apex_190100@ddb01> apex export -workspaceid 4482715458638925 -expWorkspace
Exporting Workspace 4482715458638925:'SALES'

  Completed at Thu Mar 10 19:56:28 EST 2022

It creates a SQL script file named w4482715458638925.sql. This script file is the workspace SALES export file. To import this workspace to new APEX instance, just run this script under APEX schema in that database.

For example, import SALES workspace to database db02

apex_190100@db02> @w4482715458638925.sql
API Last Extended:20190331
Your Current Version:20190331
This import is compatible with version: 20190331
COMPATIBLE (You should be able to run this import without issues.)

WORKSPACE 4482715458638925
Creating workspace SALES...
Creating Groups...
Creating group grants...
Creating Users...
...workspace objects
...RESTful Services
SCHEMA SALESDATA - User Interface Defaults, Table Defaults
SCHEMA HRDATA - User Interface Defaults, Table Defaults
User Interface Defaults, Attribute Dictionary
... elapsed: 14.1 sec
...done

2. Export / import application

Export single application with following command

  apex export -applicationid <application_id>

Export all applications of specified workspace

  apex export -workspaceid <workspace_id>

Both commands will create one SQL script file for each application under current directory. The script file name is f<application_id>.sql.

For example, export all applications of workspace SALES

apex_190100@db01> apex export -workspaceid 4482715458638925
Exporting Application 108:'Promotion Channel'

  Completed at Fri Mar 11 21:23:27 EST 2022

Exporting Application 126:'Sales History'

  Completed at Fri Mar 11 21:23:27 EST 2022

Exporting Application 128:'Customers'

  Completed at Fri Mar 11 21:23:27 EST 2022

Totally, three applications are exported as three SQL script files. The files' name are f108.sql, f126.sql and f128.sql.

To import application, run the exported SQL with APEX schema in new database. For example, import application "Sales History" (application id 126) into database db02

apex_190100@db02> @f126.sql
API Last Extended:20190331
Your Current Version:20190331
This import is compatible with version: 20190331
COMPATIBLE (You should be able to run this import without issues.)

APPLICATION 126 - Sales History
--application/delete_application
--application/create_application
--application/shared_components/navigation/lists/data_load_process_train_load_customers
--application/shared_components/navigation/lists/admin_reports
--application/shared_components/navigation/lists/order_page
--application/shared_components/navigation/lists/mobile_options
--application/shared_components/navigation/lists/footer_controls
--application/shared_components/navigation/lists/charts
--application/shared_components/navigation/lists/data_load_process_train_load_customers_002
--application/shared_components/navigation/lists/reports
   ...<output truncated> ...
--application/deployment/checks
--application/deployment/buildoptions
--application/end_environment
... elapsed: 8.32 sec
...done