This blog was originally published on April 21, 2021.
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. So we need a REST API, that …
- … exports an application by invoking a URL with HTTP GET.
- … imports an application by sending the export file via POST to a URL.
- … deletes an application by sending a DELETE request to a URL.
- … is protected by modern Authentication flows like OAuth2 Client Credentials.
This blog posting works with APEX 19.2 or higher, and will walk you through building, configuring and securing a simple “Application Export and Import” REST API. The REST API will enable exporting and importing applications from and to workspaces assigned to the database schema the API is installed in. However, if the database schema has the APEX_ADMINISTRATOR_ROLE assigned, the REST API will allow working with applications in all workspaces within the APEX instance.
Once the REST endpoints are installed and running, it should be easy and straightforward to extend for custom needs.
1. Building the PL/SQL Package
Recent APEX and ORDS releases contain all the building blocks we need.
- The APEX_EXPORT PL/SQL package allows to programmatically export an application
- The APEX_APPLICATION_INSTALL package contains the INSTALL procedure to programmatically import an APEX export file.
- ORDS provides the functionality to set up REST endpoints and to protect these with the OAuth2 Client Credentials authentication flow.
The procedure interfaces of the APEX_EXPORT and APEX_APPLICATION_INSTALL packages are not very suitable to be directly invoked from a REST handler. So we need to implement a “wrapper package”, with a more simple API.
Such a simplified API could look as in the following APEX_APPS_REST package – to install the code into your workspace schema, simply copy and paste and run it using either SQL*Plus, SQLcl, SQL Developer or with APEX SQL Workshop, SQL Scripts.
--=============================================================================
-- Wrapper package for ORDS Export / Import API
--
-- Contains procedures to be called by ORDS handlers for exporting or
-- importing an application. This package encapsulates all logic to map
-- the invocation of the ORDS handler to APEX_EXPORT or APEX_APPLICATION_INSTALL
-- package invocations.
--=============================================================================
create or replace package apex_apps_rest
is
--
-- This is the name of the ORDS REST Module
--
c_ords_module_name constant varchar2(16) := 'apex.apps.expimp';
--=============================================================================
-- exports an application or application components, as SQL or ZIP file.
--
-- Parameters:
-- * p_application_file Application ID to be exported; append ".zip" or ".sql"
-- to determine the file type.
-- * p_components Only export the specified components; use syntax
-- of APEX_EXPORT.GET_APPLICATION procedure; components
-- separated by comma.
-- * p_mimetype mimetype of the expected target file. Supports .sql or .zip
-- and .json in the future. Overrides the suffix specified
-- in p_application_file.
--=============================================================================
procedure export(
p_application_file in varchar2,
p_components in varchar2,
p_mimetype in varchar2 );
--=============================================================================
-- imports an application or application components, as SQL or ZIP file.
--
-- Parameters:
-- * p_export_file Export file
-- * p_mimetype Mime Type of the export file, to determine whether
-- this is ZIP or SQL
-- * p_application_id Import file as this application ID
-- * p_to_workspace if provided, import into this workspace
--=============================================================================
procedure import(
p_export_file in blob,
p_mimetype in varchar2,
p_to_workspace in varchar2 default null,
p_application_id in number default null );
--=============================================================================
-- deletes an application.
--
-- Parameters:
-- * p_in_workspace if provided, delete application in this workspace
-- * p_application_id Application ID to be deleted; extension will be ignored.
--=============================================================================
procedure delete(
p_in_workspace in varchar2 default null,
p_application_id in number );
end apex_apps_rest;
/
The EXPORT, IMPORT and DELETE procedures of this package are simple enough to be exposed as a REST API.
- EXPORT:
Based on the P_MIMETYPE parameter, the export is provided as SQL or as a ZIP file. If P_COMPONENTS is not passed, the procedure exports the whole application, otherwise it returns application components. - IMPORT:
Imports the BLOB which is passed in. The P_MIMETYPE argument indicates whether a ZIP or a SQL file was passed in. Since the database schema where this code runs in might be mapped to multiple APEX workspaces, the procedure allows to optionally pass in the workspace name also (P_TO_WORKSPACE). - DELETE:
This one is simple – it just deletes the specified application. As for the IMPORT procedure, there is a parameter to pass in the workspace, if required (P_IN_WORKSPACE).
The following code contains the implementation of the package.The logic is pretty simple: it does not do much more than preparing parameters and invoking APEX_EXPORT or APEX_APPLICATION_INSTALL.
--=============================================================================
-- Package implementation
-- (scroll down within the code window to walk through)
--=============================================================================
create or replace package body apex_apps_rest
is
LF constant varchar2(1) := chr( 10 );
--=============================================================================
-- Helper Function: Convert a CLOB to a BLOB
--=============================================================================
function clob_to_blob(
p_clob in clob )
return blob
is
l_blob blob;
l_dstoff pls_integer := 1;
l_srcoff pls_integer := 1;
l_lngctx pls_integer := 0;
l_warn pls_integer;
begin
sys.dbms_lob.createtemporary(
lob_loc => l_blob,
cache => true,
dur => sys.dbms_lob.call );
sys.dbms_lob.converttoblob(
dest_lob => l_blob,
src_clob => p_clob,
amount => sys.dbms_lob.lobmaxsize,
dest_offset => l_dstoff,
src_offset => l_srcoff,
blob_csid => nls_charset_id( 'AL32UTF8' ),
lang_context => l_lngctx,
warning => l_warn );
return l_blob;
end clob_to_blob;
--=============================================================================
-- Helper Function: Convert a BLOB to a CLOB
--=============================================================================
function blob_to_clob(
p_blob in blob )
return clob
is
l_clob clob;
l_dstoff pls_integer := 1;
l_srcoff pls_integer := 1;
l_lngctx pls_integer := 0;
l_warn pls_integer;
begin
sys.dbms_lob.createtemporary(
lob_loc => l_clob,
cache => true,
dur => sys.dbms_lob.call );
sys.dbms_lob.converttoclob(
dest_lob => l_clob,
src_blob => p_blob,
amount => sys.dbms_lob.lobmaxsize,
dest_offset => l_dstoff,
src_offset => l_srcoff,
blob_csid => nls_charset_id( 'AL32UTF8' ),
lang_context => l_lngctx,
warning => l_warn );
return l_clob;
end blob_to_clob;
--=============================================================================
-- split filename to file name and extension
--=============================================================================
procedure split_filename(
p_full_filename in varchar2,
p_filename out varchar2,
p_extension out varchar2 )
is
begin
if instr( p_full_filename, '.' ) > 0 then
p_filename := substr( p_full_filename, 1, instr( p_full_filename, '.' ) - 1 );
p_extension := lower( substr( p_full_filename, instr( p_full_filename, '.' ) + 1 ) );
else
p_filename := p_full_filename;
end if;
end split_filename;
--=============================================================================
-- sets workspace to specified workspace, or to first workspace assigned to
-- current schema
--=============================================================================
procedure set_workspace( p_workspace in varchar2 )
is
begin
if p_workspace is not null then
apex_util.set_workspace( p_workspace );
else
for w in (
select workspace
from apex_workspaces
where rownum = 1 )
loop
apex_util.set_workspace( w.workspace );
end loop;
end if;
end set_workspace;
--=============================================================================
-- Public API, see specification
--=============================================================================
procedure delete(
p_in_workspace in varchar2 default null,
p_application_id in number )
is
begin
set_workspace( p_workspace => p_in_workspace );
apex_application_install.remove_application( p_application_id => p_application_id );
end delete;
--=============================================================================
-- Public API, see specification
--=============================================================================
procedure export(
p_application_file in varchar2,
p_components in varchar2,
p_mimetype in varchar2 )
is
l_files apex_t_export_files;
l_filename varchar2(255);
l_extension varchar2(255);
l_components apex_t_varchar2;
l_blob blob;
l_as_zip boolean;
begin
split_filename(
p_full_filename => p_application_file,
p_filename => l_filename,
p_extension => l_extension );
l_as_zip := case when p_mimetype is null
then coalesce( l_extension = 'zip', false )
else coalesce( lower( p_mimetype ) = 'application/zip', false )
end;
if p_components is not null then
l_components := apex_string.split( ltrim(rtrim( p_components ) ) , ',' );
end if;
l_files := apex_export.get_application(
p_application_id => to_number( l_filename ),
p_components => l_components,
p_split => l_as_zip );
sys.dbms_lob.createtemporary(
lob_loc => l_blob,
cache => true,
dur => sys.dbms_lob.call );
if l_as_zip then
for i in 1 .. l_files.count loop
apex_zip.add_file (
p_zipped_blob => l_blob,
p_file_name => l_files(i).name,
p_content => clob_to_blob( l_files(i).contents ) );
end loop;
apex_zip.finish( l_blob );
sys.owa_util.mime_header( 'application/zip', false );
else
l_blob := clob_to_blob( l_files(1).contents );
sys.owa_util.mime_header( 'application/sql', false );
end if;
sys.htp.p( 'Content-Length: ' || sys.dbms_lob.getlength( l_blob ) );
sys.htp.p( 'Content-Disposition: attachment; filename=' || l_filename || '.' || case when l_as_zip then 'zip' else 'sql' end );
sys.owa_util.http_header_close;
sys.wpg_docload.download_file( l_blob );
end export;
--=============================================================================
-- Public API, see specification
--=============================================================================
procedure import(
p_export_file in blob,
p_mimetype in varchar2,
p_to_workspace in varchar2 default null,
p_application_id in number default null )
is
l_files apex_t_export_files := apex_t_export_files();
l_zip_files apex_zip.t_files;
--
l_dstoff pls_integer := 1;
l_srcoff pls_integer := 1;
l_lngctx pls_integer := 0;
l_warn pls_integer;
begin
set_workspace( p_workspace => p_to_workspace );
if lower( p_mimetype ) = 'application/zip' then
l_zip_files := apex_zip.get_files(
p_zipped_blob => p_export_file,
p_only_files => true );
l_files.extend( l_zip_files.count );
for i in 1 .. l_zip_files.count loop
l_files( i ) := apex_t_export_file(
l_zip_files( i ),
blob_to_clob(
apex_zip.get_file_content(
p_zipped_blob => p_export_file,
p_file_name => l_zip_files( i ) ) ) );
end loop;
else
l_files.extend(1);
l_files( 1 ) := apex_t_export_file( 'import-data.sql', blob_to_clob( p_export_file ) );
end if;
apex_application_install.set_application_id(
p_application_id => p_application_id );
apex_application_install.install(
p_source => l_files,
p_overwrite_existing => true );
end import;
end apex_apps_rest;
/
2. Creating the ORDS REST Module
With SQLcl or SQL*Plus, we could start using this package right now. However, that is not the goal of the exercise – instead we’ll now use the ORDS package in order to build a REST Module with REST Handlers to import, export and delete applications. These ORDS handlers will just call into the new APEX_APPS_REST package.
--=============================================================================
-- Set up the ORDS REST Module and its handlers
-- (scroll down within the code window to walk through)
--=============================================================================
begin
ords.enable_schema;
end;
/
sho err
--
-- delete the module if it already exists, to make this script re-runnable.
--
begin
ords.delete_module(
p_module_name => apex_apps_rest.c_ords_module_name );
exception
-- ignore errors ...
when others then null;
end;
/
sho err
begin
ords.define_module(
p_module_name => apex_apps_rest.c_ords_module_name,
p_base_path => 'deploy/app/' );
----------------------------------------------------------------------------
-- Export Handler for the full application
--
-- Parameters:
-- * app_id (URL) ID of the application to export
-- * Accept (Request Header) format in which to return the export file
--
-- Example:
--
-- curl -X GET
-- -H "Accept: application/sql
-- http://localhost:8080/ords/schema/deploy/app/102
----------------------------------------------------------------------------
ords.define_template(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_file' );
ords.define_handler(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_file',
p_method => 'GET',
p_source_type => ords.source_type_plsql,
p_source =>
q'~begin
apex_apps_rest.export(
p_application_file => :app_file,
p_components => null,
p_mimetype => null );
end;~' );
ords.define_parameter(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_file',
p_method => 'GET',
p_name => 'Accept',
p_bind_variable_name => 'ACCEPT',
p_source_type => 'HEADER' );
----------------------------------------------------------------------------
-- Export Handler for application components
--
-- Parameters:
-- * app_id (URL) ID of the application to export
-- * Accept (Request Header) format in which to return the export file
-- * (Request Body) components to export, as outlined in the documentation
-- for APEX_EXPORT.GET_APPLICATION. Components separated
-- by comma.
--
-- Example:
--
-- curl -X POST
-- -H "Accept: application/sql
-- -d 'PAGE:1,PAGE:2'
-- http://localhost:8080/ords/schema/deploy/app/102/components
----------------------------------------------------------------------------
ords.define_template(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/components' );
ords.define_handler(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/components',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source =>
q'~begin
apex_apps_rest.export(
p_application_file => :app_id,
p_components => :body_text,
p_mimetype => :accept );
end;~' );
ords.define_parameter(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/components',
p_method => 'POST',
p_name => 'Accept',
p_bind_variable_name => 'ACCEPT',
p_source_type => 'HEADER' );
----------------------------------------------------------------------------
-- Import Handler
-- curl -X POST
-- -H "Content-Type: tapplication/octet-stream"
-- --data-binary @f101.sql
-- http://localhost:8080/ords/schema/deploy/app/102
--
-- Parameters:
-- X-Target-Workspace - HTTP Header
----------------------------------------------------------------------------
ords.define_template(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/' );
ords.define_handler(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source =>
q'~begin
apex_apps_rest.import(
p_application_id => :app_id,
p_mimetype => :content_type,
p_to_workspace => :workspace,
p_export_file => :body );
end;~' );
ords.define_parameter(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_id/',
p_method => 'POST',
p_name => 'X-Target-Workspace',
p_bind_variable_name => 'WORKSPACE',
p_source_type => 'HEADER' );
----------------------------------------------------------------------------
-- Delete Handler
-- curl -X DELETE
-- http://localhost:8080/ords/schema/deploy/app/102
--
-- Parameters:
-- X-Target-Workspace - HTTP Header
----------------------------------------------------------------------------
ords.define_handler(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_file',
p_method => 'DELETE',
p_source_type => ords.source_type_plsql,
p_source =>
q'~begin
apex_apps_rest.delete(
p_application_id => :app_file,
p_in_workspace => :workspace );
end;~' );
ords.define_parameter(
p_module_name => apex_apps_rest.c_ords_module_name,
p_pattern => ':app_file',
p_method => 'DELETE',
p_name => 'X-Target-Workspace',
p_bind_variable_name => 'WORKSPACE',
p_source_type => 'HEADER' );
end;
/
--
-- the COMMIT is important.
commit
/
3. Testing the new REST API
Now we have installed the PL/SQL package, as well as the ORDS REST Handlers. We can now do a first test by calling the REST Handler to export an application. The following example assumes that application 101 exists in the APEX workspace, which is mapped to the database schema where the package and REST API are installed.
Use the curl utility to export application 101 and to store the contents in the f101.sql file. Of course, other REST clients like Postman will also work. For simplicity, this blog posting is based on curl examples.
$ curl -X GET http://localhost:8080/ords/schema/deploy/app/101 > f101.sql % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed 100 1369k 0 1369k 0 0 491k 0 --:--:-- 0:00:02 --:--:-- 491k $ more f101.sql prompt --application/set_environment set define off verify off feedback off whenever sqlerror exit sql.sqlcode rollback -------------------------------------------------------------------------------- -- -- ORACLE Application Express (APEX) export file -- -- You should run the script connected to SQL*Plus as the Oracle user -- APEX_200200 or as the owner (parsing schema) of the application. -- -- NOTE: Calls to apex_application_install override the defaults below. -- -------------------------------------------------------------------------------- begin wwv_flow_api.import_begin ( p_version_yyyy_mm_dd=>'2020.10.01' ,p_release=>'20.2.0.00.20' ,p_default_workspace_id=>1303680926490695 ,p_default_application_id=>101 ,p_default_id_offset=>6096020459023782 ,p_default_owner=>'SCHEMA' ); end; / prompt APPLICATION 101 - Sample Geolocation Showcase -- -- Application Export: -- Application: 101 -- Name: Sample Geolocation Showcase -- Exported By: SCHEMA -- Flashback: 0 :
We can now call the Import REST Handler to import the f101.sql file, as a new application with the ID 256.
$ curl -X POST \
-H"Content-Type: application/sql" \
--data-binary @f101.sql \
http://localhost:8080/ords/schema/deploy/app/256/
After this curl invocation completed, we have the application twice in our workspace.
Let’s delete that copy of the application now.
$ curl -X DELETE http://localhost:8080/ords/schema/deploy/app/256
The REST API also allows to only export application components: The following example exports pages 21 and 22 of application 101, as a ZIP file.
$ curl --output f101_pages.zip \
-X POST \
-H"Accept: application/zip" \
-H"Content-Type: text/plain" \
-d 'PAGE:21,PAGE:22' \
http://localhost:8080/ords/schema/deploy/app/101/components
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 11894 0 11879 100 15 17316 21 --:--:-- --:--:-- --:--:-- 17312
$ unzip f101_pages.zip
Archive: f101_pages.zip
:
inflating: f101/application/pages/delete_00021.sql
inflating: f101/application/pages/page_00021.sql
inflating: f101/application/pages/delete_00022.sql
inflating: f101/application/pages/page_00022.sql
inflating: f101/application/end_environment.sql
inflating: f101/install_component.sql
The new REST API appears to be functional. However, before actually using it, we want to protect it in order to only allow authenticated access.
4. Protecting the REST API
ORDS allows to protect REST APIs with the OAuth2 Client Credentials out-of-the-box. First, we need to create an ORDS role (not a database role) and to assign that role to the REST Module we created before. As a result, all handlers in that module are now protected by that role.
declare
l_roles sys.owa.vc_arr;
l_modules sys.owa.vc_arr;
l_patterns sys.owa.vc_arr;
begin
ords.create_role(
p_role_name => 'apex.apps.deployment.role');
l_roles(1) := 'apex.apps.deployment.role';
l_modules(1) := 'apex.apps.expimp';
ords.define_privilege(
p_privilege_name => 'apex.apps.expimp.priv',
p_roles => l_roles,
p_patterns => l_patterns,
p_modules => l_modules,
p_label => 'apex.apps.expimp privilege.',
p_description => 'Protects the apex.apps.expimp module.',
p_comments => null );
end;
/
commit
/
We can clearly see that the module is now protected: Unauthenticated curl requests are not accepted any more.
$ curl -X GET -I http://localhost:8080/ords/schema/deploy/app/101 HTTP/1.1 401 Unauthorized Content-Type: text/html Content-Length: 16358
Next, we need to create an OAuth2 Client, which allows us to authenticate to ORDS. For this, ORDS provides the OAUTH package. First, the APEX Apps Deployment Client is created, then the apex.apps.deployment.role role (which we created above) is assigned to that client.
begin
oauth.create_client(
p_name => 'APEX Apps Deployment Client',
p_grant_type => 'client_credentials',
p_owner => 'APEX Owner',
p_description => 'This is to authenticate for the deployment APIs',
p_support_email => 'deployment@mycompany.com',
p_privilege_names => 'apex.apps.expimp.priv');
oauth.grant_client_role(
p_client_name => 'APEX Apps Deployment Client',
p_role_name => 'apex.apps.deployment.role' );
end;
/
commit
/
Credentials for the OAuth2 Client Credentials flow are generated and can be retrieved from the USER_ORDS_CLIENTS view.
With all this in place, we can now follow the OAuth2 Client Credentials flow to first get an access token, and then we can do the actual REST call using the access token for authentication.
1. Get the access token:
$ curl -X POST -d'grant_type=client_credentials' \
-u'{client-id}:{client-secret}' \
-H"Content-Type:application/x-www-form-urlencoded" \
http://localhost:8080/ords/schema/oauth/token
{"access_token":"kKJQ2EzZMh7PaBDdTiw5sw","token_type":"bearer","expires_in":3600}
2. Perform the actual REST call using the access token:
$ curl -X GET \
-H"Authorization:Bearer kKJQ2EzZMh7PaBDdTiw5sw" \
http://localhost:8080/ords/schema/deploy/app/101 > f101.sql
% Total % Received % Xferd Average Speed Time Time Time Current
Dload Upload Total Spent Left Speed
100 1369k 0 1369k 0 0 491k 0 --:--:-- 0:00:02 --:--:-- 491k
$ more f101.sql
prompt --application/set_environment
set define off verify off feedback off
whenever sqlerror exit sql.sqlcode rollback
--------------------------------------------------------------------------------
--
-- ORACLE Application Express (APEX) export file
:
We now have protected the REST API; applications can only be exported, imported or deleted after providing correct authentication credentials.
5. Summary
In this blog posting we have shown how to build an “Application Deployment” REST API using ORDS and some custom PL/SQL code on top of the APEX_EXPORT and APEX_APPLICATION_INSTALL API packages. The setup allows to export and import APEX applications using standard REST tools like curl, Postman or others. JDBC or SQL*Plus connections are no longer required for automated application deployments.
This is just a simple example – the API can easily be extended to control supporting objects installation or whether to include translations, public or private reports. Also the shown authentication setup is only the most simple approach – ORDS would also allow to distribute the functionality to multiple REST modules and to have different roles, clients and credentials for exporting or importing.
Further Reading:
- APEX PL/SQL API: APEX_EXPORT
- APEX PL/SQL API: APEX_APPLICATION_INSTALL
- ORDS PL/SQL API: ORDS
- ORDS PL/SQL API: OAUTH
- ORDS Documentation