Technical Articles relating to Oracle Development Tools and Frameworks

  • VBCS
    January 22, 2020

Obtaining All Rows from a REST Action using Recursive Action Chain Calls

Duncan Mills


This article introduces a standard pattern that can be used to achieve the goal of obtaining all rows from a REST endpoint when that endpoint only supports a paging API.  What do I mean by a paging API? Well that's the case where there is no option to just ask the endpoint for all rows at once but instead you have to pass it (or it will assume) some fixed number of rows to fetch per call.  An example is the pattern used by Visual Builder Business Objects and ADF Business Objects exposed through REST that have a URL pattern like this:  <some endpoint>?limit=25&offset=0. Meaning start at row zero and give me 25 rows. The return payload will then typically include a flag e.g. hasMore, which tells you that there are more rows available, in which case you would need to issue another call to get the next batch of rows, starting from where you last left off e.g. <some endpoint>?limit=25&offset=25 and so on, until hasMore=false.

Of course REST APIs differ and if the endpoint has a flag to ask for all the rows at once then just use that, but let's stick to how to implement this recursion for the core Visual Builder Business Object case. 

The Scenario 

For the sake of illustration I'll be using a Business Object called Participant with an id field and an email field and my final goal is to fetch all of the emails into a single array variable in the page. 

Implementing the Pattern

Step 1 - Defining Types

To keep your code readable and maintainable, I strongly encourage  the liberal use of well defined types to make mapping clearer and less error prone.  For my use case, the Participant Business object exposes a GET many endPoint for which we'll want to generate a type, and I'll also create a simple object for the array row type called emailRecordType:  

Step 2 - Defining the Destination Array

I'll need somewhere to store all the data, so I create a page variable called emailArray with an Array type and an Array Item Type of the emailRecordType defined above.  Of course this variable will initially be empty. 

Step 3 - Define the API for the Recursive Action Chain 

As the title of the article mentioned, the process of making multiple calls to the endpoint to get all the rows will be managed through recursion, e.g. I'll have an action chain that will call itself, essentially looping until all the rows (or some max number of rows) have been obtained.  So the first part of this will be to define the API for that Action Chain in terms of the inputs to it and the data that it will return. 

So I create a new Action Chain called fetchBatch and define input parameters and a return type as follows:

Some details on each of those input parameters:

  • accumulator - this will be used to pass the set of data pulled from the endpoint to the next iteration of the loop / recursion.  It has a default value of an empty array ([])
  • fetchBatchSize - is a number input which can be used to define how many rows to get from the endpoint on each iteration.  I've defaulted it to 25, but you can set that to whatever works best for your use case. 
  • maxRows - is a number defining an approximate maximum number of rows you want to get before stopping. This is just a bit of a safety net and optional.  I've set it to a default value of 1000.
  • startFetchFrom - is a number defining where to start a given iteration from. This value will increment by the fetchBatchSize as we go through each iteration.  The default is 0 (zero) as we want to start at the beginning!

The return type for the Action Chain is again an array of the emailRecordType I defined in step 1.

Step 4 - Define the REST Action 

Now we need to implement the Action Chain and the first step is the actual call to get a batch of records.  To do this. we just drop a Call REST Endpoint action into the flow and wire that to the endpoint that I'm getting the data from: 

And let's look at that assignment mapping in more detail:

You can see here that the fetchBatchSize and the startFetchFrom input parameters are mapped to the limit and offset parameters on the REST call. In this case I've also set onlyData (to true) and the fields that I want in the return payload to optimize things a little, but that's optional.

The return type for the REST action is the getall_Participant type that was set up in step 1.

Step 5 - Save the Results of this Batch

The next action to add to the chain is one to assign the returned email addresses into the accumulator array. This just uses an Assign Variables action with the following mapping:

Notice how the Reset Target for the array assignment is set to none. This is important otherwise the accumulator won't do it's job!

Step 6 - Do We Need to Recurse?

Next we drop an If action into the Action Chain.  This will do two things for us:

  1. Check to see if the endpoint is reporting more rows are available (looking at the hasMore value on the REST response)
  2. Check to see if we've reached or exceeded the defined maxRows value

The actual condition used in the IF is:

[[ $chain.results.callRestChunked.body.hasMore && $chain.variables.accumulator.length < $chain.variables.maxRows ]]

Step 7 - Do It All Again!

If the recursion check passes then this is the point at which we need to recursively call the same action chain. To do this, we drop a Call Action Chain action in and map it to use this same chain.  The important part of course is is the mapping of the parameters:

It's all pretty simple 1-1 mapping except for the value passed into startFetchFrom which of course needs the incremented fetch starting position which we can calculate as shown here by just adding the batch size to the previous start point.

Step 8 - Remap the Updated Array

Recursively calling the Action Chain will of course result in additional rows being added to the accumulator, so on our way back up the recursion stack we need to pick up that update.  So we drop another Assign Variables action in after the Call Action Chain and re-assign the output of the recursive call back to the accumulator. 

Important! Notice that this time we set Reset Target to empty. If this was not done, the final array would include duplicated data (Just think about the logic of the recursion for a second and you'll get it).

Step 9 - Return the Result

With that we're all done. All we need to do now is return the accumulator array to the caller, so we drop a return action in and do so:

And here's an overview of the whole chain that has just been defined:

Once this chain is defined you can then just call it from your desired action chain and the recursion will take care of the rest., for example:

Join the discussion

Comments ( 2 )
  • Sivasankar A Monday, April 20, 2020
    Hi Mills,

    I tried this for Business Object working fine.
    Is it won't work for DBCS Rest endpoint,Because i didnt see any Input parameter for DBCS Based rest endpoint service.
  • Duncan Mills Thursday, April 23, 2020
    I've described a generic technique here and of course details will vary depending on what the REST service actually provides in terms of paging services.
    The offset and limit approach is used fairly widely, including by BOs, but you can easily adapt to different paging parameters
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.