DEV Community: Mostafa Mansour

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

Mostafa Mansour — Sun, 19 Jul 2026 07:04:18 +0000

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
}

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

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'

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'

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.

"Oracle FSCM REST API: What Transfers From HCM and What Doesn't"

Mostafa Mansour — Fri, 17 Jul 2026 19:49:53 +0000

If you've integrated with Oracle Fusion HCM (/hcmRestApi/), the Financials and Supply Chain surface (/fscmRestApi/) is 90% the same API with a different catalog. Here's the 10% that matters, with working examples. Replace acme.fa.us2.oraclecloud.com with your pod; all sample data is anonymized.

Same host, different path segment

HCM:  https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers
FSCM: https://acme.fa.us2.oraclecloud.com/fscmRestApi/resources/11.13.18.05/invoices

q, finders, expand, fields, and limit/offset/hasMore pagination all behave identically. Auth is the same too — Basic over TLS for dev, OAuth 2.0 for production. A 403 on an FSCM resource almost always means a missing Financials/SCM duty role, not a wrong URL.

Know how filterable a resource is before you call it

These numbers come from the real Oracle OpenAPI spec:

Resource	q fields	Finders	Children
`/invoices`	88	1	7
`/expenses`	110	4	8
`/payablesPayments`	82	2	3
`/payablesInterfaceInvoices`	186	0	0

That last row is the gotcha pattern: some resources are all q fields and no finders, others the reverse. Checking first saves you from writing a filter the endpoint can't serve.

q filters that actually work on /invoices

Real queryable fields include BusinessUnit, ApprovalStatus, AmountPaid, AccountingDate:

GET /fscmRestApi/resources/11.13.18.05/invoices
    ?q=AmountPaid=0 and BaseAmount>10000 and AccountingDate>'2026-01-01'

Lowercase and/or, single quotes on strings only, URL-encode exactly once.

Prefer finders when they exist

GET /fscmRestApi/resources/11.13.18.05/payablesPayments
    ?finder=PrimaryKey;CheckId=300000001234567

/expenses ships FindCCTransactions and friends; /receivablesInvoices has invoiceSearch. Finders map to indexed view criteria on Oracle's side — consistently faster than the equivalent q on large tables.

The FSCM-specific 404

Resource names are camelCase and case-sensitive: payablesPayments works, payablespayments is a 404 that looks like a valid path. This one costs people hours.

Full guide with expand examples, pagination, and the complete error table: Oracle FSCM REST API: Base URL, Key Endpoints, and Working Examples.

I build OPAL, an offline Oracle Fusion API explorer — 59,000+ HCM/FSCM endpoints searchable without internet, with visual q filter and finder builders and a full endpoint reference.

"6 Oracle HCM REST API Requests I Copy-Paste Every Week"

Mostafa Mansour — Tue, 14 Jul 2026 08:11:44 +0000

Oracle's HCM REST API docs describe every endpoint, but they rarely show a complete working request next to the response you'll actually get. Here are the six calls I reach for constantly — adapt the pod hostname (acme.fa.us2.oraclecloud.com) and credentials, everything else works as-is. All sample data is anonymized; Basic Auth for readability (use OAuth 2.0 in production).

Base URL for everything below:

https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05

1. Get workers, paginated properly

curl -u "integration.user@example.com:password" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?limit=10"

Always pass limit (default page size is 25, max 500). The response's hasMore: true tells you to keep going — increment offset until it flips to false.

2. One worker by person number with `q`

curl -u "user:pass" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='100001'"

String values in q must be single-quoted; numbers don't. If you get "The query parameter is invalid", the field probably isn't queryable on that endpoint — workers alone has 307 queryable fields, and it's never all of them.

3. Range filter, URL-encoded once

curl -u "user:pass" --get \
  --data-urlencode "q=PersonNumber>='100001' and PersonNumber<='100500'" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers"

Two rules: and/or lowercase, and encode the expression exactly once (--data-urlencode does this). Double-encoding is the classic cause of silently empty results.

4. Finder instead of q — faster lookups

curl -u "user:pass" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?finder=findByPersonId;PersonId=300000001234567"

Syntax is finder=<name>;<bindVar>=<value> — semicolon, not &. Finders are pre-built indexed queries; findByPersonId beats the equivalent q filter every time. Bonus: findReports;ManagerPersonId=... answers "who reports to this manager" with zero assignment traversal.

5. Cut the payload with `fields`

curl -u "user:pass" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='100001'&fields=PersonId,PersonNumber,DateOfBirth"

Routinely an 80–90% response-size cut on collections. The single easiest performance win in the whole API.

6. Inline child resources with `expand`

curl -u "user:pass" \
  "https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='100001'&expand=names,workRelationships.assignments"

