# Dynamic Parameters in Oracle APEX REST Data Source Synchronizations

# Introduction

In this post, I will describe how to dynamically pass parameters to Oracle APEX Rest Data Source Synchronizations. APEX REST Source Synchronizations allow you to declaratively Sync data from a REST API to a local table. Please read this post for more on why you may want to do that.

This works well if you want to synchronize small to medium-sized data sets. The challenge comes when you want to sync larger data sets. For example, you would not want to Synchronize 1 million records over a REST API hourly. We need a way to run our Sync every hour but only pick up records that have changed in the last hour. Oh, and by the way, we want to do this declaratively!

APEX REST Source Synchronization Steps to the Rescue.

# Use Case

We have been tasked with Synchronizing Time Entries from the [Harvest Time Entry](https://www.getharvest.com/) SaaS Application to a local Oracle DB table for use in an APEX Application. We must also:

* Ensure our local table is never more than 4 hours out of date.
    
* Allow time entries to be changed for up to 7 days after their initial entry.
    

Finally, the Harvest instance already has over 100,000 time entries, so we cannot Sync all of them every four hours.

Although this example is specific to Harvest, this approach will work for most REST-based APIs.

# REST Source

The first thing we need to do is to create a REST Source that we can use to fetch time entries from Harvest. Enter a static ID and the Harvest API Endpoint URL:

![Create APEX REST Source 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074155537/f86c0962-e867-4635-9d64-ac1a1e6f5236.png align="center")

Click Next and confirm the Base URL and Service URL Path:

![Create APEX REST Source 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074217432/6a88130f-9990-43f4-8f64-7b702fcbe1a9.png align="center")

Next, configure Pagination for the Harvest API:

![Create APEX REST Source 3](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074255290/c308a215-42fb-4e57-991b-d1f9514a846d.png align="center")

Then, set up a new APEX Web Credential. **Note**: For Harvest, when entering the 'Value', you need to enter 'Bearer ABC', where ABC is your Harvest API token.

![Create APEX REST Source 4](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074283585/c6d251d2-c0e3-41ac-9a4a-5c9bb1572f99.png align="center")

Then, for this particular Harvest API, we need to click 'Advanced' and enter an 'HTTP Header' parameter indicating our company ID (Harvest-Account-ID).

![Create APEX REST Source 5](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074656130/6f658a54-7c90-4729-a3ca-2476bd282873.png align="center")

Then, we can click Discover to verify that our REST Source setup works.

![Create APEX REST Source 6](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074775188/83aaa511-cd1d-4432-9fa7-44ae9f488183.png align="center")

We should end up with a REST Source definition that looks something like this.

![Create APEX REST Source 7](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074825142/fe02b8c7-85a9-4ad3-b798-e212e90392fd.png align="center")

## Add Parameters

For Harvest, we are going to Sync time entries between two dates. Harvest provides two 'URL Query String' parameters for this purpose: `from` and `to`. We must set these parameters up in the REST source we just created. The screenshot below shows all three parameters.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075019324/c7988971-01e7-4bed-ba8b-31ebf07fba9a.png align="center")

## Identify a Unique Key

Before moving on to Synchronization, we must identify a Unique Key in the Data Source for our REST Source. Edit the REST Source and click 'Edit Data Profile':

![APEX REST Source Data Profile Unique Key 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075179755/af75e363-73fc-4e9c-88a3-92d0c08a44cc.png align="center")

Then, turn on the 'Primary Key' switch for the column that is a unique identifier for each record the REST API returns. In this case, it is the `ID` column.

Identifying a unique key allows us to perform a Merge operation when syncing records from our REST source.

![APEX REST Source Data Profile Unique Key 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075251068/d929c65e-f191-4372-a88a-6210da997dcc.png align="center")

That completes the REST Source definition.

# Setup Synchronization

Now that we have a working APEX REST Data Source, we can configure it to Synchronize to a local table. Start this process by clicking on the 'No' link under the 'Synchronized' column for the REST Source.

![Create APEX REST Source Syncing 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1716074825142/fe02b8c7-85a9-4ad3-b798-e212e90392fd.png align="center")

Then, enter the table name where you want the data Synced. APEX is going to create this table for you. Alternately, you can create your own table and select it here. If you create your table, you must ensure your table column names match those in the 'Name' column of your REST Source Data Profile.

![Create APEX REST Source Syncing 2](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075489971/142bc285-db83-447d-8fdb-faf1254e9634.png align="center")

![Create APEX REST Source Syncing 3](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075503830/e22a9584-4925-47d2-bc54-ba04f26c5410.png align="center")

After clicking 'Create Table', your REST Source Synchronization is ready to run.

![Create APEX REST Source Syncing 4](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075517123/c306815c-a1af-4fb6-8783-b20c7bb63438.png align="center")

Note that I selected 'Merge' as the 'Synchronization Type'. Merge will use the Primary Key we identified and perform a SQL Merge (Insert / Update) on our table when performing the Sync. This makes sense for time entries because they could change, and we want to update our local table with the latest values.

## Schedule the Sync

Let's schedule the REST Source Synchronization to run every four hours:

![Scheduling the APEX REST Source Synchronization](https://cdn.hashnode.com/res/hashnode/image/upload/v1716075958748/99d8c9f1-0593-47a8-9dc8-b499fbc360fe.png align="center")

Save and Run the Synchronization to ensure it works as expected and that records are synced to the local table.

![REST Source Sync Log](https://cdn.hashnode.com/res/hashnode/image/upload/v1716076458732/df79f316-18be-4c11-8d04-1b71d0a8e06e.png align="center")

Our REST Source and the Synchronization are now ready to go. The problem is, as it stands now, we are going to Sync all the time entries every four hours.

# Passing Dynamic Parameters

Finally, we have come to the point of this blog post!

We must create an APEX REST Source Synchronization Step to pass parameters to the REST Source at runtime. To do so, navigate to the Synchronization page for the REST Source.

![REST Source Synchronization Step 1](https://cdn.hashnode.com/res/hashnode/image/upload/v1716076607643/0ac67add-5b2d-48b5-9752-6203fb61dcd5.png align="center")

In this case, we want to fetch time entries between 7 days ago and today. To do this, we can use a PL/SQL expression to get the date from 7 days ago, return it to the `from` parameter, and get today's date for the `to` parameter. The screenshot below shows the REST Source Synchronization Step with the two parameters configured.

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1716077165913/db450755-7640-4cc3-be68-03c991d5163b.png align="center")

Options for 'Value Type' are:

* `Static` Provide a static value.
    
* `PL/SQL Expression` Run a PL/SQL Expression as we did above and pass the result to the REST API when the REST Source runs.
    
* `SQL Query (return a single value)` Pick up a value from a table and pass it to the REST API when the REST Source runs.
    
* `PL/SQL Function Body` Run PL/SQL code to figure out the parameter value (maybe even call another REST API) and pass it to the REST API when the REST Source runs.
    

If we run the Synchronization and then navigate to Monitor Activity > Web Service Activity Log, we can see that APEX calculated the from and to parameters at run time and passed them on to the Harvest REST API via the query string in the URL.

![APEX Web Service Activity Log](https://cdn.hashnode.com/res/hashnode/image/upload/v1716077588260/816c92b8-847b-42f3-a193-ff78600d9288.png align="center")

Pretty Cool!

# Conclusion

In this post, I showed you how to use APEX REST Source Synchronization Steps to pass values into your REST APIs at runtime. This powerful feature is invaluable when performing incremental synchronization of REST APIs to a local table in your Oracle Database.