27 Notes

This section provides additional resources when implementing RDS.

APEX

For more information around building performant APEX applications, refer to the Managing Application Performance section of the APEX App Builder User's Guide.

For full details on developing APEX applications, refer to the APEX documentation.

APEX and Autonomous Databases

Because RDS is built using Oracle Autonomous Data Warehouse (ADW), there are limitations with functionality provided by Oracle Application Express. These limitations are documented at https://docs.oracle.com/en/cloud/paas/autonomous-database/serverless/adbsb/apex-notes-autonomous.html.

Please review Known Limitations and Issues. In general, a known limitation describes a case where a documented capability is not available or does not work as expected in the RDS SaaS environment.

Known Limitations and Issues

Limits on Service Initiated Queries and PL/SQL Blocks

  1. 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).

  2. 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.

APEX and Progressive Web Apps (PWA)

There is an APEX application issue where a login will result in a 401 Authorization Required error. This error can occur for PWA-enabled APEX applications as the result of a bug in APEX. As a workaround, disable the PWA for your APEX application. For additional details on PWA-enabled APEX applications, consult the APEX documentation.

Data Access

Those individuals who have been given access to the APEX UI have unrestricted access to the data available in RDS. It is not possible to further restrict view or data level access.

APEX Roles and Privileges

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 UI Explain Plan

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 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 - 1) 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>));

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.

Replication, Refresh, and CSN_NBR

There are circumstances where inconsistencies arise between source and target schema that are beyond Golden Gate replication’s ability to handle. When this happens, a data refresh from the source is required.

Note:

Only replicated data is affected. There will be no changes made to your extensions.

In any case, this refresh requires an outage. If the problem can be isolated to a single table, then only a partial refresh (single table) is performed. If the problem cannot be isolated to a single table, then the entire schema is refreshed. Any refreshed table will have a null for CSN_NBR in each row, which means previously preserved CSN_NBRs used for purposes such as incremental updates will no longer be valid.  It is also worth noting that the CSN_NBR for seeded data, i.e., initial data load, will be null.

OAUTH Token Scope

Currently ORDS services are authenticated via OAUTH, but if your deployment has stage and prod environments both protected with the same Identity domain, then OAUTH tokens generated in stage also work for prod. If you determine that this degree of accessibility poses a security risk, you may mitigate this risk by implementing a custom ORDS_PREHOOK function to perform additional authentication. See ORDS PRE-HOOK for additional details.

APEX Administration Services

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.

SMTP Services

Only Oracle’s OCI SMTP server is supported for sending mail from the RDS ADW instance using the APEX_MAIL package.

Obtaining RDS Outbound IP Address

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

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

Exporting Query Results in APEX

One can export query results from 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 the export detects a situation where it thinks there would be a duplicate column name in the output file. For example, downloading the results of this query that has two columns named DEPTNO gives a zero byte 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.

CPU Utilization Reporting

CPU capacity is purchased in oCPU units but is reported in ECPU units in the Retail Home Application Dashboard. 4 ECPU units = 1 CPU unit. So, the reported CPU will be 4 times the expected amount. This inconsistency will be rectified in the next release.