---
# === IDENTITY ===
id: business/erp-integration/oracle-rest-api-pagination-pitfalls/2026
canonical_question: "What are Oracle REST API pagination pitfalls - hasMore behavior, totalResults unreliability, offset degradation?"
aliases:
  - "Oracle Fusion REST API pagination issues"
  - "Oracle REST API limit offset hasMore totalResults problems"
  - "Oracle ERP Cloud REST API 500 record limit pagination"
  - "Oracle Fusion Cloud API pagination performance degradation"
entity_type: erp_integration
domain: business > erp-integration > oracle-rest-api-pagination-pitfalls
region: global
jurisdiction: global
temporal_scope: 2024-2026

# === SYSTEM PROFILE ===
systems:
  - name: "Oracle Fusion Cloud ERP"
    vendor: "Oracle"
    version: "25A-25D"
    edition: "Enterprise"
    deployment: cloud
    api_surface: "REST (fscmRestApi)"

# === VERIFICATION ===
last_verified: 2026-03-09
confidence: 0.86
version: 1.0
first_published: 2026-03-09

# === TEMPORAL VALIDITY ===
temporal_validity:
  status: evolving
  last_breaking_change: "24D — totalResults behavior changed for some endpoints"
  next_review: 2026-09-05
  change_sensitivity: medium

# === CONSTRAINTS ===
constraints:
  - "Maximum limit per request is 500 (some endpoints cap at 499) — cannot exceed regardless of parameter value"
  - "Default limit is 25 if not specified — server may override to improve performance"
  - "totalResults is unreliable when combined with offset — may cap at current page count"
  - "Offset-based pagination degrades with large datasets — performance drops significantly at high offsets"
  - "hasMore can return false incorrectly when totalResults is capped"
  - "No cursor-based pagination available — offset/limit is the only mechanism"

# === SKIP CONDITIONS ===
skip_this_unit_if:
  - condition: "User needs file-based bulk import, not REST API"
    use_instead: "business/erp-integration/oracle-fbdi-common-failures/2026"
  - condition: "User needs SOAP web services, not REST"
    use_instead: "Search knowledgelib.io for Oracle SOAP web services — no dedicated unit yet"

# === AGENT HINTS ===
inputs_needed:
  - key: data_volume
    question: "How many total records need to be retrieved?"
    type: choice
    options:
      - "< 500 records (single page)"
      - "500-10,000 records"
      - "10,000-100,000 records"
      - "> 100,000 records"
  - key: endpoint_type
    question: "Which Oracle REST API resource are you paginating?"
    type: choice
    options:
      - "fscmRestApi (ERP — POs, invoices, suppliers)"
      - "hcmRestApi (HCM — employees, positions)"
      - "crmRestApi (CX — accounts, opportunities)"
      - "commonRestApi (cross-module)"
  - key: use_case
    question: "What is the pagination use case?"
    type: choice
    options:
      - "Full data extraction / ETL"
      - "Incremental sync (delta changes)"
      - "Search / query with pagination"
      - "Reporting / analytics data pull"

# === DISTRIBUTION ===
canonical_source: "https://knowledgelib.io/business/erp-integration/oracle-rest-api-pagination-pitfalls/2026"
suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-03-09)"

# === RELATED UNITS ===
related_kos:
  depends_on: []
  related_to:
    - id: "business/erp-integration/oracle-erp-cloud-upgrade-impact-integrations/2026"
      label: "Quarterly updates can change REST API pagination behavior"
    - id: "business/erp-integration/oracle-fbdi-common-failures/2026"
      label: "FBDI alternative for bulk data loads"
  solves: []
  alternative_to: []
  often_confused_with:
    - id: "business/erp-integration/oracle-fbdi-common-failures/2026"
      label: "FBDI (file-based import) — different from REST API pagination"

