ORA-30926 Caused by a Duplicate Source Feed in a Nightly MERGE

Cause. MERGE matches in two phases. During the update phase a target row may be matched by at most one source row. PRODUCT_STAGE has no unique constraint and three product_id values occur twice, so a target row matches two source rows with different names and timestamps. Oracle cannot decide which one wins and raises ORA-30926: unable to get a stable set of rows in the source tables. The whole statement fails and rolls back; nothing is partially applied.

Why LOG ERRORS did nothing. The error logging clause captures row-level errors raised while applying an insert or update - conversion errors, constraint violations and the like - and writes those rows to the error table. ORA-30926 is detected during matching, before any row can be attributed to a single source row. There is no offending row to log, so the statement aborts with an empty error table.

Secondary problem. ON (NVL(t.product_id,0) = s.product_id) wraps the target primary-key column in a function. The optimizer cannot use the unique index on PRODUCTS.product_id for the join and is likely to full-scan 2.1 million rows or perform a full index scan. The NVL is also logically pointless: a NULL target key can never match a source row anyway, so the expression only costs index access.

Deduplicate the source before merging.

MERGE INTO products t
USING (
  SELECT product_id, name, modified_ts
  FROM (
    SELECT s.product_id, s.name, s.modified_ts,
           ROW_NUMBER() OVER (
             PARTITION BY s.product_id
             ORDER BY s.modified_ts DESC, s.name
           ) AS rn
    FROM product_stage s
    WHERE s.product_id IS NOT NULL
  )
  WHERE rn = 1
) s
ON (t.product_id = s.product_id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.modified_ts = s.modified_ts
WHEN NOT MATCHED THEN
  INSERT (product_id, name, modified_ts)
  VALUES (s.product_id, s.name, s.modified_ts);

Why this works.

• The inline view returns exactly one row per product_id, so the matched phase is unambiguous and ORA-30926 cannot occur.
• The ON clause is a plain equijoin on the primary key, restoring unique-index access.
• The product_id IS NOT NULL filter prevents an attempted insert with a NULL key, which would fail with ORA-01400 after the merge has already done work.

Choosing the tie-breaker. modified_ts DESC, name is a business rule, not a technical detail: it says the most recently changed record wins, with name as a deterministic fallback. If one warehouse feed should always override the other, encode that priority in the ORDER BY. Without a deterministic ORDER BY, ROW_NUMBER picks an arbitrary duplicate and the load is not reproducible.

Audit requirement. Capture the discarded duplicates into a reject table before merging (insert the rn > 1 rows with a reason code). Silent deduplication hides a data-quality defect in the feed.

Split form.

UPDATE products t
SET   (name, modified_ts) = (
        SELECT s.name, s.modified_ts
        FROM   product_stage s
        WHERE  s.product_id = t.product_id)
WHERE EXISTS (SELECT 1 FROM product_stage s
              WHERE s.product_id = t.product_id);

This form still breaks with ORA-01427 (single-row subquery returns more than one row) if the source has duplicates, so deduplication is mandatory either way. It is not a way to avoid fixing the feed.

Comparison.

• MERGE: one statement, atomic, concise, and it supports the LOG ERRORS clause for bad rows. The failure modes (ORA-30926, ORA-38104 when updating an ON-clause column) are less obvious to developers.
• UPDATE then INSERT: easier to instrument, each statement can have its own error logging and its own row counts, and each is easier to explain in a runbook. But two statements mean two chances to fail halfway; they must be wrapped in one transaction with explicit rollback handling.
• Concurrency: both are exposed to the same race if another session inserts the same product_id between the check and the insert. It surfaces as ORA-00001 on the unique key. Handle it with a retry or by serialising the load, not by ignoring it.

Judgement. Keep the single MERGE for a set-based nightly load. Prefer the split form when operations needs per-step counts and row-level rejects. Either way, fix the duplicate feed at its source boundary, because the same duplicates will break any other consumer of PRODUCT_STAGE.