Oracle REST API Pagination Pitfalls — hasMore, totalResults, Offset Degradation

TL;DR

• Bottom line: Oracle Fusion REST APIs use offset/limit pagination with a hard 500 record cap per request. totalResults is unreliable with offset, and performance degrades at high offsets.
• Key limit: Max 500 records per request (some endpoints: 499). Default is 25 if limit not specified.
• Watch out for: totalResults caps at current page size when offset is specified — hasMore may return false incorrectly.
• Best for: Integration developers needing reliable REST API pagination for Oracle Fusion Cloud.
• Authentication: Standard OAuth 2.0 — pagination behavior is independent of auth method.

System Profile

Oracle Fusion Cloud REST APIs (fscmRestApi, hcmRestApi, crmRestApi) use offset/limit pagination. The response includes hasMore, totalResults, count, offset, and limit — but several documented issues make naive implementations unreliable.

API Surfaces & Capabilities

Rate Limits & Quotas

Performance by Offset Range

Constraints

• Max 500 records per request — higher values silently capped with no error.
• totalResults unreliable with offset (Oracle Bug Doc ID 2728432.1).
• hasMore derives from unreliable totalResults — can be wrong.
• No cursor-based pagination — offset/limit only.
• Performance degrades exponentially at high offsets.

Integration Pattern Decision Tree

START — Need to paginate Oracle Fusion REST API
├── < 500 records → Single request, limit=500
├── 500-5,000 → Standard offset/limit loop (count < limit stop)
├── 5,000-50,000 → Date-range partitioning
└── > 50,000 → Do NOT use REST pagination → Use FBDI or BIP

Stop condition: count < limit (NOT hasMore)

Quick Reference

Step-by-Step Integration Guide

1. Basic Pagination Loop

Use count < limit as stop condition, NOT hasMore. [src1, src2]

offset = 0
limit = 500
while True:
    data = fetch_page(offset=offset, limit=limit)
    items = data["items"]
    count = data.get("count", len(items))
    all_records.extend(items)
    if count < limit:  # Last page
        break
    offset += limit

2. Date-Range Chunking for Large Datasets

Partition by date to avoid offset degradation. [src4]

3. Get Accurate Total First

Query with limit=0 and totalResults=true for true count. [src1]

Code Examples

Python: Production-Ready Paginator with Retry

# Input:  Oracle Fusion REST API credentials, endpoint
# Output: All records with retry and rate limit handling

class OraclePaginator:
    def __init__(self, base_url, token):
        self.base_url = base_url
        self.token = token

    def extract_all(self, endpoint, query=None):
        all_records, offset, limit = [], 0, 500
        while True:
            params = {"offset": offset, "limit": limit, "onlyData": "true"}
            if query: params["q"] = query
            resp = requests.get(f"{self.base_url}{endpoint}",
                headers={"Authorization": f"Bearer {self.token}"}, params=params)
            resp.raise_for_status()
            data = resp.json()
            items = data.get("items", [])
            all_records.extend(items)
            if data.get("count", len(items)) < limit:
                break
            offset += limit
        return all_records

cURL: Test Pagination Behavior

# Test default (25 records), max (500), and totalResults with offset
curl -s "$ORACLE_URL/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=500&totalResults=true" \
  -H "Authorization: Bearer $TOKEN" | python -c "
import sys,json; d=json.load(sys.stdin)
print(f'count={d.get(\"count\")}, hasMore={d.get(\"hasMore\")}, totalResults={d.get(\"totalResults\")}')"

Error Handling & Failure Points

Common Errors

Failure Points

• totalResults caps at 500 with offset: hasMore returns false prematurely. Fix: Use count < limit. [src2]
• Silent limit cap at 499: Some HCM endpoints cap at 499. Fix: Test each endpoint's actual max. [src3]
• Performance collapse at high offsets: 30+ sec responses. Fix: Date-range partitioning. [src5]

Anti-Patterns

Wrong: Using hasMore as Stop Condition

# ❌ BAD — hasMore is unreliable
if not data.get("hasMore"): break  # May stop at 500 records

Correct: Using count < limit

# ✅ GOOD — count < limit is always reliable
if data.get("count", len(items)) < limit: break

Wrong: Setting limit > 500

# ❌ BAD — Silently returns only 500
resp = requests.get(f"{url}?limit=5000")  # Gets 500, not 5000

Correct: limit=500 with Pagination Loop

# ✅ GOOD — Respect the 500 cap
# Use offset loop with limit=500

Common Pitfalls

• Trusting totalResults with offset: Bug Doc ID 2728432.1. Fix: Use count < limit. [src2]
• Not specifying limit: Default is 25. Fix: Always set limit=500. [src1]
• High offset performance: Degrades exponentially. Fix: Date-range partitioning. [src5]
• Not handling 429: Rate limiting. Fix: Exponential backoff + Retry-After. [src1]

Diagnostic Commands

# Test pagination metadata
curl -s "$ORACLE_URL/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=500&totalResults=true" \
  -H "Authorization: Bearer $TOKEN" | python -c "import sys,json; d=json.load(sys.stdin); print(json.dumps({k:d.get(k) for k in ['count','hasMore','totalResults','limit','offset']}, indent=2))"

# Measure response time at different offsets
for offset in 0 500 1000 5000 10000; do
  time curl -s -o /dev/null "$ORACLE_URL/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=500&offset=$offset" \
    -H "Authorization: Bearer $TOKEN"
done

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• 500-record limit is a hard cap — cannot be increased.
• totalResults unreliable with offset — known Oracle bug persisting for years.
• hasMore derives from unreliable totalResults — do not use as stop condition.
• Offset pagination is not snapshot-isolated — duplicates/gaps possible.
• Different API pillars may have slightly different pagination behavior.