# === SOURCES ===
sources:
  - id: src1
    title: "REST API for Common Features in Oracle Fusion Cloud Applications"
    author: Oracle
    url: https://docs.oracle.com/en/cloud/saas/applications-common/24d/farca/Resource_Methods.html
    type: official_docs
    published: 2024-11-01
    reliability: authoritative
  - id: src2
    title: "TotalResults Gets Limited To 500 When Offset Is Added"
    author: Oracle Support
    url: https://support.oracle.com/knowledge/Oracle%20Cloud/2728432_1.html
    type: official_docs
    published: 2024-06-01
    reliability: high
  - id: src3
    title: "What is Max supported value for limit parameter in GET REST API Call?"
    author: Oracle Support
    url: https://support.oracle.com/knowledge/Oracle%20Fusion%20Applications/2429019_1.html
    type: official_docs
    published: 2024-01-01
    reliability: high
  - id: src4
    title: "Get most out of Rest APIs in Oracle Fusion Cloud — Tips"
    author: Prateek Parasar
    url: https://www.linkedin.com/pulse/get-most-out-rest-apis-oracle-fusion-cloud-some-tips-prateek-parasar
    type: technical_blog
    published: 2024-09-15
    reliability: moderate_high
  - id: src5
    title: "OIC — How to extract large data from Oracle Fusion using REST APIs"
    author: SOAlicious
    url: https://soalicious.blogspot.com/2024/06/oic-how-to-extract-large-data-from.html
    type: technical_blog
    published: 2024-06-20
    reliability: moderate_high
  - id: src6
    title: "Get All Contracts REST API Is Only Fetching 500 Contracts"
    author: Oracle Support
    url: https://support.oracle.com/knowledge/Oracle%20Cloud/2779820_1.html
    type: official_docs
    published: 2024-03-01
    reliability: high
---

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

## TL;DR

- **Bottom line**: Oracle Fusion Cloud REST APIs use offset/limit pagination with a hard cap of 500 records per request (sometimes 499). The `totalResults` field is unreliable when combined with `offset`, and performance degrades significantly with high offset values. [src1, src2]
- **Key limit**: Maximum 500 records per request — setting `limit=1000` silently returns only 500 (or 499). Default is 25 if not specified. [src3]
- **Watch out for**: `totalResults` can cap at the current page size when `offset` is specified, and `hasMore` may return `false` incorrectly based on the capped `totalResults`. [src2]
- **Best for**: Integration developers extracting data from Oracle Fusion Cloud REST APIs who need reliable pagination.
- **Authentication**: Standard Oracle Fusion OAuth 2.0 or Basic Auth — pagination behavior is independent of auth method.

## System Profile

Oracle Fusion Cloud REST APIs (fscmRestApi, hcmRestApi, crmRestApi) all use the same pagination mechanism based on `offset` and `limit` query parameters. The response includes pagination metadata (`hasMore`, `totalResults`, `count`, `offset`, `limit`) that enables clients to iterate through result sets. However, several documented issues make naive pagination implementations unreliable, particularly for large datasets exceeding a few thousand records. This card covers all Oracle Fusion Cloud REST API pillars (ERP, HCM, CX). [src1]