Names, emails, and assignments are children — by default you only get links. Dot notation reaches nested children. Avoid expand=all on collections unless you enjoy timeouts.

The full post has 12 examples — including calling child collections directly, error responses (400/401/403) and what they actually mean, and pagination loops: Oracle HCM REST API Examples: 12 Real Requests You Can Copy.

I build OPAL, an offline Oracle Fusion API explorer — 59,000+ HCM/FSCM endpoints searchable without internet, with visual q filter and finder builders and a full endpoint reference.

"Oracle Fusion REST API Authentication: Basic Auth vs JWT vs OAuth 2.0"

Mostafa Mansour — Sun, 12 Jul 2026 08:12:37 +0000

Every Oracle Fusion REST API call — HCM, Financials, or SCM — starts with the same question: how do I authenticate? Oracle supports three mechanisms, and picking the wrong one for your scenario is the most common reason integrations stall in week one.

Here's the short version of each, when to use it, and the 401-vs-403 distinction that resolves most "authentication" tickets.

One rule first

Fusion authorization is always user-based. Whatever method you choose, the token or credential resolves to a Fusion user, and that user's roles decide what the request can touch. There is no app-only access to business data — server-to-server flows run as a dedicated integration user.

Option 1: Basic Auth over TLS

Zero setup. Credentials travel base64-encoded in the Authorization header on every request.

curl -u 'integration.user@example.com:YourPassword' \
  'https://acme.fa.us2.oraclecloud.com/hcmRestApi/resources/11.13.18.05/workers?limit=5'

Fine for development and quick internal scripts. Not great long-term: passwords expire on the pod's rotation policy (integrations break silently at 3 a.m.), and the header is the full identity — replayable if leaked.

Option 2: JWT bearer with a trusted issuer

The middle path. Your system signs a JWT with a private key; Fusion validates it against a certificate you uploaded once (Security Console → API Authentication). No identity-domain round trip per request, no password rotation problem.

{
  "iss": "www.example.com",
  "prn": "integration.user@example.com",
  "sub": "integration.user@example.com",
  "iat": 1780000000,
  "exp": 1780000900
}

Sign with RS256, send as Authorization: Bearer <jwt>. The prn/sub claim must match an active Fusion username — that user's roles are what the request runs with. Keep exp short; the token is self-contained, so a leaked one is valid until expiry.

Option 3: OAuth 2.0 via IDCS / IAM

The production-grade option, and required when a third party or federation (Entra ID, Okta) is involved. Register a confidential application in the identity domain linked to your Fusion instance, enable the grants you need, add the Fusion resource scope.

Because Fusion needs a user context, pure client-credentials tokens are not enough for business-object APIs — server-to-server flows use the user assertion variant: your app authenticates with its client credentials and asserts which Fusion user the token represents.

curl -u '<client_id>:<client_secret>' \
  -d 'grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer' \
  --data-urlencode 'assertion=<signed user-assertion JWT>' \
  --data-urlencode 'scope=<fusion app scope>' \
  'https://idcs-abc123.identity.oraclecloud.com/oauth2/v1/token'

The win over trusted-issuer JWT: central revocation, audit, token lifetime policy, and rotation live in the identity domain instead of your code.

401 vs 403 — the distinction that saves hours

401 Unauthorized — Fusion doesn't know who you are: wrong password, expired token, clock skew on iat/exp, issuer certificate not uploaded, malformed header.
403 Forbidden — Fusion knows who you are and says no: the integration user is missing the duty/job role for that resource, or data security scopes the user out of that business unit.
404 on a real endpoint — sometimes authorization in disguise: a few resources return 404 instead of 403 when the user has no access. Check roles before doubting the URL.

If /workers returns 403 but /locations returns 200, your auth is fine — a role is missing. That's fixed in the Security Console, not in your code.

Choosing

Proof of concept → Basic Auth
Scheduled integration you own end-to-end → JWT trusted issuer
Production, multiple consumers, federation, anything user-facing → OAuth 2.0

And always: a dedicated integration user with minimum roles. Never run integrations as a personal account, and never grant broad roles to "make the 403s go away."

The full guide — with the complete setup steps, header examples, and troubleshooting table — is on our blog: Oracle Fusion REST API Authentication.

I build OPAL, an offline Oracle Fusion API explorer — 59,000+ HCM/FSCM endpoints searchable without internet, with visual q filter and finder builders and a full endpoint reference.

Why the Oracle Fusion REST API q Parameter Fails and How to Fix It

Mostafa Mansour — Sat, 11 Jul 2026 08:02:05 +0000

The q parameter is one of the most useful parts of Oracle Fusion REST APIs. It lets you filter records directly from the API instead of retrieving a large result set and filtering it later in your integration layer.

