Oracle Fusion REST API Pagination and Incremental Extraction: A Practical OIC Guide

The integration that works with 20 records—and misses the rest

Imagine an OIC integration that reads Oracle Fusion transactions every night. During testing, it retrieves a small sample and finishes successfully. In production, the same call returns one page, the flow reports success, and hundreds of valid records never reach the destination. Nothing crashed. The design simply treated a collection response as the complete dataset.

Oracle Fusion REST collection endpoints commonly expose limit, offset, count and hasMore. Some also provide totalResults. Those values describe pages, not the complete business transaction. A reliable extraction design must handle pagination, changing data, retries and the checkpoint that determines what to read next.

First, understand the collection response

A typical collection response follows this shape. The values below are illustrative, not the result of a live Fusion call:

{
  "items": [ /* 100 records in this example */ ],
  "count": 100,
  "hasMore": true,
  "limit": 100,
  "offset": 0
}

count is how many items this response contains. limit is the effective page size, which a service can adjust. offset identifies the starting position. hasMore=true means another page may be available. Do not confuse the returned count with the full number of matching records. Oracle's collection documentation also warns that offset-based paging is not automatically ordered; if the collection changes while you fetch it, pages may shift.

For example, page 1 might return 100 rows and page 2 another 100. If you stop after page 1, the integration silently under-extracts data. If you always add 100 to the offset without accounting for the API's effective page size, you risk gaps when the server overrides your requested limit.

A practical paging pattern for Oracle Integration Cloud

In OIC, use a REST Adapter invoke to read a collection, and maintain loop variables for the current offset, requested page size and whether another page is needed. The exact names and mapping operations will vary by integration design, but the control flow is straightforward:

1. Initialize the offset to zero and choose a moderate page size supported by the endpoint.
2. Invoke the GET collection endpoint using the current limit and offset.
3. Map or stage each item, recording its stable source identifier for deduplication.
4. Read hasMore and the effective limit or returned count from the response.
5. Advance the offset according to the paging contract for that specific resource, then repeat while hasMore is true.

For a standard offset API where each request's offset represents the starting position and all preceding pages are complete, advancing by the effective page size is common. Check the resource's documentation and test the final short page. Protect against an invalid combination such as hasMore=true with an empty page: stop with a recorded error rather than retrying the same offset indefinitely.

offset = 0
repeat:
    response = GET collection(limit=100, offset=offset)
    validate response
    upsert each response.items item by source ID
    if response.hasMore == false: break
    if response.count == 0: fail with diagnostic details
    offset = offset + response.limit

This is pseudocode to explain the pattern, not an OIC mapper expression or a copy-and-paste script. In a real integration, configure the REST Adapter operation and map its query parameters and response schema explicitly. Check each object's documented request parameters: not every Fusion endpoint supports identical filters or sorting fields.

Why ordering matters more than page size

Consider an API ordered newest first. You read records 1–100. Before you request offset 100, a new record arrives at the beginning. That insertion shifts the next page boundary. A previously processed record might appear twice, while other concurrent changes can cause omissions.

Where supported, request a stable, deterministic order using orderBy, ideally including a unique tie-breaker. An order such as last-update timestamp alone may still contain ties. Use the endpoint's actual supported attribute names, not a guessed field from a different Fusion module. Even deterministic ordering does not freeze a changing source collection; it only makes the extraction more predictable.

For large or frequently changing datasets, a bounded time window plus controlled overlap is safer than paging a constantly moving unfiltered collection. For very large data export requirements, evaluate a bulk extraction tool such as BICC rather than assuming REST pagination is the right tool. Oracle explicitly distinguishes real-time REST integrations from large-volume import/export use cases in its SCM guidance.

Add incremental extraction without losing late changes

A full extract reads everything every time. An incremental extract should read only records created or modified within a defined time window, if that resource supports an appropriate filter field. Some resources expose fields such as a last-update timestamp; others do not. Verify the resource metadata and allowed q expression before configuring the filter.

Maintain two checkpoints: the last successfully completed watermark and a fixed upper bound captured at the start of the current run. Query a window between them rather than an open-ended “up to now” selection. Use a small overlap at the lower boundary where appropriate, then deduplicate by a stable record identifier and modification version or timestamp. Overlap protects against timing precision and records that become visible slightly late, but it does not eliminate all source-system consistency limits.

Here is an illustrative execution sequence:

• 08:00: read the last successful watermark and freeze an upper bound for this run.
• 08:01 onward: retrieve every page that matches the resource's verified update-time filter and order.
• After processing: reconcile expected and processed counts where the API provides reliable counts.
• On complete success: advance the saved watermark to the chosen upper bound.
• On failure: retain the previous successful checkpoint and retry safely rather than skipping the incomplete window.

Persist the checkpoint in a reliable datastore, not only as a transient OIC variable. If a single run exceeds your execution window, introduce durable page or batch checkpoints with an explicit resume strategy. Use database upserts or another idempotent destination operation so that reprocessing the same source record does not create duplicates.

Handle the errors that appear in real integrations

A robust pagination loop distinguishes authentication, throttling, temporary failures and invalid requests. Oracle's REST Adapter guidance mentions retry handling for 401, 429 and server-side errors, and caching OAuth tokens where applicable.

• 401: investigate token expiry, credentials and privileges. Refresh credentials through the supported authentication mechanism; avoid blindly repeating an unauthorized call.
• 429: respect server throttling guidance such as Retry-After when present, reduce concurrency, and retry with bounded backoff.
• 500/502/503/504: retry transient failures with exponential backoff and an upper limit. Escalate persistent failures with the endpoint, response code and correlation details.
• 400 or incompatible filter: inspect query syntax and supported fields. Repeatedly retrying an invalid request wastes calls.

Never store access tokens or sensitive business payloads in plain diagnostic logs. Log the resource name, run ID, page offset, HTTP status, duration, count, error category and checkpoint state instead. Make the failure visible: a job that completes with fewer records but no warning is harder to debug than a job that stops with a useful error.

An example checklist for an OIC-to-database extract

Suppose OIC reads a Fusion resource and writes to an integration staging table. Give the staging table a uniqueness rule on the source business key, and track last-modified information when exposed by the API. Use upsert behavior for replays. For each run, capture requested pages, returned records, accepted records, duplicates, rejected records and the final watermark.

Before moving to production, deliberately test an empty collection, a single full page, multiple pages, a short final page, a record changed during extraction, two rows with the same update timestamp, a simulated 429 response, and a failure after several pages. Verify that a rerun does not duplicate rows and that an incomplete run does not advance the successful watermark.

There is no universal page size or incremental filter that works across every Oracle Fusion module. The implementation must be based on the specific resource contract, expected volume, service limits and destination semantics. That is the difference between a demo flow that looks correct and an integration that can be supported confidently.

Useful Oracle references

• Oracle Fusion SCM REST: Working with Collections and Pagination
• Oracle Fusion Sales and Service: Collection Query Parameters
• Oracle Integration 3: REST Adapter Invocation Best Practices
• Oracle Fusion SCM Integration REST Best Practices and Constraints

Editorial note: Code and timelines in this guide illustrate design principles; they are not claimed to be validated against a specific customer's Fusion environment. A technical reviewer should confirm field names, retry policies and page-boundary behavior for the intended resource before publication.