| Property | Value |
|---|---|
| **Vendor** | Oracle |
| **System** | Oracle Fusion Cloud (ERP, HCM, CX) |
| **API Surface** | REST (fscmRestApi, hcmRestApi, crmRestApi) |
| **Pagination Type** | Offset/Limit (no cursor-based option) |
| **Max Records Per Page** | 500 (some endpoints: 499) |
| **Default Page Size** | 25 |
| **Deployment** | Cloud |
| **API Docs** | [Oracle Fusion REST API Reference](https://docs.oracle.com/en/cloud/saas/applications-common/24d/farca/Resource_Methods.html) |
| **Status** | GA |

## API Surfaces & Capabilities

| Pagination Feature | Expected Behavior | Actual Behavior | Reliability |
|---|---|---|---|
| `limit` parameter | Set page size up to server max | Capped at 500 (silently — no error) | Reliable (within 500 cap) |
| `offset` parameter | Skip N records | Works but performance degrades at high values | Unreliable at scale |
| `hasMore` response | `true` if more records exist | Can return `false` incorrectly with offset | Unreliable |
| `totalResults` response | Total matching records | Caps at current page size when offset is used | Unreliable |
| `count` response | Records in current page | Accurate count of current page | Reliable |
| `links.next` response | URL for next page | Available on most endpoints | Reliable |
| `onlyData=true` param | Omit links, metadata | Reduces response size significantly | Reliable |
| `totalResults=true` param | Include total count | Required to get totalResults in response | Reliable (but value may be wrong) |

[src1, src2, src3]

## Rate Limits & Quotas

### Pagination Limits

| Limit Type | Value | Applies To | Notes |
|---|---|---|---|
| Max records per request | 500 | All REST endpoints | Some endpoints cap at 499 |
| Default page size | 25 | All REST endpoints | If limit not specified |
| Min page size | 1 | All REST endpoints | — |
| Max offset value | No hard limit | All REST endpoints | But performance degrades significantly |
| Practical offset limit | ~10,000 | All REST endpoints | Beyond this, response times degrade |
| totalResults accuracy | Unreliable with offset | All REST endpoints | May cap at current page count |

[src1, src3, src5]

### Performance Characteristics

| Offset Range | Response Time (typical) | Reliability | Recommendation |
|---|---|---|---|
| 0 - 2,000 | 1-3 seconds | High | Standard pagination works well |
| 2,000 - 10,000 | 3-10 seconds | Medium | Consider filter-based chunking |
| 10,000 - 50,000 | 10-30 seconds | Low | Use date-range partitioning |
| > 50,000 | 30+ seconds or timeout | Very low | Do not use offset — use FBDI or BIP |

[src4, src5]

## Authentication

| Flow | Use When | Token Lifetime | Notes |
|---|---|---|---|
| OAuth 2.0 JWT Bearer | Server-to-server extraction | Session timeout (default 2h) | Recommended for batch pagination |
| OAuth 2.0 Auth Code | User-context operations | Access: 2h, Refresh: until revoked | User permissions determine accessible records |
| Basic Auth | Development/testing | Session-based | Not recommended for production |

### Authentication Gotchas

- Token expiry during long pagination runs: If you are paginating through >10,000 records, your OAuth token may expire mid-extraction. Fix: Refresh the token proactively before each page request if the token is near expiry. [src4]
- Integration user permissions determine the result set — pagination returns only records the authenticated user can access. Different users may get different totalResults for the same query. [src1]

## Constraints

- Maximum 500 records per request — setting `limit` to any higher value silently returns only 500 (or 499 on some endpoints). No error is generated. [src3]
- `totalResults` is unreliable when combined with `offset` — it may cap at the current page size (e.g., showing `totalResults: 500` when the actual total is 50,000). [src2]
- `hasMore` derives from `totalResults` — if `totalResults` is wrong, `hasMore` will also be wrong, potentially returning `false` when more records exist. [src2]
- No cursor-based pagination is available — all Oracle Fusion REST APIs use offset/limit exclusively, which means concurrent data modifications can cause skipped or duplicate records. [src1]
- Performance degrades with high offset values because the database still scans through all preceding records. [src5]
- Some endpoints (notably HCM LOV-related endpoints) exhibit different pagination behavior than documented. [src2]

## Integration Pattern Decision Tree

```
START — Need to paginate Oracle Fusion REST API
├── Total records to extract?
│   ├── < 500 records
│   │   └── Single request with limit=500 — no pagination needed
│   ├── 500 - 5,000 records
│   │   └── Standard offset/limit loop — reliable range
│   │       ├── Set limit=500
│   │       ├── Increment offset by 500 each iteration
│   │       └── Stop when count < limit (NOT when hasMore=false)
│   ├── 5,000 - 50,000 records
│   │   └── Filter-based chunking recommended
│   │       ├── Partition by date range (CreationDate, LastUpdateDate)
│   │       ├── Each chunk < 5,000 records
│   │       └── Paginate within each chunk
│   └── > 50,000 records
│       └── Do NOT use REST API pagination
│           ├── Option A: FBDI export + BIP report
│           ├── Option B: BI Publisher data model extract
│           └── Option C: OIC bulk extract pattern
├── Incremental sync (delta only)?
│   ├── YES → Filter by LastUpdateDate > last_sync_timestamp
│   │   └── Paginate the filtered result set
│   └── NO → Full extraction — use chunking or FBDI
├── Need accurate total count?
│   ├── YES → Query with limit=0&totalResults=true first
│   │   └── Then paginate with actual offset/limit
│   └── NO → Use count < limit as stop condition
└── Concurrent data modifications possible?
    ├── YES → Expect duplicates/gaps — implement deduplication
    └── NO → Standard pagination is sufficient
```

## Quick Reference

| Problem | Symptom | Root Cause | Fix |
|---|---|---|---|
| Only 500 records returned | Set limit=1000 but got 500 | Server caps limit at 500 | Use limit=500 and paginate |
| Only 25 records returned | Did not set limit parameter | Default is 25 | Explicitly set limit=500 |
| hasMore=false but more data exists | totalResults capped at page size | Known Oracle bug with offset | Use count < limit as stop condition |
| totalResults shows 500 but actual is 50K | totalResults caps when offset used | Oracle bug (Doc ID 2728432.1) | Do not rely on totalResults |
| Slow responses at high offsets | Offset > 10,000 | Database scans all preceding rows | Use date-range partitioning |
| Missing or duplicate records | Data modified during pagination | Offset pagination not snapshot-isolated | Add deduplication logic |
| Different results per user | Integration user has limited access | Row-level security filtering | Verify user permissions |
| Offset 0 and offset 1 return same first record | 1-indexed offset on some endpoints | Inconsistent offset behavior | Test offset behavior per endpoint |

[src2, src3, src5, src6]

## Step-by-Step Integration Guide

### 1. Basic Pagination Loop (Reliable for < 5,000 records)

The simplest correct pagination pattern: use `count < limit` as the stop condition, NOT `hasMore`. [src1, src2]

```python
# Input:  Oracle Fusion REST API URL, OAuth token
# Output: All records from the endpoint

import requests

def paginate_oracle_rest(base_url, endpoint, token, query_params=None, max_records=None):
    """Reliable pagination for Oracle Fusion REST API.

    Key: Use count < limit as stop condition, NOT hasMore.
    hasMore is unreliable when totalResults caps at page size.
    """
    url = f"{base_url}{endpoint}"
    headers = {"Authorization": f"Bearer {token}"}
    all_records = []
    offset = 0
    limit = 500  # Max supported — do not set higher

    while True:
        params = {"offset": offset, "limit": limit, "onlyData": "true"}
        if query_params:
            params.update(query_params)

        resp = requests.get(url, headers=headers, params=params, timeout=60)
        if resp.status_code != 200:
            raise Exception(f"API error: {resp.status_code} - {resp.text}")

        data = resp.json()
        items = data.get("items", [])
        count = data.get("count", len(items))

        all_records.extend(items)

        # Stop condition: count < limit means we got the last page
        # Do NOT use hasMore — it is unreliable with offset
        if count < limit:
            break

        offset += limit

        # Safety: max records limit
        if max_records and len(all_records) >= max_records:
            break

    return all_records

# Usage
records = paginate_oracle_rest(
    base_url="https://your-instance.fa.us2.oraclecloud.com",
    endpoint="/fscmRestApi/resources/11.13.18.05/purchaseOrders",
    token="YOUR_TOKEN",
    query_params={"q": "Status=OPEN"}
)
print(f"Retrieved {len(records)} records")
```

**Verify**: Record count matches expected. If significantly fewer, check user permissions.

### 2. Date-Range Chunking (For > 5,000 records)

Partition the extraction by date ranges to avoid high-offset performance degradation. [src4, src5]

```python
# Input:  Date range, endpoint, token
# Output: All records partitioned by creation date

from datetime import datetime, timedelta

def chunked_extract(base_url, endpoint, token, start_date, end_date, chunk_days=7):
    """Extract large datasets using date-range partitioning.

    Avoids high-offset performance degradation by splitting
    the query into date-range chunks.
    """
    all_records = []
    current_start = start_date

    while current_start < end_date:
        current_end = min(current_start + timedelta(days=chunk_days), end_date)

        # Filter by CreationDate range
        date_filter = (
            f"CreationDate >= '{current_start.strftime('%Y-%m-%d')}' "
            f"and CreationDate < '{current_end.strftime('%Y-%m-%d')}'"
        )

        chunk_records = paginate_oracle_rest(
            base_url=base_url,
            endpoint=endpoint,
            token=token,
            query_params={"q": date_filter}
        )

        all_records.extend(chunk_records)
        print(f"  Chunk {current_start.date()} to {current_end.date()}: {len(chunk_records)} records")

        current_start = current_end

    return all_records

# Usage
records = chunked_extract(
    base_url="https://your-instance.fa.us2.oraclecloud.com",
    endpoint="/fscmRestApi/resources/11.13.18.05/purchaseOrders",
    token="YOUR_TOKEN",
    start_date=datetime(2025, 1, 1),
    end_date=datetime(2026, 3, 1),
    chunk_days=30  # Monthly chunks
)
print(f"Total extracted: {len(records)} records")
```

**Verify**: Sum of all chunk counts equals expected total.

### 3. Incremental Sync Pattern

For ongoing synchronization, extract only records modified since the last sync. [src4]

```python
# Input:  Last sync timestamp, endpoint
# Output: Records modified since last sync

import json
from pathlib import Path

def incremental_sync(base_url, endpoint, token, state_file="sync_state.json"):
    """Extract only records modified since last sync.

    Stores last sync timestamp in a state file.
    Uses LastUpdateDate filter to avoid full extraction.
    """
    # Load last sync timestamp
    state = {}
    if Path(state_file).exists():
        with open(state_file) as f:
            state = json.load(f)

    last_sync = state.get("last_sync", "2020-01-01T00:00:00Z")
    current_time = datetime.utcnow().isoformat() + "Z"

    # Query for records modified since last sync
    query_filter = f"LastUpdateDate > '{last_sync}'"
    records = paginate_oracle_rest(
        base_url=base_url,
        endpoint=endpoint,
        token=token,
        query_params={"q": query_filter, "orderBy": "LastUpdateDate:asc"}
    )

    # Save new sync timestamp
    state["last_sync"] = current_time
    state["last_record_count"] = len(records)
    with open(state_file, "w") as f:
        json.dump(state, f, indent=2)

    return records

records = incremental_sync(
    base_url="https://your-instance.fa.us2.oraclecloud.com",
    endpoint="/fscmRestApi/resources/11.13.18.05/purchaseOrders",
    token="YOUR_TOKEN"
)
print(f"Delta: {len(records)} modified records")
```

**Verify**: First run returns all records. Subsequent runs return only newly modified records.

### 4. Get Accurate Total Count First

If you need the real total before paginating, query with `limit=0` and `totalResults=true`. [src1]

```python
# Input:  Endpoint, query filter
# Output: Accurate total record count

def get_total_count(base_url, endpoint, token, query_filter=None):
    """Get accurate total count before paginating.

    Uses limit=0 to avoid returning data, just the count.
    Must include totalResults=true in params.
    """
    url = f"{base_url}{endpoint}"
    headers = {"Authorization": f"Bearer {token}"}
    params = {"limit": 0, "totalResults": "true"}
    if query_filter:
        params["q"] = query_filter

    resp = requests.get(url, headers=headers, params=params, timeout=30)
    if resp.status_code == 200:
        data = resp.json()
        total = data.get("totalResults", 0)
        print(f"Total records: {total}")
        return total
    else:
        print(f"Error: {resp.status_code}")
        return -1

total = get_total_count(
    base_url="https://your-instance.fa.us2.oraclecloud.com",
    endpoint="/fscmRestApi/resources/11.13.18.05/purchaseOrders",
    token="YOUR_TOKEN",
    query_filter="Status=OPEN"
)
```

**Verify**: Compare returned total against the actual record count from a full pagination run.

## Code Examples

### Python: Production-Ready Pagination with Retry

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

import requests
import time
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

class OraclePaginator:
    """Production-ready Oracle Fusion REST API paginator.

    Handles: 500-record cap, unreliable hasMore, token refresh,
    retry with backoff, and rate limiting.
    """
    def __init__(self, base_url, token, max_retries=3):
        self.base_url = base_url
        self.token = token
        self.session = requests.Session()
        retry = Retry(total=max_retries, backoff_factor=2,
                      status_forcelist=[429, 500, 502, 503, 504])
        self.session.mount("https://", HTTPAdapter(max_retries=retry))

    def extract_all(self, endpoint, query=None, fields=None):
        all_records = []
        offset = 0
        limit = 500

        while True:
            params = {"offset": offset, "limit": limit, "onlyData": "true"}
            if query: params["q"] = query
            if fields: params["fields"] = fields

            resp = self.session.get(
                f"{self.base_url}{endpoint}",
                headers={"Authorization": f"Bearer {self.token}"},
                params=params, timeout=60
            )

            if resp.status_code == 429:  # Rate limited
                wait = int(resp.headers.get("Retry-After", 60))
                time.sleep(wait)
                continue

            resp.raise_for_status()
            data = resp.json()
            items = data.get("items", [])
            count = data.get("count", len(items))

            all_records.extend(items)

            if count < limit:  # Last page
                break
            offset += limit

        return all_records

# Usage
paginator = OraclePaginator(
    base_url="https://your-instance.fa.us2.oraclecloud.com",
    token="YOUR_TOKEN"
)
pos = paginator.extract_all(
    endpoint="/fscmRestApi/resources/11.13.18.05/purchaseOrders",
    query="Status=OPEN",
    fields="POHeaderId,OrderNumber,Status,TotalAmount"
)
```

### cURL: Test Pagination Behavior

```bash
# Input:  Valid OAuth token, endpoint
# Output: Pagination metadata fields

# Test 1: Default pagination (expect 25 records)
curl -s "https://your-instance.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/purchaseOrders" \
  -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\")}, limit={d.get(\"limit\")}, offset={d.get(\"offset\")}')
"

# Test 2: Max page size (expect 500 records)
curl -s "https://your-instance.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=500" \
  -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\")}')
"

# Test 3: Check if limit > 500 is silently capped
curl -s "https://your-instance.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=1000" \
  -H "Authorization: Bearer $TOKEN" | python -c "
import sys,json; d=json.load(sys.stdin)
print(f'Requested limit=1000, got count={d.get(\"count\")}, limit={d.get(\"limit\")}')
"

# Test 4: Check totalResults behavior with offset
curl -s "https://your-instance.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/purchaseOrders?limit=500&offset=500&totalResults=true" \
  -H "Authorization: Bearer $TOKEN" | python -c "
import sys,json; d=json.load(sys.stdin)
print(f'offset=500: totalResults={d.get(\"totalResults\")}, hasMore={d.get(\"hasMore\")}, count={d.get(\"count\")}')
"
```

## Data Mapping

### Pagination Response Fields

| Field | Type | Description | Reliable? | Notes |
|---|---|---|---|---|
| `items` | array | Records in current page | Yes | Always accurate |
| `count` | integer | Number of items in current page | Yes | Always accurate |
| `hasMore` | boolean | Whether more pages exist | No | Can be wrong with offset |
| `totalResults` | integer | Total matching records | No | Caps at page size with offset |
| `limit` | integer | Page size used | Yes | May be lower than requested |
| `offset` | integer | Current offset position | Yes | Always accurate |
| `links` | array | Pagination links (next, prev) | Mostly | Available on most endpoints |

### Data Type Gotchas

- `totalResults` requires explicitly passing `totalResults=true` as a query parameter to be included in the response — without it, the field may not appear at all. [src1]
- `offset` is 0-indexed on most Oracle Fusion endpoints, but some Saviynt/third-party integrations report 1-indexed behavior — test per endpoint. [src5]
- The `links.next` URL in the response is the most reliable pagination mechanism when available — it correctly encodes the next offset. [src1]

## Error Handling & Failure Points

### Common Error Codes

| Code | Meaning | Cause | Resolution |
|---|---|---|---|
| 200 + empty items | No records match query | Filter too restrictive or permissions | Check query and user access |
| 200 + count=0, hasMore=false | End of results | Normal — last page | No action needed |
| 200 + totalResults mismatch | totalResults capped at page size | Known Oracle bug (Doc ID 2728432.1) | Use count < limit as stop condition |
| 400 Bad Request | Invalid query syntax | Malformed q parameter | Check SCIM filter syntax |
| 401 Unauthorized | Token expired | Long pagination run | Refresh token before each page |
| 404 Not Found | Invalid API version or endpoint | Version deprecated | Check describe endpoint for current version |
| 429 Too Many Requests | Rate limited | Too many concurrent requests | Exponential backoff with Retry-After header |
| 500 Internal Server Error | Oracle server error | High offset or complex query | Simplify query, reduce offset range |

[src1, src2, src3]

### Failure Points in Production

- **totalResults caps at 500 with offset**: When you add an `offset` parameter to a query, `totalResults` may incorrectly show 500 (matching the limit) instead of the true total. This causes `hasMore` to return `false`, stopping pagination prematurely. Fix: Never use `hasMore` as the stop condition. Use `count < limit` instead. [src2]
- **Silent limit cap at 499**: Some Oracle Fusion endpoints (notably HCM) cap the limit at 499 instead of 500. Setting `limit=500` returns only 499 records with `hasMore=true`. Fix: Test each endpoint's actual max limit and adjust accordingly. [src3]
- **Performance collapse at high offsets**: Offset pagination at >10,000 records causes response times to exceed 30 seconds and may trigger timeouts. Fix: Use date-range partitioning or FBDI for large extractions. [src5]
- **Token expiry during long runs**: Paginating >10,000 records can take 20+ minutes, exceeding the default OAuth token lifetime. Fix: Check token expiry before each page request and refresh proactively. [src4]

## Anti-Patterns

### Wrong: Using hasMore as the Stop Condition

```python
# ❌ BAD — hasMore is unreliable when totalResults caps
while True:
    data = fetch_page(offset)
    items = data["items"]
    all_records.extend(items)
    if not data.get("hasMore", False):  # BUG: may return false prematurely
        break
    offset += limit
# Result: Only fetches first 500 records even if 50,000 exist
```

### Correct: Using count < limit as the Stop Condition

```python
# ✅ GOOD — count < limit is always reliable
while True:
    data = fetch_page(offset)
    items = data["items"]
    count = data.get("count", len(items))
    all_records.extend(items)
    if count < limit:  # Last page — fewer records than requested
        break
    offset += limit
# Result: Correctly fetches all records regardless of totalResults bugs
```

### Wrong: Setting limit > 500 and Expecting All Records

```python
# ❌ BAD — Assuming you can get 5000 records in one call
resp = requests.get(f"{url}?limit=5000")
items = resp.json()["items"]
print(f"Got {len(items)} records")  # Prints 500, not 5000
# No error message — silently capped
```

### Correct: Always Use limit=500 and Paginate

```python
# ✅ GOOD — Respect the 500 cap, paginate explicitly
all_items = []
offset = 0
while True:
    resp = requests.get(f"{url}?limit=500&offset={offset}")
    items = resp.json()["items"]
    all_items.extend(items)
    if len(items) < 500:
        break
    offset += 500
```

### Wrong: Using Offset for Very Large Datasets

```python
# ❌ BAD — Offset > 50K causes timeouts and unreliable results
for offset in range(0, 200000, 500):
    data = fetch_page(offset)  # Timeout at offset ~50,000
```

### Correct: Date-Range Partitioning for Large Datasets

```python
# ✅ GOOD — Partition by date to keep each chunk small
for month_start in date_range(start, end, months=1):
    month_end = month_start + timedelta(days=30)
    records = paginate_with_filter(
        q=f"CreationDate >= '{month_start}' and CreationDate < '{month_end}'"
    )
    all_records.extend(records)
```

## Common Pitfalls

- **Trusting totalResults with offset**: Oracle's documented bug (Doc ID 2728432.1) causes totalResults to cap at the limit value when offset is used. Fix: Use `count < limit` as the stop condition and get the true total separately with `limit=0&totalResults=true`. [src2]
- **Not specifying limit parameter**: The default page size is 25 records — many developers forget to set it and wonder why only 25 records come back. Fix: Always explicitly set `limit=500` for data extraction. [src1]
- **Ignoring response time at high offsets**: Performance degrades exponentially with offset. An extraction that starts at 1 second per page may take 30+ seconds per page at offset 50,000. Fix: Use date-range partitioning for datasets >5,000 records. [src5]
- **Not handling rate limiting (429)**: Oracle Fusion Cloud enforces API rate limits. Rapid pagination without backoff triggers 429 responses. Fix: Implement exponential backoff and respect the Retry-After header. [src1]
- **Assuming all endpoints behave identically**: Some endpoints cap at 499 instead of 500. Some endpoints support `links.next`, others do not. Fix: Test pagination behavior per endpoint before building production integrations. [src3]

## Diagnostic Commands

```bash
# Check max supported limit for an endpoint
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'limit={d.get(\"limit\")}, count={d.get(\"count\")}, totalResults={d.get(\"totalResults\")}, hasMore={d.get(\"hasMore\")}')
"

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

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

