16 Migrating from mod_plsql to ORDS

This chapter demonstrates how a mod_plsql application is migrated to Oracle REST Data Services (ORDS).

Oracle REST Data Services is a Java EE-based alternative for Oracle HTTP Server and mod_plsql. An Oracle HTTP Server mod_plsql application can be migrated to ORDS by defining new ORDS configuration files. The mod_plsql database resources such as before procedures, after procedures, request validation functions, owa_custom packages, doc upload procedures and doc tables require no change when you are migrating to ORDS.

Topics:

16.1 Oracle HTTP Server mod_plsql Authentication

Oracle HTTP Server mod_plsql applications are configured in a database access descriptor (DAD) file.

The following example mod_plsql application provides the methods to authenticate the requests against the Oracle Database:
  • Basic authentication: The username and password are stored in the DAD file and so the end user is not required to log in. This method is useful for web pages that provide public information.
  • Basic dynamic authentication: The users provide credentials in a browser HTTP basic authentication dialog box. The only way to log out is to close all the instances of the browser.
  • Custom authentication: Enables applications to invoke a user-written authentication function to authenticate the users within the application and not at the database level.

Parent topic: Migrating from mod_plsql to ORDS

16.2 Example Oracle HTTP Server DAD file

This section provides an example Oracle HTTP Server DAD file.

The following dads.conf file includes three locations demonstrating the basic, basic dynamic and custom authentications and the following directives:
  • PlsqlBeforeProcedure
  • PlsqlAfterProcedure
  • PlsqlRequestValidationFunction
  • PlsqlDocumentTablename
  • PlsqlDocumentProcedure

Example 16-1 dads.conf file


# ============================================================================
#                     mod_plsql DAD Configuration File
# ============================================================================
<Location /pls/basic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
  PlsqlDocumentTablename            privileged_user.doc_table
  PlsqlDocumentProcedure            privileged_user.upload
</Location>
<Location /pls/basic_dynamic_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           Basic
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>
<Location /pls/custom_auth>
  SetHandler pls_handler
  Order deny,allow
  Allow from all
  AllowOverride                     None
  PlsqlDatabaseUsername             PRIVILEGED_USER
  PlsqlDatabasePassword             passwordF0R$0RD5Example
  PlsqlDatabaseConnectString        oracle-ee:1521:ORCLPDB1 ServiceNameFormat
  PlsqlAuthenticationMode           CustomOwa
  PlsqlBeforeProcedure              sample_plsql_app_metadata.beforeProc
  PlsqlAfterProcedure               sample_plsql_app_metadata.afterProc
  PlsqlRequestValidationFunction    sample_plsql_app_metadata.validationFunc
</location>

Parent topic: Migrating from mod_plsql to ORDS

16.3 Mapping mod_plsql Settings to ORDS

This section shows the mappings of mod_plsql settings to ORDS.

ORDS allows you to specify configuration files that are similar to a location defined in an Oracle HTTP Server mod_plsql DAD file. Each configuration file is defined in ords_conf/ords/conf directory and the configuration file is then mapped to a particular URL using the ords_conf/ords/url-mapping.xml file. ORDS provides the following configurable parameters that can be used when migrating mod_plsql directives:

Table 16-1 Mappings of mod_plsql Directives to ORDS Settings

mod_plsql Setting ORDS Setting Description
PlsqlDatabaseUserName db.username

Specifies the username to use to log in to the database.

ORDS and mod_plsql are equivalent.
PlsqlDatabasePassword db.password

Specifies the password to use to log in to the database.

ORDS and mod_plsql are equivalent.
PlsqlDatabaseConnectString

Multiple Settings such as:

  • db.hostname

  • db.port

  • db.servicename

  • db.sid

Specifies the connection to an Oracle database.

ORDS and mod_plsql are equivalent.
PlsqlAuthenticationMode security.requestAuthenticationFunction

Specifies the authentication mode to use to allow access.

When security.requestAuthenticationFunction is not specified, ORDS behavior is same as Basic mode of mod_plsql.

When security.requestAuthenticationFunction is specified, ORDS can perform the same action as example dad directive PlsqlAuthenticationMode CustomOwaof mod_plsql.

Example ORDS equivalent configuration parameter:

<entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>

ORDS and mod_plsql are equivalent.
PlsqlBeforeProcedure procedure.preProcess

Specifies the procedure to be invoked before calling the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlAfterProcedure procedure.postProcess

Specifies the procedure to be invoked after calling the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlRequestValidationFunction security.requestValidationFunction

Specifies an application-defined PL/SQL function that can allow or disallow further processing of the requested procedure.

