17.4.6 Working with REST Data Sources for Oracle Cloud SaaS Applications

Learn about using APEX REST Data Sources with Oracle Cloud SaaS applications.

• About REST Data Source Support for Oracle Cloud SaaS Applications
  Learn about REST Data Source support for Oracle Cloud SaaS applications.
• Creating REST Data Source for Oracle Cloud SaaS Apps
  Learn about creating a REST Data Source for an Oracle Cloud SaaS application.
• REST Data Source Definitions for Oracle Cloud SaaS Apps
  Learn about viewing REST Data Source definitions for an Oracle Cloud SaaS application.
• REST Data Source Runtime Features for Oracle Cloud SaaS Apps
  Learn about Oracle Cloud SaaS application runtime features.
• Filter Parameters for Query By Example and Parent and Child Use Cases
  Create filter parameters for Query By Example and parent and child use cases.
• Working Against a Sandbox
  Associate an APEX application with a sandbox by defining a sandbox name on the Shared Components, Component Settings, Oracle Cloud Applications (SaaS) REST Service page.
• Best Practices for Configuring REST Service Base URL
  Learn about best practices for configuring REST Service Base URL.
• Overriding Default Runtime Headers If Necessary
  Define a REST Data Source parameter to use a later REST framework version.
• Data Profile Column Annotations
  Add Data Profile Column annotations using the Additional Info attribute.

17.4.6.1 About REST Data Source Support for Oracle Cloud SaaS Applications

Learn about REST Data Source support for Oracle Cloud SaaS applications.

Oracle APEX simplifies the process of building APEX applications that query, insert, update, and delete data from Oracle Cloud applications (SaaS) REST Service endpoints. This support includes all REST APIs for:

• Oracle Fusion Cloud Applications business objects (possibly customized by Fusion Application business administrators)
• Oracle Visual Builder application business objects
• Custom apps that use Oracle Application Development Framework (ADF) business components

See Also:

• Oracle Fusion Cloud Applications Suite
• Working with Business Objects in Developing Applications with Oracle Visual Builder
• Oracle Application Development Framework (ADF)

17.4.6.2 Creating REST Data Source for Oracle Cloud SaaS Apps

Learn about creating a REST Data Source for an Oracle Cloud SaaS application.

To create a REST Data Source for an Oracle Cloud SaaS application, run the Create REST Data Source Wizard. When prompted, select the REST Data Source Type, Oracle Cloud Applications (SaaS) REST Service. This topic describes key decision points when running the Create REST Data Source Wizard.

Tip:

For more details on all wizard options, see item Help or see the generic discussion. Creating a REST Data Source.

To create a REST Data Source:

1. Navigate to the REST Data Sources page:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.
   4. Under Data Sources, select REST Data Sources.
2. On the REST Data Sources page, click Create.
   The Create REST Data Source Wizard appears.
