We already know that Data Synchronization is a feature which enables developers to automatically sync the contents of a local table with the data from an external REST service. Basically, APEX invokes the REST Service defined in the REST Data Source, downloads all data and synchronizes to a local table. APEX can create the local table based on the columns in the REST Data Source's Data Profile. Since the table is physically available in the database schema, developers can add indexes, change physical properties, or even add more columns. APEX Components (reports, charts, etc) will be able to use the synchronized local table instead of invoking the the actual REST Service request.
Today in this post I am going to show you how to build this REST data source synchronization.
Step 1: Create a REST Data Source
First, create a REST Data Source. From APEX 20.2, Web Source Module is renamed as REST Data Source. Navigate to your application Shared Components --> REST Data Sources.
Click Create. Select From Scratch for Create REST Data Source and click Next.
Select the default Simple HTTP for REST Data Source Type, enter a name for the REST Data Source and enter the json feed URL Endpoint. Here I have used https://api.github.com/users/oracle/repos. Then, click Next.
Keep the defaults and click Next.
This REST Data Source does not need authentication. Click Discover.
Review the columns in the Data Profile. Click Create REST Data Source. Now we can see the newly created REST Data Source.
Step 2: Define the Primary Key for Merge process
In this example, we will configure the Merge synchronization type. If you are choosing to merge data every time it is synchronized, it is important to first ensure that the Data Profile has a Primary Key defined.
Under Data Profile click Edit Data Profile.
Under Columns, click the Edit (pencil) icon for ID.
In the Data Profile Column dialog, turn on the Primary Key switch for the ID column. Click Apply Changes.
Step 3: Configure Data Synchronization
Navigate to the REST Data Source page now and click Manage Synchronization option.
Synchronization is not configured yet. In this step, we provide the name of a new table or an existing table. I selected New Table for Synchronize To and enter GITHUB_API for Table Name. Then, click Save.
We can view the SQL that is used to create the table. Click Show SQL.
We can also save this SQL as a supporting object. Click Save as Supporting Object.
Under Table Status, click Create Table.
The table is now ready for synchronization. We can view the table in SQL Workshop. Click View in SQL Workshop. Notice that the table is empty now.
Navigate to REST Data Source page again. Click Manage Synchronization.
Now, select the synchronization type to use. APEX allows to create any of the following three synchronization types:
Append - Append rows to the local table. Use this when no primary key has been defined in the data profile.
Merge - Merge rows into the local table. The data profile must have a Primary Key defined to use this option. If a row for the given primary key value exists, the row will be updated, otherwise the row will be created.
Replace - Empty the local table before loading new data
In this example, I used Merge.
In this example, for Frequency I selected Minutely and entered 15 for Interval. Then, click Set Execution Interval.
Now its ready to test the data synchronization. Click Save and Run. We see that the data synchronization is triggered.
Refresh the page. Scroll down to review the log. To see the logs, click the View HTTP Request Log icon.
In the Object Browser detail pane, click Dependencies. REST Synchronization section includes details such as the application ID, name, REST Source, job interval and the synchronization schedule.
Navigate back to the Data Synchronization page now. Under REST Data Source, notice the message on when the next scheduled synchronization is due. Note that synchronizations are kicked off by the coordinator job every 15 minutes, starting at the top of the hour.
Step 4: View Synchronization Usage
Whenever APEX components use the local synchronization table as their data source, we can view the synchronization usage in a report.
Go to the Data Synchronization page of REST Data Source. Under Table Status, click Synchronization Usage.
This is a simple step by step process to build REST synchronization to local table.
Thanks for reading my post. Hope you liked it !!