Replacing WM_CONCAT with Deterministic LISTAGG on Oracle 19c

A browser request returned HTTP 200, yet the response body was empty and the page showed no data. The status code looked successful, but the backend log told a different story: mapped SQL had failed with ORA-00904 while calling WM_CONCAT.

The repair was not a global text substitution. String aggregation has ordering, duplicate, delimiter, alias, and overflow semantics. A safe Oracle 19c migration had to preserve each one, prove that every runtime copy changed, and keep the final deployment boundary separate from source-level success.

Start with the first failed boundary

The useful execution chain was:

HTTP 200, empty body
  -> exception caught
  -> mapped SQL fails
  -> ORA-00904: WM_CONCAT

Oracle defines ORA-00904 as an invalid identifier or column name (ORA-00904). In this run, the identifier was the legacy aggregate itself. The target Oracle 19c database did not recognize it.

That distinction matters. The empty response was not evidence that the query returned zero rows. It was a secondary behavior of the application exception path. The database error was the earliest failing boundary and therefore the right place to repair.

Inventory every call before editing

The first search found most occurrences, but not all of them. Three capitalization variants appeared only after a case-insensitive scan. The final inventory covered eleven XML query mappings.

A useful pre-edit record includes:

• the file and query identifier;
• the expression being concatenated;
• the delimiter;
• the alias consumed by application code;
• the grouping columns and filters;
• the required output order;
• whether duplicates are significant;
• any parallel aggregates that must stay positionally aligned.

This record prevents a mechanical rewrite from silently changing a query contract. It also gives the post-edit scan an exact expected scope.

Replace syntax while preserving semantics

The replacement pattern was:

LISTAGG(
  value_expression,
  ','
) WITHIN GROUP (
  ORDER BY stable_key
)

Oracle documents that LISTAGG orders values within each group and concatenates the measure expression. It is deterministic only when the ORDER BY list produces unique ordering (Oracle 19c LISTAGG).

The migration therefore applied five constraints.

1. Keep the delimiter. Existing consumers expected comma-separated values, so the replacement retained the comma.
2. Do not add DISTINCT. The legacy queries did not request duplicate elimination. Removing repeats would change visible data.
3. Choose a stable order. Each aggregate used the business key that already defined row order. Where one key cannot be unique, add a documented tie-breaker instead of relying on fetch order.
4. Align parallel lists. If one query returns a name list and a code list, both must use the same ordering key. Otherwise item positions can describe different rows.
5. Preserve aliases and filters. Application code still reads the old result aliases, and the original WHERE and GROUP BY logic remains part of the contract.

An outer character conversion that no longer changed the LISTAGG result type was removed only where it was demonstrably redundant. It was not treated as a required part of the migration.

Treat overflow as a data-contract decision

LISTAGG returns VARCHAR2 for character input. Oracle 19c documents a maximum of 4,000 bytes when MAX_STRING_SIZE=STANDARD and 32,767 bytes when it is EXTENDED. The default overflow behavior is ON OVERFLOW ERROR, which raises ORA-01489; ON OVERFLOW TRUNCATE deliberately returns an incomplete list (Oracle 19c LISTAGG).

This repair did not add truncation. A clipped identifier or name list could misrepresent business data while making the query appear successful. The safer policy was:

• measure LENGTHB for representative grouped results on the target database;
• compare the maximum with the actual environment limit;
• retain fail-closed overflow behavior;
• redesign the output or adopt an explicitly approved truncation contract if future growth approaches the limit.

Oracle's own 19c tutorial demonstrates both ORA-01489 and the explicit truncation option (avoiding LISTAGG overflow). The choice is observable application behavior, not a cosmetic SQL detail.

Verify source, build, artifact, and database separately

The repair used independent checks because each catches a different failure mode.

Source and XML

• A case-insensitive scan reported zero remaining WM_CONCAT calls.
• The eleven affected files contained 42 expected LISTAGG expressions.
• Every modified mapping file parsed as XML.

Build

The required multi-module application chain compiled with its isolated JDK 8 toolchain. One historical module outside the required artifact path still had a pre-existing dependency problem; its mapping XML and SQL were inspected, but the unrelated module was not presented as newly buildable.

Packaged artifact

The final application package was scanned recursively rather than trusting the source tree. Across 935 embedded XML entries, it contained no runtime WM_CONCAT reference and contained the expected packaged LISTAGG expressions. The source and package counts differ because not every source mapping belongs in the selected artifact.

Oracle 19c

Representative queries ran against a sanitized Oracle 19c test database. They no longer raised ORA-00904, did not raise ORA-01489, and their measured byte lengths remained below the configured limit.

Together these checks prove query compatibility through the database and packaged-artifact boundaries. They do not prove that a deployed page is healthy.

Keep deployment outside the completed claim

The repaired artifact was not deployed during this work. A later release still needs to verify the real page, response body, log stream, and representative data after deployment.

The precise conclusion is therefore: the legacy aggregate was removed from the affected source and packaged runtime; the replacement preserved the reviewed query semantics; the required build completed; and representative Oracle 19c queries passed identifier and overflow checks. Production recovery remains a separate acceptance step.

That boundary is as important as the SQL change. It prevents a successful compile from being reported as a successful release.