Oracle Fusion Cloud REST API Deep Dive: 499-Record Limit, Pagination, Expand & Field Filtering

TL;DR

• Bottom line: Oracle Fusion REST API caps every GET response at 499 records -- implement offset-based pagination loops checking hasMore to retrieve complete datasets. Use REST for real-time CRUD under 10K records; switch to FBDI for bulk imports and BICC for bulk extracts.
• Key limit: 499 records per GET request (hard cap); ~5,000 API calls/hour/user; POST operations recommended max 500 records per batch. Setting limit > 499 silently returns 499 and misreports totalCount.
• Watch out for: Setting limit=500 does NOT return 500 records -- it returns 499 AND reports totalCount as 499, hiding the true total count. Always use limit=499 and rely on the hasMore flag.
• Best for: Real-time individual record CRUD, moderate-volume queries (<10K records), interactive lookups with field filtering and expand for child resources.
• Authentication: Basic Auth over SSL (simplest), OAuth 2.0 via Oracle IAM/IDCS (recommended for production), SAML 2.0 bearer token, or JWT token.

System Profile

Oracle Fusion Cloud Applications (also marketed as Oracle Cloud ERP, Oracle Cloud SCM, Oracle Cloud HCM, and Oracle CX) exposes a comprehensive REST API surface for real-time integration with business objects across all pillars. The REST API is built on the Oracle ADF Business Components framework and has evolved through multiple framework versions (v1 through v4). This card covers the REST API surface specifically -- not SOAP, BICC, or FBDI. All Oracle Fusion Cloud editions share the same REST API capabilities.

API Surfaces & Capabilities

Oracle Fusion Cloud offers multiple API surfaces for different integration patterns. REST is the primary modern surface, but understanding when to use alternatives is critical. [src5, src6]

Rate Limits & Quotas

Per-Request Limits

Rolling / Daily Limits

Authentication

Oracle Fusion Cloud REST APIs use a global Oracle Web Services Manager (OWSM) security policy called Multi Token Over SSL. [src4]

Authentication Gotchas

• Basic Auth sends credentials with every request and does not support MFA. Oracle recommends OAuth 2.0 for all production integrations. [src4]
• OAuth 2.0 token endpoint URL varies by Oracle IAM domain -- do not hardcode; it changes between environments. [src4]
• JWT bearer flow requires registering the signing certificate in Oracle IAM as a trusted partner. Self-signed certificates work for development; use CA-signed for production. [src4]
• The integration user's role and data security policies determine API access scope. A 403 Forbidden error usually means missing role assignment, not wrong credentials.

Constraints

• 499-record hard cap: Every REST GET request returns a maximum of 499 records regardless of the limit parameter value. Setting limit=500 or higher silently returns 499 and incorrectly reports totalCount as 499.
• REST is not for bulk operations: Oracle explicitly recommends against using REST API for high-volume data extraction (>10K records) or bulk imports. Use BICC for outbound bulk and FBDI for inbound bulk.
• Framework version affects response structure: REST framework v3+ changes nested child resources from arrays to collection objects with hasMore/count metadata. Integration code written for v1/v2 responses will break on v3+ payloads.
• No webhooks/push notifications via REST: REST API is purely request/response. For event-driven patterns, you must use Business Events via Oracle Integration Cloud (OIC) or poll with LastUpdateDate filters.
• SOAP deprecated for new development: Oracle has stated SOAP services should not be used when an equivalent REST API exists.
• Multi-tenant fair-use throttling: Oracle can throttle individual tenants that generate excessive API load, even below the per-user limit.
• No direct database access: Oracle Fusion Cloud provides no direct SQL access. All data access must go through REST, BICC, BIP, or FBDI interfaces.

Integration Pattern Decision Tree