## Version History & Compatibility

| Release | Date | Status | Pagination Changes | Impact |
|---|---|---|---|---|
| 25D | 2025-11 | Current | No known pagination changes | — |
| 25C | 2025-08 | Supported | New endpoints with standard pagination | Low |
| 25B | 2025-05 | Supported | Some HCM endpoints changed offset behavior | Test HCM endpoints |
| 25A | 2025-02 | Supported | No pagination changes | — |
| 24D | 2024-11 | Supported | totalResults behavior changed for some endpoints | Medium |

[src1, src2]

### Deprecation Policy

Oracle does not explicitly version or deprecate pagination behavior — it is considered an implementation detail that may change between releases. The 500-record limit and offset/limit mechanism have been stable since at least 2020, but the totalResults bug has persisted since at least 2022 (Doc ID 2728432.1) and shows no sign of being fixed. [src2, src3]

## When to Use / When Not to Use

| Use When | Don't Use When | Use Instead |
|---|---|---|
| Extracting < 5,000 records via REST API | Full data extraction > 50,000 records | FBDI export or BI Publisher report |
| Incremental sync (delta by LastUpdateDate) | One-time data migration | FBDI import/export |
| Real-time search with paginated results | Bulk analytics data extraction | Oracle Analytics Cloud or OTBI |

## Important Caveats

- The 500-record limit is a hard cap that cannot be increased — not even by Oracle Support.
- `totalResults` is unreliable when combined with `offset` — this is a known, documented Oracle bug that has persisted for multiple years.
- `hasMore` derives from the (unreliable) `totalResults` — do not use it as your stop condition.
- Offset-based pagination is not snapshot-isolated — records added/deleted during pagination can cause duplicates or gaps.
- Performance degrades with high offsets — use date-range partitioning for large datasets.
- Different API pillars (fscm, hcm, crm) may have slightly different pagination behavior — test per endpoint.

## Related Units

- [Oracle FBDI Common Failures](/business/erp-integration/oracle-fbdi-common-failures/2026)
- [Oracle ERP Cloud Quarterly Update Impact on Integrations](/business/erp-integration/oracle-erp-cloud-upgrade-impact-integrations/2026)