Previous Next JavaScript must be enabled to correctly display this content

## 18.5.7.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.

Parent topic: Working with REST Data Sources for Oracle Cloud SaaS Applications