X

The Oracle APEX blog is your source for APEX news, technical tips and strategic direction

Synchronize Data from REST Services to Local Table. With no code at all!

Chaitanya Koratamaddi
Senior Principal Product Manager, Oracle APEX | Database Tools

Oracle APEX developers often might require not just access to an external REST service, but also copy the data or parts of the data to a local table in the APEX schema. This data might be required for custom processing in stored procedures, or for complex reports including aggregations which is otherwise not feasible directly using remote data in real-time. Developers can achieve this data synchronization by building custom PL/SQL logic to load data from a REST service to a local table using APEX_WEB_SERVICE and APEX_EXEC.

In APEX 20.2, Web Source Modules are named REST Data Sources. Starting APEX 20.2, developers don't need to take the pains of building custom PL/SQL code in order to copy data from external REST services to local tables. APEX now declaratively supports data synchronization from a REST Data Source (formerly known as Web Source Module) to a local table. You can trigger Data Synchronization manually or on a regular schedule by  defining a DBMS_SCHEDULER expression. 

What is REST Data Source Synchronization?

Data Synchronization feature 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.

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.

Why REST Data Source Synchronization?

  • Provide efficient reporting on large data sets coming from a REST service: Some REST Services can potentially return large amounts of result data that includes pagination. If a reporting requirement needs larger data sets, this would lead to multiple HTTP requests and poorer response times. Also, you cannot use indexes or other database features to speed up reporting queries. In this case, you want to synchronize the remote data to a local table and have the reports working on local tables.
  • Collect data from REST Services for consumption by PL/SQL packages or other logic: If data from the REST service is replicated to local tables, developers will be able to perform all sorts of processing and also generate different types of reports.
  • 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 timeframe, Data Synchronization helps you to frequently fetch REST data and store it in a local table. This 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 Sourcees 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 needs still 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.

This blog post covers steps in detail to perform REST Data Source synchronization to local tables. In the first example, you learn how to configure data synchronization and define synchronization schedule. In the second example, you learn how to restrict data synchronization by defining REST Data Source parameters and adding in synchronization steps.

Prerequisites

  • You can try out the examples in any Oracle APEX 20.2 instance. Note that the CREATE JOB privilege must be granted to the application's parsing schema in order to execute REST source synchronizations on schedule. You can also sign up for a free workspace on the customer evaluation instance apex.oracle.com

  • To perform the steps in the second example, you need the The Movie Database API key.  


Using Synchronized Table for APEX Components

In this example, you first create a REST Data Source using the USGS (U.S. Geological Survey) JSON feed URL for URL Endpoint. Next, you create an interactive report that utilises this REST Data Source. Then, you define a primary key in the REST Data Source's Data Profile and configure the synchronization steps. After the synchronization is triggered, you review the logs and also the local table data. Finally, you update the interactive report region to utilise the local table as a data source.

In your workspace, first create a blank application without any pages. In this example, you create a blank application named REST Data Source Sync

1. Creating a REST Data Source

  1. First, create a REST Data Source. Note that starting APEX 20.2, Web Source Module is renamed as REST Data Source.  
    Navigate to your application Shared Components and under Data Sources, click REST Data Sources.


     
  2. Click Create. Select From Scratch for Create REST Data Source and click Next.
     
  3. Accept the default Simple HTTP for REST Data Source Type, enter a name for the REST Data Source and enter the USGS json feed URL for URL Endpoint. https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_hour.geojson. Then, click Next.


     
  4. You want to create a new Remote Server. Accept the defaults for Base URL and Service URL Path and click Next.
     
  5. This REST Data Source does not need authentication. Click Discover.
     
  6. Review the columns in the Data Profile. Click Create REST Data Source. Now you see the newly created REST Data Source under Shared Components.


     

2. Creating an Interactive Report Region

