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.

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).

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.

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 v('APP_ID') = 100 then
    p_config.base_url := 'http://example100.com';
  else
    p_config.base_url := 'http://example.com';
  end if;
end;