X

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

Synchronize Parent-Child REST Sources

Carsten Czarski
Consulting Member of technical Staff

Synchronize Parent-Child REST Sources: Banner

With APEX 20.2, the new REST Source Synchronization feature was introduced, which allows to synchronize data from a REST API to a local table - without any code at all. This posting will show, how two REST APIs (which model a parent-child relationship) can be synchronized to two(!) local tables. Some PL/SQL code will be required, but declarative APEX features will be used to the maximum possible extent.

Prerequisites

First, we need two REST APIs to play with. Let's prepare two ORDS REST Handlers on the EMP and DEPT tables. The following script can run in SQL Workshop and sets everything up.

begin
    ords.enable_schema;
end;
/

declare
    c_parent_module_name constant varchar2(20) := 'syncdemo.parent';
begin
    ords.delete_module(
        p_module_name => c_parent_module_name );

    ords.define_module(
        p_module_name => c_parent_module_name,
        p_base_path   => 'syncdemo/' );

    ords.define_template(
        p_module_name => c_parent_module_name,
        p_pattern     => 'dept/' );

    ords.define_handler(
        p_module_name => c_parent_module_name,
        p_pattern     => 'dept/',
        p_method      => 'GET',
        p_source_type => ords.source_type_collection_feed,
        p_source      => 'select * from dept' );

    ords.define_template(
        p_module_name => c_parent_module_name,
        p_pattern     => 'emp/:deptno' );

    ords.define_handler(
        p_module_name => c_parent_module_name,
        p_pattern     => 'emp/:deptno',
        p_method      => 'GET',
        p_source_type => ords.source_type_collection_feed,
        p_source      => 'select * from emp where deptno = :deptno' );

    commit;
end;
/

In SQL WorkshopRESTful Services, the two new REST APIs look as follows:

Parent-Child REST APIs in SQL Workshop, REST Services

The new "Parent" and "Child" REST APIs in APEX SQL Workshop, RESTful Services.

We can invoke the parent REST API by just copying the full URL and pasting it in the browser.

Invoking the "Parent" REST API

Invoking the Parent REST API with a browser.

We can also invoke the Child REST API, however, that one always requires a parent ID to be passed in. Without a parent ID, the API returns nothing; and exactly this will be the challenging part for our synchronization.

The Child REST API always requires a parent ID to be passed in.

Invoking the Child REST API with a browser.

Create REST Data Sources

Now head over to APEX, create an application and create REST Data Sources for both of the REST APIs. Start with the parent REST API ...

Create REST Data Source "Parent"

Create REST Data Source "Parent"

APEX should be able to auto-detect everything. Click Create REST Data Source to create the object.

APEX detects columns and data types automatically.

APEX detects columns and data types automatically.

For the Child REST API, we need to declare the dynamic part of the URL by using the :deptno placeholder. To have column and data type detection working properly, make sure to pass a value which returns a result.

The URL of the Child REST API contains a dynamic placeholder

The URL of the Child REST API contains a dynamic placeholder

Discovery should work fine for this one as well. When done, you should have two REST Data Sources defined.

We have REST Data Sources for both Parent and Child APIs.

We have REST Data Sources for both Parent and Child APIs.

Synchronization for the Parent API

Let's now configure Synchronization for the "Parent" REST API - which is the easy and straightforward part. First we need to make sure that our REST Data Source has a Primary Key defined. So edit the REST Data Source and click the Edit Data Profile button.

The Data Profile for the Parent REST API.

The Data Profile for the Parent REST API.

The DEPTNO column is the primary key of this REST API, so let's edit this column to see whether it is declared as such in the Data Profile as well.

Enable the Primary Key switch for the DEPTNO column.

Enable the Primary Key switch for the DEPTNO column.

When creating a REST Data Source, APEX is typically not able to detect primary keys, since this kind of information is typically not provided within the response of a REST API. So we need to change this manually. Enable the Primary Key switch for the DEPTNO column and save the changes.

