Stable set of rows?
My students are into the upload lab and they’ve encountered the wonderful ORA-30926
error from a MERGE
statement. An example of the MERGE
for an upload is in this earlier blog post.
This is the wonderful error message, which doesn’t seem to have meaning for many. The key is the non-deterministic where clauses phrase. That phrase means that the the query inside the USING
clause returns a non-unique set of rows. The returned rows can’t be matched uniquely against the target table in the ON
clause. The ON
clause is where the MERGE
statment matches the source query’s rows against the target table’s rows. The best join condition in a MERGE
statement is one between a surrogate primary and foreign key column.
Error: ORA-30926 Text: Unable TO GET a stable SET OF ROWS IN the SOURCE TABLES. -------------------------------------------------------------------- Cause: A stable SET OF ROWS could NOT be got because OF LARGE dml activity OR a non-deterministic WHERE clause. Action: Remove any non-deterministic WHERE clauses AND reissue the dml. |
The problem is very much like when you write what you think is a single-row subquery but find out it’s actually a multiple-row subquery when it return an ORA-01422
error in Oracle.
As a rule, I’ve found that using the DISTINCT
operator in the source SELECT
statement fixes it most of the time because the join isn’t returning a unique set of rows. Although, the better solution requires that you identify how to gain a unique result set. Alternatively, you need to re-examine the logic of your WHERE
clause. It also happens when the SELECT
clause returns date-time data types like SYSDATE
instead of date data types. A date-time can be converted by using the TRUNC
ate function like this:
MERGE INTO TABLE_NAME target USING (SELECT ... , TRUNC(SYSDATE) AS creation_date FROM ...) SOURCE ON (target.primary_key_column = SOURCE.primary_key_column) WHEN MATCHED THEN UPDATE SET last_updated_by = SOURCE.last_updated_by , last_update_date = SOURCE.last_update_date WHEN NOT MATCHED THEN INSERT VALUES ( column_list_of_values ); |
Hope this helps my students and others … 😉