ORDS_EXPORT PL/SQL Package Reference

This section describes how the ORDS_EXPORT package enables users to export REST-enabled objects within a schema.

The ORDS_EXPORT PL/SQL package enables the users to export Oracle REST Data Services (ORDS) metadata as a PL/SQL script which can then be executed to recreate the metadata. This facilitates migration, backup, and deployment of ORDS configurations.

EXPORT_SCHEMA

Format

FUNCTION export_schema(
   p_include_modules       IN BOOLEAN,
   p_include_privileges    IN BOOLEAN,
   p_include_roles         IN BOOLEAN,
   p_include_oauth         IN BOOLEAN,
   p_include_rest_objects  IN BOOLEAN,
   p_include_jwt_profiles  IN BOOLEAN,
   p_include_enable_schema IN BOOLEAN,
   p_export_date           IN BOOLEAN
   p_cleanup_missing_objects IN BOOLEAN DEFAULT FALSE
) RETURN CLOB;

Description

The export_schema function enables the users to export REST modules, templates, handlers, privileges, roles, OAuth clients, REST Objects, and JWT profiles within the CURRENT_SCHEMA.

Table 1 Parameters

Parameter	Description
`p_include_modules`	Specifies whether `modules/templates/handlers/parameters` calls are included in the export. Set the value to `TRUE` to include the calls, otherwise set the value to `FALSE`.
`p_include_privileges`	Specifies whether all privilege calls are included in the export. Set the value to `TRUE` to include the calls otherwise, set the value to `FALSE`.
`p_include_roles`	Specifies whether all the role calls are included in the export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_include_oauth`	Specifies whether all Oauth client calls are included in the export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_include_rest_objects`	Specifies whether all REST-object calls are included in the export. Set the value to `TRUE` to include the calls. Otherwise set the value to `FALSE`.
`p_include_jwt_profiles`	Specifies whether JWT profile call is included in the export. Set the value to `TRUE` to include the calls. Otherwise set the value to `FALSE`.
`p_include_enable_schema`	Specifies whether `ORDS.ENABLE_SCHEMA` call is included in the export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_export_date`	Specifies whether the export date is included in the header export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_cleanup_missing_objects`	Specifies how pre-existing ORDS objects are handled when the export script is executed. When the value is set to `TRUE`, applying the export script to the schema removes any existing ORDS objects that are not included in the script, subject to the following rules: Modules not included in the export script are deleted. REST-enabled object definitions not included in the export script are removed (the REST definitions are dropped), but the underlying database objects are not dropped. Roles not included in the export script and not associated with OAuth clients or JWT profiles are deleted. Privileges that are not included in the export script and not associated with OAuth clients are deleted. Note: Set the following parameters to `TRUE`: `p_include_modules` `p_include_privileges` `p_include_roles` `p_include_rest_objects` and. Set the following parameters to `FALSE`: `p_include_oauth` `p_include_jwt_profiles`

Examples

Example 12-1 Export schema

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT.EXPORT_SCHEMA();
END;

Example 12-2 Customizing content using parameters

The export_schema function enables the users to customize the items going to be included in the output script.

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT.EXPORT_SCHEMA(
    P_INCLUDE_MODULES => TRUE,
    P_INCLUDE_PRIVILEGES => TRUE,
    P_INCLUDE_ROLES => TRUE,
    P_INCLUDE_OAUTH => TRUE,
    P_INCLUDE_REST_OBJECTS => TRUE,
    P_INCLUDE_JWT_PROFILES => FALSE,
    P_INCLUDE_ENABLE_SCHEMA => TRUE,
    P_EXPORT_DATE => FALSE
  );
END;

Example 12-3 Synchronizing changes from a schema to another

The schema export function can be used to keep two schemas synchronized. To promote changes from the current schema to another schema, including new, updated, and deleted objects use the p_cleanup_missing_objects parameter.

DECLARE
  v_Return CLOB;