In this section, you create an interactive report that would utilise the REST Data Source created in the Creating a REST Data Source section above.

  1. Navigate to the application home page and create an interactive report. In this example, enter USGS for page name.
     
  2. In the Create Interactive Report wizard, Report Source section, select the following values and click Create.
    • Data Source: REST Data Source
    • REST Data Source: USGS (the REST Data Source created in the Creating a REST Data Source section)


     
  3. Click Save and Run Page.
     
  4. Enter your workspace Username and Password. Then, click Sign In. The interactive report displays data based on the feed from the past one hour.

3. Defining the Primary Key for Merge

In this example, you are configuring 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.

  1. Navigate to your application Shared Components > Data Sources > REST Data Sources and click your REST Data Source. In this example, click USGS.


     
  2. Under Data Profile click Edit Data Profile.
     
  3. Under Columns, click the Edit (pencil) icon for ID.
     
  4. In the Data Profile Column dialog, turn on the Primary Key switch for the ID column. Click Apply Changes.

4. Configuring Data Synchronization

  1. Navigate to your REST Data Source page now. In this example, click the USGS tab.


     
  2. Click Manage Synchronization that appears on the right side of the page.


     
  3. Synchronization is not configured yet. In this step, you provide the name of a new table or an existing table. 
    In this example, select New Table for Synchronize To and enter USGS_LOCAL for Table Name. Then, click Save.


     
  4. The synchronization table is not created yet.
    You can view the SQL that is used to create the table. Click Show SQL.
     
  5. You can also save this SQL as a supporting object. Click Save as Supporting Object.
     
  6. Then, enter a name for Script Name, accept the default for Script Type and click Save Script. The Supporting objects script is now created. Click Apply Changes
     
  7. Under Table Status, click Create Table.


     
  8. The table is now ready for synchronization. Notice the message The synchronization table exists and matches the Data Profile. You can view the table in SQL Workshop. Click View in SQL Workshop. Notice that the table is empty now.
     
  9. Navigate to your REST Data Source page again. Click Manage Synchronization.
  10. Now, select the synchronization type to use. APEX allows you to create any of the following three synchronization types:

    • Append - Append rows to the local table. You typically 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, select Merge.
  11. Define a synchronization schedule now. In the Synchronization Schedule field, you can 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 be provided manually. To have more complex execution schedules, review details about the Scheduler Calendaring Syntax in the documentation. To build simple expressions you can use the Interval Builder.

    Click the Interval Builder (wrench) icon. This dialog assists in building a simple Scheduler Calendaring Expression to specify how often the synchronization job executes.

  12. The Interval Builder dialog displays. In this example, for Frequency select Minutely and enter 15 for Interval. Then, click Set Execution Interval.

  13. Scroll down the page to review the Advanced Settings.
     
  14. You are now ready to test the data synchronization. Click Save and Run. You can see that the data synchronization is triggered.
     
  15. Refresh the page. Scroll down to review the log. In the example, notice that the request is successful and it fetched 11 rows.
    Under Details, click the View HTTP Request Log icon.


     
  16. Review the HTTP log. Then, close the dialog.
     
  17. Data is now synchronized and available in the local table. Open SQL Workshop in a new tab. In the Object Browser, click Data to review the local table data. You might notice there is a difference in the number of rows since you last viewed this table.
     
  18. 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.


     
  19. 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 10 minutes, starting at the top of the hour. So the actual execution timestamp of a synchronization can be up to a few minutes after the scheduled timestamp.

5. Updating the Interactive Report Region Source

The interactive report you created in the Creating an Interactive Report Region section uses the REST Data Source as report source. That is, whenever the page is loaded, APEX invokes the external REST service directly and fetches data. But now that the synchronized table is available locally, you can update the interactive report region source to use the local table instead.

  1. Navigate to the interactive report page in your application. Under Rendering, select the report region.
     
  2. In the Property Editor, the Region tab is selected by default. Turn on the Use Synchronizaiton Table switch. The report will now use the local table, which is configured within the Data Synchronization section of the REST Data Source, instead of reaching out to the external REST service.


     
  3. Click Save and Run Page.