ORDS and mod_plsql are equivalent.
PlsqlDocumentTablename owa.docTable

Specifies the table in the database to which all documents are uploaded.

ORDS and mod_plsql are equivalent.

PlsqlDocumentProcedure N/A

Specifies the procedure to call when a document download is initiated.

In ORDS the document procedure is the requested resource. It is not defined in the configuration file.

ORDS and mod_plsql are equivalent.
PlsqlDocumentPath N/A

ORDS has no equivalent.
PlsqlDefaultPage misc.defaultPage

Specifies the default procedure to call if none is specified in the URL.

ORDS and mod_plsql are equivalent.
PlsqlErrorStyle debug.printDebugToScreen

Specifies the error reporting mode for mod_plsql errors.

debug.printDebugToScreen is equivalent to PlsqlErrorStyle DebugStyle, otherwise there is no equivalent.

ORDS and mod_plsql are equivalent.
PlsqlExclusionList security.exclusionList

Specifies a pattern for procedures, packages, or schema names which are forbidden to be directly run from a browser.

ORDS and mod_plsql are equivalent.

See Understanding Configurable Parameters.

PlsqlIdleSessionCleanupInterval jdbc.InactivityTimeout

Specifies the time (in minutes) in which the idle database sessions should be closed and cleaned.

Value can be 0 to N seconds. Where, 0 (default) means that the idle connections are not removed from pool.

ORDS and mod_plsql are equivalent.
PlsqlMaxRequestsPerSession jdbc.MaxConnectionReuseCount

Specifies the maximum number of requests a pooled database connection should service before it is closed and re-opened.

Default value is 1000.

ORDS and mod_plsql are equivalent.
PlsqlInfoLogging N/A See Understanding Configurable Parameters.
PlsqlLogDirectory N/A See Understanding Configurable Parameters.
PlsqlLogEnable N/A See Understanding Configurable Parameters.
PlsqlSessionStateManagement N/A

Specifies how package and session state should be cleaned up at the end of each request.

ORDS always performs: dbms_session.modify_package_state(dbms_session.reinitialize) at the end of each request.
PlsqlAlwaysDescribeProcedure N/A

Specifies whether the mod_plsql application should describe a procedure before trying to run it.

ORDS always describes procedure on first access, and then the definition is cached. Changes in signature are detected and recached.
PlsqlConnectionValidation N/A

Specifies the mechanism the mod_plsql module should use to detect terminated connections in its connection pool.

ORDS always validates connections on borrow.
PlsqlFetchBufferSize N/A

Specifies the number of rows of content to fetch from the database for each trip, using either owa_util.get_page or owa_util.get_page_raw.

ORDS materializes results as a 32K VARCHAR or CLOB if results are greater than 32K, so not applicable.
PlsqlNLSLanguage N/A

Specifies the NLS_LANG variable.

ORDS, Java, and JDBC use unicode.
PlsqlTransferMode N/A

PlsqlTransferMode specifies the transfer mode for data from the database back to the mod_plsql application.

ORDS always uses unicode.
PlsqlBindBucketLengths N/A

Specifies the rounding size to use while binding the number of elements in a collection bind.

Rarely used in mod_plsql, and JDBC has no equivalent concept.
PlsqlBindBucketWidths N/A

Specifies the rounding size to use while binding the number of elements in a collection bind.

Rarely used in mod_plsql and JDBC has no equivalent concept.
PlsqlCacheCleanupTime N/A ORDS has no equivalent.
PlsqlDMSEnable N/A ORDS does not support DMS.
PlsqlSessionCookieName N/A ORDS does not offer session management for PL/SQL Gateway calls.
PlsqlCacheDirectory N/A ORDS has no equivalent.
PlsqlCacheEnable N/A ORDS has no equivalent.
PlsqlCacheMaxAge N/A ORDS has no equivalent.
PlsqlCacheMaxSize N/A ORDS has no equivalent.
PlsqlCacheTotalSize N/A ORDS has no equivalent.
PlsqlCGIEnvironmentList N/A ORDS has no equivalent.
PlsqlConnectionTimeout N/A ORDS has no equivalent.
PlsqlPathAlias N/A ORDS has no equivalent.
PlsqlPathAliasProcedure N/A ORDS has no equivalent.
PlsqlUploadAsLongRaw N/A ORDS has no equivalent.

Parent topic: Migrating from mod_plsql to ORDS

16.4 Example ORDS Configuration Files

The following sections show how the example mod_plsql application can be migrated to ORDS.

Topics:

Parent topic: Migrating from mod_plsql to ORDS

16.4.1 Example Configuration File for Basic Authentication

