fusion-query-mcp

An MCP server that lets an AI agent explore Oracle Fusion Cloud (SaaS) schema metadata, run pre-built BI Publisher reports with bind parameters, and — the part that matters — validate the results against ground truth you already trust, iterating until the answer is provably right.

Read §1 before anything else. This server does not run arbitrary SQL, and that is not a gap waiting to be filled. It is a boundary the Fusion pod enforces. Every design decision below follows from it.

1. The boundary that shapes everything

1.1 Fusion has no database

Oracle Fusion SaaS gives no direct database access. The only sanctioned way to get SQL results out is a BI Publisher Data Model of type SQL Query, executed through the BI Publisher Run Report API. The SQL inside a data model is fixed at design time; only parameter values travel over the API.

That much is documented Oracle behaviour. The interesting part is what happens next.

1.2 The obvious design, and why it is dead

The obvious way to build a query server on top of that is to create one data model whose SQL is a lexical parameter holding the whole statement:

SELECT * FROM (&p_query)      -- Plan A
SELECT &p_select FROM &p_from &p_where &p_group &p_order   -- Plan B

An &-prefixed BI Publisher parameter is substituted into the SQL text before parsing, so in principle it can carry anything. This is the widely repeated community pattern, and it is what this project was originally built around.

It does not work on a current Fusion pod. Controlled experiments against the customer's live pod, not guesses:

So: binds are substituted, lexicals are not. Not "malformed", not "mis-declared" — substituted with nothing, silently, every time. Plan B fails identically because it also uses &. This is near-certainly deliberate Oracle hardening: arbitrary SQL text arriving over a SaaS reporting API is exactly the thing a multi-tenant vendor must prevent, and a lexical parameter is the hole that would allow it.

Consequences for anyone tempted to "fix" this:

• There is no clever escaping, encoding or wrapper that restores lexicals. The substitution happens (or does not) inside BI Publisher, before your text is ever SQL.

• View Data in the data-model editor also renders the lexical empty, so you cannot distinguish "my parameter is wrong" from "this pod does not do lexicals" by looking at the editor. Only the round trip tells you, and it tells you ORA-00903 either way. That ambiguity is what costs the day.

• The failure mode is identical to a beginner mistake, which is why the original design survived so long before being disproved.

1.3 What the server does instead

Pre-built reports, run with bind values.

An administrator creates each report's data model once, with real SQL and :bind variables. The server never sends SQL — it sends values for the binds of a report that already exists in the catalog. The registry of runnable reports lives in config.yaml under fusion.reports.

Schema exploration survives in full, because its SQL is fixed and only the LIKE patterns vary. That is what the three shipped reports are.

┌──────────────┐   MCP (stdio)   ┌──────────────────────────┐   HTTPS   ┌──────────────────────────────┐
│ Claude Code /│ ───────────────▶│  fusion-query-mcp        │ ─────────▶│ Oracle Fusion Cloud          │
│ Claude agent │                 │  ├ report registry       │           │  BI Publisher                │
└──────────────┘                 │  ├ execution backends    │           │  ├ XX_MCP_LIST_TABLES_RPT    │
                                 │  │  ├ SOAP (this pod)    │           │  ├ XX_MCP_DESCRIBE_TABLE_RPT │
                                 │  │  └ REST (404s here)   │           │  ├ XX_MCP_SEARCH_COLUMNS_RPT │
                                 │  ├ xml result parser     │           │  └ …your own reports         │
                                 │  ├ validation engine     │           │     SQL fixed, :binds vary   │
                                 │  ├ fixtures store        │           └──────────────────────────────┘
                                 │  ├ fusion hints KB       │
                                 │  └ audit log             │
                                 └──────────────────────────┘

This costs flexibility and buys three things worth having:

1. It works. The path is verified end to end on a live pod.

2. A real boundary. An agent can only run what an administrator built and registered. See §9 Security.

3. Reviewable SQL. Every statement lives in a catalog object a functional owner can open, read and change, instead of being improvised per request.

Non-goals: no data model / catalog object creation via API; no DML or DDL of any kind; no pagination or streaming; no OTBI logical SQL; no per-end-user identity mapping (single service account).

1.4 The legacy lexical path is still shipped

fusion_run_query and fusion_validate_query still exist, and fusion.datasources / engine_mode still configure them. They are for a pod that does honour lexical substitution. On a pod that does not — and you should assume yours does not until you have proved otherwise — they will fail with ORA-00903 and the server's error hint will tell you exactly that and point you at the report tools. Do not spend a day there.

Related MCP server: mcp-oraclefusion

2. The tool surface

All tools are read-only against Fusion's data and return structured JSON with a short summary. Two write to the BI Publisher catalog rather than to business records: fusion_author_report (opt-in) and fusion_bootstrap.

