1. App Builder User's Guide
  2. Managing Application Data
  3. Managing REST Enabled SQL References
  4. Understanding REST Enabled SQL Service References

17.3.1 Understanding REST Enabled SQL Service References

Create REST Enabled SQL Service references to execute SQL or PL/SQL defined on a remote database.

Parent topic: Managing REST Enabled SQL References

17.3.1.1 About REST Enabled SQL Service References

Use REST Enabled SQL Service references to execute SQL or PL/SQL defined on a remote database.

Oracle APEX installations that meet the minimum Oracle REST Data Services (ORDS) requirements can execute any SQL or PL/SQL through a REST endpoint.

Developers create REST Enabled SQL references by defining a name, the endpoint URL, and authentication information within Shared Components. APEX passes the SQL or PL/SQL query to ORDS over REST, and a self-describing JSON response is returned. The JSON object contains result set meta data, the result data, and pagination details.

Because REST Enabled SQL services are stored at the workspace-level within APEX components, they are available to all applications within a workspace. Developers can utilize REST Enabled SQL references for interactive reports, interactive grids, classic reports, forms, master detail forms, calendars, JET charts, trees, and PL/SQL processes. References can also be used with Calendars, JET Charts, Trees, and PL/SQL processes.

Parent topic: Understanding REST Enabled SQL Service References

17.3.1.2 REST Enabled SQL Service Reference Requirements

Review the minimum requirements for using REST Enabled SQL Service references.

Requirements for using REST Enabled SQL Service references include:

Parent topic: Understanding REST Enabled SQL Service References

17.3.1.3 About MySQL Support

MySQL only supports read-only APEX components.

The following table lists supported APEX components and unsupported features when using a REST Enabled SQL Service references with a remote MySQL database.

APEX Component Unsupported Features and Comments

Classic reports

BLOB column (see below).

Interactive reports
  • Pivot View
  • Some aggregate functions (for example, Ratio To Report, Median, Approx Count Distinct)
  • Flashback
  • BLOB column (see below)
  • Column display based on LOV cannot use Static LOVs

Interactive grids
  • Editing (DML support)
  • Some aggregate functions (for example, Ratio To Report, Median, Approx Count Distinct)
  • Control breaks require a Primary Key column to be defined
  • Filtering on multi-value columns
  • Flashback
  • Column display based on LOV cannot use Static LOVs

Faceted search and smart filters

Multi-value facets.

Calendars

Drag and drop.

Form regions

Only support for read-only forms. DML is not supported..

Charts
  • Box plot charts
  • Some aggregate functions (for example, Ratio To Report, Median, Approx Count Distinct)

Cards

n/a

Column toggle reports

n/a

Reflow reports

n/a

Shared lists of values

n/a

Map regions

MySQL native Geometry type is not supported. Also, the map query must return GeoJSON.

Trees

Column chosen as Order Siblings By must be a VARCHAR data type (no numeric or date columns).

Execute Code page process

MySQL is not supported, and cannot be chosen from the list of remote servers when using REST Enabled SQL.

Automations

MySQL servers are supported for the Automation Query, but are not supported as a target within an Automation Action and the Execute Code action type.

BLOB column in interactive report and classic report

No support for BLOB columns in interactive reports and classic reports since these do not support REST Enabled SQL in general. BLOB columns are supported for the Cards regions and for Form Display.

Parent topic: Understanding REST Enabled SQL Service References

17.3.1.4 Differences between REST Enabled SQL Service References and Database Links

Learn how REST Enabled SQL Service references differ from database links.

Both REST Enabled SQL Service references and database links enable developers to access data remotely. However, these features access remote data differently. Key differences between database links and REST Enabled SQL Service references include:

  • Database Link
    • Functions at the SQL-level which enables developers to use remote tables and local tables in the same SQL query.
    • Works over SQL*Net, which can be problematic when connecting to a Cloud instance over the internet.
  • REST Enabled SQL Service References
    • Functions at the workspace-level. Developers can create an APEX component with a query on a REST enabled SQL Service but cannot join it to a local table.
    • Works with JSON over HTTP(s) which makes it easy-to-use it in Cloud environments or over the internet.

Both Database Links and REST Enabled SQL fetch data over the network which is significantly slower than fetching data from a table in the local database. When evaluating the best approach for your environment, be sure to evaluate the impact on page view performance and always consider replicating remote data in local tables, with an appropriate refresh algorithm.

Parent topic: Understanding REST Enabled SQL Service References

17.3.1.5 Exporting and Importing REST Enabled SQL Services

Create REST Enabled SQL Service references to execute SQL or PL/SQL defined on a remote Oracle database.

When you export an application, used REST Enabled SQL references are added to the export file. If you export an application and import it into another workspace, APEX checks whether the target workspace already contains REST Enabled SQL references with the same static ID. If a REST Enabled SQL reference already exists, the application uses the existing reference. If the reference does not exist, it is created in the target workspace.

Parent topic: Understanding REST Enabled SQL Service References