Example 16-2 ords_conf/databases/basic_auth/pool.xml


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>
    <entry key="db.username">PRIVILEGED_USER</entry>
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- http://localhost:8086/ords/pls/basic_auth/sample_plsql_app.sample_public_proc-->
    <!-- http://localhost:8086/ords/pls/basic_auth/sample_plsql_app.privileged_public_proc-->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
    <entry key="owa.docTable">sample_plsql_app.doc_table</entry>
</properties>

Parent topic: Example ORDS Configuration Files

16.4.2 Example Configuration File for Basic Dynamic Authentication

Example 16-3 ords_conf/databases/basic_dynamic_auth/pool.xml


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>    
    <!--  NOTE THAT IF THIS USER HAS EXECUTE PRIVILEGE ON THE RESOURCE THEN jdbc.auth.enabled IS IGNORED -->
    <!--  IF THIS USER DOES NOT HAVE EXECUTE PRIVILEGE ON THE RESOURCE THEN jdbc.auth.enabled IS INVOKED AND THE CREDENTIALS OF A PRIVILEGED USER HAS TO BE PROVIDED-->
    <entry key="db.username">NON_PRIVILEGED_USER</entry> 
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <entry key="jdbc.auth.enabled">true</entry>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- INVOKE jdbc.auth.enabled :   http://localhost:8086/ords/pls/basic_dynamic_auth/sample_plsql_app.sample_privileged_proc -->
    <!-- IGNORE jdbc.auth.enabled :   http://localhost:8086/ords/pls/basic_dynamic_auth/sample_plsql_app.sample_public_proc   -->
    <!-- Because jdbc.auth.enabled is ignored when referencing the sample_public_app, the beforeProc,afterProc and validationFunc must be accessible by NON_PRIVILEGED_USER -->
    <!-- The following objects are executed by the same credentials used to access the resource -->
    <!-- If the resource can be accessed by the db.username then that connection is used to access these methods -->
    <!-- If the resource cannot be accessed by the db.username then jdbc.auth.enabled is invoked and those credentials as used to access these methods -->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
</properties>

Parent topic: Example ORDS Configuration Files

16.4.3 Example Configuration file for Custom Authentication

Example 16-4 ords_conf/databases/custom_auth/pool.xml


<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
    <comment>Saved on Wed Jul 25 10:22:37 UTC 2018</comment>
    <entry key="db.username">PRIVILEGED_USER</entry>
    <entry key="db.password">!passwordF0R$0RD5Example</entry>
    <!-- Example url -->
    <!-- See url-mapping.xml -->
    <!-- http://localhost:8086/ords/pls/custom_auth/sample_plsql_app.sample_proc -->
    <!-- privileged_user.owa_custom.authorize requires the following as the custom login -->
    <entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>
    <entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>
    <entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>
    <entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>
</properties>

Parent topic: Example ORDS Configuration Files

16.5 Example ORDS URL Mapping

This section shows the example mapping between base-path url and the configuration files.

Example 16-5 ords_conf/databases/basic_auth/paths

/pls/basic_auth

Example 16-6 ords_conf/databases/basic_dynamic_auth/paths

/pls/basic_dynamic_auth

Example 16-7 ords_conf/databases/custom_auth/paths

/pls/custom_auth

Parent topic: Migrating from mod_plsql to ORDS

16.6 Example ORDS Default Configuration

This section shows the example default configuration setting for ORDS.

The settings.xml file provides the database connection details used by all configurations.

Note:

To turn off procedure validation caching, set security.maxEntries value to 0. This is necessary to emulate Oracle HTTP Server mod_plsql.

Example 16-8 ords_conf/global/settings.xml

<?xml version = '1.0' encoding = 'UTF-8'?>
<!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
   <!-- by default security.maxEntries = 2000 which means 2000 procedures validity will be cached-->
   <!-- this is fine for applications like apex where the validation of a procedure does not change  -->
   <!-- for applications migrating from mod_plsql the cache should be disabled so that procedures validity is determined for each request -->
   <!-- this is done by setting security.maxentries to 0 -->
   <entry key="security.maxEntries">0</entry>
   <entry key="db.hostname">oracle-ee</entry>
   <entry key="db.port">1521</entry>
   <entry key="db.servicename">orclpdb1</entry>
</properties>

Parent topic: Migrating from mod_plsql to ORDS

16.7 Oracle REST Data Services Functionality

Oracle REST Data Services is a J2EE-based servlet which offers increased functionality including a web-based configuration, enhanced security, and file caching.

The following sections explains the different functionalities equivalent to mod_plsql module.

Parent topic: Migrating from mod_plsql to ORDS