When back on the Edit REST Data Source page, click Manage Synchronization on the right hand side.

Click "Manage Synchronization" on the right hand side.

Click "Manage Synchronization" on the right hand side.

We want to synchronize data from the "Parent" REST API. The table to synchronize to does not yet exists, so choose to use a New Table and use LOCAL_PARENT_TABLE as the table name.

Configure synchronization for the parent table: Provide the local table name.

Configure synchronization for the parent table: Provide the local table name.

APEX does not silently create that table, so the Edit Synchronization page correctly states that this table does not yet exist. However, the SQL DDL statement can be reviewed and clicking the Create Table button will actually create the new table.

The new table does not yet exist. Click "Create Table" to actually create it.

The new table does not yet exist. Click "Create Table" to actually create it.

Once the table is created, we can pick the Merge synchronization method and configure a scheduled interval. The Merge method is based on the Primary Key Columns defined in the data profile. Depending on the values, which are coming in from the REST API, it will either create new rows in the local synchronization table, or it will update existing rows. Behind the scenes, the SQL MERGE statement is used. 

The Save and Run button allows to try the new synchronization out.

Configure schedule and synchronization type, and click "Save and Run" to run the synchronization.

Configure schedule and synchronization type, and click "Save and Run" to run the synchronization.

After synchronization ran, contents of LOCAL_PARENT_TABLE can be reviewed in SQL Workshop. So far, so good.

 

Review table data in SQL workshop after the synchronization ran.

Synchronization for the Child API

Now, repeat the same steps for the "Child" REST API.

  • In the Data Profile section, make sure that the EMPNO column is declared as the Primary Key.
  • Configure Synchronization for the LOCAL_CHILD_TABLE.
  • Review the CREATE TABLE SQL statement and click the Create Table button.
  • Choose the MERGE synchronization method and click the Save and Run button to try synchronization out.

The log will show that the synchronization actually worked. However, even if we run the synchronization multiple times, there are only three rows (and as we know, the EMP table has 14).

The Log shows that three rows were synchronized.

The Log shows that three rows were synchronized.

Review the table SQL Workshop, in order to see which rows were synchronized.

SQL Workshop shows actual table contents.

SQL Workshop shows actual table contents.

SQL Workshop shows us, that only rows with a DEPTNO of 10 were synchronized. And that is, because when we've created the REST Data Source, we have used the value of "10" as the default value for the :deptno REST Data Source parameter. So APEX will always invoke the REST API with a value of 10, which explains the rows we're seeing.

To deal with this case, REST Synchronization contains the concept of Steps. Navigate back to the Child REST API in Shared Components, then to Synchronization settings, and click the Add Step button to configure a new Synchronization Step for another :deptno value.

REST Synchronization with two "Steps"

REST Synchronization with two "Steps"

After clicking Save and Run again, the LOCAL_CHILD_TABLE will contain eight rows (for DEPTNO values 10 and 20).

LOCAL_CHILD_TABLE in SQL Workshop: We now have rows for DEPTNO 10 and 20

LOCAL_CHILD_TABLE in SQL Workshop: We now have rows for DEPTNO 10 and 20

We could now add more Synchronization Steps. And the Expression column in a Synchronization Step can even accept and process a SQL Query. However, such a SQL Query must return only one row and one column, and is therefore unsuitable when users need to pass and synchronize multiple values. After all, the Parent-Child synchronization needs to run multiple steps dynamically, and the amount of steps is unknown at design time.

So we need something more flexible. And this is where the APEX_REST_SOURCE_SYNC PL/SQL package comes into the picture. To play with this package, first "truncate" the LOCAL_CHILD_TABLE to have it empty again.

Truncate the LOCAL_CHILD_TABLE in SQL Workshop.

Truncate the LOCAL_CHILD_TABLE in SQL Workshop.

Flexible Synchronization with PL/SQL

APEX_REST_SOURCE_SYNC.DYNAMIC_SYNCHRONIZE_DATA allows to provide parameter values for a synchronization, but without having to explicitly define a Synchronization Step. So, in order to synchronize all rows of the "Child" REST API for DEPTNO = 20, we would use the following code (the "deptno" value is still hard-coded yet):

