Pre-General Availability: 2018-03-02

18.3 Managing REST Enabled SQL References

Execute SQL query or PL/SQL defined at the component-level on a remote database using REST Enabled SQL References.

18.3.1 About REST Enabled SQL Service

REST Enabled SQL Service references enable developers 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 or over the internet in the Cloud which can be problematic.

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

    • Queries created 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.

18.3.2 Before You Begin: REST Enabled SQL Service Requirements

Before you can create a REST Enabled SQL Reference you must:

  1. Set up a remote database with Oracle REST Data Services (ORDS) release 17.3 or later installed.

    Tip:

    This installation must be completely independent from the Oracle REST Data Services used as the Oracle Application Express web server

  2. On the remote database, configure and enable the REST Enabled SQL service feature.

  3. Use ORDS.ENABLE_SCHEMA to enable the target schema on the remote database to be accessed by Oracle Application Express . Log in to the database schema to be enabled for REST Enabled SQL using a "classic" client and execute the following:

    begin
        ords.enable_schema;
    end;
    /
      
    commit
    /
    

    The REST Enabled SQL service is then available with the URL in the following format:

    http://server:port/ords-context-root/schema.

Use this URL when creating the REST enabled SQL service reference in Oracle Application Express. The following is an example of a REST Enabled SQL service for the SCOTT schema with ORDS running in standalone mode:

http://server:port/ords/scott/

18.3.3 Creating a REST Enabled SQL Service Reference

You can create a REST Enabled SQL service reference from either Workspace Utilities or Shared Components

To create a REST Enabled SQL service reference:

  1. Navigate to the REST Enabled SQL page:
    • From Workspace Utilities:
      1. On the Workspace home page, click App Builder.

      2. Click Workspace Utilities.

      3. Click REST Enabled SQL Services.

    • From Shared Components:
      1. On the Workspace home page, click App Builder.

      2. Select an application.

      3. On the Application home page, click Shared Components in the center of the page.

      4. Under Data Sources, select REST Enabled SQL.

  2. On the REST Enabled SQL page, click Create.
  3. For Create REST Enabled SQL Service, select From Scratch and click Next.

    Tip:

    If you select, As copy of an existing REST Enabled SQL Service, select the application to copy from and follow the on-screen instructions.

  4. Specify the following attributes:
    1. Name - Enter a descriptive name for this REST Enabled SQL Service. 
    2. Endpoint URL - Enter the base URL for this REST Enabled SQL service. Include the Oracle REST Data Services context root and schema URL prefix, for example, https://host:port/ords/schema.

      Tip:

      Do not add .../_/sql to the end of the URL.

    3. Click Next.
  5. For Authentication, specify the appropriate credentials:
    1. Credentials - Select the credential to authenticate against.

      Tip:

      You can edit Credentials from either on either the Shared Components page or Workspace Utilities page.

    2. Credential Name - Enter a descriptive name for the credentials.
    3. Authentication Type - Specify the Authentication type. Oracle Application Express supports Basic Authentication and the OAuth2 Client credentials flows.
    4. Client ID - For Basic Authentication, enter the database user name. For OAuth2, enter the client ID. This information will not be encrypted. User names for Basic Authentication are converted to upper case. To include a case-sensitive user name, prefix it with the exclamation mark (!).
    5. Client Secret - Enter the password or OAuth2 Client Secret. This information is stored encrypted and cannot be retrieved in clear text.
    6. Verify Client Secret - Enter the password or client secret again to verify your input. When the two values are not identical, an error message displays.
  6. Click Create.

    Oracle Application Express will test the REST Enabled SQL service. If everything has been done correctly (that is, you have included the correct URL, schema name and password) you should see a message that resembles the following:

    REST Enabled SQL Service created.

    Success

    The REST Enabled SQL Service is available. The test query returned the following result.

    Hello, Application Express! You are connected as USERNAME. The local database timestamp is: 2017-11-16 09:38:12 -08:00.

18.3.4 Editing a REST Enabled SQL Service Reference

To edit a REST Enabled SQL service:

  1. Navigate to the REST Enabled SQL page:
    • From Workspace Utilities:
      1. On the Workspace home page, click App Builder.

      2. Click Workspace Utilities.

      3. Click REST Enabled SQL Services.

    • From Shared Components:
      1. On the Workspace home page, click App Builder.

      2. Select an application.

      3. On the Application home page, click Shared Components in the center of the page.

      4. Under Data Sources, select REST Enabled SQL.

    The REST Enabled SQL page appears listing the server same, sase URL, and defined authentication.
  2. Click the server name.
  3. REST Enabled SQL attributes include:
    1. Name - Enter a descriptive name for this REST Enabled SQL Service. 
    2. Endpoint URL - Enter the base URL for this REST Enabled SQL service. Include the Oracle REST Data Services context root and schema URL prefix, for example, https://host:port/ords/schema.

      Tip:

      Do not add .../_/sql to the end of the URL.

    3. Authentication Required - Choose whether this REST Enabled SQL Service requires Authentication.
    4. Credential - Choose the credentials to authenticate against this REST Enabled SQL Service.

      Tip:

      You can edit Credentials from either on either the Shared Components page or Workspace Utilities page.

  4. Session attributes include:
    1. Initialization Code - Enter code to be executed immediately after connecting to the REST Enabled SQL service and before the component SQL is being executed.
    2. Cleanup Code - Enter code to be executed immediately after the component SQL is being executed. View Documentation.
  5. Advanced attributes include:
    1. Static ID - Use the Static ID to reference the Remote Server in API Calls. Static IDs are also used to identify an existing Remote Server when the application is being exported and imported to another workspace.
    2. Server Time Zone - Time Zone which the REST Enabled SQL service uses to decode DATE and TIMESTAMP values. This value is updated each time you click Save and Test.
  6. Click Apply Changes or Save and Test.

18.3.5 Deleting a REST Enabled SQL Service Reference

To edit a REST Enabled SQL service:

  1. Navigate to the REST Enabled SQL page:
    • From Workspace Utilities:
      1. On the Workspace home page, click App Builder.

      2. Click Workspace Utilities.

      3. Click REST Enabled SQL Services.

    • From Shared Components:
      1. On the Workspace home page, click App Builder.

      2. Select an application.

      3. On the Application home page, click Shared Components in the center of the page.

      4. Under Data Sources, select REST Enabled SQL.

    The REST Enabled SQL page appears listing the server same, sase URL, and defined authentication.
  2. Click the server name.
  3. Click Delete.

18.3.6 Specifying a Remote Database in an Existing Component

Once you create a REST Enabled SQL service, you can use the remote database when creating classic reports, interactive reports, CSS calendars, or charts.

To use a remote database in an existing Oracle Application component:

  1. View the page containing the component in Page Designer:
    1. On the Workspace home page, click the App Builder icon.
    2. Select an application.
    3. Select a page.
    Page Designer appears.
  2. In Page Designer, select the region containing the component (if not already selected).
  3. Find Source and edit the following attributes:
    1. Location - Select Remote Database.
    2. REST Enabled SQL Service - Select a REST Enabled SQL Service reference.
    3. Type - Select Table/View or SQL Query.
      ""
    4. Configure the table or provide a SQL Query.
    5. Configure the remaining attributes as needed.
  4. Click Save or Save and Run Page.