How do I fix ORA-01790?

Cast the binds so every branch has a declared type. Verbose, but it removes the inference entirely. Or keep MERGE single-row and batch with a plain INSERT, falling back to per-row handling only on conflict. Normalise values before binding so one column never receives two different types across a batch.

Oracle · error reference

ORA-01790 on a multi-row MERGE with bind variables

ORA-01790: expression must have same datatype as corresponding expression

Oracle cannot infer a type for a bind variable inside a UNION ALL branch, so a batched MERGE fails on values that all look identical. Why batching causes it and single rows do not.

What the database is telling you

In a UNION ALL, Oracle requires the corresponding columns of every branch to share a datatype. A naked bind variable has no declared type, so Oracle infers one from context — and when the only context is other binds, different branches can be assigned different types. The comparison then fails even though every value you passed was the same kind of thing.

Why it shows up in a migration

It only appears once you batch. Oracle has no ON CONFLICT and no INSERT IGNORE, so a skip-or-upsert load has to be expressed as MERGE, and a multi-row MERGE means USING (SELECT ? FROM dual UNION ALL SELECT ? FROM dual ...). That UNION of bind-only SELECTs is the exact shape that trips the error — which is why the same data loads fine one row at a time and dies in batches of 500.

Common causes

A multi-row MERGE whose USING clause is a UNION ALL of bind-only SELECTs.
NULL passed in one branch and a value in another for the same column.
A mix of str and bytes, or int and Decimal, bound to one column across a batch.

How to fix it

Cast the binds so every branch has a declared type. Verbose, but it removes the inference entirely.

USING (SELECT CAST(? AS NUMBER) id, CAST(? AS VARCHAR2(200)) name FROM dual
       UNION ALL
       SELECT CAST(? AS NUMBER), CAST(? AS VARCHAR2(200)) FROM dual) s

2
Or keep MERGE single-row and batch with a plain INSERT, falling back to per-row handling only on conflict.
3
Normalise values before binding so one column never receives two different types across a batch.

What DBShifts does about it

DBShifts takes the MERGE path only for single-row calls on Oracle. A multi-row batch uses a plain INSERT; if that hits a duplicate key the batch is retried row by row, and each of those single-row calls goes through MERGE and applies the skip or upsert correctly. Full migrations truncate the target first, so the common case stays on the fast path.

Bulk transfer engine

Migrating between engines?

DBShifts converts the schema, moves the data, and then proves the two sides match with a type-aware checksum per table — not just a row count. Errors like this one are handled on the way, and the ones that cannot be handled are reported rather than logged and forgotten.

Start free See the migration paths

Related errors

Oracle

ORA-32795: cannot insert into a generated always as identity column

Oracle refuses explicit values for a GENERATED ALWAYS AS IDENTITY column. Why it breaks every data load that carries its own primary keys, and the one-word change that fixes it.

Oracle

ORA-02291: parent key not found during a data load

A child row arrived before its parent. Why load order alone will not save you, and how deferred constraints turn an unsolvable ordering problem into a solved one.

← All database errors