Union Deduplication, MERGE Instability and a Non-Key-Preserved View

UNION is a set operator: it removes duplicate rows from the combined result. Any (patient_key, updated_ts) pair present in both views - and any pair duplicated inside one view - collapses to a single row, so the output is smaller than the sum of the inputs. That is correct set semantics and wrong ETL semantics if you expect row-level lineage.

ORA-00932 is a separate restriction: DISTINCT, UNION, INTERSECT and MINUS cannot operate on LOB columns, because there is no general equality/hash comparison for CLOB. UNION ALL does not need to compare rows and therefore accepts CLOBs.

-- keep every row, then deduplicate deliberately on the business key
SELECT source_system, patient_key, updated_ts, note
FROM (SELECT s.*,
             ROW_NUMBER() OVER (PARTITION BY source_system, patient_key
                                ORDER BY updated_ts DESC) AS rn
      FROM (SELECT source_system, patient_key, updated_ts, note FROM src_a
            UNION ALL
            SELECT source_system, patient_key, updated_ts, note FROM src_b) s)
WHERE rn = 1;

Key rules to remember: set operators require the same number of columns with compatible datatypes (the first query's column names win), they treat NULLs as equal (unlike =), and INTERSECT binds tighter than UNION and MINUS, which evaluate left to right. If the row count itself is an audit control, reconcile with UNION ALL counts before deduplicating, so silent data loss is visible.

ORA-30926 means the source produced more than one row matching the same target row. Oracle must apply at most one source row per target row for a deterministic MERGE; when STAGING_EXTRACT contains two rows for the same (source_system, patient_key) - typically a partial reload or a retried extract - the statement aborts rather than picking arbitrarily.

Diagnose before fixing:

SELECT source_system, patient_key, COUNT(*)
FROM staging_extract
GROUP BY source_system, patient_key
HAVING COUNT(*) > 1
ORDER BY 3 DESC;

Fix by collapsing the source to one row per key, keeping the latest version and using a deterministic tiebreak:

MERGE INTO patient_master m
USING (SELECT source_system, patient_key, updated_ts
       FROM (SELECT s.*,
                    ROW_NUMBER() OVER (PARTITION BY source_system, patient_key
                                       ORDER BY updated_ts DESC) AS rn
             FROM staging_extract s)
       WHERE rn = 1) s
ON (m.source_system = s.source_system AND m.patient_key = s.patient_key)
WHEN MATCHED THEN UPDATE SET m.updated_ts = s.updated_ts
WHEN NOT MATCHED THEN INSERT (source_system, patient_key, updated_ts)
VALUES (s.source_system, s.patient_key, s.updated_ts);

Related restriction: MERGE cannot update any column referenced in the ON clause - attempting SET m.patient_key = ... there raises ORA-38104. Since MERGE is not automatically transactional across errors, run it inside an explicit transaction and check SQL%ROWCOUNT against the pre-computed distinct key count before COMMIT.

A view is updatable only if the target table is key-preserved: each row of the view maps to at most one row of that base table, and the updated column comes from that table. PATIENT_ACTIVE_V joins PATIENT_MASTER to FACILITY. Oracle treats PATIENT_MASTER as key-preserved (its key is preserved through the join) but FACILITY as not key-preserved, because a single patient row can join to multiple facility rows. Setting facility_code therefore cannot be translated into an unambiguous update of one base row, and Oracle raises ORA-01779.

Confirm the dictionary view first:

SELECT column_name, updatable, insertable, deletable
FROM user_updatable_columns
WHERE table_name = 'PATIENT_ACTIVE_V';

FACILITY_CODE will show NO.

Option 1 - update the base table directly (usually correct):

UPDATE facility SET facility_code = 'X' WHERE facility_id = 42;

The view was only being used for its row filter; if the filter must be preserved, apply it explicitly in the WHERE clause of the statement.

Option 2 - INSTEAD OF trigger on the view when the view is the sanctioned interface and the update must fan out or be redirected:

CREATE OR REPLACE TRIGGER patient_active_v_io
INSTEAD OF UPDATE ON patient_active_v
FOR EACH ROW
BEGIN
  UPDATE facility SET facility_code = :NEW.facility_code
  WHERE facility_id = :OLD.facility_id;
END;

Remember that INSTEAD OF triggers are row-level only, that the view must be non-key-preserved for the trigger to be the mechanism, and that WITH CHECK OPTION on the view constrains which rows may be updated through it.