17.4.6 Managing Data Synchronization

Enable Data Synchronization to keep the contents of a local table automatically in sync with the data from a REST service.

See Also:

Configuring Background Jobs in Oracle APEX Administration Guide

17.4.6.1 About Data Synchronization

Data Synchronization enables developers to automatically sync the contents of a local table with the data from a REST service.

You can trigger Data Synchronization manually or on a regular schedule, using a scheduler job. Note that the CREATE JOB privilege must be granted to the application's parsing schema in order to execute REST source synchronizations on schedule.

Oracle APEX can create the local table based on the visible columns in the REST Data Source Data Profile. Since the table is a visible object in the database schema, developers can add indexes, change physical properties, or even add more columns.

If the table is not in sync with the Data Profile, Data Synchronization continues to work for columns present in both the table and the Data Profile (other columns are ignored). However, APEX generates SQL DDL statements to sync table columns back to the Data Profile. These statements can be added to the application as supporting objects.

Data Synchronization Uses Cases

Common use cases using REST Data Source Data Synchronization include:

  • Provide efficient reporting on large data sets coming from a REST service

    Some REST Services can return large amounts of result data that includes pagination. If a reporting requirement needs larger data sets, this could result to multiple HTTP requests and poorer response times. Also no indexes or other database features can be used to speed up reporting queries. In this case, Oracle recommends enabling Data Synchronization to sync the remote data to a local tables and have any reports based on local tables.

  • Collect data from REST Services for consumption by PL/SQL packages or other logic

    Having data from the REST service replicated in a local table enables developers to perform all sorts of processing and create a wider variety of reports on local tables.

  • Collect data from a REST API over a longer period of time

    Some REST APIs only provide data for a recent time frame. For reporting on a larger time frame, Data Synchronization enables developers to fetch REST data and store it in a local table. Note that this use case also requires a "merge by primary key" capability since two subsequent REST requests will return an overlapping data set.

  • Utilize Intelligent caching for APEX components

    REST Data Sources already provide a caching mechanism: APEX caches the JSON response (or responses) as CLOBs in a caching table. If an APEX component uses the REST Data Source and the cache is not stale, the HTTP request is saved and the cached JSON is used. However, JSON still needs to be parsed (that is, large data sets lead to a lot of JSON parsing overhead). Also, indexes or other database technology cannot be used to speed up queries. Caching within a named local table creates a better level of caching since JSON is no longer parsed and the table can be optimized to support reporting queries.

17.4.6.2 Accessing the Data Synchronization Page

Access the the Data Synchronization page by selecting a REST Data Source and then selecting Manage Synchronization on the right side of the page.

To access the Data Synchronization page:

  1. Navigate to the REST Data Sources page:
    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 Data Sources.
  2. On the REST Data Sources page, click the REST Source Name.
  3. Click Manage Synchronization on the right side of the REST Data Source page.
    The Data Synchronization page appears.

17.4.6.3 Configuring Data Synchronization

Configure Data Synchronization to automatically sync a local table with data from a REST service.

You can trigger synchronization manually or on a regular schedule using a scheduler job.

To configure Data Synchronization:

  1. Navigate to the Data Synchronization page.
    The Data Synchronization page appears.
  2. Under Details:
    1. Local Table Owner - Select the schema of the local table to synchronize data to. By default, the application Parsing Schema is used.
    2. Synchronize to - Select either New Table or Existing Table and click Apply Changes.

      If creating a new table, the message Table does not exist appears. Click Create Table.

    3. Synchronization Type - Select a synchronization type:
      • Append - Append rows to the local table. This is typically used when no primary key has been defined in the data profile.

      • Merge - Merge rows into the local table. To use this option, the data profile must have a Primary Key defined. If a row for the given Primary Key value exists, the row is updated.. Otherwise the row is created.

      • Replace - Empty the local table before loading new data.

    4. Job Execution Interval - Use the Database Scheduler Calendaring Syntax to define repeating synchronization schedules, such as "every Tuesday and Friday at 4:00 p.m." or "the second Wednesday of every month.". This calendaring syntax can either be provided manually using simple expressions can be built using the Interval Builder by clicking the Hammer icon adjacent to the input field.

      Note:

      To learn more about Scheduler Calendaring Syntax, see DBMS_SCHEDULER Operational Notes in Oracle Database PL/SQL Packages and Types Reference

  3. Under Steps - Use Steps to pass parameter values or specific external filters to the REST Service.
    If no step is defined, the REST Data Source will be invoked once, with default parameters. If steps are defined, APEX will invoke the REST Data Source once for each step, and pass the configured parameters.
  4. Under Advanced Settings:
    1. Commit Interval - Specify the commit interval to use during data synchronization. If not specified, the whole synchronization will be one transaction, with a commit after the last row. If an interval is specified, APEX will commit each time after the specified amount of rows have been processed.
    2. HTTP Request Limit - If required, provide a maximum number of HTTP requests to make for each synchronization step. If this limit is exceeded, synchronization will stop and logged as Incomplete.
      Note that due to the actual execution method, synchronization will exceed this limit by one request. So if you provide a limit of 10, synchronization will stop after the 11th request has been made.
    3. Enable Simple Rate Limiting - When enabled, APEX ensures that only a limited amount of HTTP requests is executed within the given time frame. This rate limiting is purely based on the number of HTTP requests being executed within a time frame. It does not look into HTTP response headers returned by the REST service.
  5. To save your changes, click Save.
  6. To execute Data Synchronization, click Save and Run.

17.4.6.4 Clearing Synchronization Settings

Clear synchronization settings.

Clearing synchronization settings stops synchronization processing and resets all REST Data Source attributes related to synchronizations. If APEX components are using the local synchronization table, they will be reset to use the REST Service directly.

To clear synchronization settings:

  1. Navigate to the Data Synchronization page.
    The Data Synchronization page appears.
  2. Click Clear Settings.

17.4.6.5 Viewing Synchronize Usage

View a report of application components currently using the local synchronization table as their data source.

View the Synchronize Usage report:

  1. Navigate to the Data Synchronization page.
    The Data Synchronization page appears.
  2. Under Details, click Synchronize Usage.
    A report appears showing application components currently using the local synchronization table as their data source.

17.4.6.6 Managing the REST Data Source Synchronization Log

Learn about the REST Data Source Synchronization log.

See Also:

Managing the REST Source Synchronization Log in Oracle APEX Administration Guide

17.4.6.6.1 About the REST Data Source Synchronization Log

The REST Data Source Synchronization log records details of previously executed synchronizations.

Once you have successfully executed a synchronization, the REST Data Source Synchronization log displays under Log on the Data Synchronization page.

The REST Data Source Synchronization log contains details about previously executed synchronizations such as the status (that is, Success or Failure), the amount of processed rows, and the amount of HTTP requests being made. You can drill down from the Synchronization log to the Web Services log to review each individual HTTP request which was made for this synchronization.

17.4.6.6.2 Purging the Synchronization Log

Purge the REST Data Source Synchronization log.

To purge REST Data Source Synchronization log:

  1. Navigate to the Data Synchronization page.

    The Data Synchronization page appears.

    The REST Data Source Synchronization log displays under Log at the bottom of the page.

  2. Under Log, click Purge Log.