6. Viewing the Synchronization Usage

Whenever APEX components use the local synchronization table as their data source, you can view the synchronization usage in a report.

  1. Go to the Data Synchronization page of your REST Data Source. Under Table Status, click Sychronization Usage.


     
  2. In this example, notice that the report shows the interactive report details. Close the dialog.


If you do not want to continue the synchronization and click Clear Settings button in the Data Synchronization page, a warning is displayed. The Synchronization Usage dialog is displayed which lists the APEX components using the local table. Clicking Clear Settings button will disable the Use Synchronization Table attribute and therefore components invoke the external REST service directly again.

Restricting the Data Synchronization

Sometimes you might need to restrict the data synchronization to a specific tenant or a specific time frame. If the external REST API has parameters, then you can use these parameters to control the exact data that should be synchronized to the local table. For example, you use the Movie Database API with a parameter to perform search for specific movies. The API https://api.themoviedb.org/3/search/movie?api_key=<replace with your API Key>&query=star%20trek fetches data for the Star Trek movies.

In this example, you learn how to restrict the data synchronization by specifying REST Data Source parameters and then creating the synchronization steps. You also learn how to switch from an Append Synchronization type to a Merge Synchronization type. In this example, you trigger the data synchronization manually.

1. Creating a REST Data Source

First, create a REST Data Source. In the Create REST Data Source wizard, select / enter the following.

  • Create REST Data Source method: From scratch
  • Name: Name for the REST Data Source. In this example, enter Movies
  • URL Endpoint: https://api.themoviedb.org/3/search/movie?query=star%20trek
  • Authentication > Authentication Type: URL Query String
  • Authentication > Name: Name of the Query String parameter. In this example, enter api_key.
  • Authentication > Value: The (secret) value for the URL Query String parameter. That is, enter your API Key value


​​​​​​

Click Discover and then click Create REST Data Source.

2. Configuring and Triggering the Data Synchronization

  1. In the REST Data Sources page, click Movies. Review the details. Notice that there is a parameter that has been listed.
    Click Manage Synchronization.


     
  2. In the Data Synchronization page, under Details, enter MOVIES_LOCAL for Table Name and click Save.
     
  3. The synchronization table does not yet exist. Click Create Table.
     
  4. The table is now ready for synchronization. Under Details, accept the default Append for Synchronization Type. Then, click Save and Run.


     
  5. Data synchronization is triggered. Scroll down and view the Log.


     
  6. You want to view the synchronized table data. Scroll up and click View in SQL Workshop.
     
  7. In the Object Browser detail pane, click Data. Notice that the movies data is  now displayed in the local table and the number of rows is the same as you viewed in the log. At this point of time, if you click Dependencies, the REST Synchronication > Active attribute displays No.

3. Adding Synchronization Steps

The REST Data Source includes a parameter with value Star Trek and therefore only those rows with the string Star Trek are fetched and synchronized to the local table. At the moment, the synchronization is using the REST Source Parameter defaults. If you want to synchronize rows of other movies for example Harry Potter and Matrix, then you define synchronization steps and specify the parameter values accordingly. Each step synchronizes data for a different set of REST Data Source parameters. Perform the followings steps: 

  1. Navigate back to the Data Synchronization page. Under Steps, click Add Step.


     
  2. In the Edit Synchronization Step dialog, enter Star Trek for Static ID. Notice that you can define Static, SQL Query, PL/SQL Express and PL/SQL Function Body parameter value types.
    In this example, select Static for parameter Value Type, enter Star Trek for Expression and click Apply Changes.


     
  3. Repeat steps 1 and 2 above to add Harry Potter for Static ID and Expression.
     
  4. Parameters can also be dynamic. Repeat step 1 above.  
    In the Edit Synchronization Step dialog, enter Matrix for Static ID. Select PL/SQL Expression for parameter Value Type, and enter 'mat' || 'rix' for Expression. Click Apply Changes.

