18.3.1 About REST Enabled SQL Service

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

Because REST Enabled SQL services are stored at the workspace-level within Application Express components, they are available to all applications within a workspace. To use REST Enabled SQL services, the remote database must include Oracle REST Data Services (ORDS) release 17.3 or later on the front-end and have the REST Enabled SQL feature enabled. All SQL and PL/SQL is sent to the Oracle REST Data Services (ORDS) instance which executes it on the remote database.

How Do REST Enabled SQL Service References Differ from Database Links?

Both REST Enabled SQL services 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 Services 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:

    • Functions at the workspace-level. Developers can create an Application Express 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.

Exporting and Importing REST Enabled SQL Services

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, Application Express 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.