6 Getting Started with Extensions

Overview

Oracle Retail Data Store (RDS) enables you to extend Oracle Retail Cloud Services by developing custom applications, RESTful APIs, and database logic within a controlled, cloud-based environment. These extensions are built in writable schemas, separate from the read-only data replicated from the source systems.

This chapter summarizes the structures, tools, and environment characteristics that shape how you build and deploy extensions in RDS.

Access to Replicated Data

Each cloud service replicates selected operational data into read-only replicate schemas using Oracle GoldenGate. These schemas expose views only; tables are not accessible or modifiable. Replicated objects are accessed using a fully qualified name.

For more details on replication design and schema structure, see Chapter 1.

Custom Schemas and Workspaces

All RDS extensions are implemented within writable schemas. These schemas are distinct from the read-only replicate schemas and are the foundation for all customer-developed applications, services, and custom logic.

There are three types of writable schemas used in RDS:

Primary Custom Schema

Each cloud service that supports RDS includes a primary custom schema:

  • It is writable and associated with an APEX workspace.
  • Customers use it to create APEX applications, RESTful services, PL/SQL packages, and custom tables or views.
  • It is granted SELECT privileges on one or more read-only replicate schemas, allowing access to cloud service data through views.
  • All APEX development takes place within this schema’s workspace.

This is the default environment for customer extensions.

Auxiliary Custom Schemas (Per Cloud Service)

Some cloud services provide one or more auxiliary custom schemas, such as XO_XADMIN or RICS_BDI1:

  • These are read-only and not associated with an APEX workspace.
  • They are provided for organizational or functional separation within a specific cloud service.
  • They do not have access to replicate views or other schemas by default.
  • Customers must explicitly grant privileges from their primary custom schema to access or use these schemas.

These schemas are useful for modular development or isolating specific components of a solution.

Auxiliary APEX Workspaces (RDS_CUSTOM_1, RDS_CUSTOM_2, RDS_CUSTOM_3)

In addition to service-specific schemas, RDS provides three general-purpose writable schemas:

  • These are writable and associated with APEX workspaces, but not tied to any specific cloud service.
  • They start with no privileges—access to replicate views or other schemas must be granted manually.
  • These schemas are ideal for:
    • Sandboxing or experimenting with logic.
    • Restricting access to certain users or development efforts.
    • Refining security by isolating sensitive processes.

They allow advanced users to segment their development environment as needed.

Summary: Schema and Workspace Types

Type Writable APEX Workspace Tied to Cloud Service Notes
Primary Custom Schema Yes Yes Yes Main development environment. SELECT access to all replicated schema
Auxiliary Custom Schema Yes No Yes SELECT access to all replicated schema
Auxiliary APEX Workspaces (RDS_CUSTOM_1/2/3) Yes Yes No General-purpose; no privileges by default

Schema Access and Grants

RDS supports two distinct grant scenarios for enabling access across schemas:

Grants to Custom Objects

Custom tables, views, and packages created in a primary or auxiliary custom schema can be shared with other schemas using standard GRANT statements.

  • These grants must be managed by the object owner.
  • Use cases:
    • Sharing a lookup table with a REST service defined in another schema.
    • Allowing a secondary schema to run a reporting package.

Example: 

GRANT SELECT ON
        <a-custom-mfcs-table> TO xocs_rds_custom;

These grants are not recursive—the grantee cannot grant access to others unless explicitly given the WITH GRANT OPTION.

Grants to Replicated Objects

Access to views in replicate schemas is initially granted to the primary and auxiliary custom schema with the WITH GRANT OPTION. This means the primary schema can grant access to replicated views to auxiliary workspace schemas; that is, RDS_CUSTOM_1, RDS_CUSTOM_2, or RDS_CUSTOM_3.

  • This is how auxiliary APEX workspaces get access to replicated data.
  • No need for intervention from Oracle or additional metadata configuration.

Example: 

GRANT SELECT ON <an-xocs-view> TO rds_custom_1;
Because of the WITH GRANT OPTION, any schema that has access to a replicated view can grant it to another schema. This provides flexibility for customers managing multi-schema solutions or enforcing separation of concerns.

Grants to Auxiliary Workspace Schema

You should encapsulate grants for each Auxiliary workspace in a procedure, one per auxiliary workspace. The owner of the procedure should be one of the product workspace schemas (for example, MFCS), which schema owns which grant procedure is your decision.

Execute permission for each grant procedure should be granted to the appropriate Auxiliary workspace. The procedure should be declared with "authid definer". The later should allow the procedure to be called from RDS_CUSTOM_1/2/3 and achieve the desired result.

Ideally, the customer invokes the procedure periodically using a scheduled job, but it can be invoked in an ad hoc manner. Although we are working on a solution to avoid dropping views which then drops grants, there is no guarantee that we will not drop a replicated view in the future.

Summary Table

Grant Type Object Location Granting Schema Notes
Custom object Primary/Auxiliary Custom / Auxiliary workspace Schema Object owner Explicit grant needed
Replicated object Replicate Schema Any schema with grant option (primary or auxiliary schema) Grants allowed due to WITH GRANT OPTION

Synonym Management