START -- User needs to integrate with Oracle Fusion Cloud
+-- What's the data flow direction?
|   +-- OUTBOUND (reading from Fusion)?
|   |   +-- How many records per extraction?
|   |   |   +-- < 1,000 records --> REST API with limit=499 + offset pagination
|   |   |   +-- 1,000 - 10,000 records --> REST API pagination loop (2-20 API calls)
|   |   |   +-- 10,000 - 100,000 records --> Consider BIP reports or BICC incremental
|   |   |   +-- > 100,000 records --> BICC (mandatory for large extracts)
|   |   +-- Need real-time or scheduled?
|   |       +-- Real-time --> REST API with LastUpdateDate filter for delta
|   |       +-- Scheduled --> BICC incremental extract
|   +-- INBOUND (writing to Fusion)?
|   |   +-- How many records per load?
|   |   |   +-- < 500 records --> REST API POST/PATCH
|   |   |   +-- 500 - 100,000 records --> FBDI (file-based import via REST upload)
|   |   |   +-- > 100,000 records --> FBDI with split files
|   |   +-- Need immediate confirmation?
|   |       +-- YES --> REST API (synchronous response)
|   |       +-- NO --> FBDI (asynchronous, poll for status)
|   +-- EVENT-DRIVEN (react to changes in Fusion)?
|       +-- Business Events available for your object?
|       |   +-- YES --> Subscribe via OIC ERP Cloud Adapter
|       |   +-- NO --> REST API polling with LastUpdateDate filter
|       +-- Need guaranteed delivery?
|           +-- YES --> OIC + dead letter queue
|           +-- NO --> REST polling with retry
+-- Error tolerance?
    +-- Zero-loss required --> Idempotent design + FBDI (built-in per-record error reporting)
    +-- Best-effort --> REST with retry on 429/5xx

Quick Reference

REST API Endpoint Reference

Common API Base URLs

Key Query Parameters

Step-by-Step Integration Guide

1. Authenticate and obtain access token

Set up OAuth 2.0 client credentials flow with Oracle IAM for production integrations. [src4]

# OAuth 2.0 Client Credentials -- obtain access token
curl -s -X POST \
  "https://YOUR_IDCS_DOMAIN.identity.oraclecloud.com/oauth2/v1/token" \
  -H "Content-Type: application/x-www-form-urlencoded" \
  -u "CLIENT_ID:CLIENT_SECRET" \
  -d "grant_type=client_credentials&scope=urn:opc:resource:consumer::all"

Verify: Response contains access_token and expires_in fields

2. Query a resource with pagination

Fetch records using the 499-record pagination pattern. Always use hasMore to control the loop. [src2, src3]

# Fetch first page of invoices (499 records max)
curl -s -X GET \
  "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices?limit=499&offset=0&fields=InvoiceId,InvoiceNumber,InvoiceAmount" \
  -H "Authorization: Bearer $ACCESS_TOKEN"

Verify: Check count and hasMore fields in response

3. Implement full pagination loop

Loop through all pages using offset increments until hasMore is false. [src3]

all_records = []
offset = 0
while True:
    params = {"limit": 499, "offset": offset, "onlyData": "true"}
    resp = requests.get(url, headers=headers, params=params)
    data = resp.json()
    all_records.extend(data.get("items", []))
    if not data.get("hasMore", False):
        break
    offset += 499

Verify: len(all_records) matches total record count in Oracle Fusion

4. Use expand and field filtering for efficiency

Reduce payload size by selecting only needed fields and expanding child resources inline. [src1]

# Fetch invoice with expanded lines and selected fields
curl -s -X GET \
  "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices/12345?\
fields=InvoiceId,InvoiceNumber&expand=invoiceLines(fields=LineNumber,LineAmount)" \
  -H "Authorization: Bearer $ACCESS_TOKEN"

Verify: Response includes invoiceLines nested under the invoice

5. Create a record via POST

Insert a new business object. [src7]

# Create a new invoice
curl -s -X POST \
  "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices" \
  -H "Authorization: Bearer $ACCESS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"InvoiceNumber":"INV-2026-001","InvoiceAmount":15000.00,"InvoiceCurrency":"USD"}'

Verify: HTTP status 201 -- response body contains system-generated InvoiceId

6. Handle rate limiting and errors

Implement exponential backoff for 429 and 5xx responses. [src7]

for attempt in range(max_retries):
    resp = requests.get(url, headers=headers, timeout=60)
    if resp.status_code == 200:
        return resp.json()
    elif resp.status_code == 429:
        time.sleep(min(2 ** attempt * 2, 120))
        continue
    elif resp.status_code >= 500:
        time.sleep(min(2 ** attempt * 3, 180))
        continue

Verify: Test with invalid endpoint returns 404; invalid auth returns 401

Code Examples

Python: Paginated extraction of approved invoices

# Input:  Oracle Fusion Cloud credentials, business unit filter
# Output: Complete list of approved invoices as Python dicts

import requests, time

