← Back to blog

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

By Mostafa Mansour 8 min read Oracle FusionREST APIPaginationlimit offsethasMore

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": [ ... ]
}

limit and offset basics

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

# Rows 51–100 of the workers collection
curl -u 'integration.user@example.com: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:

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 'integration.user@example.com: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:

totalResults: useful, but not free

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

curl -u 'integration.user@example.com: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. 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.

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 'integration.user@example.com: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:

  1. Page through the parent collection with the hasMore loop.
  2. 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 'integration.user@example.com: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.

Common errors and gotchas

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.