ORA-30926 and MERGE

ORA-30926: unable to get a stable set of rows in the source tables

When i got a phone call reporting this error message, the problem sounded much more threatening than it actually turned out to be later on.

After doing some homework, that is some google and metalink search, I asked for a copy of the source code raising this error and it turned out to be a simple problem: the join conditions specified in the ON clause of the MERGE statement were not on a unique key.

The MERGE statement works on the assumption that a 1:1 or a 1:0 relationship exists between the source table (or view) and the target table. This means that when you write the ON clause, the columns you specify there should be either the primary key or a unique key for both the source and the target table.

For instance, if you have two tables having both a primary key made up of 3 columns, you can't specify just two of them in the ON clause, otherwise you'll get this error at runtime, provided the source values are not unique by pure chance.

The somewhat erratic behavior of the error is really the only bad aspect of this problem.

CREATE TABLE target
(a NUMBER, b NUMBER, c NUMBER, d NUMBER,
constraint pk_target primary key(a,b,c) using index)
/
CREATE TABLE source (a NUMBER, b NUMBER, c NUMBER, d NUMBER,
constraint pk_source primary key(a,b,c) using index)
/
INSERT INTO target
values (1,1,1,1);
/
INSERT INTO target
values (1,1,2,2);
/
INSERT INTO target
values (1,1,3,3);
/
INSERT INTO source
values (1,1,1,1);
/
INSERT INTO source
values (1,1,2,2);
/
INSERT INTO source
values (1,1,3,3);
/

MERGE INTO target t1
USING (SELECT a, b, c, d from source) t2
   ON (t1.a = t2.a
AND t1.b = t2.b)
 WHEN MATCHED THEN
      UPDATE SET t1.c = t2.c,
                t1.d = t2.d
 WHEN NOT MATCHED THEN
      INSERT (a, b, c, d)
      VALUES (t2.a, t2.b, t2.c, t2.d)
/

ORA-30926: unable to get a stable set of rows in the source tables

ROLLBACK
/

But now let's try with a different set of values that will make our join to work anyway and see that we won't get any error this time:

INSERT INTO target
values (1,1,1,1);
/
INSERT INTO target
values (2,2,2,2);
/
INSERT INTO target
values (3,3,3,3);
/
INSERT INTO source
values (1,1,1,1);
/
INSERT INTO source
values (2,2,2,2);
/
INSERT INTO source
values (3,3,3,3);
/


MERGE INTO target t1
USING (SELECT a, b, c, d from source) t2
   ON (t1.a = t2.a
AND t1.b = t2.b)
 WHEN MATCHED THEN
      UPDATE SET t1.c = t2.c,
                t1.d = 2 * t2.d
 WHEN NOT MATCHED THEN
      INSERT (a, b, c, d)
      VALUES (t2.a, t2.b, t2.c, t2.d)
/

A	B	C	D
1	1	1	2
2	2	2	4
3	3	3	6

See message translations for ORA-30926 and search additional resources

---

ORA-30926: impossibile ottenere un set di righe stabile nelle tabelle origine
ORA-30926: no se ha podido obtener un juego de filas estable en las tablas de origen
ORA-30926: no es pot obtenir un conjunt de files estable a les taules d'origen
ORA-30926: impossible d'obtenir un ensemble de lignes stables dans les tables source
ORA-30926: Stabile Zeilengruppe in den Quelltabellen kann nicht eingelesen werden
ORA-30926: η λήψη ενός σταθερού συνόλου γραμμών από τους πίνακες προέλευσης δεν είναι δυνατή
ORA-30926: kan ikke hente et stabilt sæt rækker i kildetabellerne
ORA-30926: kan inte hämta en stabil uppsättning rader i källtabellerna
ORA-30926: kan ikke hente et stabilt sett av rader i kildetabellene
ORA-30926: lähdetauluista ei saada vakaata rivijoukkoa
ORA-30926: nem olvasható be állandó sorkészlet a forrástáblákból
ORA-30926: nu s-a putut obţine un set de rânduri stabil în tabelele sursă
ORA-30926: Kan geen stabiele rijenset ophalen uit de brontabellen.
ORA-30926: não foi possível obter um conjunto de linhas estável nas tabelas de origem
ORA-30926: incapaz de obter um conjunto de linhas estável nas tabelas de origem
ORA-30926: невозможно получить устойчивый набор строк в исходных таблицах
ORA-30926: nelze získat stabilní sadu řádků ve zdrojových tabulkách
ORA-30926: nemožno získať stabilnú množinu riadkov v zdrojových tabuľkách
ORA-30926: nie można uzyskać stabilnego zestawu wierszy z tabel źródłowych
ORA-30926: kaynak tablolarında sabit bir satır kümesi alınamıyor