class OracleFusionClient:
    def __init__(self, host, client_id, client_secret, idcs_domain):
        self.host = host
        self.token_url = f"https://{idcs_domain}.identity.oraclecloud.com/oauth2/v1/token"
        self.auth = (client_id, client_secret)
        self.access_token = None

    def authenticate(self):
        resp = requests.post(self.token_url, auth=self.auth,
            data={"grant_type": "client_credentials",
                  "scope": "urn:opc:resource:consumer::all"}, timeout=30)
        resp.raise_for_status()
        self.access_token = resp.json()["access_token"]

    def get_all_records(self, resource, query=None, fields=None):
        url = f"https://{self.host}/fscmRestApi/resources/11.13.18.05/{resource}"
        headers = {"Authorization": f"Bearer {self.access_token}"}
        all_items, offset = [], 0
        while True:
            params = {"limit": 499, "offset": offset, "onlyData": "true"}
            if query: params["q"] = query
            if fields: params["fields"] = fields
            resp = requests.get(url, headers=headers, params=params, timeout=60)
            if resp.status_code == 429:
                time.sleep(30); continue
            resp.raise_for_status()
            data = resp.json()
            all_items.extend(data.get("items", []))
            if not data.get("hasMore", False): break
            offset += 499
        return all_items

JavaScript/Node.js: Fetch with expand and field filtering

// Input:  Oracle Fusion Cloud credentials, resource name
// Output: Records with expanded child resources

const https = require('https');

async function fetchWithExpand(host, token, resource, id, expandChild, fields) {
  let url = `https://${host}/fscmRestApi/resources/11.13.18.05/${resource}/${id}`;
  const params = new URLSearchParams();
  if (fields) params.set('fields', fields);
  if (expandChild) params.set('expand', expandChild);
  params.set('onlyData', 'true');
  url += '?' + params.toString();

  return new Promise((resolve, reject) => {
    const req = https.get(url, {
      headers: { 'Authorization': `Bearer ${token}` }
    }, (res) => {
      let data = '';
      res.on('data', chunk => data += chunk);
      res.on('end', () => {
        if (res.statusCode === 200) resolve(JSON.parse(data));
        else reject(new Error(`HTTP ${res.statusCode}: ${data}`));
      });
    });
    req.on('error', reject);
  });
}

cURL: Common diagnostic queries

# Input:  Access token, Oracle Fusion Cloud host
# Output: JSON responses for common API operations

# Discover available resources
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05" \
  -H "Authorization: Bearer $TOKEN" | python3 -m json.tool | head -50

# Get resource metadata
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices/describe" \
  -H "Authorization: Bearer $TOKEN"

# Pagination test -- check hasMore behavior
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices?\
limit=2&totalResults=true" -H "Authorization: Bearer $TOKEN"

Data Mapping

REST API Field Type Reference

Data Type Gotchas

• Oracle Fusion REST API returns dates as strings in YYYY-MM-DD format with no timezone. The actual timezone depends on the integration user's profile. Always configure the integration user with UTC timezone. [src7]
• Flexfield segment names differ between tenants. Use the /describe endpoint to discover segment names dynamically. [src1]
• Amount fields do not include currency -- you must fetch the currency field separately.
• LOV fields return internal codes via API but display human-readable meanings in the UI. Use expand=lookups to get both. [src1]

Error Handling & Failure Points

Common Error Codes

Failure Points in Production

• Silent 499 truncation: Setting limit=500 returns 499 records AND reports totalCount=499, hiding that more records exist. Fix: Always use limit=499 and loop on hasMore=true. [src2, src3]
• Framework version mismatch: Code expecting v1/v2 array responses breaks on v3+ collection responses. Fix: Check framework version in API URL and parse responses accordingly. [src1]
• Integration user timezone: Date filters use the integration user's timezone preference, not UTC. Fix: Set all integration users to UTC timezone in Oracle Fusion user preferences. [src7]
• Flexfield drift: DFF segments are configurable and can change without notice. Fix: Use the /describe endpoint to validate flexfield segments before processing.
• OAuth token caching failure: Not caching the OAuth token and requesting a new one per API call exhausts the rate limit. Fix: Cache the access token and reuse until near expiry. [src4]
• FBDI silent row failures: FBDI import jobs can succeed at job level while individual rows fail. Fix: Always check the FBDI import error report after each job. [src5]

Anti-Patterns

Wrong: Trusting totalCount instead of hasMore for pagination

# BAD -- totalCount is capped at 499 when limit >= 500
response = requests.get(f"{url}?limit=500")
data = response.json()
total = data["totalResults"]  # Returns 499 (WRONG!)
# Developer thinks there are only 499 records

