How to Export and Import APEX Applications with SQLcl

Export an APEX application with SQLcl, split it into files for Git, review a change, and install it in another workspace under a new ID.

SQLcl, Oracle's command-line tool for the database, has an APEX command that exports applications without opening App Builder. One command writes an application to a single SQL file, or splits it into one file per page and shared component, which suits Git much better. The same files are installed in another workspace with the APEX_APPLICATION_INSTALL package, under a new application ID if needed. This guide runs the whole round trip: list, export, split, commit to Git, review a change, and import into a second workspace.

What You Need

  • SQLcl, which needs Java. The examples use SQLcl 26.3 with Java 21, against Oracle APEX 26.1.
  • A source workspace, here DEVOPS_DEV, with its schema DEVOPS_DEV and application 500, Order Tracker: a small app with a report and a form on an ORDERS table.
  • A target workspace, here DEVOPS_PROD, with its schema DEVOPS_PROD.

Start SQLcl and connect as the schema that owns the application, for example with sql devops_dev@localhost:1521/freepdb1. The examples run in an empty directory, where SQLcl writes the export files.

List the Applications

Example:

apex list

Output:

WORKSPACE_ID         WORKSPACE     APPLICATION_ID    APPLICATION_NAME    BUILD_STATUS       LAST_UPDATED_ON  LAST_UPDATED_BY
17966849567032340    DEVOPS_DEV    500               Order Tracker       Run and Develop    06-10-26         DEVOPS_DEV

Export an Application to One File

Example:

apex export -applicationid 500

Output:

Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
File f500.sql created

f500.sql holds the complete application. It is the same kind of file App Builder exports, and it installs the same way.

Split the Export for Git

A single file of thousands of lines is hard to review. With -split, each page and component gets a file of its own:

Example:

apex export -applicationid 500 -split -exporiginalids -skipexportdate -overwrite-files

Output:

Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
File f500/install.sql created
  • -split writes the f500 directory, with install.sql calling every other file in order.
  • -exporiginalids writes the component IDs as they are in the workspace, instead of computing them for the export.
  • -skipexportdate leaves the date line out of the files.
  • -overwrite-files replaces existing files. Without it, a second export into the same directory does not overwrite: SQLcl writes new files such as page_00002_1.sql next to the old ones.

The directory looks like this:

Example:

ls f500
ls f500/application
ls f500/application/pages
find f500 -type f | wc -l

Output:

application
install.sql
create_application.sql
delete_application.sql
deployment
end_environment.sql
pages
plugin_settings.sql
set_environment.sql
shared_components
user_interfaces
page_00000.sql
page_00001.sql
page_00002.sql
page_00003.sql
page_00004.sql
page_00005.sql
page_09999.sql
page_groups.sql
      45

Every page has its own file, and the 45 files together make up the application.

Export a Readable Version

The SQL files are made for installing, not for reading. The APEXLANG export type writes the application as readable text, one .apx file per page and per group of shared components, which makes code reviews easier:

Example:

apex export -applicationid 500 -exptype APEXLANG -dir readable -overwrite-files

Output:

Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
File readable/order-tracker/application.apx created

Example:

find readable -name "*.apx" | sort

Output:

readable/order-tracker/application.apx
readable/order-tracker/page-groups.apx
readable/order-tracker/pages/p00000-global-page.apx
readable/order-tracker/pages/p00001-home.apx
readable/order-tracker/pages/p00002-orders.apx
readable/order-tracker/pages/p00003-order.apx
readable/order-tracker/pages/p00004-order-list.apx
readable/order-tracker/pages/p00005-order-details.apx
readable/order-tracker/pages/p09999-login.apx
readable/order-tracker/shared-components/authentications.apx
readable/order-tracker/shared-components/authorizations.apx
readable/order-tracker/shared-components/breadcrumbs.apx
readable/order-tracker/shared-components/build-options.apx
readable/order-tracker/shared-components/component-settings.apx
readable/order-tracker/shared-components/lists.apx
readable/order-tracker/shared-components/lovs.apx
readable/order-tracker/shared-components/static-files.apx
readable/order-tracker/shared-components/themes/universal-theme/theme.apx

-exptype accepts several types at once, for example APPLICATION_SOURCE,APEXLANG for the installable files and the readable ones together.

Commit the Export to Git

Example:

git init -q
git add f500
git commit -q -m "Order Tracker, release 1.0"
git log --oneline

Output:

d858d2e Order Tracker, release 1.0

See an Application Change in Git

Here the change is a new version number, set with APEX_APPLICATION_ADMIN, followed by the same split export:

Example:

-- a change in the app: a new version number
begin
  apex_util.set_workspace('DEVOPS_DEV');
  apex_application_admin.set_application_version(p_application_id => 500, p_version => 'Release 1.1');
  commit;
end;
/
apex export -applicationid 500 -split -exporiginalids -skipexportdate -overwrite-files

Output:

PL/SQL procedure successfully completed.

Exporting Workspace DEVOPS_DEV - application 500:Order Tracker
File f500/install.sql created