Also exposed: the knowledge base as MCP resources (fusion://hints, fusion://hints/{topic}) and the working protocol as an MCP prompt (fusion-query-workflow).

The protocol an agent follows is: discover reports → explore schema (tables, columns, flexfields) → run a registered report, or compose SQL and run it ad-hoc → validate against ground truth.

2a. Agent-composed SQL — fusion_adhoc_query and fusion_author_report

Both are off by default and exist for one goal: hand the agent a report specification and let it produce the numbers — explore the schema, resolve DFF segments, build the query, run it — or mint the report in the system, with no human in the loop.

The mechanism is the §1 discovery turned around. The pod refuses to let SQL travel through a report (&p_query is never substituted), but nothing stops SQL from becoming one: fusion_adhoc_query authors an ephemeral data model + report via the catalog service (§3.2a), runs it once through the normal pipeline — binds, parameter-echo verification, redaction, audit — and deletes both objects in a finally. The real SQL enters the audit log before the round trip. fusion_author_report does the same authoring but keeps the pair, verifies it with a probe run, and registers it in reports.dynamic.yaml (never config.yaml — see below).

What survives from the report path: the read-only guard (now guarding genuinely caller-supplied SQL), the deny-list, max_sql_chars, the parameter-echo check on every bind, redaction and audit. What changes is the trust boundary, and this must be said plainly:

With allow_adhoc_queries on, the allow-list is no longer "the registered reports" but "any SELECT the guard admits, as the service account". The real boundary becomes the service account's Fusion roles and row-level Data Security. Scope that account before switching this on:

fusion:
  allow_adhoc_queries: true      # ephemeral ad-hoc SELECTs
  allow_report_authoring: true   # persistent agent-minted reports

Three rules keep the two registries honest: config.yaml is human-owned and is never written by the server; agent-minted reports live in reports.dynamic.yaml (gitignored), so wiping them is deleting one file; and on a name collision config.yaml always wins — an agent report can never shadow a human one.

2b. The local docs snapshot — fusion_docs_*

Oracle publishes full documentation for every Fusion table and view (the OEDM books on docs.oracle.com): column business descriptions, primary keys, foreign keys, indexes, flexfield mappings, and for views their defining SQL. None of that lives in the pod's dictionary, and each live schema tool costs a 12–14 s BI Publisher round-trip.

Build a local snapshot once:

pip install fusion-query-mcp[scrape]
fusion-query-oedm --release 26b --books procurement financials

Snapshots are one file per quarterly release, kept together under snapshots/ (snapshots/oedm_docs_<release>.sqlite3, the CLI's default name). On first docs use the server resolves the pod's release — the fusion.pod_release pin if set, else one ad-hoc probe of AD_PRODUCT_GROUPS when allow_adhoc_queries is on — and serves the matching file, falling back to oedm_db_path (and then to the newest release-named sibling) when no exact match exists. Every fallback is echoed in the tools' notes, and fusion_health_check reports pod_release next to the snapshot it chose.

When a snapshot is available, fusion_docs_describe_table and fusion_docs_search_columns serve exploration from it in milliseconds — including full-text search over column descriptions, so "supplier hold reason" finds the column without knowing any naming convention. Responses are slice-shaped (filter with columns_like) to keep token cost down.

The division of authority is deliberate: docs for meaning, pod for truth. The snapshot never decides what exists — a column present in the docs but missing on the pod fails loudly inside fusion_adhoc_query's echo-verified execution, so drift cannot produce a silent wrong answer. Custom objects and the pod's case-twin duplicates are only visible to the live tools; in a 10-table trial (release 26c vs a live pod) the docs matched the dictionary column-for-column on every verified table. Re-runs resume where they stopped; --refresh re-fetches after a release upgrade.

2c. The REST catalogue snapshot — fusion_api_*

Everything above reads. This snapshot exists to support doing: it is what turns "register a purchase requisition" into a list of questions a person can answer.

fusion-query-apicat purchaseRequisitions suppliers --probe-writes

Two passes, because the pod tells the truth in two different ways.

Pass 1 — the map. GET /fscmRestApi/resources/latest/<res>/describe gives every field with type, business description, length, list-of-values and child collections. Complete and safe. It is also large: one resource is 0.5–1.3 MB, so runtime discovery is not an option — this is exactly why a snapshot exists, the same argument as §2b.

Pass 2 — the truth (--probe-writes). POST an empty body and read which layer rejects it. HTTP 401/403 means the account cannot write the resource at all; HTTP 400 means the request cleared authorization and reached business validation, which then names the fields it wanted. Nothing is created either way.

Pass 2 exists because the declared metadata is wrong in both directions, measured on the pod 2026-08-15:

The best derivation from the declared flags still missed a genuinely required field — and that is the failure that hurts, because the agent builds a confident, incomplete payload. So the snapshot stores declared flags as declared_mandatory hints, and required_fields separately as what the pod itself demanded. required_source says which you are looking at; when it says "not probed", do not trust the hints.

fusion_api_list_actions lists what this account was observed able to create — never what /describe advertises. fusion_api_describe returns the interview script for one resource (25 settable fields on purchaseRequisitions, not the 68 declared; LOV child views separated from real child collections like lines). fusion_api_search maps a phrase the user said onto the field name the API expects.

Unlike the OEDM snapshot, this one is per pod, not per release: it records what a specific service account was observed able to do, and that does not carry across pods even on the same release.

The safety limit of pass 2, stated plainly: it is safe because a resource with required fields cannot be created by an empty body. A resource whose fields are all optional would be created. So it never runs unless asked, never sweeps discovered resources, and treats an HTTP 201 as an incident — deleting the record immediately and reporting it loudly.

2d. Doing things — fusion_resolve_value, fusion_prepare_action, fusion_commit_action

Everything before this reads. These three change data, and they are shaped around what the pod does not provide.

The loop. fusion_api_list_actions says what this account may create. fusion_api_describe gives the field list. fusion_resolve_value turns each name the user said into the id the API wants. fusion_prepare_action renders exactly what would travel and returns a token — nothing reaches Fusion yet. Only fusion_commit_action, with that token and confirmed=True, sends it.

Why the pause is here. Fusion has no "show a human first": an authorised, valid POST executes on arrival. Oracle already enforces privilege (HTTP 403 on a resource this account cannot write) and validation (PreparerId is required, POR-2010313) far better than this server could — see §9 — so what is left for this layer is confirmation, idempotency, and a record of intent.

Why refusal is a normal outcome. There is no dry-run for a Fusion create: a valid POST commits, so an invalid one is the only safe probe. Being refused is therefore how missing fields are discovered, and fusion_commit_action returns the pod's own sentence plus the field names parsed out of it rather than a summary — that sentence is the next question to ask the user.

Idempotency. Because retrying is the normal path, accidental double-submission is the normal risk, and Fusion offers no idempotency key on these resources. A fingerprint of verb + resource + record + payload is recorded when a commit succeeds; an identical action returns the first outcome instead of creating a second requisition. Only successes are remembered — re-running a refused attempt after adding the missing field is the intended workflow. The guard is per-process and not persisted, and it survives a pod rebind so rebinding cannot become a way to commit twice.

Resolving values runs on the read channel, not the action channel: the list-of-values views /describe advertises are not addressable over REST (all four candidate paths answered 404, probed 2026-08-15), so the mapping lives in knowledge/value_resolvers.yaml and the lookup is an ordinary guarded, audited SELECT. The user's text always travels as a bind. When several rows match, the candidates come back for the user to choose between — the tool never picks one.

Policy lives under actions: in config.yaml: a general posture plus exceptions named one at a time (actions.overrides), the same shape as the SQL guard's forbidden keywords plus explicit allowances.

2e. Scheduled processes — fusion_submit_job, fusion_job_status

The REST channel covers what has a resource. A great deal of Fusion does not: Import Payables Invoices, Create Accounting, bulk loads. Those run through ErpIntegrationService (61 operations, verified on the pod 2026-08-15).

fusion_submit_job(package, definition, parameters, confirmed=True)  -> request id
fusion_job_status(request_id)                                       -> state

Two things to know, both from the service's own contract rather than from documentation:

parameters is positional. The WSDL states it plainly: "The order of the parameters is maintained as per the list. The corresponding entry in the list should be blank when a given parameter is not passed." Pass "" for a skipped argument — omitting it shifts every later one by a slot, and the job runs against the wrong data without complaining.

A submitted job cannot be recalled. This is the sharp difference from the REST channel. A REST create is usually refused and changes nothing, so retrying is cheap; once an ESS request id exists, the job is queued. Submission returns when the job is queued, not when it has done anything — poll fusion_job_status before telling anyone it worked. finished is false for any state not known to be terminal, deliberately: one extra poll beats losing track of a running job.

Bulk loads (FBDI). fusion_import_bulk_data reads a file from disk, uploads it to UCM and queues the import jobs that consume it; fusion_find_uploaded_files answers "did it land, and in the right place?" without writing anything.

fusion_import_bulk_data(file_path, account, jobs, confirmed=True)
fusion_find_uploaded_files(prefix, account)   # read-only

Two things to get right, both taken from the service's own schemas (DocumentDetails.xsd, EssJob.xsd) rather than from documentation:

The UCM account is the quiet failure. A file filed under the wrong account uploads successfully and is then invisible to the import job — no error anywhere, on either side. That is why account has no default and why fusion_find_uploaded_files exists. Check it against the job's own documentation (fin$/payables$/import$ and the like) before confirming.

Job parameters are encoded two different ways in the same service. This is the trap worth memorising:

paramList and parameterList differ by two characters and take opposite shapes; all three were read off the schemas rather than inferred. A parameter containing a comma is refused rather than silently split — the schema defines no escaping, so splitting would shift every later argument by a slot and the job would run against the wrong data without complaining. Use fusion_submit_job for such parameters.

When a job fails, get the log. fusion_job_status reporting ERROR is the least useful true statement available; the reason is in the log.

fusion_job_log(request_id, file_type="log", save_to="./logs")

File contents are never returned inline — a job log can be megabytes — so pass save_to to write them and get the paths back. One undecodable file in a response does not lose the others.

The rest of the channel. fusion_export_bulk_data runs an extract job and leaves the output in UCM (marked destructive because a queued job cannot be recalled, not because it changes data). fusion_update_interface_data is the repair path: when an import loads ten thousand rows and rejects forty into the interface tables, it replaces those forty rather than reloading everything — load_request_id decides which load is being corrected, so read it back to the user before confirming.

2f. The worklist — fusion_list_tasks, fusion_task_detail, fusion_act_on_task

Approvals and notifications waiting for the signed-in user. A third channel: neither the REST resources nor ErpIntegrationService reach it.

fusion_list_tasks(status="ASSIGNED")
fusion_task_detail(number)
fusion_act_on_task(number, outcome, comment, confirmed=True)

Finding it took six failed guesses. fscmRestApi/.../notifications, worklistTasks under both the FSCM and HCM APIs, userNotifications and the older TaskQueryService SOAP endpoint all answer HTTP 404. What answers is /bpm/api/4.0/tasks.

Available outcomes are per task AND per user. Each task carries an actionList; entries with actionType: System are plumbing (REASSIGN, ESCALATE, ACQUIRE), and everything else is a business outcome. Observed on this pod with one account: two e-signature tasks offer APPROVE/REJECT, two change-order tasks offer only OK, and an absence approval offers nothing at all — it is held by a group the account has not acquired. So fusion_act_on_task reads the live task first and refuses an outcome that is not on it; an empty outcomes list is a real state, not an error.

No replay guard, deliberately. An approved task is no longer ASSIGNED, so a second attempt fails at the service. This is the one place in the server where the pod's own state is the duplicate protection.

Task payloads need Accept: application/xml. The business content behind a task (/tasks/{n}/payload) is XML only — application/json and even text/xml answer HTTP 406, while application/xml and */* work.

2g. Any Fusion API, not just procurement

Fusion has one REST API per product pillar. Resources may be written bare (purchaseRequisitions, resolving against FSCM) or qualified with a family:

hcm:workers          -> /hcmRestApi/resources/latest/workers
crm:accounts         -> /crmRestApi/resources/latest/accounts
purchaseRequisitions -> /fscmRestApi/resources/latest/purchaseRequisitions

Aliases (fscm, hcm, crm, scm, fin, prc, helpdesk) are a convenience, not a whitelist: any value ending in RestApi passes through, so a pillar the alias table has never heard of is reachable the day the pod exposes it. What each account may actually write there is still Oracle's decision, and still discovered by probing rather than declared.

2h. Any SOAP service — fusion_soap_*

ErpIntegrationService has a dedicated backend because it is used constantly and its quirks are worth encoding. That does not generalise: Fusion exposes dozens more services, and whole areas of functionality (purchase order change orders, opportunity management, BI Publisher catalog administration) exist only over SOAP.

fusion-query-apicat --soap /fscmService/PurchaseOrderService --soap /crmService/OpportunityService

fusion_soap_list_services()
fusion_soap_describe(service, operation=None)
fusion_soap_call(service, operation, parameters, confirmed=True)

Snapshotted, not discovered per call — and for a correctness reason, not a speed one. The envelope shape differs per service. Measured on the pod:

An invoker that hardcoded any one of those would build a perfectly well-formed envelope that the other two reject. So each service's namespace pair is read from its own WSDL and stored. (The speed argument holds too — the ERP WSDL is 128 KB and its parameter names live in separately-fetched schemas.)

Both shapes are verified live: getESSJobStatus through the generic path returns the same answer as the dedicated fusion_job_status, and getFolderContents on the single-namespace BI Publisher service succeeds.

Parameters are sent in contract order, not dict order. A declared parameter you omit is sent as an empty element rather than dropped, because for positional operations omitting it shifts every later argument by a slot. A name that is not in the contract is reported back as ignored rather than silently discarded.

Everything here is gated as destructive, including read-only operations. Maintaining a list of which of several hundred operation names are safe would be a list that is wrong the moment a service is added; one door with one confirmation is the honest trade.

Descriptive flexfields — fusion_describe_flexfields

Customer-added fields land in the generic ATTRIBUTE1..n columns, and only the DFF definition says what each one means. The tool reads the definition tables this pod actually has (fnd_df_segments_b joined to _tl for the user-facing prompt, language-filtered), so a spec saying "supply duration" is resolvable to PO_HEADERS_ALL.ATTRIBUTE2 without a human. The DFF code is usually the base table name without _ALL. Two traps it already encodes: on this pod mainline definition rows carry SANDBOX_ID = '1' — the "obvious" SANDBOX_ID IS NULL filter returns zero rows forever — and segments outside Global Data Elements are only valid where ATTRIBUTE_CATEGORY equals their context code.

Row limits

A registered report has no row cap of its own — the data model returns what it returns, and the tool layer slices to max_rows and reports truncated honestly. Defaults come from limits.default_max_rows, ceiling from limits.hard_max_rows.

The guard

The SQL guard (SELECT/WITH only, DML and DBMS_* rejected, deny-list patterns, row-cap injection, missing-alias warning) protects every path that carries caller-supplied SQL: the legacy fusion_run_query, and now fusion_adhoc_query / fusion_author_report (§2a). It is deliberately absent from the registered-report path: there is no SQL in a report call to guard. The equivalent control there is that an administrator wrote the SQL and registered the report.

3. One-time Fusion setup — once per report

A Fusion administrator must do this. The server cannot create catalog objects for you. Do it once per report you want the agent to be able to run; the four exploration reports in §3.3 are the minimum for a useful server.

For those four, it is one command — they ship as SQL files inside the package and fusion-query-bootstrap creates them (§3.0). Reach for §3.2a only for a report of your own, and for §3.2b only when you would rather click.

3.0 The four core reports — fusion-query-bootstrap

fusion_list_tables, fusion_describe_table, fusion_search_columns and fusion_describe_flexfields are not implemented in Python: each one runs a BI Publisher report that has to exist in your catalog first. Until it does, the tools fail — and they fail after fusion_health_check passes, because the connection is fine and only the catalog objects are missing.

fusion-query-bootstrap --dry-run     # checks the manifest, contacts no pod
fusion-query-bootstrap               # creates and verifies all four

The SQL lives in src/fusion_query_mcp/bootstrap/*.sql and the rest of each contract — binds, defaults, and the column aliases the server parses — in manifest.yaml beside it. §3.3 below documents those files; it is no longer the place you copy them from.

What the command guarantees:

• Offline-fatal first. Every check that does not need a pod — the read-only guard, declared columns against the SQL's own aliases, declared parameters against its :binds — runs before the first network call, so a mismatch cannot leave half the reports created.

• Idempotent. An object already in the catalog is skipped, not overwritten. Re-running after a partial failure finishes the job. --force is the only way to replace one, and it is genuinely destructive: the pod has no upsert, so replacing means deleting the pair first (§8.7).

• Verified, honestly. Each new report is run once through the real pipeline with echo checking on. A failed echo check is an error — the pod ignored the binds, so the report would answer the same thing whatever it is asked. Zero rows is only a warning: the probe binds (FND_%, CREATED_BY — chosen to be pillar-neutral) may simply match nothing on your pod.

• Registry-aware. It ends by naming any report that exists on the pod but is missing from fusion.reports, with the block to paste. Start from config.example.yaml and there is nothing to paste: the four are already declared there, and a test holds the manifest and that file in step.

Useful flags: --pod NAME (multi-pod layouts), --only NAME (repeatable), --data-source — ApplicationDB_FSCM by default, which an HCM-only pod must override — and --folder for a catalog location other than /Custom/MCP.

fusion_bootstrap — the same thing, without the shell

The agent can do this itself. fusion_bootstrap is the MCP tool over the same manifest: it creates whichever of the four are missing, verifies each one, and writes the registry entries for any the config does not already declare — so an agent that meets a bare pod, finds fusion_list_tables failing, and calls this, is querying a minute later with no human in the loop.

It is deliberately much narrower than fusion_author_report:

The default differs because the boundaries differ. Authoring widens what statements can reach the pod, which is a thing an administrator must weigh; bootstrapping widens nothing — the statements are fixed, reviewed and tested offline, and no caller-supplied SQL travels through it. What it can write is eight catalog objects with known names, and only onto paths that are empty. The real limit is the same one as everywhere else: a service account without BI authoring roles simply gets refused by Fusion. Set allow_bootstrap: false for a pod whose catalog must not be written to at all.

Replacing an existing report stays human-only, in a shell, with --force.

3.1 Service account

A Fusion user with BI authoring/consuming roles sufficient to create and run BI Publisher objects — typically BI Administrator for setup, and a narrower custom role for runtime.

Important: all row-level Data Security is evaluated against this account, not against whoever talks to the MCP server. Rows this account cannot see simply do not exist as far as any agent using this server is concerned.

3.2a Headless authoring — fusion-query-author

The pod exposes /xmlpserver/services/v2/CatalogService (verified on this pod 2026-08 alongside ExternalReportWSSService), and a data model / report pair is just two small zip archives — so the whole of §3.2b can be done by a CLI:

fusion-query-author --name open_pos \
    --sql-file open_pos.sql \
    --param "p_bu_name=%" --param "p_date_from=1900-01-01" \
    --verify "p_bu_name=%" --verify "p_date_from=2026-01-01"

That creates /Custom/MCP/XX_MCP_OPEN_POS_DM.xdm and ..._RPT.xdo, runs the report once with the --verify binds through the same pipeline the server uses (parameter-echo check included), and prints the config.yaml registry block to paste. Rules the CLI enforces so the pod cannot be handed a silent-failure shape:

• The SQL goes through the same read-only guard as everything else — authoring is privileged, not exempt.

• Result columns are derived from the top-level select-list aliases; a * projection or an unaliased expression is refused rather than guessed (--column passes them explicitly if you must).

• A statement containing ]]> is rejected: it would terminate the CDATA section inside the data model and truncate the SQL without an error.

• An existing catalog object is never overwritten without --force.

Credentials come from the same FUSION_USER / FUSION_PASS environment variables as the server. Two wire quirks the client pins (each cost a live round trip to learn): the three catalog operations address objects by three different element names (reportAbsolutePath / reportObjectAbsolutePathURL / objectAbsolutePath), and upload types are xdmz / xdoz.

Why this is a CLI and not an MCP tool. The report registry is the allow-list an agent operates inside. If the agent could author reports, the allow-list would guard nothing: any SQL becomes reachable by first minting a report for it. Creating a report is a human decision here, exactly as it was when a browser did it.

One happy consequence: the "a report needs sample data" rule turned out to be a UI-wizard gate only — an uploaded report runs fine without any, so the save-as-sample step (and the native dialog that can freeze the browser) disappears entirely on this path.

3.2b The browser recipe

For each report, in this order. The order is not cosmetic — steps 4 and 5 each depend on the one before, and the failure messages do not say so.

1. New Data Model → Data Set → SQL Query.

   • Data Source: ApplicationDB_FSCM (Financials / SCM / Procurement). A data set is bound to exactly one data source, so HCM or CRM needs its own data model.

   • Type of SQL: Standard SQL.

2. Write the SQL with :bind variables, not &lexicals. Every value the caller may vary is a bind:

   WHERE UPPER(t.table_name) LIKE UPPER(:p_pattern)

   Rules that are not optional:

   • Alias every selected expression with a plain uppercase identifier. Result column names become XML element names in the response; an unaliased expression can produce a document the parser cannot read.

   • Prefer UPPER(x) = UPPER(:p_x) over x = :p_x for anything compared against a dictionary name — see §8.2.

   • No trailing semicolon.

3. Accept BIP's offer to create the parameters. On OK, the editor notices the :bind variables and offers to create matching parameters automatically. Say yes. It creates them with the right names and types, which is fiddlier to get right by hand. Then open Parameters and give each one a default value that returns rows (e.g. p_pattern → %HEADERS%, p_owner → FUSION). You need those defaults for step 4.

   This is the opposite of the lexical case, where the "enter values for lexical references" prompt does not create the parameter and you must add it by hand. Binds are the easy path in every respect.

4. Click View Data and make it succeed. It runs the SQL with the parameter defaults from step 3. Fix the SQL until rows come back. This step is a hard prerequisite, not a sanity check — see step 5.

5. Generate sample data (View Data → set a row count → Save as Sample Data). A report cannot be created from a data model that has no sample data, and sample data can only be produced by a View Data run that succeeded. So a data model whose defaults return nothing, or error, is a dead end that only announces itself two steps later when the report wizard refuses to proceed.

6. Save the data model under /Shared Folders/Custom/MCP/.

7. Create the report on that data model, any trivial layout. In report properties / output formats, enable Data (XML) output — attributeFormat: "xml" is what returns raw row data instead of a rendered document.

8. Note both identifiers and put them in config.yaml (§3.4):

   • SOAP absolute path: /Custom/MCP/XX_MCP_LIST_TABLES_RPT.xdo

   • REST path (relative to Shared Folders), URL-encoded: Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT

3.3 The four shipped reports

These back fusion_list_tables, fusion_describe_table, fusion_search_columns and fusion_describe_flexfields. fusion-query-bootstrap (§3.0) creates all four for you — each statement below is the shipped file src/fusion_query_mcp/bootstrap/<name>.sql, reproduced here with the reasoning that shaped it. The column aliases are the contract the server parses, so keep the aliases exactly as written; edit one and the manifest beside the file must move with it, which tests/unit/test_bootstrap.py enforces offline. p_owner defaults to FUSION and p_table_pattern to % in the registry, so a caller may omit them.

list_tables — XX_MCP_LIST_TABLES_RPT

Binds: :p_pattern, :p_owner. Returns: OWNER, TABLE_NAME, APPROX_ROWS, TABLE_COMMENT.

SELECT t.owner        AS OWNER,
       t.table_name   AS TABLE_NAME,
       t.num_rows     AS APPROX_ROWS,
       tc.comments    AS TABLE_COMMENT
FROM   all_tables t
LEFT JOIN all_tab_comments tc
       ON tc.owner = t.owner
      AND tc.table_name = t.table_name
WHERE  UPPER(t.table_name) LIKE UPPER(:p_pattern)
AND    UPPER(t.owner)      LIKE UPPER(:p_owner)
ORDER BY t.num_rows DESC NULLS LAST, t.table_name
FETCH FIRST 5000 ROWS ONLY

num_rows is a stale optimiser statistic, not a live count — which is why it is surfaced to the agent as APPROX_ROWS. Ordering by it puts the business table above its interface, history and staging cousins, which is the single most useful ranking for schema discovery.

p_owner is compared with LIKE, not =, so a caller can pass % to search every schema the service account can see.

The FETCH FIRST must stay above limits.hard_max_rows (5000 vs 2000). It is a safety net against a pathological pattern, not the row cap — the tool layer does the capping, and it can only report truncated honestly if its limit is the one that binds. Set the data model's ceiling at or below hard_max_rows and a broad search silently returns a partial answer that looks complete.

describe_table — XX_MCP_DESCRIBE_TABLE_RPT

Binds: :p_table, :p_owner. Returns: OWNER, TABLE_NAME, COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, COLUMN_COMMENT, TABLE_COMMENT.

SELECT c.owner          AS OWNER,
       c.table_name     AS TABLE_NAME,
       c.column_id      AS COLUMN_ID,
       c.column_name    AS COLUMN_NAME,
       c.data_type      AS DATA_TYPE,
       c.data_length    AS DATA_LENGTH,
       c.data_precision AS DATA_PRECISION,
       c.data_scale     AS DATA_SCALE,
       c.nullable       AS NULLABLE,
       (SELECT MAX(cc.comments)
          FROM all_col_comments cc
         WHERE cc.owner       = c.owner
           AND cc.table_name  = c.table_name
           AND cc.column_name = c.column_name) AS COLUMN_COMMENT,
       (SELECT MAX(tc.comments)
          FROM all_tab_comments tc
         WHERE tc.owner      = c.owner
           AND tc.table_name = c.table_name)   AS TABLE_COMMENT
FROM   all_tab_columns c
WHERE  UPPER(c.table_name) = UPPER(:p_table)
AND    UPPER(c.owner)      LIKE UPPER(:p_owner)
ORDER BY c.column_id

The comments come from scalar subqueries, not joins, and that is deliberate. ALL_COL_COMMENTS can return more than one row for a column when the account reaches the object through several grants, and a LEFT JOIN would turn that into duplicated columns — the exact fan-out this server exists to detect, in the server's own SQL. A scalar subquery cannot multiply rows, so the shape is safe by construction rather than by inspection.

OWNER and TABLE_NAME are projected for a reason that is not obvious until it bites you: this pod carries two objects whose names differ only in case, so a case-insensitive lookup returns both, interleaved by COLUMN_ID. The server groups the rows by (OWNER, TABLE_NAME) and refuses to present them as one table — see §8.3. Grouping is by key, not by row order, so the simple ORDER BY c.column_id is sufficient.

search_columns — XX_MCP_SEARCH_COLUMNS_RPT

Binds: :p_pattern, :p_owner, :p_table_pattern. Returns: OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, APPROX_ROWS.

SELECT c.owner       AS OWNER,
       c.table_name  AS TABLE_NAME,
       c.column_name AS COLUMN_NAME,
       c.data_type   AS DATA_TYPE,
       t.num_rows    AS APPROX_ROWS
FROM   all_tab_columns c
LEFT JOIN all_tables t
       ON t.owner = c.owner
      AND t.table_name = c.table_name
WHERE  UPPER(c.column_name) LIKE UPPER(:p_pattern)
AND    UPPER(c.owner)       LIKE UPPER(:p_owner)
AND    UPPER(c.table_name)  LIKE UPPER(:p_table_pattern)
ORDER BY t.num_rows DESC NULLS LAST, c.table_name, c.column_name
FETCH FIRST 5000 ROWS ONLY

describe_flexfields — XX_MCP_DESCRIBE_FLEXFIELDS_RPT

Backs fusion_describe_flexfields. Binds: :p_flexfield, :p_context. Returns one row per DFF segment with the COLUMN_NAME mapping. Authored headless with fusion-query-author (§3.2a) — no browser was involved.

SELECT s.DESCRIPTIVE_FLEXFIELD_CODE  AS FLEXFIELD_CODE,
       f.NAME                        AS FLEXFIELD_NAME,
       s.CONTEXT_CODE                AS CONTEXT_CODE,
       s.SEGMENT_CODE                AS SEGMENT_CODE,
       st.NAME                       AS SEGMENT_PROMPT,
       s.COLUMN_NAME                 AS COLUMN_NAME,
       s.SEQUENCE_NUMBER             AS SEQUENCE_NUMBER,
       s.ENABLED_FLAG                AS ENABLED_FLAG,
       s.REQUIRED_FLAG               AS REQUIRED_FLAG,
       s.DISPLAY_TYPE                AS DISPLAY_TYPE,
       s.DEFAULT_VALUE               AS DEFAULT_VALUE,
       TO_CHAR(s.VALUE_SET_ID)       AS VALUE_SET_ID
FROM   fnd_df_segments_b s
LEFT   JOIN fnd_df_segments_tl st
       ON  st.APPLICATION_ID = s.APPLICATION_ID
       AND st.DESCRIPTIVE_FLEXFIELD_CODE = s.DESCRIPTIVE_FLEXFIELD_CODE
       AND st.CONTEXT_CODE = s.CONTEXT_CODE
       AND st.SEGMENT_CODE = s.SEGMENT_CODE
       AND st.LANGUAGE = USERENV('LANG')
       AND NVL(st.SANDBOX_ID, '~') = NVL(s.SANDBOX_ID, '~')
       AND NVL(st.ENTERPRISE_ID, -1) = NVL(s.ENTERPRISE_ID, -1)
LEFT   JOIN fnd_df_flexfields_tl f
       ON  f.APPLICATION_ID = s.APPLICATION_ID
       AND f.DESCRIPTIVE_FLEXFIELD_CODE = s.DESCRIPTIVE_FLEXFIELD_CODE
       AND f.LANGUAGE = USERENV('LANG')
       AND NVL(f.SANDBOX_ID, '~') = NVL(s.SANDBOX_ID, '~')
       AND NVL(f.ENTERPRISE_ID, -1) = NVL(s.ENTERPRISE_ID, -1)
WHERE  UPPER(s.DESCRIPTIVE_FLEXFIELD_CODE) LIKE UPPER(:p_flexfield)
AND    UPPER(s.CONTEXT_CODE) LIKE UPPER(:p_context)
ORDER  BY s.DESCRIPTIVE_FLEXFIELD_CODE, s.CONTEXT_CODE, s.SEQUENCE_NUMBER
FETCH  FIRST 5000 ROWS ONLY

Deliberately NO sandbox or seed-set filter. On this pod the mainline definition rows carry SANDBOX_ID = '1', so the "obvious" SANDBOX_ID IS NULL predicate returns zero rows — forever, without an error. Verified empirically before authoring (total = distinct = 10 for the PO_HEADERS DFF, so no striping duplication either). Re-check both facts when recreating this on another pod.

Views. The shipped set covers tables only. Fusion exposes a great deal through _V / _VL views that never appear in all_tables, so if you need them, build one more report over all_views the same way and register it. The exploration tools will not invent one for you.

3.4 Register the report

Add it to config.yaml under fusion.reports. Nothing else makes a report runnable — the registry is the allow-list:

fusion:
  reports:
    list_tables:
      soap_path: "/Custom/MCP/XX_MCP_LIST_TABLES_RPT.xdo"
      rest_path: "Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT"
      description: "Tables matching a LIKE pattern, most-populated first."
      parameters: [p_pattern, p_owner]      # accepted binds; anything else is rejected
      defaults: {p_owner: "FUSION"}         # used for whatever the caller omits

description and parameters are what fusion_list_reports shows an agent, so write the description for the agent, not for yourself: say what the report returns and what the parameters mean.

parameters is not optional in practice: an entry declaring none accepts none, because forwarding an undeclared name is how a typo reaches the pod unnoticed.

parameters is a hand-kept copy of the bind names inside the data model, and that is a real failure mode. Rename :p_pattern to :p_name in the data model and forget to change it here, and the server sends p_pattern, BI Publisher ignores an unknown parameter, the report runs on its stored defaults, and the pod answers HTTP 200 with rows that look entirely reasonable. Nothing errors.

The server defends against this because the pod hands over a receipt: every bind it actually received is echoed back at the root of the data XML. run_report compares that echo with what it sent and fails the call when they disagree, rather than returning rows that answer a different question. Set verify_echo: false on a report only if you have established that it does not echo — and understand you are switching the check off, not fixing it.

3.5 Prove the transport before wiring anything up

Which transport? On the pod this was built against, the REST endpoint /xmlpserver/services/rest/v1/reports/{path}/run answers a plain 404 — the v1 API is simply not exposed — while the SOAP ExternalReportWSSService works. Check yours:

# REST
curl -u "$FUSION_USER:$FUSION_PASS" -X POST \
  "https://<pod>.oraclecloud.com/xmlpserver/services/rest/v1/reports/Custom%2FMCP%2FXX_MCP_LIST_TABLES_RPT/run" \
  -H "Content-Type: multipart/form-data" \
  -F 'ReportRequest={"byPassCache":true,"attributeFormat":"xml","parameterNameValues":{"listOfParamNameValues":[{"name":"p_pattern","values":["%HEADERS%"]}]}};type=application/json' \
  -o out.bin -v

# SOAP
curl -X POST "https://<pod>.oraclecloud.com/xmlpserver/services/ExternalReportWSSService" \
  -H "Content-Type: application/soap+xml;charset=UTF-8" -H "SOAPAction: submitRequest" \
  -u "$FUSION_USER:$FUSION_PASS" --data @docs/soap_smoke.xml

docs/soap_smoke.xml ships with this repo. It is addressed at the legacy lexical report, so edit the .xdo path and the parameter block to point at one of your own reports before using it as a transport probe. SOAP returns the report bytes Base64-encoded inside <reportBytes>.

Set fusion.backend to rest or soap accordingly, then run fusion_health_check, which executes the list_tables report with a narrow pattern and reports backend, elapsed time, row count and the registered report names.

4. Install and run

Requires Python 3.12 and uv. Nothing else — no clone, no virtualenv, no paths to get right:

uvx --from git+https://github.com/ruya-grp/Fusion-MCP fusion-query-init

It asks four things — pod name, pod URL, service account, password — and then does the rest: writes the pod under ~/.fusion-query/pods/<name>/, creates the four exploration reports on the pod, registers them, runs the health check, and prints the line that registers the server with your MCP client. A live run ends like this:

Registered 4 report(s) in .../pods/my-pod/reports.dynamic.yaml
PASS  ok: Pod reachable via soap in 10186 ms; 241 row(s) from
      report:list_tables(p_owner=FUSION, p_pattern=%HEADERS%); 4 registered report(s).

Everything it does is available separately (--no-bootstrap, --no-verify, --no-input for scripting), and the pod it writes is a plain directory you can edit afterwards.

Where things live. An installed copy has no project directory and no dependable working directory, so configuration lives in $FUSION_HOME, or ~/.fusion-query/ when that is unset. An explicitly set FUSION_HOME wins over anything found by looking around, which is what makes one registration line work on every machine.

Working from a checkout

For developing the server itself:

uv venv --python 3.12
uv sync --all-groups

Then the same commands are on the path as uv run fusion-query-init, uv run fusion-query-bootstrap, and so on. A config.yaml or pods/ in the working directory takes precedence, so a checkout keeps behaving like a checkout.

Adding a pod by hand — four facts, nothing else

A pod is a directory under pods/. Its name is the pod name — what fusion_use_pod binds to — and the whole of its configuration is one line:

mkdir -p "pods/my pod" && cp docs/pod-template/config.yaml "pods/my pod/"
$EDITOR "pods/my pod/config.yaml"          # set base_url
cp docs/pod-template/.env.example "pods/my pod/.env"
$EDITOR "pods/my pod/.env"                 # set FUSION_USER / FUSION_PASS

fusion:
  base_url: https://your-pod.fa.ocs.oraclecloud.com

That is a complete pod. Everything else has a working default:

• transport — backend: auto asks the pod which of SOAP/REST it publishes and remembers the answer (§8.5). There is one correct value per pod and the pod knows it, so it is not something to discover and record by hand.

• reports — created and registered by fusion_bootstrap on first use (§3.0), or by fusion-query-bootstrap from a shell.

• limits, security, redaction — defaults that work; config.example.yaml is the annotated reference for tuning them, not a file you must fill in.

One global variable, many pods. If FUSION_USER / FUSION_PASS are also set process-wide, they must name the same account as the pod's .env. When the two disagree the server refuses rather than picking: answering as the wrong service account returns HTTP 200 and plausible rows, with row-level Data Security evaluated against the wrong person.

Single-pod setup

Without a pods/ directory, one config.yaml at the project root does the same job. Start from the same template rather than from the full example:

cp docs/pod-template/config.yaml config.yaml

config.example.yaml is the annotated reference for everything you may set:

cp config.example.yaml config.yaml   # the long form, if you want the comments

Export credentials — never put them in the YAML:

export FUSION_USER='svc_mcp_query'
export FUSION_PASS='...'

Verify the transport end to end:

uv run python -c "from fusion_query_mcp.server import fusion_health_check as h; print(h())"

Then create the four reports the exploration tools run on (§3.0). This is the step a passing health check does not cover — the connection can be perfect while the catalog is empty:

uv run fusion-query-bootstrap

Registering with Claude Code

uv sync installs a console script into the venv, so point the client straight at it and pass the config path explicitly (an MCP server does not inherit the shell's working directory):

claude mcp add fusion-query --scope user --env FUSION_MCP_CONFIG=/path/to/OracleMCP/config.yaml -- /path/to/OracleMCP/.venv/bin/fusion-query-mcp

On Windows the executable is .venv\Scripts\fusion-query-mcp.exe. Verify with claude mcp list, which should report ✓ Connected.

Or configure any MCP client directly:

{
  "mcpServers": {
    "fusion-query": {
      "command": "/path/to/OracleMCP/.venv/bin/fusion-query-mcp",
      "env": { "FUSION_MCP_CONFIG": "/path/to/OracleMCP/config.yaml" }
    }
  }
}

Supplying credentials to a client-launched server

A server started by an MCP client does not see variables you exported in a terminal, so FUSION_USER / FUSION_PASS have to reach it another way. Two options, and the trade-off is real:

• User-level environment variables (preferred). Stored by the OS and inherited by every child process, so the password never lands in a file inside this repository:

  setx FUSION_USER "svc_mcp_query"    # Windows; sign out and back in to apply

  On macOS/Linux, set them in your login shell profile or a keychain helper.

• claude mcp add --env FUSION_PASS=... works, but writes the password in clear text into ~/.claude.json. Use it only for a throwaway sandbox pod.

Either way the password stays with you: nothing in this project reads, stores or logs it, and config.yaml holds only the names of the variables.

5. Configuration reference

See config.example.yaml for the annotated template.

Environment overrides: FUSION_BASE_URL, FUSION_BACKEND, FUSION_PARAM_SHAPE, FUSION_ENGINE_MODE, FUSION_MAX_ROWS, FUSION_HARD_MAX_ROWS, FUSION_TIMEOUT_SECONDS, FUSION_MAX_SQL_CHARS, FUSION_FIXTURES_DIR, FUSION_AUDIT_PATH, FUSION_MCP_CONFIG (path to the config file itself).

6. Validating against ground truth

This is the feature that turns "the report ran" into "the answer is correct". You supply facts you already trust — a total read off the Fusion UI, an exported spreadsheet, a known document number — and the agent checks the result against them. It works identically on fusion_validate_report and (on a lexical-capable pod) fusion_validate_query; only the execution path differs.

Expectation types

Not every expectation can see a fan-out, and it is worth knowing which. Join fan-out copies rows verbatim, so an expectation only detects it if duplication can falsify what it claims:

• Catches it: unique_key (that is its whole job); reference_dataset, which fails when a key the reference supplied comes back more than once — an extra key still passes in contains_all mode, because a wider scope is what that mode is for; row_count with op: eq; aggregate over sum or count.

• Blind to it, by arithmetic: aggregate over max, min, avg or count_distinct. Uniform duplication does not move any of them. No fix is possible inside the expectation — pair them with unique_key or an exact row_count. cross_check inherits the same blindness through bind.left.

• Blind to it, by design: contains_row asserts that a row exists, and match is often a deliberately non-unique pattern ({"STATUS": "OPEN"}). Its detail reports how many rows matched, so duplication is visible on the passing result, but the verdict stays green.

This is the concrete form of the caveat below: pick two expectation types that fail for different reasons, not two spellings of the same claim.

cross_check needs a SQL-capable pod. It re-derives a number by running a second, simpler SQL statement — which this pod cannot do (§1.2). On a report-only pod the expectation does not crash the validation; it comes back as a failed expectation saying so. To get independent evidence without it, register a second, deliberately simpler report and compare the two results.

Validating a report

{
  "report": "list_tables",
  "params": {"p_pattern": "PO_HEADERS%"},
  "expectations": [
    {"type": "row_count", "op": "min", "value": 1},
    {"type": "column_set", "required": ["OWNER", "TABLE_NAME", "APPROX_ROWS"]},
    {"type": "unique_key", "columns": ["OWNER", "TABLE_NAME"]}
  ]
}

Expectations can also come from a saved fixture by name (fixture:), which is how a check becomes a regression test you re-run after a quarterly patch.

Fixture file

name: hhc_open_pos_aug2026
description: >
  Open standard POs for BU HHC-Entity01, August 2026.
  Ground truth from Procurement work area export on 2026-08-10.
datasource: fscm
expectations:
  - type: row_count
    op: between
    min: 1
    max: 500
  - type: column_set
    required: [PO_NUMBER, BU_NAME, SUPPLIER, TOTAL_AMOUNT]
  - type: unique_key
    columns: [PO_NUMBER]
  - type: contains_row
    match: {PO_NUMBER: "PO-2026-000123", TOTAL_AMOUNT: 150000}
    numeric_tolerance: 0.01
  - type: aggregate
    fn: sum
    column: TOTAL_AMOUNT
    op: eq
    value: 4250000.00
    tolerance: 0.50

A fuller, annotated version ships at docs/example_fixture.yaml — copy it into fixtures/ and edit. It is loaded by the test suite, so it cannot drift out of sync with the expectation schema. A fixture's sql: field is documentation of the statement the ground truth describes; on a report-only pod, the data model holds the real SQL and only the expectations are evaluated.

Two honest caveats

1. A passing fixture proves consistency with the ground truth you supplied, not universal correctness. Use at least two independent expectation types per fixture — a contains_row and an aggregate, say. Two expectations restating the same fact prove almost nothing.

2. A missing expected row is not always a query bug. Fusion row-level Data Security on the service account can legitimately hide rows. When contains_row fails with no near miss, widen the parameters until the row should be in scope; if it is still absent, the account cannot see it, and no amount of SQL will change that.

The working loop

The fusion-query-workflow prompt ships the full protocol. In short: fusion_list_reports to see what exists → fusion_search_columns / fusion_describe_table to check schema rather than guessing → run the report with max_rows <= 20 while iterating → validate → save the fixture. If the answer needs SQL no registered report provides, the correct outcome is to say so and ask an administrator for a new report, not to improvise.

7. Fusion schema traps

fusion_get_hints covers these in depth, and they matter most to whoever writes the SQL inside a data model. The three that corrupt results without raising an error:

• _TL translation tables need AND t.LANGUAGE = USERENV('LANG'), or rows multiply by the number of installed languages.

• _F / _M date-effective tables need TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date, or rows multiply by the number of history versions.

• _ALL tables span business units and orgs. The UI's implicit BU context does not apply to raw SQL — filter org_id / bu_id deliberately.

Also: Fusion column names rarely equal UI labels (the UI's "Supplier" is VENDOR_NAME on POZ_SUPPLIERS_V), and all_tables.num_rows is a stale optimiser statistic, surfaced as APPROX_ROWS for that reason.

8. What surprised us on this pod

Verified by experiment, not inferred. Every item here cost time to discover.

8.1 Lexicals are not substituted; binds are

Covered in §1.2. It is the finding everything else follows from. If you take one thing from this README: &p_query becomes the empty string, :p_x arrives verbatim.

8.2 The dictionary is lower case

The pod serves its data dictionary in lower case — po_headers_all, gl_je_headers — because the objects were created with quoted lower-case names. Oracle's usual "unquoted identifiers are stored upper case" intuition is wrong here. Upper-casing a table name before comparing it returns zero rows, which reads as "that table does not exist" rather than as a bug. Every dictionary predicate therefore compares through UPPER() on both sides, so it works on either convention.

8.3 Two objects, one name, different case

The pod carries both FUSION.po_headers_all and FUSION.PO_HEADERS_ALL — two distinct objects with the same name in different case. The lower-case one is the real table (974 rows, populated comments); the upper-case one has no statistics and no comments.

A case-insensitive describe_table therefore returns 576 rows for a 288-column table, every column name appearing twice. Presented as one table that is simply wrong — it looks like a broken join, and the natural "fix" (dedupe by column name) hides a real fact about the pod. fusion_describe_table groups by (OWNER, TABLE_NAME) and makes the ambiguity visible instead of resolving it silently.

8.4 Comments are populated

Table and column comments are genuinely present on this pod, contrary to the common assumption that Fusion ships them NULL. They are useful enough to an agent that both list_tables and describe_table project them.

8.5 REST /run is not exposed; SOAP is

/xmlpserver/services/rest/v1/reports/{path}/run answers a plain 404 here, while ExternalReportWSSService works. Do not read the 404 as an authentication or path-encoding problem; the endpoint is not there.

This used to mean backend: soap in every config, discovered by an administrator and written down. It no longer does: backend: auto is the default and asks the pod. SOAP is probed first — not as a preference, but because ExternalReportWSSService sits beside the CatalogService this server already requires unconditionally for authoring, ad-hoc queries and bootstrapping. A pod where SOAP is missing cannot run this server at all, whatever backend says, so the transport that might be absent is the one probed second.

What does not trigger a fallback matters more than what does:

The choice is remembered for the process, so the probe is paid once. Pinning backend: soap or rest still skips it entirely.

8.6 BI Publisher echoes your parameters as data

The response document carries every report parameter back as a leaf element at the root, alongside the repeating row groups. A report that matched nothing returns only the echoes:

<DATA_DS><P_OWNER>FUSION</P_OWNER><P_PATTERN>PO_HEADERS%</P_PATTERN></DATA_DS>

and one that matched rows returns them mixed together:

<DATA_DS><P_OWNER>FUSION</P_OWNER><P_PATTERN>%HEADERS%</P_PATTERN>
  <G_1><OWNER>FUSION</OWNER><TABLE_NAME>po_headers_all</TABLE_NAME></G_1>
  <G_1><OWNER>FUSION</OWNER><TABLE_NAME>gl_je_headers</TABLE_NAME></G_1>
</DATA_DS>

A naive parser reads the first document as one row of parameter values, which inverts every row-count check: row_count eq 0 fails and min 1 passes on an empty result. The rule this project uses: a row is a child of the root that has children of its own; root-level leaves are never rows, and dropping them is reported in ResultSet.warnings.

8.7a A missing config used to be a silently wrong one

config.example.yaml was in the list of filenames treated as "the configuration", so a checkout with no config.yaml quietly ran on the annotated template — whose base_url is the placeholder https://<pod>.oraclecloud.com. The server started, listed reports, and failed every call with a DNS error that pointed at the network rather than at "there is no configuration". It is no longer in that list: a missing config is now simply missing, and the health check says so.

8.7 uploadObject refuses an occupied path — so --force never overwrote

Found by bootstrapping into a scratch folder, twice. --force was implemented as "skip the client-side existence check", which is not the same thing as overwriting: the pod answers

PublicReportServiceImpl::executeUploadReport Failure:
Due to Report with Path [/Custom/.../XX_MCP_DESCRIBE_TABLE_DM.xdm] already exist!

There is no upsert. author_report therefore takes replace as well as force, and both CLIs map --force onto the pair: the report is deleted first, then the data model — that order, because deleting the model out from under the report would orphan it if the second call failed. fusion_author_report (the MCP tool) still passes force only, so nothing an agent can call deletes a catalog object.

Two smaller facts from the same runs: uploadObject creates the folder if it does not exist — /Custom/MCP_BOOTSTRAP_TEST was never made by hand — and a report run through run_report is bounded only by its own FETCH FIRST, since that path applies no row cap by design (§2 Row limits). The describe_flexfields probe returns exactly 5000 for that reason, so bootstrap names the ceiling rather than presenting the number as a total.

9. Security, privacy, audit

• The report registry is a real boundary. An agent can only run reports an administrator built and registered in config.yaml, with only the parameters that report declares — an unknown parameter name is rejected before any round trip. Compared with the original design, where any SELECT the guard allowed could reach the pod, this is a genuine improvement: the set of possible statements is finite, reviewable, and owned by a human.

• Credentials come only from environment variables or the OS keychain. Never in config.yaml, never in logs.

• Single-account trust model. Every caller of this MCP server inherits the service account's full read scope. Row-level Data Security is evaluated against that account, not the human asking. If several people or clients will use the server, the report registry, the deny-list and the redaction list are the only differentiators. The SQL guard is defence in depth, not a security boundary — the real boundary is the service account's Fusion roles. Scope them narrowly.

• Redaction. Columns matching security.redact_columns are masked in tool output and in fixtures the server writes (PDPL alignment). Validation warns when an expectation references a redacted column, because such a comparison can never pass.

• Audit. One JSONL line per execution: timestamp, tool, datasource or report, backend, the call (report:list_tables(p_pattern=PO_HEADERS%) for a report, the SQL text on the legacy path), row count, elapsed ms, ORA code. Row payloads are not logged unless audit.log_row_data: true.

• Fixtures hygiene. fixtures/ is gitignored by default. Use synthetic or masked ground truth when the repository is shared — real ground truth is client data.

• Parameter values travel inside JSON/XML over HTTPS to Oracle. No third parties.

10. Development

uv run pytest -q                      # unit tests, no network
uv run pytest -q -m "not integration" # same, explicit
FUSION_LIVE=1 uv run pytest tests/integration -q   # live pod, env-gated

Unit tests never touch the network: the backends are exercised against canned multipart and SOAP payloads under tests/data/, and the pipeline and tool layers run against a fake pod. The live suite in tests/integration/ is the only thing that can confirm the [VERIFY-ON-POD] items in §11.

Agent evaluations are in tests/evals/ — see tests/evals/README.md for why there are two sets and why only one of them ships with answers filled in.

Layout:

src/fusion_query_mcp/
├── server.py           MCP tool surface
├── pipeline.py         the one execution path (execute + run_report)
├── metadata_sql.py     SQL builders — legacy lexical path only
├── config.py           YAML + env configuration, incl. the report registry
├── models.py           shared types
├── audit.py            JSONL audit log
├── redaction.py        column masking
├── fixtures_store.py   fixture persistence
├── backends/           soap_bip (this pod), rest_bip
├── engine/             sql_guard, limiter, xml_result, errors
├── validation/         models, evaluator, differ
└── knowledge/          fusion_hints.yaml + loader

SDK note: the design brief named FastMCP. In the official mcp Python SDK v2 that class was renamed MCPServer; the decorator surface is otherwise the same, and this project targets the current name rather than pinning to v1.

11. [VERIFY-ON-POD] checklist

Work through these with your Fusion administrator and record the answers in config.yaml. Items 1 and 2 decide whether you get the report architecture or the legacy one, so do them first.

1. Are lexicals substituted? Build one throwaway data model SELECT * FROM (&p_query), create the report, run it over the API with p_query = SELECT 1 AS N FROM DUAL. ORA-00903 means no — use reports, and do not try to make it work. Anything else means your pod is more permissive than the one this was built on.

2. Are binds honoured? A data model SELECT :p_x AS ECHO FROM DUAL must return your sentinel verbatim. If this fails too, the server cannot work at all on this pod.

3. REST /run functional? If it 404s → backend: soap (§3.5).

4. parameterNameValues shape (REST only) — flat vs item. Auto-detected; record the result as param_shape.

5. Dictionary case. Run list_tables with p_pattern = %HEADERS% and look at the returned names. Lower case, upper case, or both (§8.2, §8.3)?

6. Service-account roles confirmed by the Fusion admin; decide which pillars (FSCM only, or also HCM/CRM) get their own data models, since a data set is bound to exactly one data source.