ORDS_EXPORT_ADMIN PL/SQL Package Reference

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

The ORDS_EXPORT_ADMIN package enables users to export REST-enabled objects within a schema with the ORDS_ADMINISTRATOR_ROLE role. Administrators can selectively export specific REST-related artifacts using the provided schema name and additional parameters. This capability is particularly useful for documenting and migrating REST services as it ensures that only the necessary objects are included.

The following sections explains on how the parameters are used to provide fine-grained control over the export process and cater to various administrative needs.

ords_export_admin.export_schema

Format

FUNCTION ords_export_admin.export_schema(
  p_schema                  IN VARCHAR2,
  p_include_modules         IN BOOLEAN DEFAULT TRUE,
  p_include_privileges      IN BOOLEAN DEFAULT TRUE,
  p_include_roles           IN BOOLEAN DEFAULT TRUE,
  p_include_oauth           IN BOOLEAN DEFAULT TRUE,
  p_include_rest_objects    IN BOOLEAN DEFAULT TRUE,
  p_include_jwt_profiles    IN BOOLEAN DEFAULT TRUE,
  p_include_enable_schema   IN BOOLEAN DEFAULT TRUE,
  p_export_date             IN BOOLEAN DEFAULT TRUE,
  p_runnable_as_admin       IN BOOLEAN DEFAULT TRUE
  p_cleanup_missing_objects IN BOOLEAN DEFAULT FALSE
)
RETURN CLOB;

Description

ords_export_admin.export_schema function is only valid for exporting the schemas that are previously REST-enabled.

Table 1 Parameters

Parameter	Description
`p_schema`	Specifies the name of the REST-enabled schema you want to export.
`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 privileges call 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_object`	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 date when the export is made is included in the header export. Set the value to `TRUE` to include the calls, otherwise set the value to `FALSE`.
`p_runnable_as_admin`	Specifies whether Public or Admin packages are used in the exported script. Set the value to `TRUE` if the exported script is planned to run as an administrator user targeting another schema, otherwise set the value to `FALSE` if current user is the target schema.
`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 specified 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 definition is dropped), but the underlying database objects are not dropped. Roles not included in the export script and not associated to 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 13-1 Exporting with defaults

Following is an example of exporting the schema with all the boolean parameters set as default to TRUE. The exported script contains all the objects.

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT_ADMIN.EXPORT_SCHEMA(
    P_SCHEMA => 'TEST_SCHEMA'
  );
END;

Example 13-2 Exporting with parameters

Following example uses all the optional parameters to indicate:

Not to include the JWT profiles in the export script
Output script does not have the exported date (useful to run the differences)

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT_ADMIN.EXPORT_SCHEMA(
    P_SCHEMA => 'TEST_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,
    P_RUNNABLE_AS_ADMIN => TRUE
  );
END;

Example 13-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 specified 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_ADMIN.EXPORT_SCHEMA(
	P_SCHEMA => <SCHEMA_NAME>,
	P_INCLUDE_OAUTH => FALSE,
	P_INCLUDE_JWT_PROFILES => FALSE,
    P_CLEANUP_MISSING_OBJECTS => TRUE
  );
END;
/

The generated script includes a call to ORDS_ADMIN.FINALIZE_IMPORT, that prunes ORDS objects from the specified schema using 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.

ords_export_admin.export_module

Format

FUNCTION ords_export_admin.export_module(
    p_schema                    IN VARCHAR2,
    p_module_name               IN VARCHAR2,
    p_include_enable_schema     IN BOOLEAN DEFAULT TRUE,
    p_include_privleges         IN BOOLEAN DEFAULT TRUE,
    p_privs_with_other_mod_refs IN BOOLEAN DEFAULT TRUE,
    p_export_date               IN BOOLEAN DEFAULT TRUE,
    p_runnable_as_admin         IN BOOLEAN DEFAULT FALSE
)
RETURN CLOB;

Description

ords_export_admin.export_module function is only valid for exporting modules by name in schemas that are previously REST-enabled.

Table 2 Parameters

Parameter	Description
`p_schema`	Specifies the name of the REST-enabled schema containing the module you want to export.
`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 a call to `enable_schema` is included in the export. Set the value to `TRUE` to include the call, otherwise set the value to `FALSE`.
`p_include_privileges`	Specifies whether the privileges and roles 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`	Specifies whether the privileges being exported include references to modules other than the module 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 will be included in the header export. Set the value to `TRUE` to include the calls. Otherwise, set the value to `FALSE`.
`p_runnable_as_admin`	Specifies whether public or admin packages are used in the exported script. Set the value to `TRUE` if the exported script is planned to run as an administrator user targeting another schema. Otherwise, set the value to `FALSE` if the current user is the target schema.

Examples

Following is an example of exporting the module in a schema using only the required parameters (using defaults for the rest).

Example 13-4 Exporting with defaults

The exported script contains the following:

Call to REST enable the schemaDefinition of roles and privileges
The date when the export was run (export date)
Calls to ORDS public (non-admin) packages

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT_ADMIN.EXPORT_MODULE(
    P_SCHEMA => 'TEST_SCHEMA',
    P_MODULE_NAME => 'module_1'
  );
END;

Example 13-5 Exporting with parameters

Following is an example using all the optional parameters to:

Include call to enable_schema
Include roles and privileges
Include reference to other modules protected by privilege
Not include export_date
Use the ORDS Admin packages in the code

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT_ADMIN.EXPORT_MODULE(
    P_SCHEMA => 'TEST_SCHEMA',
    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,
    P_RUNNABLE_AS_ADMIN => TRUE
  );
END;

ords_export_admin.export_oauth_client

Format

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

Description

ords_export_admin.export_oauth_client function is valid only for exporting OAuth clients by name in schemas that are previously REST-enabled.

Table 3 Parameters

Parameter	Description
`p_schema`	Specifies the name of the REST-enabled schema containing the OAuth Client you want to export.
`p_client_name`	Specifies the OAuth Client’s name for which to generate code.
`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 if the date when the export was made will be included in the header export. Set the value to `TRUE` to include it. Otherwise, set the value to `FALSE`.
`p_runnable_as_admin`	Specifies whether public or admin packages are used in the exported script. Set the value to `TRUE` if the exported script is planned to run as an administrator user targeting another schema. Otherwise, set the value to `FALSE` if the current user is the target schema.

Examples

Following is an example of exporting an OAuth client in a schema using only the required parameters (using defaults for the rest):

Example 13-6 Exporting with Defaults

The exported scripts contain the following:

Includes Privilege and Role associated with the OAuth client
The date when the export was run (export date)
Calls to ORDS admin packages

DECLARE
  v_exported_script CLOB;
BEGIN
  v_exported_script := ORDS_EXPORT_ADMIN.EXPORT_OAUTH_CLIENT(
    P_SCHEMA => 'TEST_SCHEMA',
    P_CLIENT_NAME => 'client_1'
  );
END;

Example 13-7 Exporting with Parameters

Following is an example using all the optional parameters to:

Include Privilege and Role associated with the OAuth client
Not include the date when the export was run (export date)
Use the ORDS public (non admin) packages in the code

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