3. Method - Select From Scratch and click Next.
4. General:
   1. REST Data Source Type - Select Oracle Cloud Applications (SaaS) REST Service.
   2. Name - Enter a descriptive name for this REST Data Source.
   3. URL Endpoint - Enter the Endpoint URL (starting with http:// or https://) for the REST Data Source.
   4. Click Next.
5. Remote Server - Accept the defaults.

   This step shows how APEX manages the REST API's endpoint URL as the combination of a Base URL and a Service URL Path. To learn more about configuring Base URL and a Service URL Path, see XX.

6. Settings, Pagination Type - Accept the default and click Next.
7. Authentication Required - Choose an existing credential if appropriate, or choose an Authentication Type and enter the relevant details for defining a new credential.
8. Click Create REST Source Manually.
   The REST Data Source is created.

17.4.6.3 REST Data Source Definitions for Oracle Cloud SaaS Apps

Learn about viewing REST Data Source definitions for an Oracle Cloud SaaS application.

You can view a REST Data Source on the REST Data Source page as described in Editing a REST Data Source. A newly created Oracle Cloud SaaS application data source has all the operations the end point supports, a Data Profile reflecting all of its attributes, and a root resource name assigned. Once defined, you can use your REST Data Source in any APEX region while building pages. You can also use it programmatically using the appropriate procedures and functions available in the APEX_EXEC package.

See Also:

APEX_EXECin Oracle APEX API Reference

Operations

If the endpoint supports them all, the maximum set of wizard created operations includes:

• GET rows with filtering, ordering, and pagination support
• GET row by resource key
• POST (insert)
• PATCH (update)
• DELETE

Data Profile

The Data Profile contains a primary key column named APEX$RESOURCEKEY mapped to the REST service's resource key that uniquely identifies each row. This ensures that all REST services work in a consistent way against any endpoint, including ones whose underlying business object has a single-attribute primary keys, a multiple-attribute primary key, or defines an alternative unique key as a resource key. The data profile also has appropriately defined columns for all other business object attributes. Note that some Fusion Application business objects contain hundreds of standard attributes, and some can be customized to increase that number even further. Chances are very high that your APEX application does not need to work with all of these attributes, but the Create REST Data Source Wizard defines a Data Profile column for all of the attributes. You can streamline the amount of data that will be exchanged between the APEX engine and a particular Oracle Cloud REST Data Source app as follows:

• Delete Data Profile columns you are sure you will never need.
• Mark Data Profile columns as not Visible to the APEX engine, without having to delete them.
• Ensure each region uses only the necessary columns by marking the others as Commented Out, by setting their Server-side Condition to Never, or by deleting them from the region's column list or the page items representing them.

APEX only requests the necessary field data for each region. There is no precise upper-limit on the maximum amount of REST endpoint business object attributes you can work with in a single request, but in practice trying to query the data of hundreds of attributes for a single region, you may encounter runtime exceptions. The actual limit will depend on the length of the attribute names involved and the total number of attributes participating in the query.

To ensure the best experience of working with REST endpoints for Fusion Application business objects containing hundreds of attributes, the Create REST Data Source Wizard defines all the data profile columns for the REST service, and then limits the ones marked Visible to a maximum of 150 columns. If your REST endpoint's business object contains custom fields (whose names are suffixed by " _c "), the wizard tries to give priority to include as many of those as possible in the 150 visible columns that it chooses by default. After the REST Data Source is created, at any time you can adjust which data profile columns are visible by editing the data source and updating its Data Profile.

Root Resource Name Setting

Each REST Data Source has a Root Resource Name setting whose value gets automatically inferred by the Create REST Data Source Wizard, but which can be set manually for Oracle Cloud SaaS Apps REST data sources upgraded from previous APEX releases. This setting identifies the case-sensitive name of the root resource for the endpoint.

When working with an endpoint such as .../latest/employees that defines a "top level business object" like an employee, the Root Resource Name will match the name of the REST resource (for example, employees). However, when defining an REST service for a collection of related child objects such as Vacation Requests that are owned by an employee using an endpoint URL such as .../latest/employees/:empid/child/VacationRequests, the Root Resource Name of this Vacation Requests data source would also be employees since it is part of a tree of data that is owned by the root Employee object. The setting value is optional for top-level REST endpoints since APEX can infer the correct root resource name in that case, but for data sources based on nested child resources, the setting is mandatory.

17.4.6.4 REST Data Source Runtime Features for Oracle Cloud SaaS Apps

Learn about Oracle Cloud SaaS application runtime features.

At runtime, a REST Data Dource based on a REST endpoint supports pagination, data filtering, ordering, and batch lost update protection. It supports enabling total results computation and disabling batch DML when needed.

Pagination and Total Results Computation

Both for user interface purposes and REST synchronization, a REST Data Source supports paging through rows returned by the endpoint.

To support showing the end user a pagination display such as 1-20 of 200, go to the Settings tab and set Use Total Results? to Yes. This setting causes your application to show the total number of results without having to retrieve them all. If the particular REST endpoint your are working with does not support pagination, configure its corresponding data source's Use Pagination? to No.

Data Filtering

The APEX engine delegates nearly all of the data filtering operations an end user can perform in your application to the REST endpoint for server-side execution. This includes case insensitive tokenized row search. In addition, as necessary, when defining a region you can specify an External Filter using a Service filter expression. This is a SQL-like predicate using the case sensitive business object attribute names from the REST payload. For reference, the attribute names are what appears in the Selector field of each Data Profile column's definition. The filter language supports AND and OR conjunctions, grouping using parentheses, and operations. For example, a filter might look like the following:

(category like 'SALES%' or (purchaseDate between '2023-07-15' and '2023-07-31'))

You can perform case-insensitive searches by wrapping the attribute name with upper(). For example:

upper(lastName) = 'STAR'

External filters do not support bind variables, but they do support APEX substitution parameters (such as &P3_NAME.). These variables must be used as the right-hand-side of a filter predicate and their values will be automatically single-quoted by the APEX engine when the substituted value is replaced at runtime. For example, an external where clause might resemble the following:

upper(lastName) = &P3_NAME.

Assuming that the P3_NAME page item contains the value CHIP , the REST service will see the external where clause of:

upper(lastName) = 'CHIP'

Data Ordering

The APEX engine delegates data ordering an end user can perform to the REST endpoint for server-side execution. In addition, as necessary, when defining a region based on an REST Data Source, you can specify an External Order Bys clause. The syntax is a comma-separated list of one or more case sensitive attribute names, each suffixed by :asc for ascending order or :desc for descending order. For example, the following external order bys expression would sort ascending by department number and then descending by date hired:

departmentNumber:asc,dateHired:desc

Batch Lost Update Protection

When an end user modifies one or more rows of data from a REST Data Source, APEX enforces lost update protection as an efficient batch operation. For example, if a user inserts four new rows in an interactive grid region, modifies three rows, and deletes two rows, clicking Save causes the APEX engine to perform the lost update protection checks for all nine rows in a single round trip to the REST endpoint.

Batch DML

When an end user modifies one or more rows of data from a REST Data Source, by default the APEX engine saves all the modified rows in a single, efficient batch operation. For example, suppose a user inserts four new rows in an interactive grid region, modifies three rows, and deletes two rows, and then clicks Save. After passing the lost update protection checks mentioned above, the APEX engine sends all nine rows in a single round trip to the REST endpoint. This means that all row modifications succeed as a unit, or else they all fail as a unit. This simplifies application development by avoiding any custom business logic to undo a partially successful transaction.

If you notice the REST endpoint you are working with does not behave as expected using batch DML, you can disable this on the Settings tab. Set Use Batch DML? to No . When batch DML is disabled for a REST Data Source, each delete, insert, and update is done using a separate call to the REST endpoint.

Validation Errors Display as APEX Errors Automatically

When an end user saves changes to a page, any region based on a REST Data Source will automatically report any validation errors as an APEX error. The user therefore sees validation failures raised by server-side business object validation rules or triggers in the same, familar way they are used to seeing error messages defined inside the APEX application itself. Multiple error messages will each appear on a new line in the APEX error message. When working with the error message programmatically, use the four-character string "<br>" as the delimiter between error messages to split the single message into the possibly multiple, separate error messages it may contain.

17.4.6.5 Filter Parameters for Query By Example and Parent and Child Use Cases

Create filter parameters for Query By Example and parent and child use cases.

To easily build cascading select list and Query By Example (QBE) use cases, an APEX REST Data Source lets you define filter parameters on the GET operation related to the Fetch rows database action. These are URL Pattern parameters with a specially formulated parameter name that declaratively configures three aspects of runtime behavior:

• The case-sensitive name of the attribute to filter. For example:

  SomeAttr

• Supported filter operators to use:

  • equals ( eq )
  • case insensitive contains ( contains )
  • case-insensitive starts-with ( startswith )

• The desired behavior when the parameter's value is null:

  • Ignore the filter if the parameter value is null ( ignoreifnull )
  • Return no rows if the parameter value is null ( norowsifnull )
  • Match a null value ( matchifnull )

The filter parameter name has the following format:

attrName_operator$behavior

Cascading List Use Case

For example, on a data source named SubcomponentsForComponent suppose you want to filter on the parent attribute named componentId using the equals operator and return no rows if its value is null, the filter parameter name to define is:

componentId_eq$norowsifnull

When building a page with cascading select lists for P3_COMPONENT_ID and P3_SUBCOMPONENT_ID, the latter can be a select list page item based on a shared component LOV that uses the SubcomponentsForComponent REST Data Source. This LOV can assign the value of the data source's componentId_eq$norowsifnull parameter to the value of page item P3_COMPONENT_ID. Finally, configure P3_COMPONENT_ID as the parent item of the P3_SUBCOMPONENT_ID select list page item in Page Designer and your cascading lists will work as expected.

Query by Example Page Use Case

On a data source named People to perform case insensitive contains matching on firstName and lastName attributes, ignoring the respective filter if its value is null, the two filter parameter names to define are:

• firstName_contains$ignoreifnull
• lastName_contains$ignoreifnull

Back in the query by example page, you can define page items P4_FIRST_NAME and P4_LAST_NAME and configure the two REST Data Source parameters above to get their value from the respective page items. After making sure the P4_FIRST_NAME and P4_LAST_NAME are mentioned in the search results region's Page Items to Submit attribute, you have a working query by example page.

17.4.6.6 Working Against a Sandbox

Associate an APEX application with a sandbox by defining a sandbox name on the Shared Components, Component Settings, Oracle Cloud Applications (SaaS) REST Service page.

When Oracle Fusion Applications business administrators customize the SaaS application's business object data model, they makes their pending modifications in the context of a named, private development area called a sandbox. The changes made in the sandbox are not visible to end users using the production application. Oracle APEX lets you create apps whose Oracle Cloud Apps REST Data Sources can work against the REST endpoints for the business objects undergoing active customization in the sandbox.

Administrators simply configure the appropriate sandbox name in their APEX application using the Shared Components, Component Settings, Oracle Cloud Applications (SaaS) REST Service page as described in Configuring an Oracle Cloud Applications (SaaS) REST Service. After a sandbox name is defined, App Builder uses the REST endpoints reflecting the pending version of the business objects that exists in that sandbox when creating, editing, and executing any REST Data Sources in that application.

This allows you to iteratively develop your Fusion Applications business objects customizations in parallel with an APEX application that needs to work with the customized version of their business object data. If your add new custom objects in the Fusion Apps backend, you can just define a new REST Data Source to use that custom object from the sandbox. If you add new custom attributes to an existing object, APEX will see those custom attributes in the sandbox, too. If you previously defined an APEX REST Data Source for a particular Oracle SaaS REST endpoint, you can easily update it to reflect new customizations you made in the meantime. For example, if you add additional attributes in the sandbox, you can just perform the following steps to iteratively see the new custom attributes in her APEX application as well:

1. Edit the existing REST Data Source, edit the Data Profile, and click Rediscover. APEX presents any new attributes and lets you extend the data profile to include them.

2. Edit an existing region in a page that is using this REST Data Source and choose Synchronize Columns from the context menu. This will include the new columns into the region in question. When performing a Synchronize Columns, if you have marked columns or page items in a region to be Commented Out, then they will remain commented out and only the new fields will be added as uncommented columns or page items as appropriate.

When it's time to publish the sandbox, the you just clear the sandbox name in your APEX app and it instantly begins using the now-published REST endpoint again.

17.4.6.7 Best Practices for Configuring REST Service Base URL

Learn about best practices for configuring REST Service Base URL.

When you build an APEX application, it's common to use distinct development and production environments. When that application makes use of a REST Data Source, you will often want it to use a different REST endpoint URL in DEV than in PROD. This lets you test your app in development without disturbing production end users or production data. The APEX Remote Server feature makes it easy to accommodate this usage pattern when working with REST Data Sources, including Oracle Cloud SaaS application REST Data Sources.

When defining a REST Data Source, you provide the Endpoint URL. APEX manages this URL value as a two-part string. The leading part is the Base URL and it comes from a particular Remote Server definition. The trailing part of the Endpoint URL is the Service URL Path. It's important to understand this concept because APEX makes it easy to change the value of the Remote Server Base URL when you deploy your app to a different environment. The value of the Remote Server Base URL stays sticky to the target deployment environment so you can easily deploy updated version of your app from DEV to PROD and the Remote Server Base URL definition in PROD stays how you configured it for PROD.

Study the URLs of the REST endpoints you need to work with, and how they need to change when switching between DEV and PROD environments. This lets you decide where to split the Endpoint URLs into a changeable Base URL part and the static Service URL Path that is the same in both environments. While running the Create REST Data Source Wizard, on the Remote Server step you can adjust the Remote Server and Service URL Path so the remote server's Base URL encapsulates the leading half of the REST service's endpoint URL that needs to change when deploying your application to another APEX instance. You can then adjust the Service URL Path to retain the trailing half of the service's endpoint URL that will always stay the same across deployments. You can also always adjust how a REST Data Source's endpoint URL is split between base URL and service path URL at a later time, too.

17.4.6.8 Overriding Default Runtime Headers If Necessary

Define a REST Data Source parameter to use a later REST framework version.

By default APEX uses version 6 of the REST framework in its communication with the endpoint. If a particuar REST Data Source you define needs to use a later REST framework version, you can achieve this by defining a module parameter (or operation parameter) of type Header. Name the header REST-Framework-Version and provide the unquoted, overridden version number you need to use as the value of the header. For example:

REST-Framework-Version = 7

By default APEX will configure the appropriate header on REST requests to use the sandbox name you configure in the Shared Components, Component Settings, Oracle Cloud Applications (SaaS) REST Service page of your application if it's value is non-null. If that app-level setting is null, then no header gets sent. If you need to override the app-level sandbox name setting for a particular REST Data Source, then you can define a module (or operation) parameter of type Header to achieve this. Name the header Metadata-Context and set the value to one of the following:

• sandbox="YourOtherSandboxName", assuming the sandbox is named YourOtherSandbox
• Leave the header value blank (or null) as a signal that this particular data source (or operation) should avoid using a sandbox.

17.4.6.9 Data Profile Column Annotations

Add Data Profile Column annotations using the Additional Info attribute.

In addition to a properly configured data type, format mask (for dates), and REST payload selector, you can use the Data Profile Column Additional Info attribute to include a comma-separated list of one or more of the following case-sensitive tag names used to optimize how APEX communicates with the REST Data Source endpoint:

• RemotePK - Omit null remote primary keys from create payloads so the service defaults their values.
• CreateOnly - Exclude column from update payloads.
• ReadOnly - Exclude column from DML payloads.
• HasDefault - Omit a null-valued column from create payload to prioritize the serverside default.
• Required - Not currently enforced but useful to be aware of (and possibly enforced by APEX in the future).