4. Triggering the Data Synchronization

Now that the synchronization steps are defined, you want to trigger the synchronization. Perform the following steps:

  1.  First, under Log, click Purge Log.
    Then, scroll up and click Save and Run.
     
  2. Data synchronization is triggered. Refresh the page and then click Log. The log now displays entries for each of the synchronization step. In the example, notice that 20 rows have been fetched for each of the step. That is, synchronization has been executed for each of the step. Under Details, click the View HTTP Request Log icon for each of the step to review the HTTP request details.


     
  3. Scroll up and click View in SQL Workshop button. Click Data and you see data for Matrix, Harry Potter, and Star Trek movies.

5. Changing the Synchronization Type

If you trigger the Append synchronization type multiple times, then your local table ends up having duplicate rows as the rows are appended upon every synchronization. You now want to switch to the "Merge"synchronization type. Perform the following steps:

  1. In the Data Synchronization page, under Details, click Merge. Then, click Save.
     
  2. Notice the error. Append synchronisation type does not need a primary key to be defined in the data profile. But, Merge type requires a primary key.
     
  3. To define the primary key in the Data Profile, navigate to the REST Data Source definition page. Click the Movies tab.


     
  4. Click Data Profile and then click Edit Data Profile.
     
  5. Under Columns, click the Edit (pencil) icon for ID.
     
  6. In the Data Profile Column dialog, turn on the Primary Key switch for the ID column. Click Apply Changes.
     
  7. Navigate back to your REST Data Source definition page now. In this example, click the Movies tab.
     
  8. Click Manage Synchronization that appears to the right side of the page.
     
  9. In the Data Synchronization page, notice the warning that says the table is different from the Data Profile. Click Show SQL to see the ALTER TABLE statements to adjust table columns to the data profile.


     
  10. The ALTER TABLE statement adds the primary key constraint to the local table definition. Click Alter Table.


     
  11. Now you notice a different error that states primary key violation. You have triggered the Append synchronization type earlier in this example. And now you are not allowed to create a primary key constraint.


     
  12. To fix this issue, delete all of the existing rows in the local table. Open SQL Workshop in a different tab and navigate to SQL commands to run the statement: DELETE FROM table_name. In this example, run DELETE FROM movies_local.
     
  13. Switch back to the Data Synchronization page. Click Alter Table. The table is altered successfully.
     
  14. The synchronization table now matches the Data Profile. First, click Log and then click Purge Log
     
  15. Under Details, select Merge for Synchronization Type.
     
  16. You can trigger the synchronization. Click Save and Run.
     
  17. Click Log. In the example, notice that each synchronization step fetched 20 rows.
     
  18. You want to trigger another synchronization. Click Save and Run again.
     
  19. Refresh the page and then click Log. Notice that there are three more new entries in the log now. Although the log has additional entries because of synchronization steps, the local table would just have the merged data.


     
  20. Click View in SQL Workshop. Click Data. Notice that irrespective of the number of synchronisations you trigger, the number of rows in the synchronized table remain the same.

 

Summary

Oracle APEX 20.2 introduced data synchronization from a REST Data Source (formerly known as Web Source Module) to a local table. Synchronization can run either on Schedule or on Demand, by calling the APEX_REST_SOURCE_SYNC package. Developers don't need to build custom PL/SQL code in order to copy data from REST services to local tables; APEX provides this as a declarative option.

APEX can generate the local target table automatically, based on the attributes of the REST Data Source. REST Source Data can be appended or merged to the local table. Replacing all local data is also supported. APEX components using the REST Data Source can be configured to use the local table instead. Technical details like HTTP request limits, commit intervals or delete methods for the Replace mode are configurable.
 

Join the discussion

Comments ( 1 )
  • zaki Monday, November 16, 2020
    scale up to new level with this awesome feature
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.