Example:

git status --short
git diff -U0 | grep '^[-+][^-+]'

Output:

 M f500/application/create_application.sql
?? f500.sql
?? readable/
-,p_flow_version=>'Release 1.0'
+,p_flow_version=>'Release 1.1'
-,p_version_scn=>'18822141'
+,p_version_scn=>'18822354'
  • Of the 45 files, only create_application.sql changed: the version, and p_version_scn, which records the database change number of the last change.
  • f500.sql and the readable directory were not committed in this example, so Git lists them as untracked.

Import into Another Workspace with a New Application ID

The export contains the application, not its tables. Create the objects it uses in the target schema first, connected as DEVOPS_PROD:

Example:

-- the app's table in the target schema
create table if not exists orders (
  order_id         number generated by default as identity primary key,
  customer_name    varchar2(100) not null,
  delivery_method  varchar2(10) default 'DELIVERY' not null
                   constraint orders_method_ck check (delivery_method in ('PICKUP', 'DELIVERY')),
  delivery_address varchar2(200),
  notes            varchar2(400));

Output:

Table ORDERS created.

Then set the install options and run install.sql of the split export:

Example:

begin
  apex_application_install.set_workspace('DEVOPS_PROD');
  apex_application_install.set_application_id(600);
  apex_application_install.generate_offset;
  apex_application_install.set_schema('DEVOPS_PROD');
  apex_application_install.set_application_alias('ORDER-TRACKER-PROD');
end;
/
@f500/install.sql
select application_id, application_name, alias, owner, version
from   apex_applications where workspace = 'DEVOPS_PROD';

Output:

PL/SQL procedure successfully completed.

--install
--application/set_environment
--application/delete_application
--application/create_application
--application/plugin_settings
--application/shared_components/navigation/lists/navigation_bar
--application/shared_components/navigation/lists/navigation_menu
--application/shared_components/navigation/lists/page_navigation
--application/shared_components/navigation/listentry
--application/shared_components/files/icons_app_icon_144_rounded_png
--application/shared_components/files/icons_app_icon_192_png
--application/shared_components/files/icons_app_icon_256_rounded_png
--application/shared_components/files/icons_app_icon_32_png
--application/shared_components/files/icons_app_icon_512_png
--application/shared_components/security/authorizations/administration_rights
--application/shared_components/navigation/navigation_bar
--application/shared_components/logic/application_settings
--application/shared_components/navigation/tabs/standard
--application/shared_components/navigation/tabs/parent
--application/shared_components/user_interface/lovs/boolean
--application/pages/page_groups
--application/shared_components/navigation/breadcrumbs/breadcrumb
--application/shared_components/navigation/breadcrumbentry
--application/shared_components/user_interface/themes
--application/shared_components/user_interface/theme_style
--application/shared_components/user_interface/theme_files
--application/shared_components/user_interface/template_opt_groups
--application/shared_components/user_interface/template_options
--application/shared_components/globalization/language
--application/shared_components/logic/build_options
--application/shared_components/globalization/messages
--application/shared_components/globalization/dyntranslations
--application/shared_components/security/authentications/oracle_apex_accounts
--application/user_interfaces/combined_files
--application/pages/page_00000
--application/pages/page_00001
--application/pages/page_00002
--application/pages/page_00003
--application/pages/page_00004
--application/pages/page_00005
--application/pages/page_09999
--application/deployment/definition
--application/deployment/checks
--application/deployment/buildoptions
--application/end_environment
...done

   APPLICATION_ID APPLICATION_NAME    ALIAS                 OWNER          VERSION
_________________ ___________________ _____________________ ______________ ______________
              600 Order Tracker       ORDER-TRACKER-PROD    DEVOPS_PROD    Release 1.1

1 row selected.
  • set_workspace names the target workspace.
  • set_application_id installs the application as 600, because ID 500 is already used in this APEX instance. generate_offset shifts the internal component IDs so they do not clash with those of application 500.
  • set_schema makes DEVOPS_PROD the parsing schema, and set_application_alias gives the copy its own URL alias.
  • Running the same block and script again later replaces application 600 with the newer version.

Things to Know

  • The single-file export installs the same way: run the same block, then @f500.sql.
  • Run the import as the target workspace's schema, or as a user with the APEX_ADMINISTRATOR_ROLE role, as the header of every export file says.
  • Commit the export after each change, so Git shows who changed which page and when.
  • For applications that change together with tables and code, SQLcl Projects packages both into one release.

Related Guides

Conclusion

SQLcl's apex export writes an application to one file or, with -split, to one file per component, ready for Git. Add -overwrite-files when exporting again into the same directory, review changes with git diff, and install the export in another workspace with APEX_APPLICATION_INSTALL, under a new application ID and schema when needed.

Tags
Vinish Kapoor
Vinish Kapoor

An Oracle ACE, author of four books on Oracle APEX, SQL and PL/SQL, and Oracle Forms, and a software developer building Oracle database applications since 2001.

guest

0 Comments
Oldest
Newest Most Voted