BEGIN
  v_Return := ORDS_EXPORT.EXPORT_SCHEMA(
    P_INCLUDE_MODULES => TRUE,
    P_INCLUDE_PRIVILEGES => TRUE,
    P_INCLUDE_ROLES => TRUE,
    P_INCLUDE_OAUTH => FALSE,
    P_INCLUDE_REST_OBJECTS => TRUE,
    P_INCLUDE_JWT_PROFILES => FALSE,
    P_INCLUDE_ENABLE_SCHEMA => TRUE,
    P_EXPORT_DATE => TRUE,
    P_CLEANUP_MISSING_OBJECTS => TRUE
  );
END;
/

The generated script includes a call to ORDS.FINALIZE_IMPORT, prunes ORDS objects from the schema (where the script is executed) according to the following rules:

Modules not included in the export script are deleted.
REST-enabled object definitions not included in the export script are removed (the REST definitions are dropped), while the underlying database objects remain unchanged.
Roles not included in the export script and not referenced by OAuth clients or JWT profiles are deleted.
Privileges not included in the export script and not referenced by OAuth clients are deleted. Note: Renaming auto-generated privileges and roles for auto REST-enabled objects is not supported. If the script encounters this condition during execution, It will fail.

EXPORT_MODULE

Format

FUNCTION export_module (
   p_module_name             IN VARCHAR2,
   p_include_enable_schema   IN BOOLEAN ,
   p_include_privs           IN BOOLEAN ,
   p_privs_with_other_mod_refs   IN BOOLEAN DEFAULT FALSE,
   p_export_date             IN BOOLEAN DEFAULT TRUE
) RETURN CLOB;

Description

The export_module function exports a specified module, including templates/handlers, roles and privilege, and reference to other modules secured with the same privilege.

Table 2 Parameters

Parameter	Description
`p_module_name`	Specifies the name of the module to be exported. The exported module includes the defined templates, handlers, and parameters.
`p_include_enable_schema`	Specifies whether the `ORDS.ENABLE_SCHEMA` call is included in the export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_include_privs`	Specifies whether the privilege and role associated with the module are included in the export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_privs_with_other_mod_refs`	whether the exported privileges include references to modules other than the one specified in `p_module_name`. Set the value to `TRUE` for privilege definitions to include references to all modules. Otherwise, set the value to `FALSE` for privilege definitions to only refer to the module being exported.
`p_export_date`	Specifies if the date when the export was made is included in the header export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.

Examples

Example 12-4 Export REST module

The export_module function enables the users to use the name of the module. By default, the exported code includes the following:

Module definition
Templates and handlers contained in the module
Privilege and role associated to the module
Call to REST enable the schema
Export Date

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT.EXPORT_MODULE(
    P_MODULE_NAME => 'module_1'
  );
END;

Example 12-5 Export customizing content using parameters

The export_module function enables the users to customize which items are included in the output script.

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT.EXPORT_MODULE(
    P_MODULE_NAME => 'module_1'
    P_INCLUDE_ENABLE_SCHEMA => TRUE,
    P_INCLUDE_PRIVILEGES => TRUE,
    p_PRIVS_WITH_OTHER_MOD_REFS => TRUE,
    P_EXPORT_DATE => FALSE
  );
END;

EXPORT_OAUTH_CLIENT

Format

FUNCTION export_oauth_client (
   p_client_name                            IN VARCHAR2,
   p_include_security_definitions           IN BOOLEAN,
   p_export_date                            IN BOOLEAN DEFAULT TRUE
) RETURN CLOB;

Description

The export_oauth_client function exports OAuth clients by name from the current schema provided the schema has been previously REST-enabled.

Export can include:

Privilege and role associated with the OAuth Client
Export generation date

Table 3 Parameters

Parameter	Description
`p_client_name`	Specifies the name of OAuth client to be exported.
`p_include_security_definitions`	Specifies whether the privilege and role associated with the OAuth client are included in the export. Set the value to `TRUE` to include them. Otherwise, set the value to `FALSE`.
`p_export_date`	Specifies whether the export date is included in the header of the export file. Set the value to `TRUE` to include it. Otherwise, set the value to `FALSE`.

Example 12-6 Exporting OAuth Client

The export_oauth_client function enables the users to select which items are included in the output script.

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT.EXPORT_OAUTH_CLIENT(
    P_CLIENT_NAME => 'client_1',
    P_INCLUDE_SECURITY_DEFINITIONS => TRUE,
    P_EXPORT_DATE => FALSE,
  );
END;