Previous Next JavaScript must be enabled to correctly display this content

## 18.5.10 Managing Data Synchronization

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

- About Data Synchronization
  Data Synchronization enables developers to automatically sync the contents of a local table with the data from a REST service.
- About Importing and Exporting REST Source Synchronizations
  After importing an application, REST Source Synchronizations are disabled. Learn how to enable synchronization after importing.
- Configuring REST Synchronization
  Configure REST synchronization to automatically sync a local table with data from a REST service.
- Viewing REST Synchronizations Defined in an App
  View REST Synchronizations defined in the current application.
- Clearing Synchronization Settings
  Clear synchronization settings to stop synchronization processing and reset all REST Data Source attributes related to synchronizations.
- Viewing Synchronize Usage
  View a report of application components currently using the local synchronization table as their data source.
- Managing the REST Synchronization Log
  Learn about the REST Synchronization log.

See Also:

Configuring Background Jobs in Oracle APEX Administration Guide

Parent topic: Managing REST Data Sources

### 18.5.10.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.

Parent topic: Managing Data Synchronization

### 18.5.10.2 About Importing and Exporting REST Source Synchronizations

After importing an application, REST Source Synchronizations are disabled. Learn how to enable synchronization after importing.

Developers often import applications into a workspace multiple times, for versioning or other purposes. If synchronizations were enabled during these imports, this would lead to multiple instances of the synchronization running simultaneously which is not the desired result. Therefore, Oracle APEX disables synchronizations after you import an application.

You can activate synchronization directly in Shared Components, REST Data Sources, Manage Synchronizations, or in Runtime Only environment by using the following PL/SQL block as shown in the following examples:

Example 18-1 Enable synchronization and start execution schedule immediately

    declare
        l_app_id number := {new application ID};
    begin
        apex_session.create_session(
            p_app_id => l_app_id,
            p_page_id => {one of the application pages},
            p_username => '{workspace user}');

        apex_rest_source_sync.enable(
            p_application_id => l_app_id,
            p_module_static_id => '{REST Source static ID}');
    end;

Example 18-2 Enable synchronization and set next execution timestamp explicitly

    declare
        l_app_id number := {new application ID};
    begin
        apex_session.create_session(
            p_app_id => l_app_id,
            p_page_id => {one of the application pages},
            p_username => '{workspace user}');

        apex_rest_source_sync.reschedule(
            p_application_id => l_app_id,
            p_module_static_id => '{REST Source static ID}');
            p_next_run_at => trunc( sysdate + 1 ), -- next midnight
    end;

Parent topic: Managing Data Synchronization

### 18.5.10.3 Configuring REST Synchronization

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

To create a new REST Synchronization, first create a new REST Data Source and then click Manage Synchronization to configure the details.

To configure data synchronization:

1. Navigate to Shared Components:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.

   The Shared Components page appears.

2. View the REST Data Source:
   1. On the Shared Components page, find Data Sources and select REST Data Sources.
   2. On the REST Data Sources page, select the REST Source Name.
3. Click Manage Synchronization .
   The REST Synchronization page appears.
4. 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.
      - If Existing Table:
        - Table Name - Select an existing table name.
        - Nested Rows - Select to synchronize data to the local table.
        - Click Save.
      - If New Table:
        - Table Name - Enter the name of a new table.
        - Nested Rows - Select Nested Rows to synchronize data to the local table.

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

        - Click Save.
   3. Table Name -
   4. 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.

   5. Synchronization Schedule - 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`.

      You can enter calendaring syntax manually or declaratively by using the Interval Builder to build simple expressions. To use the Interval Builder, click the Wrench icon.

      For more complex execution schedules, review details about the Scheduler Calendaring Syntax in DBMS_SCHEDULER PL/SQL package, and provide your expression manually.

      Note:

      To learn more about scheduler calendaring syntax, see DBMS_SCHEDULER Operational Notes in Oracle AI Database PL/SQL Packages and Types Reference

5. Under Steps - Click Add Step 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.
6. 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. For example, 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.
7. To save your changes, click Save.
8. To execute REST Synchronization, click Save and Run.

Parent topic: Managing Data Synchronization

### 18.5.10.4 Viewing REST Synchronizations Defined in an App

View REST Synchronizations defined in the current application.

To view REST Synchronizations defined for the current application:

1. Select the application and navigate to Shared Components:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components.

   The Shared Components page appears.

2. Under Tasks, click REST Synchronizations.
   The Synchronization page appears displaying REST synchronizations defined in the current application.

See Also:

Configuring REST Synchronization

Parent topic: Managing Data Synchronization

### 18.5.10.5 Clearing Synchronization Settings

Clear synchronization settings to stop synchronization processing and reset 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. Select the application and navigate to Shared Components:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components
2. On the The Shared Components page, select REST Synchronizations from the Tasks list.
3. Select the REST Synchronization.
4. Click Clear Settings.

Parent topic: Managing Data Synchronization

### 18.5.10.6 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. Select the application and navigate to Shared Components:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components
2. On the The Shared Components page, select REST Synchronizations from the Tasks list.
3. Select the REST Synchronization.
4. Under Details, click Synchronize Usage.
   A report appears showing application components currently using the local synchronization table as their data source.

Parent topic: Managing Data Synchronization

### 18.5.10.7 Managing the REST Synchronization Log

Learn about the REST Synchronization log.

- About the REST Synchronization Log
  The REST Synchronization log records details of previously executed synchronizations.
- Purging the Synchronization Log
  Purge the REST Synchronization log.

See Also:

Deleting REST Synchronization Log Entries in Oracle APEX Administration Guide

Parent topic: Managing Data Synchronization

#### 18.5.10.7.1 About the REST Synchronization Log

The REST Synchronization log records details of previously executed synchronizations.

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

The REST 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.

Parent topic: Managing the REST Synchronization Log

#### 18.5.10.7.2 Purging the Synchronization Log

Purge the REST Synchronization log.

To purge the REST Synchronization log:

1. Select the application and navigate to Shared Components:
   1. On the Workspace home page, click App Builder.
   2. Select an application.
   3. On the Application home page, click Shared Components
2. On the The Shared Components page, select REST Synchronizations from the Tasks list.
3. Select the REST Synchronization.

   The Synchronization page appears.

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

4. Under Log, click Purge Log.

Parent topic: Managing the REST Synchronization Log