16.7.1 Basic Authentication

This section describes the basic authentication implemented using ORDS.

The database credentials are specified in the ORDS configuration file. The db.username must have the required privileges to access the resources.

Note:

The entry security.requestAuthenticationFunction is not specified.

Parent topic: Oracle REST Data Services Functionality

16.7.2 Basic Dynamic Authentication

This section describes how basic dynamic authentication is implemented using ORDS.

A default db.username and db.password must be specified in ORDS configuration file when providing basic dynamic authentication for accessing the resources.

The resources that cannot be accessed using this type of authentication can be accessed if the following conditions are satisfied:
  • The value for <entry key="jdbc.auth.enabled">true</entry> entry must be true..
  • The security.requestAuthenticationFunction entry must not be specified.
  • When ORDS response prompts a Basic HTTP Authentication dialog box in a browser, the credentials provided by the user must have the required privileges, then the resource is made available.

    Note:

    If the credentials are provided through the browser HTTP authentication dialog box, then the only way to log out is to close all the instances of the browser.

Parent topic: Oracle REST Data Services Functionality

16.7.3 Custom Authentication

This section describes how custom authentication is implemented using ORDS.

A function is specified to perform the custom authentication. This function has access to the owa variables. Resources are only available if the following function returns a TRUE value:

<entry key="security.requestAuthenticationFunction">privileged_user.owa_custom.authorize</entry>

The authentication function must have signature as shown in the following code snippet:
/**
* OWA_CUSTOM used in mod_plsql when the following is used in the dad configuration file
  PlsqlAuthenticationMode       Custom
  In ORDS environment this can reside in any schema as long as the connection has execute privileges
  In mod_plsql this has to reside in the connections schema as you cannot specify the name of the schema,package or function
  ex: PlsqlAuthenticationMode           CustomOwa
*/
CREATE OR REPLACE PACKAGE OWA_CUSTOM  AS
/**
 * Response:
  >IF Failed
   WWW-Authenticate in response header
   Authorization Required
   You are not authorized to access the requested resource. Check the supplied credentials (e.g., username and password).
 */
FUNCTION authorize RETURN BOOLEAN;
END OWA_CUSTOM ;
/

Parent topic: Oracle REST Data Services Functionality

16.8 ORDS Features

This section describes the ORDS features that are useful when you are migrating from a mod_plsql application to ORDS.

Topics:

Parent topic: Migrating from mod_plsql to ORDS

16.8.1 Request Validation Function

This section explains the use of request validation function.

The request validation function restricts the access to resources. The request validation function is provided with the name of the resource being requested and returns TRUE or FALSE value in response.

If the request validation function returns a FALSE value, then ORDS terminates the request.

Example 16-9 security.requestValidationFunction

<entry key="security.requestValidationFunction">sample_plsql_app_metadata.validationFunc</entry>

You can choose any name for the validation function. However, the signature must be in the following format:

CREATE OR REPLACE FUNCTION validationfunc(procedure_name VARCHAR2) RETURN BOOLEAN IS.

Parent topic: ORDS Features

16.8.2 Pre Process Feature

This section describes the procedure.preProcess ORDS configuration parameter.

The procedure.preProcess ORDS configuration parameter allows a comma delimited list of procedures that are executed before the requested resource.

Example 16-10 procedure.preProcess

Following example code snippet shows a use case for logging in:

<entry key="procedure.preProcess">sample_plsql_app_metadata.beforeProc</entry>

Parent topic: ORDS Features

16.8.3 Post Process Feature

This section describes the procedure.postProcess ORDS configuration parameter.

The procedure.postProcess ORDS configuration parameter allows a comma delimited list of procedures that are executed after the requested resource.

Example 16-11 procedure.postProcess

Following example code snippet shows a use case for logging out:

<entry key="procedure.postProcess">sample_plsql_app_metadata.afterProc</entry>

Parent topic: ORDS Features

16.8.4 File Upload Feature

This section describes the ORDS file upload feature.

The ORDS configuration parameter owa.docTable, defines the table name where the uploaded files persist.

Example 16-12 Table upload

CREATE TABLE DOC_TABLE (
     NAME               VARCHAR(256)   UNIQUE NOT NULL,
     MIME_TYPE          VARCHAR(128),
     DOC_SIZE           NUMBER,
     DAD_CHARSET        VARCHAR(128),
     LAST_UPDATED       DATE,
     CONTENT_TYPE       VARCHAR(128),
     CONTENT            LONG RAW,
     BLOB_CONTENT       BLOB );

Example 16-13 Procedure upload

You can choose to have any name for the upload function. However, the signature must match the following POST request:


