Use REST Data Sources to access to REST services or generic JSON data feeds in applications and use the data Oracle APEX components.

Oracle APEX provides direct integration of REST Data Sources in classic reports, interactive reports, CSS Calendar, and JET Charts. A REST Data Source can contain one or many Operations which are the references to a concrete external web service. Configurations at the REST source level are shared across all contained operations.

Oracle APEX supports the following built-in REST Data Sources types:

• Simple HTTP - Select this option for a simple HTTP data feed. Oracle APEX assumes that all data is returned with the first request and that the server does not support server-side filtering, ordering, or other advanced REST service feature. All invocation details and parameters must be configured manually.

• Oracle REST Data Services - Denotes Oracle REST Data Services (ORDS). These REST services follow the Oracle REST standard, which means that GET, POST, PUT, or DELETE operations are standardized. Server-side filtering and ordering are supported. Oracle APEX can leverage these features by delegating report order-bys and filters to the REST service.

• REST Enabled SQL Query - Denotes a REST Source on top of a SQL Query executed on a REST Enabled SQL reference. Oracle APEX supports filtering and pagination, but no DML on these endpoints.

  Advantages of the REST Enabled SQL Query type include:

  • Provides a REST Source for a dedicated SQL query to all components in the APEX application.
  • Components can simply use the REST Source and do not have to replicate the remote SQL query.
  • REST Source Synchronization enables you to download data from a REST Enabled SQL Source to a local table.

• Oracle Cloud Applications (SaaS) REST Service - Denotes RESTful web services provided by Oracle Cloud SaaS Applications. Use this web service type to integrate Oracle SaaS REST Services into your Oracle APEX application. Oracle Oracle APEX supports pagination for these services.

  See Working with REST Data Sources for Oracle Cloud SaaS Applications

• Oracle Cloud Infrastructure (OCI) REST Service - Denotes RESTful web services provided by Oracle Cloud Infrastructure. Use this web service type to integrate OCI REST Services (for example, accessing the Object Store) into your Oracle APEX application. Oracle APEX supports pagination for these Services.

• OData REST Service - Denotes a REST service which implements the Open Data (OData) protocol, which is an ISO/IEC approved, OASIS standard that defines a set of best practices for building and consuming RESTful APIs.

  Note:

  Oracle APEX currently only supports the Fetch APIs (read data); DML operations for OData are not yet supported.

A Data Profile describes how a Data Source format is being parsed and converted to rows and columns.

The Data Profile of a REST Data Source contains information about how the response of a REST API must be parsed in order to extract data. A Data Profile stores the following information:

• The Response Format, that is, whether a REST Source returns data in JSON, XML or CSV formats.
• Information about Array Columns. An Array Column within the response contains a set of rows.
• Data Profile Columns store information about its name, the data type and the name of the attribute within the response from the REST API.

The example that follows shows a sample Data Profile named emp with a Format of JSON.

Description of the illustration data_profile.png

APEX uses the information within the data profile to automatically parse the response and to provide the data in rows and columns, using the configured data types so that APEX components, such as reports, charts, calendars or others, can use them.

The Row Selector of the data profile determines which XML or JSON attribute contains the set of rows. APEX makes that collection of rows available to the consuming component. Each Data Profile column represents an attribute of the response data, within the row set denoted by the Row Selector. For XML or CSV formats, APEX always treats the response as a flat, table-like data structure.

JSON Data Profiles

APEX supports defining Array Columns to represent a nested array within a row set. The nested array can have one or many Data Profile columns which reference the array column as their parent. Note that cyclical references are not allowed. In other words, a column cannot reference an Array Column as a parent if that column is a child or grandchild of the current column.

If an APEX component is created based on a REST Data Source and the Data Profile contains array columns, Page Designer enables you to choose an array column. The direct children of that chosen array column, and its parent columns are then available to the APEX component. In the example that follows, the Data Profile, Array Columns select list is selected.

Description of the illustration component_on_rest_data_source.png

Learn about supported authentication types and credentials in REST Data Sources.