Introduction
Every APEX developer knows how to export and import applications using the Application Builder. And since an APEX export file is actually a SQL script, it’s also pretty obvious, that one can use SQL*Plus or SQLcl to deploy the application on the target system. So far, so good.
The export part of the process can also be automated: APEX provides the APEXExport command line utility for years now. This Java-based tool allows to build shell scripts to export APEX applications or workspaces. Such export scripts, combined with SQL*Plus invocations to import on the target system, people can build automated deployment processes.
However …
- Running the APEXExport utility requires either Linux/Unix access or JDBC connection details to the APEX database. That way of accessing the APEX instance is often complicated or not even supported (for instance, because of Firewalls or Cloud environments).
- The same applies to the import part, which requires SQL*Plus access to the target database. An alternative is to use the SQLcl utility and REST Enabled SQL on the server (which uses HTTPS instead of a JDBC connection), but that feature must be enabled on the target system as well, which is often not the case.
A typical modern way of automating things is to use REST APIs – and we can do the same for APEX application exports and deployments. Operations with APEX applications then simply map on HTTP operations:
- HTTP GET exports an application.
- HTTP PUT imports an application.
- HTTP DELETE deletes an application.
The good news is that such a REST API is available today. With the release of APEX 23.1, ORDS (Oracle REST Data Services) introduces a REST API to export and deploy APEX applications.
Fig. 1: Documentation of the ORDS REST API to export and import APEX applications
Prerequisites
To use the REST APIs described in this blog post, ORDS 23.1 or higher is required. Also, when installing or configuring ORDS, make sure to activate the Database API, as described in the ORDS documentation. You can verify using the ORDS config command – the database.api.enabled property must be true.
/.../ordsdir $ dbtools/bin/ords --config config config list
ORDS: Release 23.2 Production on Mon Oct 09 11:34:42 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/.../ordsdir/config/
Database pool: default
Setting Value Source
----------------------- -------------- -----------
database.api.enabled true Global
db.connectionType basic Pool
db.hostname localhost Pool
db.password ****** Pool-Wallet
If the value is false, then the Database API, and also the REST APIs to deploy APEX applications are not available. Enable as follows:
/.../ordsdir $ dbtools/bin/ords --config config config set database.api.enabled true
ORDS: Release 23.2 Production on Mon Oct 09 11:39:46 2023
Copyright (c) 2010, 2023, Oracle.
Configuration:
/.../ordsdir/config/
The global setting named: database.api.enabled was set to: true
We will authenticate to the REST API using database user names and passwords. For this to work, also make sure that the restEnabledSql.active property is also enabled (use same steps as for the database.api.enabled property).
Also, one the database user we want to authenticate with, must be REST Enabled. One option to do that is to use APEX SQL Workshop, RESTful Services. Make sure that the Schema Access shows an Access Status of ENABLED.
Fig. 2: A REST Enabled Schema in SQL Workshop, RESTful Services
If the schema is not REST enabled, enable it either by clicking the Register Schema with ORDS button in APEX, or run the following PL/SQL block using SQL Developer, SQLcl or SQL*Plus while logged in as that database user.
begin
ords.enable_schema;
end;
$ curl -X GET \
-u "CCZARSKI:*****" \
http://localhost:8085/ords/cczarski/_/db-api/stable/apex/workspaces/cczarski/applications/
{
"items" : [
{
"last_updated_on" : "2023-10-09T09:45:33Z",
"application_group" : null,
"links" : [
{
"rel" : "self",
}
],
"availability_status" : "Available with Developer Toolbar",
"last_updated_by" : "CCZARSKI",
"application_alias" : "DEMO-APP",
"application_name" : "Demo App",
"application_id" : 100,
"workspace" : "CCZARSKI",
"application_owner" : "CCZARSKI",
"page_count" : 3
},
{
"last_updated_on" : "2023-07-25T16:01:08Z",
"availability_status" : "Available with Developer Toolbar",
"links" : [
{
"rel" : "self"
}
],
"application_group" : null,
"application_name" : "test APEX-7309",
"application_alias" : "TEST-APEX-7309",
"last_updated_by" : "CCZARSKI",
"page_count" : 7,
:
Authentication is done using a database user and password. The database user (schema) must be REST Enabled. After authentication, we will be able to access applications in all workspaces, which have that very database schema assigned. The request path of the URL is built as outlined in the following table:
| Path | Meaning |
|---|---|
ords/ |
ORDS context root. Determined when ORDS is installed. |
cczarski/ |
The schema alias often equals the schema name and can be looked up in APEX SQL Workshop, RESTful Services on the overview screen under Schema Alias (see Screenshot X above). |
_/db-api/stable/ |
Use current stable version of the Database REST API. |
workspace/cczarski/applications/ |
Return a list of all applications in the CCZARSKI workspace. Note that the database user we authenticated with could be assigned to multiple workspaces, so it’s required to specify the workspace here again. |
Exporting an application via REST
As we’re able to access the Database REST API now, we can perform the first export operation. Let’s export application 100, and we want to have the “classic” APEX export format, the SQL file.
$ curl -X GET \
-u "CCZARSKI:****" \
http://localhost:8085/ords/cczarski/_/db-api/stable/apex/applications/100?export_format=SQL_SCRIPT > f100.sql
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 707k 0 707k 0 0 2702k 0 --:--:-- --:--:-- --:--:-- 2702k
$ cat f100.sql
prompt --application/set_environment
set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
--------------------------------------------------------------------------------
--
-- Oracle APEX export file
--
-- You should run this script using a SQL client connected to the database as
-- the owner (parsing schema) of the application or as a database user with the
-- APEX_ADMINISTRATOR_ROLE role.
--
-- This export file has been automatically generated. Modifying this file is n
:
The /apex/applications/{application ID} endpoint returns “an application”. This endpoint can return three different kinds of output.
- If the export_format Query String parameter is omitted, only a JSON with the most important application attributes is returned.
- If SQL_SCRIPT is provided as the export_format parameter, then an application export is performed and a single SQL file is returned.
- If SQL_ZIP is passed as the export_format, then the application export is split, zipped up and a ZIP file is returned. The ZIP file contains one single file for each application component, and is the preferred format to check into version control systems like Git or SVN.
The API documentation for the /apex/applications/{application ID} endpoint lists even more Query String parameters. For instance, to request an export with “Original IDs” (which is important to get the same internal ID values as that application was imported with before), the request would look as follows:
$ curl -X GET \
-u "CCZARSKI:****" \
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 707k 0 707k 0 0 2702k 0 --:--:-- --:--:-- --:--:-- 2702k
curl -X GET \
-u "CCZARSKI:apex" \
{
"code": "BadRequest",
"title": "Bad Request",
"message": "An error occurred when processing the request. The error message is ORA-20987: APEX - Security Group ID
(your workspace identity) is invalid. - Contact your application administrator.\n. Please review the request before trying again.",
"type": "tag:oracle.com,2020:error/BadRequest",
:
Importing an application using REST
The REST endpoint to import an application looks a bit different. The database user we are authenticating with, can be assigned to multiple workspaces, so we need to specify the workspace to import the application into. Also, when importing an application using the APEX Builder, one can decide to create a new application from the import file, or to replace an existing one. The REST Import provides the same options. Any import is a PUT request to the /apex/workspaces/{workspace_name}/applications/{application_id} endpoint.
Thus the following request imports the application in the file f100.sql (which we exported before) into the CCZARSKI workspace, as an application with ID 14711. Whether the import creates a new application or replaces an existing one, solely depends on the application ID being used in the URL endpoint.
curl -X PUT \
-u "CCZARSKI:****" \
-H "Content-Type:application/sql" \
--data-binary @f100.sql \
"http://localhost:8085/ords/cczarski/_/db-api/stable/apex/workspaces/cczarski/applications/14711
{
"items" : [
{
"application_name" : "Demo App",
"last_updated_by" : "CCZARSKI",
"workspace" : "CCZARSKI",
"application_alias" : "DEMO-APP14711",
"application_group" : null,
"application_owner" : "CCZARSKI",
"application_id" : 14711,
"page_count" : 3,
:
}
]
}
curl -X PUT \
-u "CCZARSKI:****" \
-H "Content-Type:application/sql" \
--data-binary @f100.sql \
"http://localhost:8085/ords/cczarski/_/db-api/stable/apex/workspaces/other-workspace/applications/14711
{
"code": "BadRequest",
"title": "Bad Request",
"message": "An error occurred when processing the request. The error message is ORA-20987: APEX - Invalid workspace ID. -
Contact your application administrator.\n. Please review the request before trying again.",
"type": "tag:oracle.com,2020:error/BadRequest",
"instance": "tag:oracle.com,2020:ecid/w9XnLgLGTXrCwUgMBRl3rg",
:
Deleting an Application using REST
We can also remove an application using the Database REST API. Obviously, we have to perform a DELETE request, and the endpoint is /apex/workspaces/{workspace_name}/applications/{application_id}. As the DELETE endpoint does only return the HTTP-204 (“No Content”) status code, and no request body, we’ll use the -v curl switch to see that information in the console output.
$ curl -v \
-X DELETE \
-u "CCZARSKI:****" \
* TCP_NODELAY set
* Connected to localhost (::1) port 8085 (#0)
* Server auth using Basic with user 'CCZARSKI'
:
< HTTP/1.1 204 No Content
< X-ORDS_DEBUG: true
< X-Frame-Options: SAMEORIGIN
<
* Connection #0 to host localhost left intact
Individual Application Components
The REST API also allows to export individual components of an application: We will use the same endpoint as we used to export the full application. However, now we have to perform a POST request, and the request body will contain the list of components to export.
$ curl -X POST \
-u "CCZARSKI:****" \
-H "Content-Type: application/json" \
-d '{"components": ["PAGE:1","PAGE:2","LOV:3592518833461236"]}' \
"http://localhost:8085/ords/cczarski/_/db-api/stable/apex/applications/100?export_format=SQL_ZIP&with_original_ids=true" > f100_components.zip
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 5587 0 5529 100 58 49810 522 --:--:-- --:--:-- --:--:-- 50333
$ unzip -l f100_components.zip
Archive: f100_components.zip
Length Date Time Name
--------- ---------- ----- ----
1098 10-09-2023 15:19 f100/application/set_environment.sql
1575 10-09-2023 15:19 f100/application/shared_components/user_interface/lovs/lov_rest.sql
393 10-09-2023 15:19 f100/application/pages/delete_00001.sql
1124 10-09-2023 15:19 f100/application/pages/page_00001.sql
393 10-09-2023 15:19 f100/application/pages/delete_00002.sql
5304 10-09-2023 15:19 f100/application/pages/page_00002.sql
228 10-09-2023 15:19 f100/application/end_environment.sql
304 10-09-2023 15:19 f100/install_component.sql
--------- -------
10419 8 files
For the component export, the request body must be a JSON object, which contains the components member. That components member is an array of strings – each string consists of the component type to be exported, followed by a colon (:), and then the component ID. The pretty-printed JSON request body looks as follows:
{
"components": [
"PAGE:1",
"PAGE:2",
"LOV:3592518833461236"
]
}
The APEX dictionary view APEX_APPL_EXPORT_COMPS allows to query all exportable components of an application, as shown in the following screenshot:
Fig. 4: The APEX_APPL_EXPORT_COMPS shows all exportable components of an application
So, after looking up the component types and IDs in the APEX_APPL_EXPORT_COMPS view, we can construct the JSON array of components to be exported and POST that JSON to the export REST API, as shown above.
Importing components is similar to importing a full application. We’re executing a PUT request to the /apex/workspaces/{workspace_name}/applications/{application_id} endpoint; and the request body contains the ZIP or SQL file containing the application components to import.
curl -X PUT \
-u "CCZARSKI:****" \
-H "Content-Type:application/zip" \
--data-binary @f100_components.zip \
{
"items" : [
{
"application_name" : "Demo App",
"last_updated_by" : "CCZARSKI",
"workspace" : "CCZARSKI",
"application_alias" : "DEMO-APP14711",
"application_group" : null,
"application_owner" : "CCZARSKI",
"application_id" : 14711,
"page_count" : 3,
:
}
]
}
As with APEX Application Builder, single components can only be imported into the same application as they were exported from. Importing components into a different application is not supported.
However, the REST API will only throw errors for a component import if the target application is not present or the database user has no privileges to import components into it. Components which cannot be imported into the target application (because they were exported from another application, or because they’re referencing non-existent other components) will silently be ignored.
Using the REST API from APEX: APEX_WEB_SERVICE
A REST API can be used with any client application. Instead of curl, we can use APEX_WEB_SERVICE; and get application exports from another APEX instance!
A PL/SQL block to get an export of an application from an APEX server is very simple and looks as follows. This block references page items for the actual REST Endpoint URL and the application to export. APEX_WEB_SERVICE.MAKE_REST_REQUEST_B performs the actual REST request and the result is stored in the BLOB variable. In this example, that BLOB is simply downloaded, but anything can be done: can be stored into a table or pushed to another REST API.
declare
l_export apex_data_export.t_export;
begin
l_export.content_blob := apex_web_service.make_rest_request_b(
p_url => rtrim( :P1_REST_ENDPOINT_URL, '/')
|| '/_/db-api/stable/apex/applications/'
|| :P1_APP_ID
|| '?export_format=SQL_SCRIPT',
p_http_method => 'GET',
p_username => 'CCZARSKI',
p_password => 'apex' );
if apex_web_service.g_status_code != 200 then
apex_error.add_error(
p_message => 'Export attempt failed with HTTP-' || apex_web_service.g_status_code,
p_display_location => apex_error.c_on_error_page );
else
l_export.mime_type := 'application/sql';
l_export.file_name := 'f' || :P1_APP_ID || '.sql';
apex_data_export.download(
p_export => l_export,
p_content_disposition => 'attachment' );
end if;
end;
Summary
The new REST APIs in ORDS 23.1 and higher can make the deployment automation for APEX applications way simpler than it was before. Neither Oracle Client nor JDBC driver is needed any more in order to perform an automated export; neither SQL*Plus nor SQLcl is required for an automated import. All these things can be done with the REST client of choice, and all through standard HTTP. Moving APEX applications from on-premises to the Cloud (potentially passing several firewalls) becomes as easy as a simple curl invocation.
Additionally, regular exports to check in APEX applications into version control can benefit from these REST APIs. Simply export as a ZIP and then check into the version control of your choice. As all of this is standards-based, such integration can be done using shell scripts, Hudson or Jenkins job or any other programming language. REST APIs are the way to go for exporting and deploying APEX applications.