24 Additional Notes

This chapter groups implementation guidance under the following categories:

  • Not Supported in RDS: Capabilities that are not available in the RDS environment.

  • Limited in RDS Environment: Capabilities that exist but have important constraints.

  • Workarounds: Features that do not work as expected but can be supported with additional effort.

  • Internal Use Only: Implementation details visible to developers but not intended for use.

Not Supported/Available in RDS

APEX Roles and Privileges Not Supported

APEX roles and privileges are not available for RDS services. Any attempt to attach privileges to a service will result in that service becoming inaccessible. When invoking the service, the ORDS services container will respond with a 401, authorization required.

APEX Workspace Administration Disabled

APEX Administration services are not available. For example, you will not be able to create or remove workspaces. You will, however, be able to manage users as described in APEX User Management.

GoldenGate Source/Target Use Not Supported

Use of RDS as a Golden Gate source is not supported. The use of RDS as a Golden Gate target beyond pre-configured retail cloud services is not supported.

PWA-Enabled APEX Apps Not Supported

PWA-enabled APEX applications are not supported in RDS due to a known APEX bug that results in a 401 Authorization error. Disable the PWA setting in your application as a workaround.

No Support for SFTP

There is no support for using SFTP. The workaround is to use Object Store.

Preconfigured Schema and Workspace Model

There is no support for the creation of new workspaces, users, or schema. The workspaces and schema are pre-configured and immutable.

Immutable Privilege Model for Replicated Objects

The baseline privilege model for replicated database objects cannot be changed. Select access to replicated data is granted automatically within primary custom schemas.

Individuals with access to a primary custom schema through the APEX UI are automatically granted select access to all replicated data in RDS. It is not possible to further restrict view- or row-level access within a primary custom schema.

Individuals with access only to an auxiliary custom schema (RDS_CUSTOM_1/2/3) must be explicitly granted select on the desired replicated views. Without such grants, they will not have access to any replicated data.

OAuth Scope Enforcement Not Supported

RDS does not support OAuth scope-based access control for IDCS client apps. Scopes in the token are not enforced.

Limited in RDS Environment

Service-Initiated Queries Limited to 300 Seconds

Service-initiated queries and PL/SQL blocks (that is, the service source) must complete in less than 300 seconds. This limitation is a standard timeout and not configurable. Queries and blocks of longer durations must be run asynchronously and report results using other approaches (that is, an output table populated by one service and queried by another, output to object storage, and so on).

Service Source Length Limited to 4000 Characters

Service source is limited to 4000 characters. Character count for GET oriented queries can be reduced using views without sacrificing the automatic to-JSON translation.

Only OCI SMTP Server Supported

To send email from your RDS ADW instance using the APEX_MAIL package, you must provision an OCI SMTP server. Only Oracle’s OCI SMTP server is supported for sending email from RDS ADW.

APEX Limitations in Autonomous Database

Because RDS is built using Oracle Autonomous Data Warehouse (ADW), there are limitations with functionality provided by Oracle Application Express. These limitations are documented in Oracle APEX Limitations on Autonomous Database.

Workarounds

Explain Plan via APEX UI Requires Workaround

Explain Plan in the APEX UI does not work as expected. Explain plan requires select access on replicated TABLES. The APEX user, however, only has access to replicated views by design. See the Explain Plan prerequisites in SQL Language Reference: Explain Plan. Nevertheless, you can still access the explain plan functionality using a workaround.

Steps for this workaround:

  1. Perform your query against the database - it may be helpful if you include a hint so that you can later find the SQL_ID for the query when performing step 2. For example we've added a hint with DEMO_WORKAROUND so that it's easy to find our query: 

    select /* +DEMO_WORKAROUND */ item, item_desc from rds_wv_item_master where status = 'A';

  2. Next, find the SQL_ID for the query you ran in step 1. In our example, this returned two rows that had the actual query from step 1 and the other the one we executed against v$sqlarea. We want the non-v$sqlarea one of those listed: 

    select sql_id, sql_text from v$sqlarea where upper(sql_text) like '%DEMO_WORKAROUND%';

  3. Next issue the following command to get the execution plan:

    select * from table(dbms_xplan.display_cursor(<your SQL_ID>));

Exporting Query Results in APEX

One can export query results using APEX > SQL Workshop > SQL Commands. There is, however, a known issue where the resulting export is a zero-length file. This problem can occur when there are duplicate column names in the output file. For example, downloading the results of this query, which has two columns named DEPTNO, gives a zero-length file: 

select e.deptno, d.deptno from emp e, dept d where d.deptno = e.deptno

The export thinks DEPTNO is duplicated in the output and fails. Downloading a variant of the query that has all unambiguous column names works as expected: 

select e.deptno as emp_deptno, d.deptno from emp e,dept d where d.deptno = e.deptno

Note that the following query is also problematic and will fail:

select * from emp e, dept d where d.deptno = e.deptno

More generally, when joining results, you will need to use AS <your-output-column-name> to manually disambiguate output column names.

Enhancing OAuth Security with ORDS Prehook

ORDS services are authenticated through OAUTH, but if your deployment has stage and production environments that are both protected with the same identity domain, then OAUTH tokens generated for stage also work for production. If you determine that this degree of accessibility poses a security concern, you may mitigate this risk by implementing a custom ORDS_PREHOOK function to perform additional authentication. See ORDS PRE-HOOK for additional details. Host names for production and non-production environments have distinct signatures.

Retrieving RDS Outbound IP Address

If the RDS outbound IP address is required for ingress allowlisting, it can be found with the following query:

select json_value(cloud_identity, '$."OUTBOUND_IP_ADDRESS"[\*]') FROM v$pdbs ;

Internal Use Only

Reserved Application ID Range

Oracle has reserved the APEX application ID range from 30001-39999 for future enhancements. Use of APEX application IDs within this range in custom code may result in undefined behavior.

GoldenGate Metadata Fields Are Not CDC

You might notice TARGET_COMMIT_DATETIME, LAST_DM, and CSN_NBR columns on replicated views. These columns are hidden on the source but visible on the target. These columns are for internal use only by GoldenGate. They are not to be confused with change data capture support.