Understanding REST Enabled SQL Service References

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

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.

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:

• A remote Oracle Database that includes Oracle REST Data Services (ORDS) 23.3 or later on the front-end.

• A remote MySQL Database that includes Oracle REST Data Services (ORDS) 23.3 or later on the front-end.

  See Using the REST Enabled SQL Service with MySQL Database in Oracle REST Data Services Installation and Configuration Guide and REST Enabled SQL Service and MySQL Database in Oracle REST Data Services Developer’s Guide.

• An administrator must enable RESTful Services either at the workspace or instance-level as described in Enabling RESTful Services and Defining a Path Prefix for a Workspace and Enabling RESTful Services for an Instance in Oracle APEX Administration Guide.

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.

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.

Populating the Endpoint URL Dynamically

Populate the server portion of the Endpoint URL dynamically using a callback procedure you specify in the Configuration Procedure attribute.

You can edit the Configuration Procedure attribute on the Edit Remote Server page. See Editing or Deleting a Remote Server.

Flexible Remote Server works as follows:

• When the REST Enabled SQL reference is invoked, the APEX Engine evaluates the Configuration Procedure attribute.

• If the Configuration Procedure attribute has a value, either stored in the database or in an anonymous block in the PL/SQL Code attribute, the APEX Engine executes the procedure to fetch a collection of substitution parameters that are then used to change the Endpoint URL.

Flexible Remote Server Use Cases

Common use cases for using a Flexible Remote Server include:

• Environment dependencies - A Flexible Remote Server is a good choice when the environment is dependent upon where the server or application is deployed (for example, in production, test or development instances). Instead of manually changing the Endpoint URL attribute for each environment, you can use an API to change it dynamically.

  Tip: You can also use APPLICATION_ADMIN.SET_REMOTE_SERVER procedure to change the Endpoint URL in a deployment script. See SET_REMOTE_SERVER Procedure in Oracle APEX API Reference

• Application data or the application user dependencies - A Flexible Remote Server enables you to use the Configuration Procedure attribute to change the Endpoint URL at runtime depending on dynamic parameters. These dynamic parameters are flexible and can be based on a configuration table, items in Session State, Application Items or Substitution Items.

  The results of the Configuration Procedure are cached and reused within the same request (page view or page processing).

Note: Flexible Remote Servers are only supported for REST Data Source and Authentication Server Types or Remote Servers used in REST Enabled SQL.

Configuring a Flexible Remote Server

To configure a Flexible Remote Server, edit the REST Enabled SQL service and enter a procedure name in the Configuration Procedure attribute. This procedure can be the name of a procedure stored in the database, stored in a database package procedure, or a procedure stored in the PL/SQL Code attribute.

This signature of the procedure must have two parameters of type:

• p_info in apex_plugin.t_remote_server_info

• p_config in out apex_plugin.t_remote_server_config

The parameter p_config has two optional attributes which can be changed in the configuration procedure:

• p_config.base_url - base_url changes the Endpoint URL.

• p_config.substitutions - substitutions assigns name/value pairs using apex_t_varchar2. Oracle APEX substitutes each #NAME# in the base url or Endpoint URL.

Together these two attributes determine the actual Endpoint URL the Remote Server uses.

Tip: To view Configuration Procedure examples, see item Help for the Configuration Procedure attribute.

The following example changes the base URL if the application ID is 100:

procedure my_server_config(
  p_info    in  apex_plugin.t_remote_server_info,
  p_config  out apex_plugin.t_remote_server_config )
is
begin
  if v('APP_ID') = 100 then
    p_config.base_url := 'http://example100.com';
  else
    p_config.base_url := 'http://example.com';
  end if;
end;

You can also can use placeholders by enclosing the “name” with the number symbol (#) and use substitutions to replace the value in those placeholders. Consider the following example:

procedure my_server_config(
p_info   in  apex_plugin.t_remote_server_info,
p_config out apex_plugin.t_remote_server_config )
is
begin
if p_info.application_id = 100
then
  p_config.base_url := 'https://#cust#.example.com';
  p_config.substitutions := apex_t_varchar2();
  apex_string.plist_put( p_config.substitutions, 'cust', v('P3_CUSTOMER') );
else
  p_config.base_url := 'https://test.example.com';
end if;
end;

See Also: Editing a REST Enabled SQL Service Reference

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.