Correct: Use hasMore flag with limit=499

# GOOD -- loop on hasMore, always use limit=499
all_records = []
offset = 0
while True:
    response = requests.get(f"{url}?limit=499&offset={offset}")
    data = response.json()
    all_records.extend(data["items"])
    if not data.get("hasMore", False):
        break
    offset += 499

Wrong: Using REST API for bulk data extraction

# BAD -- extracting 500K records via REST API
# At 499 records per page = 1,000+ API calls
# At ~5,000 calls/hr limit, this takes hours
for offset in range(0, 500000, 499):
    resp = requests.get(f"{url}?limit=499&offset={offset}")
    records.extend(resp.json()["items"])

Correct: Use BICC for large extracts, REST for real-time

# GOOD -- use BICC for bulk extraction
# 1. Configure BICC extract in Oracle Fusion
# 2. Schedule incremental extract
# 3. Retrieve files from UCM
# Use REST only for real-time lookups on individual records
resp = requests.get(f"{url}/{record_id}?fields=Id,Status,Amount")

Wrong: New OAuth token for every API call

# BAD -- requests a new token for every call
def get_invoice(invoice_id):
    token = get_new_oauth_token()  # New token EVERY call!
    return requests.get(f"{url}/{invoice_id}",
                       headers={"Authorization": f"Bearer {token}"})

Correct: Cache and reuse tokens until near expiry

# GOOD -- cache token and reuse
class TokenManager:
    def get_token(self):
        if time.time() < self._expires_at - 60:  # 60s buffer
            return self._token
        resp = requests.post(self.token_url, auth=self.auth,
            data={"grant_type": "client_credentials"})
        data = resp.json()
        self._token = data["access_token"]
        self._expires_at = time.time() + data["expires_in"]
        return self._token

Common Pitfalls

• Setting limit=500 instead of 499: The max is 499, not 500. Setting 500 misreports total count, silently corrupting pagination logic. [src2]
• Not using the fields parameter: Without fields, the API returns ALL attributes including LOB fields and flexfields, dramatically increasing response size. [src1]
• Ignoring framework version differences: Integration built against framework v1 will break when Oracle upgrades to v3+ (children change from arrays to collection objects). [src1]
• Assuming SOAP and REST have the same objects: Some business objects are only available via SOAP, and some only via REST. Always verify via the REST /describe endpoint. [src6]
• Not setting integration user timezone to UTC: Date-based queries use the integration user's timezone. Two users with different timezones get different results. [src7]
• Running FBDI imports during peak hours: FBDI jobs compete with online users for processing resources. Schedule during off-peak windows. [src5]
• Not checking FBDI error reports: A "Succeeded" job may have skipped thousands of invalid rows. Always check the BIP error report. [src5]

Diagnostic Commands

# Test authentication (Basic Auth for quick testing)
curl -s -o /dev/null -w "%{http_code}" \
  "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05" \
  -u "USERNAME:PASSWORD"
# Expected: 200 (authenticated) or 401 (bad credentials)

# Discover available resources
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05" \
  -H "Authorization: Bearer $TOKEN" | python3 -m json.tool | head -20

# Get resource metadata (field names and types)
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices/describe" \
  -H "Authorization: Bearer $TOKEN"

# Test pagination -- verify hasMore behavior
curl -s "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05/invoices?limit=2&totalResults=true" \
  -H "Authorization: Bearer $TOKEN"

# Check OAuth token validity
curl -s -o /dev/null -w "%{http_code}" \
  "https://YOUR_HOST/fscmRestApi/resources/11.13.18.05" \
  -H "Authorization: Bearer $TOKEN"

Version History & Compatibility

When to Use / When Not to Use

Important Caveats

• The 499-record limit is a hard platform constraint that cannot be increased by Oracle Support or configuration. It applies uniformly across ERP, SCM, HCM, and CX pillars.
• Rate limits (~5,000 calls/hr/user) are approximate and may vary. Oracle reserves the right to adjust throttling to protect shared infrastructure.
• REST API availability and resource coverage varies by Oracle Fusion Cloud release. New business objects are added each quarterly release.
• Framework version behavior is controlled by the API URL version string, not a global tenant setting. Different integrations can use different framework versions simultaneously.
• Oracle Fusion Cloud is SaaS-only with no on-premise deployment option. All API traffic traverses the public internet or Oracle Cloud Infrastructure FastConnect.