It is also one of the most common reasons Fusion API calls fail.

A request that looks correct can still return an error like:

The query parameter is invalid.

or:

Invalid query expression.

or sometimes the request succeeds but the response is not filtered the way you expected.

This usually happens because Oracle Fusion REST filtering is not the same as SQL filtering. The q parameter only works with fields and operators that are supported by the specific REST resource you are calling.

This guide explains the most common reasons the q parameter fails and how to troubleshoot it correctly.

What is the q parameter in Oracle Fusion REST APIs?

The q parameter is used to filter records in Oracle Fusion REST API requests.

A simple example:

GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='10001'

Another example from Financials:

GET /fscmRestApi/resources/11.13.18.05/invoices?q=InvoiceNumber='INV-1001'

The general pattern is:

q=FieldName operator value

Common operators include:

=
!=
>
<
>=
<=

Some endpoints also support operators such as LIKE, BETWEEN, or null checks, but support is not universal. You must confirm what the specific endpoint accepts.

Why the q parameter fails

Most q issues come from one of these causes:

The field is not queryable.
The field belongs to a child resource.
The field name is wrong.
The value format is wrong.
The operator is not supported for that field.
The filter is being applied at the wrong API level.
The endpoint expects a finder instead of a normal q filter.

Let's go through each one.

1. The field is not queryable

Not every field returned by a REST API response can be used inside q.

This is one of the biggest surprises for Fusion developers.

You may see a field in the response, for example:

{
  "PersonNumber": "10001",
  "DisplayName": "Test Employee",
  "HireDate": "2024-01-01"
}

But that does not automatically mean all three fields are valid in q.

This may work:

GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='10001'

But this may fail depending on the endpoint metadata:

GET /hcmRestApi/resources/11.13.18.05/workers?q=HireDate='2024-01-01'

The response field and the queryable field list are not always the same.

How to fix it

Before writing the filter, check whether the field is queryable for that REST resource.

You can do that by reviewing the OpenAPI metadata or the Oracle REST documentation for that resource. If the field is not listed as queryable, use another field, a finder, or a lower-level child endpoint.

2. The field belongs to a child resource

This is very common with the Workers API.

For example, you may want active employee assignments only:

AssignmentType = 'E'
AssignmentStatusType = 'ACTIVE'
PrimaryFlag = true

But these fields usually belong to the worker assignment child resource, not the top-level worker resource.

This kind of request may not work as expected:

GET /hcmRestApi/resources/11.13.18.05/workers?q=AssignmentStatusType='ACTIVE'

Why?

Because /workers filters workers at the worker level. It does not always filter nested child records such as work relationships or assignments.

Even if you use expand, the q filter is still applied to the parent resource unless Oracle explicitly supports filtering the child resource in that call.

Example:

GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='10001'&expand=workRelationships.assignments

This can return the worker and expand assignments, but it does not mean you can filter the assignment rows from the parent q parameter.

How to fix it

Call the child resource directly when you need child-level filtering.

The pattern is usually:

GET /hcmRestApi/resources/11.13.18.05/workers/{workersUniqID}/child/workRelationships/{workRelationshipsUniqID}/child/assignments?q=AssignmentStatusType='ACTIVE'

This is more work because you may need parent IDs first, but the filter is applied at the correct resource level.

3. The field name is wrong

Oracle Fusion REST field names are case-sensitive in many contexts.

This can fail:

q=personnumber='10001'

This is the correct style:

q=PersonNumber='10001'

The same applies to fields such as:

InvoiceNumber
BusinessUnit
PersonId
AssignmentId
DocumentType

Small spelling or casing differences can break the filter.

How to fix it

Copy the exact field name from the REST metadata or from a working response. Do not guess field names from the Fusion UI label.

For example, the UI may say:

Person Number

But the API field may be:

PersonNumber

4. The value format is wrong

String values normally need quotes.

Correct:

q=PersonNumber='10001'

Risky or invalid:

q=PersonNumber=10001

For dates, use the format expected by the API. In many cases, ISO date format is safest:

q=CreationDate>='2026-01-01'

For booleans, check the endpoint behavior. Some APIs use true/false, while others expose Y/N, flags, or lookup codes.

Examples:

q=PrimaryFlag=true

or:

q=StatusCode='Y'

The correct value depends on the field definition.

5. The operator is not supported

Developers often try SQL-style expressions in Oracle Fusion REST APIs.

For example:

q=DisplayName LIKE '%John%'

This may not work on every endpoint.

Even when LIKE works in one REST resource, it may not work in another. Oracle Fusion REST APIs are large, and behavior can vary by module and resource.

How to fix it

Start with simple equality:

q=DisplayName='John Smith'

Then test other operators only after confirming the field supports them.

