DEV Community

Mostafa Mansour
Mostafa Mansour

Posted on • Originally published at opalapi.dev

"The 6 Pagination Bugs That Eat Oracle Fusion REST API Integrations"

#oracle #api #rest #tutorial

Every collection GET against an Oracle Fusion REST API is paginated whether you ask for it or not. Call GET /workers, treat the response as "all workers," and you silently got the first 25 rows — nothing warns you that thousands more exist. Here are the six pagination bugs I keep seeing in Fusion integrations, and the loop that avoids all of them. Replace acme.fa.us2.oraclecloud.com with your pod; all sample data is anonymized.

The response wrapper 

{
  "items": [ ... ],
  "count": 25,
  "hasMore": true,
  "limit": 25,
  "offset": 0
}
Enter fullscreen mode Exit fullscreen mode

count is this page's size, not the total. hasMore is your loop condition. limit is what the server actually used — it can override what you asked for. offset is 0-based.

The six bugs

1. First 25 rows only. No limit sent, default applied, hasMore ignored. The classic.

2. Off-by-one offset. offset is 0-based; a 1-based assumption (a real bug in identity-management connectors) duplicates or drops one record at every page boundary.

3. Silent limit override. The cap is 500 on most resources. Ask for limit=1000, get 500, increment offset by 1000 → every second page skipped. Always increment by the response's limit.

4. Unstable ordering. No orderBy means rows can shift between calls — nightly syncs "lose" records nondeterministically. Always sort on a stable unique key (PersonId, InvoiceId).

5. totalResults on every page. It forces an extra count query per request. Fetch it once with limit=1, then run the loop without it.

6. Restarting after a 429. A 500-per-page loop over a big resource is thousands of calls. Back off and resume from the saved offset; don't start over.

The loop that avoids all six 

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 + $(echo "$PAGE" | jq '.limit')))
done
Enter fullscreen mode Exit fullscreen mode

onlyData=true strips per-item HATEOAS links and shrinks payloads dramatically — always use it when iterating.

Filter before you paginate

limit/offset compose with q and finders. A q filter that cuts 48,000 rows to 3,000 saves you 90 pages before pagination starts:

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'
Enter fullscreen mode Exit fullscreen mode

Which fields are filterable is endpoint-specific — /workers alone has 307 queryable fields, and the free field-level reference at opalapi.dev/apis lists q fields, finders, and child resources for the top HCM and FSCM endpoints.

Expanded children paginate too

From REST framework v3, an expanded child returns a collection wrapper with its own hasMore — and any child with more than 500 items isn't expanded at all (you get a links entry instead). For large children (assignments across history), skip expand and paginate the child URI directly:

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'
Enter fullscreen mode Exit fullscreen mode

Originally published on the OPAL blog — the full version covers totalResults costs, hasMore-vs-row-security gotchas, and framework-version differences. OPAL is a free offline desktop explorer for 59,000+ Oracle Fusion endpoints: opalapi.dev.

Top comments (0)