Oracle Fusion REST API Pagination: limit, offset, hasMore, and totalResults

Every collection GET against an Oracle Fusion REST API is paginated whether you ask for it or not. If you call GET /workers and process the response as “all workers,” you silently got the first 25 rows — the default page size — and nothing tells you loudly that thousands more exist. Half the pagination bugs in Fusion integrations come from that one default; the other half come from mishandling offset. This guide covers the parameters, the canonical hasMore loop, what totalResults really costs, and the special case of paginating expanded child resources.

All examples use an anonymized pod (acme.fa.us2.oraclecloud.com) and placeholder data — swap in your own.

The pagination fields in every collection response

A collection GET returns a wrapper, not a bare array:

{
  "items": [ ... ],
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0,
  "links": [ ... ]
}

count — how many items are in this page (not the total).
hasMore — true if the server has more rows past this page. This is your loop condition.
limit — the page size the server actually used (it can override what you asked for).
offset — the index of the first item in this page, 0-based.
totalResults — only present when you explicitly request it (see below).

limit and offset basics

limit sets the page size, offset sets the starting index:

# Rows 51–100 of the workers collection
curl -u '[email protected]:YourPassword' \
  'https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?limit=50&offset=50&onlyData=true'

Three rules that prevent most surprises:

Default limit is 25 when you don’t send one.
The cap is 500 on most resources — ask for limit=10000 and the server quietly serves 500 (check the limit field in the response to see what you actually got).
offset is 0-based — offset=0 starts at the first row, offset=50 at the 51st. If your middleware assumes 1-based offsets (a real bug seen in identity-management connectors), every page after the first drops or duplicates one record.

onlyData=true strips the per-item links arrays and shrinks payloads dramatically — always use it when you’re iterating pages and don’t need HATEOAS links.

The canonical hasMore loop

Never precompute page counts from a total. Loop on hasMore:

BASE='https://acme.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/invoices'
LIMIT=500
OFFSET=0
while :; do
  PAGE=$(curl -s -u '[email protected]:YourPassword' \
    "$BASE?limit=$LIMIT&offset=$OFFSET&onlyData=true&orderBy=InvoiceId")
  echo "$PAGE" | jq -r '.items[].InvoiceNumber'
  [ "$(echo "$PAGE" | jq '.hasMore')" = "true" ] || break
  OFFSET=$((OFFSET + LIMIT))
done

The same shape in pseudo-code for any client:

offset = 0
do:
    page = GET resource?limit=500&offset=offset&onlyData=true&orderBy=<stable key>
    process(page.items)
    offset += page.limit        # use the server's limit, not your requested one
while page.hasMore

Two details matter more than they look:

Increment by page.limit, not your requested limit. If the server overrides your page size, incrementing by the wrong number skips rows.
Always send orderBy on a stable, unique key (PersonId, InvoiceId). Offset pagination without a deterministic sort order can return duplicates or gaps when rows are inserted between page calls — this shows up as “random missing records” in nightly syncs and is close to impossible to reproduce in testing.

totalResults: useful, but not free

totalResults is off by default because it forces an extra count query on every page:

curl -u '[email protected]:YourPassword' \
  'https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?totalResults=true&limit=1&onlyData=true'

{
  "items": [ ... ],
  "totalResults": 48213,
  "count": 1,
  "hasMore": true,
  "limit": 1,
  "offset": 0
}

Use it once up front — with limit=1 — when you genuinely need a total (progress bars, reconciliation counts), then run the actual export loop without it. Requesting totalResults=true on every page of a large extraction adds a count query per page for information you already learned on page one.

Sorting with orderBy: syntax, multiple fields, and the child-resource limit

orderBy is what makes an offset-paginated loop safe in the first place (see above), but its own syntax has two wrinkles worth knowing before you build on it.

Basic syntax:

?orderBy=PersonId              # ascending is the default
?orderBy=PersonId:desc         # descending
?orderBy=LastUpdateDate:desc,PersonId:asc   # multi-field: first key wins, second breaks ties

Multiple fields are comma-separated, with the first field as the primary sort and each subsequent field breaking ties on the one before it — the same idea as a SQL ORDER BY col1, col2. Always put the unique key last (or alone) so ties can’t produce a nondeterministic order between page calls.

The child-resource limit. orderBy sorts on the collection you’re querying directly — it does not reach into a child resource’s attributes. A real, still-unresolved Cloud Customer Connect thread (“Fusion HCM REST API: How to Use OrderBy on an Attribute From a Child Resource?”) is a developer hitting exactly this: sorting /workers by something on assignments or emails isn’t a supported pattern, and the error you get back doesn’t say so directly — it just rejects the attribute as invalid (URL request parameter orderBy with value workRelationships.StartDate:asc is not valid). There’s a dedicated Oracle Support knowledge article about child-resource query attributes not behaving as expected, which confirms this is a known framework constraint, not a one-off bug. If you need parent rows ordered by a child attribute, there’s no documented server-side way to do it; the practical workaround is to pull the parent collection unordered (or ordered by its own unique key for stable pagination), then sort client-side once you have the child data you need, or restructure the query to start from the child resource’s own top-level endpoint if one exists.

Don’t assume orderBy and q share one uniform child-resource rule just because both reject some child attributes. A field that filters fine with q=assignments.AssignmentStatusType='ACTIVE' on a resource is not a guarantee that orderBy=assignments.AssignmentStatusType will be accepted on that same resource — the two parameters are validated independently, so test each against your actual pod rather than assuming one implies the other. Before guessing which top-level attributes are safe to sort on, check the resource’s /describe output (see the describe endpoint guide) or look up the endpoint in OPAL’s endpoint catalog, which lists real q-fields and child-resource structure offline so you’re not burning live API calls on trial and error.