If partial search is required, check whether the endpoint provides a finder that supports keyword-style search.

6. Null checks can be sensitive

Another common issue is filtering null values.

Developers may try:

q=InactiveDate is null

or:

q=InactiveDate=null

Whether this works depends on the resource and supported query syntax. Some resources support null-style filtering. Others do not.

How to fix it

Check the supported query operators for that endpoint. If null filtering is not supported, you may need to retrieve a smaller set of records using another condition, then filter null values in your integration layer.

Not perfect, but better than pulling the full dataset without any filter.

7. You may need a finder instead of q

Oracle Fusion REST APIs often expose named finders.

A finder is a predefined search pattern with specific parameters.

Example:

GET /hcmRestApi/resources/11.13.18.05/workers?finder=findByPersonId;PersonId=300000123456789

Finders can be more reliable than q when Oracle has already defined the intended search path.

If a q filter keeps failing, check whether the endpoint has a finder for the same business scenario.

Practical troubleshooting checklist

When a q filter does not work, follow this order:

Confirm the endpoint is correct.
Confirm the field belongs to that exact resource, not a child resource.
Confirm the field is queryable.
Confirm the exact field name and casing.
Confirm the value format.
Start with one filter condition only.
Add additional conditions one by one.
Check whether a finder exists.
Test the child endpoint directly if filtering child data.
Only filter in the integration layer after reducing the response as much as possible.

Example: Workers API q filter

A simple worker lookup:

GET /hcmRestApi/resources/11.13.18.05/workers?q=PersonNumber='10001'

If you need assignment-level conditions, do not assume this will work from the parent worker resource:

GET /hcmRestApi/resources/11.13.18.05/workers?q=AssignmentStatusType='ACTIVE'

Instead, inspect the worker child resources and test the assignments child endpoint.

Example: Invoices API q filter

A common invoice lookup:

GET /fscmRestApi/resources/11.13.18.05/invoices?q=InvoiceNumber='INV-1001'

If you need invoice attachments, use expand:

GET /fscmRestApi/resources/11.13.18.05/invoices?q=InvoiceNumber='INV-1001'&expand=attachments

But remember: expanding attachments does not mean the parent q filter can filter attachment fields.

How OPAL helps

OPAL is built for this exact workflow.

Instead of guessing the q syntax from documentation pages, OPAL lets you search Oracle Fusion REST endpoints, inspect available query fields, check finders, browse child resources, and test the request directly against your Fusion environment.

This is especially useful when working with large APIs like Workers, Invoices, Purchase Orders, Document Records, Learning, or Procurement resources.

You can download OPAL here:

https://opalapi.dev

Final recommendation

Do not treat the q parameter like SQL.

Treat it as a resource-specific filter syntax.

The safest approach is:

Check the exact REST resource.
Check the queryable fields.
Avoid filtering child data from the parent endpoint.
Use finders when available.
Test small filters first.

This will save a lot of time and reduce unnecessary trial and error in Postman or integration tools.

This post is part of our complete Oracle HCM API guide — base URLs, authentication, q filters, finders, key endpoints, and common errors in one place.

Originally published at opalapi.dev.

DEV Community: Mostafa Mansour

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

The response wrapper

The six bugs

The loop that avoids all six

Filter before you paginate

Expanded children paginate too

"Oracle FSCM REST API: What Transfers From HCM and What Doesn't"

Same host, different path segment

Know how filterable a resource is before you call it

q filters that actually work on /invoices

Prefer finders when they exist

The FSCM-specific 404

"6 Oracle HCM REST API Requests I Copy-Paste Every Week"

1. Get workers, paginated properly

2. One worker by person number with q

3. Range filter, URL-encoded once

4. Finder instead of q — faster lookups

5. Cut the payload with fields

6. Inline child resources with expand

"Oracle Fusion REST API Authentication: Basic Auth vs JWT vs OAuth 2.0"

One rule first

Option 1: Basic Auth over TLS

Option 2: JWT bearer with a trusted issuer

Option 3: OAuth 2.0 via IDCS / IAM

401 vs 403 — the distinction that saves hours

Choosing

Why the Oracle Fusion REST API q Parameter Fails and How to Fix It

What is the q parameter in Oracle Fusion REST APIs?

Why the q parameter fails

1. The field is not queryable

How to fix it

2. The field belongs to a child resource

How to fix it

3. The field name is wrong

How to fix it

4. The value format is wrong

5. The operator is not supported

How to fix it

6. Null checks can be sensitive

How to fix it

7. You may need a finder instead of q

Practical troubleshooting checklist

Example: Workers API q filter

Example: Invoices API q filter

How OPAL helps

Final recommendation

2. One worker by person number with `q`

5. Cut the payload with `fields`

6. Inline child resources with `expand`