A synonym management API is provided to simplify access to replicated objects. Synonyms allow replicated objects to be referenced without needing to use a fully qualified name. The API has three procedures: one for creating synonyms; one for dropping synonyms; and lastly, one for cleaning up synonyms.

Note:

Synonym management only manages synonyms for replicated objects.

Create Synonyms

The create_synonyms procedure creates synonyms for all replicated views in a selected source schema.

Parameters:

I_source_schema IN VARCHAR2  - Source schema name.
I_target_schema IN VARCHAR2  - Target schema name, if NULL defaults to current_user.
I_identifier    IN VARCHAR2  - Optional prefix for synonym names.
O_status        OUT VARCHAR2 - Return status.

Drop Synonyms

The drop_synonyms will drop all synonyms associated with a selected source schema.

Parameters:

I_source_schema IN VARCHAR2  - Source schema name.
I_target_schema IN VARCHAR2  - Target schema name, if NULL defaults to current_user.
I_identifier    IN VARCHAR2  - Optional prefix for synonym names.
O_status        OUT VARCHAR2 - Return status.

Cleanup Synonyms

The cleanup_synonyms procedure identifies and drops invalid synonyms (pointing to nonexistent objects) for a selected source schema.

Parameters:

I_source_schema IN VARCHAR2  - Source schema name.
I_target_schema IN VARCHAR2  - Target schema name, if NULL defaults to current_user.
O_status        OUT VARCHAR2 - Return status.

Tools for Extension

Oracle APEX

APEX provides a low-code platform for building applications within any custom schema.

  • Each APEX workspace is mapped one-to-one with a primary custom schema.
  • Developers build forms, reports, dashboards, and services through the browser.
  • SQL Workshop provides tools to manage schema objects and write PL/SQL.
  • Database objects can be accessed in replicated and auxiliary schema using fully qualified names.

Oracle REST Data Services (ORDS)

ORDS allows you to expose data and logic as a RESTful services.

  • Services execute within your writable schema and can access both custom and replicate views.
  • Methods supported: GET, POST, PUT, and DELETE.
  • Designed for data-producing (queries, exports) and data-consuming (inserts, updates) services.

Services must return within 300 seconds to avoid timeouts. Bulk data flows should be offloaded to object storage.

Object Storage and DBMS_CLOUD

Bulk data movement is handled through Oracle Object Storage.

  • Export using DBMS_CLOUD.EXPORT_DATA and a writable PAR or credentialed URI.
  • Import using DBMS_CLOUD.COPY_DATA.

Environment Considerations

RDS is a SaaS platform with a distinct development model. While powerful, it comes with important constraints.

  • APEX-Centric Development: Most development is performed through the browser. There is no access to SQL*Developer, SQL*Loader, or shell-level tools.
  • No Native DevOps Tooling: RDS does not include version control, staging environments, or deployment pipelines. Teams must manage the code lifecycle externally.
  • Low-Code + PL/SQL: Applications and APIs are created through APEX and SQL Workshop. Custom code can be hundreds or thousands of lines, but large-scale engineering efforts should be staged externally.
  • Strict Object Boundaries: Only replicate views are accessible across schemas. Custom objects must be explicitly shared using GRANT statements.
  • Single-Schema Workspaces: Each APEX workspace provides access to one primary custom schema only.
  • Resource Constraints: Session, CPU, and runtime limits apply. Resource-intensive tasks should be optimized or offloaded to batch processes.

Prerequisites

To implement any meaningful extensions, you will need to meet the prerequisites listed below. Furthermore, the examples in this chapter can be replicated in your RDS environment. Replicating the examples will both help you understand the development context and provide assurance that the prerequisites have been met prior to starting implementation.

  • A Retail Home instance. Contact your RDS System Administrator for details – Oracle Support does not provide this information.

  • An IDCS Authorization Server host. Contact your RDS System Administrator for details – Oracle Support does not provide this information.

  • An Oracle Cloud account. Contact your RDS System Administrator for details on setting up your Oracle Cloud account.

  • A working knowledge of Oracle Retail Home.

  • A working knowledge of the Oracle Cloud Console (on the web search for Using the Oracle Cloud Console for the latest documentation).

  • Access to an APEX workspace within an RDS tenant (see user management above).

  • Access to a suitable Object Storage service.

  • Access to a suitable object storage bucket. RDS does not automatically come with a customer accessible object storage bucket. Provisioning an object storage bucket for use with RDS is a customer responsibility. Bear in mind, FTS, when available, will not be able to produce usable writable PARs for DBMS_CLOUD.EXPORT_DATA (EXPORT_DATA is expecting a prefix or bucket URI, not an object URI). Readable PARs generated by FTS for importing data into RDS, however, are usable with DBMS_CLOUD.COPY_DATA.

Accessing the APEX UI

You will need access to Retail Home endpoint.

APEX is a browser-based application. You access APEX by navigating to the Retail Home Application Navigator and tapping RDS APEX/ORDS (RDS APEX/ORDS is included in the Application Navigator by default).


accessing apex UI

It is the responsibility of RDS workspace admin to create development user accounts for each user requiring access to one or more APEX workspaces. See the APEX User Management section above for additional details.

Before proceeding:

  1. Verify access to Retail Home

  2. Verify access to the relevant APEX workspaces.