17.4.9 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. - 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. - Configuring Data Synchronization
Configure Data Synchronization to automatically sync a local table with data from a REST service. - Clearing Synchronization Settings
Clear synchronization settings. - Viewing Synchronize Usage
View a report of application components currently using the local synchronization table as their data source. - About Importing and Exporting REST Source Synchronizations
After importing an application, REST Source Synchronizations are disabled. Learn how to enable synchronization after importing. - 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
17.4.9.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
17.4.9.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:
Parent topic: Managing Data Synchronization
17.4.9.3 Configuring Data Synchronization
Configure Data Synchronization to automatically sync a local table with data from a REST service.
To configure Data Synchronization:
Parent topic: Managing Data Synchronization
17.4.9.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:
Parent topic: Managing Data Synchronization
17.4.9.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:
Parent topic: Managing Data Synchronization
17.4.9.6 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 17-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 17-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
17.4.9.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
17.4.9.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
17.4.9.7.2 Purging the Synchronization Log
Purge the REST Synchronization log.
To purge the REST Synchronization log:
Parent topic: Managing the REST Synchronization Log