--The parameters of the procedure should match the parameters of the request
--The procedure is called after ORDS performs the file upload/insert.
--This procedure can rollback the file INSERT as it is in the same transaction as the INSERT
CREATE OR REPLACE PROCEDURE upload (filename VARCHAR2 DEFAULT NULL)

Example 16-14 Curl command for file upload

curl -i -X POST -F 'filename=@helloworld.txt' "http://localhost:8086/ords/pls/basic_auth/example_user1.upload

Parent topic: ORDS Features

16.8.5 Cross-Origin Resource Sharing Feature

This section describes the Cross-Origin Resource Sharing (CORS) feature.

By default ORDS does not allow cross-origin calls to its PL/SQL gateway.

Trusted origins can be configured through the security.externalSessionTrustedOrigins configuration parameter that defines a comma separated list of origins that are trusted to make CORS request. If this parameter is empty or not configured, then no CORS requests are allowed for the PL/SQL gateway and results in a 403 Unauthorized status.

<entry key="security.externalSessionTrustedOrigins">http://example.com, https://example.com:8443</entry>

Parent topic: ORDS Features

16.8.6 Procedure Allow List

This section describes the Allow List feature for PL/SQL Gateway procedures.

Oracle REST Data Services (ORDS) provides an Allow List feature for PL/SQL Gateway procedures.You can authorize execution of custom procedures by adding them to the Allow List.

Parent topic: ORDS Features

16.8.6.1 Configuring ORDS PL/SQL Gateway Allow List

This section describes how to configure the ORDS PL/SQL Gateway Allow List.

Ensure that your PL/SQL Gateway pool is configured to use the ORDS validation function named ords_util.authorize_plsql_gateway.
./ords config --db-pool <plsql_pool> get security.requestValidationFunction
If PL/SQL Gateway pool is empty, then it is setup using the following command:
./ords config --db-pool <plsql_pool> set security.requestValidationFunction ords_util.authorize_plsql_gateway

Note:

To use the feature ORDS PL/SQL Gateway Allow list with APEX, the APEX must be installed in a Pluggable Database.

Parent topic: Procedure Allow List

16.8.6.1.1 Authorizing Procedures

This section describes how to authorize the stored procedures.

To authorize the stored procedures you must add them to the PL/SQL Gateway Allow List using the ords_admin.add_plsql_gateway_procedure procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure.
BEGIN
        ords_admin.add_plsql_gateway_procedure(
                    p_owner => 'MY_SCHEMA',
                    p_package_name => 'MY_PACKAGE', /* Can be null if not a package procedure*/
                    p_procedure_name => 'MY_STORED_PROCEDURE',
                    p_comments => 'Enabling access to Project 1'); /* Optional comments*/
END;

The Allow List stores resolved procedure names. Procedures are resolved before adding them to the list.

16.8.6.1.2 Removing Stored Procedures

This section describes how to remove the stored procedures from the PL/SQL Gateway Allow List.

To remove the stored procedures from the PL/SQL Gateway Allow List use ords_admin.remove_plsql_gateway_procedure procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure. 

BEGIN
        ords_admin.remove_plsql_gateway_procedure(
                    p_owner => 'MY_SCHEMA',            /* Schema owning the stored procedure */
                    p_package_name => 'MY_PACKAGE',    /* Can be null if not a package procedure*/
                    p_procedure_name => 'MY_STORED_PROCEDURE');
END;
16.8.6.1.3 Removing Stored Procedures in Bulk

This section describes how to remove the stored procedures in bulk from the PL/SQL Gateway Allow List.

To remove the stored procedures from the PL/SQL Gateway Allow List use ords_admin.clear_plsql_gateway_procedures procedure. You are required to have ORDS_ADMINISTRATOR_ROLE role to execute the procedure. 

BEGIN
        ords_admin.clear_plsql_gateway_procedures(
                                    p_owner => 'MY_SCHEMA'); /* Remove all procedures owned by this schema. */
END;

16.8.7 Monitoring the Allowed Procedures

To monitor which procedures have been allowed, administrator user with ORDS_ADMINISTRATOR_ROLE can use DBA_PLSQL_GATEWAY_ALLOW_LIST view.
select * from DBA_PLSQL_GATEWAY_ALLOW_LIST

Parent topic: ORDS Features

16.9 Modifying Synonyms

When you are invoking synonyms through PL/SQL Gateway, ORDS executes the procedure they point to. If an existing synonym is redefined to point to a second procedure, then revoke the EXECUTE privilege from the first procedure to force the synonym to reload and ensure that ORDS executes the second procedure.

Parent topic: Migrating from mod_plsql to ORDS