declare
    l_parameters apex_exec.t_parameters;
begin
    apex_exec.add_parameter(
        p_parameters      => l_parameters,
        p_name            => 'deptno',
        p_value           => '20' );

    apex_rest_source_sync.dynamic_synchronize_data(
        p_module_static_id => 'Child',
        p_sync_static_id   => 'dynamic "deptno" value',
        p_sync_parameters  => l_parameters );
end;

The p_module_static_id parameter is used to look up the REST Data Source within the application. So make sure that it's accurate. The Static ID for a REST Data Source can be found in the Advanced section on the REST Data Source Edit page.

Unfortunately you cannot run this code in SQL Workshop > SQL Commands. However, you can run it as an application page process, so we'll use this approach to illustrate how PL/SQL synchronizations work. So let's add a new page to the application.

  • Create a classic report on the LOCAL_CHILD_TABLE table, which shows us synchronized data.
  • Create a region, with a text field item PX_DEPTNO (here: P1_DEPTNO).
  • Create a Page Submit button ("Synchronize").
  • Create a Page Process with the above code, but replace the hard-coded '20' with the name of your page item. This should look as follows:
    PL/SQL Page Process to synchronize data.
    PL/SQL Page Process to synchronize data.

The page should look similar to the following screenshot. Type a number into the DEPTNO text field and click the Synchronize! button. After running this for 10, 20, 30, we should have all the 14 rows in the local table.

The table is empty at the beginning

The table is empty at the beginning

After Synchronizing for the first DEPTNO value...

After Synchronizing for the first DEPTNO value...

After Synchronizing for all DEPTNO values.

After Synchronizing for all DEPTNO values.

This gets us closer to our original goal, but is still too much manual interaction yet. Let's change the code of our procedure to run the "Child" synchronization for all rows of the Parent table ...

declare
    l_parameters apex_exec.t_parameters;
begin
    for d in (
        select deptno
          from local_parent_table )
    loop
        l_parameters.delete;
        apex_exec.add_parameter(
            p_parameters      => l_parameters,
            p_name            => 'deptno',
            p_value           => to_char( d.deptno ) );

        apex_rest_source_sync.dynamic_synchronize_data(
            p_module_static_id => 'Child',
            p_sync_static_id   => 'Dynamic Deptno',
            p_sync_parameters  => l_parameters );
    end loop;
end;

Now use this code for the Page Process. Also remove the P1_DEPTNO text field from your page, since a click on the Synchronize button will now synchronize the Child REST API for all rows of the Parent API (clear the LOCAL_CHILD_TABLE before trying it out).

Before synchronizing all rows at once ...

Before ...

... and after synchronizing all rows at once.

... and after Synchronization.

We're now pretty close. However, the synchronization still requires manual interaction, which we don't want. Actually, the Child REST API should be synchronized right after synchronization for the Parent REST API executed successfully. However, the REST Synchronization feature does not support something like "After Synchronization PL/SQL Code" or similar. 

So why not having both Parent and Child synchronization within the PL/SQL block ...?

declare
    l_parameters apex_exec.t_parameters;
begin
    --
    -- Step 1: Perform "Parent" Synchronization. No Parameters (empty array) are passed in.
    --
    apex_rest_source_sync.dynamic_synchronize_data(
        p_module_static_id => 'Parent',
        p_sync_static_id   => 'Dynamic Execution',
        p_sync_parameters  => l_parameters );

    --
    -- Step 2: Synchronize "Child" for all changed or new rows of the parent table.
    --
    for d in (
        select deptno
          from local_parent_table )
    loop
        l_parameters.delete;
        apex_exec.add_parameter(
            p_parameters      => l_parameters,
            p_name            => 'deptno',
            p_value           => to_char( d.deptno ) );

        apex_rest_source_sync.dynamic_synchronize_data(
            p_module_static_id => 'Child',
            p_sync_static_id   => 'Dynamic Deptno',
            p_sync_parameters  => l_parameters );
    end loop;