Pagination + q filters and finders

limit/offset compose with everything else on the URL. Filtered collections paginate exactly the same way:

# Page through all active workers, filtered server-side
curl -u '[email protected]:YourPassword' \
  'https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=assignments.AssignmentStatusType=%27ACTIVE%27&limit=500&offset=0&onlyData=true&orderBy=PersonId'

Filter first, paginate second: a q filter that cuts 48,000 rows to 3,000 saves you 90 pages before pagination even starts. The same applies to finders.

Paginating expanded child resources

expand has its own pagination story. From REST framework version 3, an expanded child comes back as a collection wrapper (with its own items, count, hasMore) instead of a bare array — precisely so large children can paginate. And any child with more than 500 items isn’t expanded at all: Oracle returns the usual links entry instead of inlining the data.

So the robust pattern for parent + large child is:

Page through the parent collection with the hasMore loop.
For each parent row where the child matters, follow the child URI and paginate it independently:

# Child collections take limit/offset like any other collection
curl -u '[email protected]:YourPassword' \
  'https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers/00020000000EACED.../child/assignments?limit=100&offset=0&onlyData=true'

If you expand several children on a wide parent query “to save calls,” you usually pay more in payload size than you save in round trips. Expand small, bounded children (emails, phones); fetch large ones (assignments across history) through their own paginated URIs.

Why the loop sometimes stalls at ~500-501 records total (a documented Oracle limitation)

The limit cap described above (500 per page) is a per-request ceiling. There’s a separate, less-known problem: on some resources, the loop itself stalls well before you’ve retrieved everything, even though you’re following the canonical hasMore pattern correctly.

The symptom, reported independently across several Cloud Customer Connect threads and confirmed in an Oracle Support knowledge article: your first call (limit=500&offset=0) returns a full 500-row page with hasMore: true, exactly as expected. Your second call (limit=500&offset=500) — the next iteration of the same loop — comes back with hasMore: false and only 1 record, even though the underlying resource clearly holds a thousand or more rows. Effectively, you can retrieve about 500-501 records total and no further, no matter what offset you send after that.

This isn’t a bug in your client code. My Oracle Support Doc ID 2734107.1 (“REST API Does Not Extract More Than 500 Records”) documents it directly, and the pattern shows up on both flat top-level collections (Financials’ accountCombinationsLOV is a commonly cited case) and child resources across HCM, FSCM, and SCM — see the open threads on fetching more than 500 records with limit/offset, child-record limits, and bulk-record maximums. It traces to an underlying ADF constraint on total row count for a given query shape, independent of the documented per-page limit cap — which is why it’s easy to mistake for a client-side pagination bug at first.

The workaround is to stop relying on plain offset walking once a resource is anywhere near this ceiling, and instead slice the collection with a q filter into buckets that each individually stay under ~500 rows, paginating each bucket with its own hasMore loop:

# Instead of one long offset walk over the whole resource, bucket by a date range
# and run the hasMore loop independently per bucket.
for RANGE in "2026-01-01,2026-03-31" "2026-04-01,2026-06-30" "2026-07-01,2026-09-30"; do
  START=$(echo "$RANGE" | cut -d, -f1)
  END=$(echo "$RANGE" | cut -d, -f2)
  OFFSET=0
  while true; do
    PAGE=$(curl -s -u '[email protected]:YourPassword' \
      --get "https://acme.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/accountCombinationsLOV" \
      --data-urlencode "q=CreationDate>=$START;CreationDate<=$END" \
      --data-urlencode "limit=500" \
      --data-urlencode "offset=$OFFSET" \
      --data-urlencode "onlyData=true")
    echo "$PAGE" | jq -c '.items[]'
    [ "$(echo "$PAGE" | jq '.hasMore')" = "true" ] || break
    OFFSET=$((OFFSET + 500))
  done
done

Pick a bucketing field that’s queryable on the resource (a date column, an ID range, a status) and narrow enough that no single bucket approaches the ceiling — check count on the first page of each bucket while you’re tuning the ranges. If you hit this on a resource where no good bucketing field exists, that’s worth a Service Request against the specific resource rather than assuming it’s a universal, permanent wall — Oracle’s own KB frames it per-resource, and behavior has shifted between releases for some objects.

Common errors and gotchas

First 25 rows only — no limit sent, default applied, hasMore ignored. The classic.
Off-by-one on offset — a 1-based assumption in the client; symptom is one duplicated or missing record per page boundary.
Silent limit override — you asked for 1,000, got 500, and incremented offset by 1,000: every second page is skipped. Increment by the response’s limit.
Unstable ordering — no orderBy, rows shifting between calls, nightly sync “loses” records nondeterministically.
429 rate limits mid-loop — a 500-per-page loop over a big resource is thousands of calls; back off on 429 and resume from the saved offset rather than restarting.
hasMore=false but you expected more — check whether row-level security trimmed the collection; the API paginates only what your roles let you see.
Loop stalls at ~500-501 records total — you’re hitting the resource-level ceiling above, not a client bug; bucket with q instead of a single long offset walk.

Check page sizes before you build the loop

How many rows a resource returns — and which fields you can filter on to shrink them — is endpoint-specific. The workers endpoint alone exposes 307 queryable fields and 20 child resources; OPAL’s endpoint catalog shows q fields, finders, and child resources for the top HCM and FSCM endpoints offline, so you can design the filter before burning API calls. For full request walkthroughs around pagination — auth, expand, error handling — see the HCM examples post and the FSCM REST API guide.

This post is part of our complete Oracle Fusion API guide — auth, base URLs, q filters, finders, and key endpoints in one place.