end;

When the Synchronize button is clicked, the PL/SQL block will first synchronize the Parent REST API, and the Child REST API afterwards; all in one.

Let's finally get rid of having to manually click a button for this.

Run PL/SQL on Schedule: Automations

We'll use the Automations feature to run the customized REST Source Synchronization automatically on schedule (see the Blog Posting for more details).

However, before creating the Automation, navigate to the "Parent" REST Data Source, edit the Synchronization and clear the configured schedule, which we'll not need any more.

Clear the configured Synchronization schedule.

Clear the configured Synchronization schedule.

Then, navigate to Shared Components, Automations and create a new Automation, as follows:

  • Name: Parent Child REST Sync
  • Type: Scheduled
  • Actions initiated on: Always
  • Execution Schedule: Daily at Midnight (feel free to use a different schedule, if needed).

Create a new Automation.Create a new Automation.

After clicking the Create button, the Edit Automation page appears. Scroll down to the Actions section, which contains a first, predefined Action.

A new Automation contains a predefined placeholder Action.

A new Automation contains a predefined placeholder Action.

Click the Pencil icon to edit this automation. Then scroll down to Code and replace the dummy code with the PL/SQL block from above to synchronize all data at once. You can use the APEX_AUTOMATION.LOG_ERROR, LOG_INFO or LOG_WARN procedures to log information during code execution.

declare
    l_parameters apex_exec.t_parameters;
begin
    --
    -- Step 1: Perform "Parent" Synchronization. No Parameters (empty array) are passed in.
    --
    apex_rest_source_sync.dynamic_synchronize_data(
        p_module_static_id => 'Parent',
        p_sync_static_id   => 'Dynamic Execution',
        p_sync_parameters  => l_parameters );

    --
    -- Step 2: Synchronize "Child" for all changed or new rows of the parent table.
    --
    for d in (
        select deptno
          from local_parent_table )
    loop
        l_parameters.delete;
        apex_exec.add_parameter(
            p_parameters      => l_parameters,
            p_name            => 'deptno',
            p_value           => to_char( d.deptno ) );

        apex_rest_source_sync.dynamic_synchronize_data(
            p_module_static_id => 'Child',
            p_sync_static_id   => 'Dynamic Deptno',
            p_sync_parameters  => l_parameters );

        -- this writes an informational message to the Automation Execution log.
        apex_automation.log_info( 
            'Child REST API synchronized for DEPTNO=' || d.deptno || '.' );
    end loop;
end;

Use the PL/SQL synchronization block as the Action Code.Use the PL/SQL synchronization block as the Action Code.

Save the changes to the Automation Action, then clear the LOCAL_CHILD_TABLE and LOCAL_PARENT_TABLE tables again and click the Save and Run button in order to have the new Automation execute. You should see the message Automation Execution initiated.

Automation Execution was kicked off after save.

Automation Execution was kicked off after save.

Now review the table contents again; they should be populated with data. Also, review the Execution Log for your Automation; which should contain the entries emitted with APEX_AUTOMATION.LOG_INFO during PL/SQL execution.

Automation Execution Log.

Automation Execution Log.

If everything is fine, we can activate the automation for scheduled execution. Edit the Automation again and change Schedule Status from Disabled to Active. Then save your changes.

Finally activate the Automation.

Finally activate the Automation.

And we're done. Based on your configured schedule, APEX will synchronize both parent and child REST APIs to local tables. And this is still open for improvements: For instance, the query on the LOCAL_PARENT_TABLE table within the PL/SQL block could be adjusted to select only specific rows for Child Synchronization. However, this quickly gets specific to the concrete synchronized REST API.

With the tools provided by APEX (REST Source Synchronizations, Automations and the accompanying PL/SQL packages), you should be able to implement almost every synchronization requirement - and without having to worry about details of low-level HTTP requests or JSON parsing.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.