MERGE Statement and wrong error message

I was scripting a MERGE statement for one of ETL process and came up with a weird error message ( ie , not a user friendly error message) .


Let me explain with the example .


ZAHIR@cedb:SQL> CREATE TABLE t_source AS
2 SELECT object_id , object_name , object_type FROM all_objects ;


Table created.

Elapsed: 00:00:03.00


ZAHIR@cedb:SQL> create table t_dest as

2 select object_id , object_name , object_type from all_objects

3 Where object_type = 'SYNONYM';

Table created.

Elapsed: 00:00:02.93


ZAHIR@cedb:SQL> Merge INTO t_dest d USING
2 ( SELECT object_name , object_type FROM t_source
3 ) s ON (s.object_name = d.object_name )
4 WHEN Matched THEN
5 UPDATE SET d.object_type = s.object_type ;


Merge INTO t_dest d USING
*
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables
Elapsed: 00:00:01.09

After some research ( googling ) found that , the cause of the issue was that the inline query from t_source does not return unique set of records by the identified ( in this case object_name )  .


ZAHIR@cedb:SQL> Select * from

2 (
3 Select object_name , count(*) from t_source

4 group by object_name having count(*) > 1

5 order by object_name )

6 Where rownum <= 10 ;


OBJECT_NAME COUNT(*)
------------------------------ ----------
/1000323d_DelegateInvocationHa 2
/1000e8d1_LinkedHashMapValueIt 2
/1005bd30_LnkdConstant 2
/10076b23_OraCustomDatumClosur 2
/100c1606_StandardMidiFileRead 2
/10128284_OpenMBeanAttributeIn 2
/1013c29d_PlanarImageServerPro 2
/101419a4_NormalDataCollector 2
/1020ed5e_Param 2
/1023e902_OraCharsetUTFE 2


10 rows selected.
Elapsed: 00:00:00.06


I wish Oracle provides bit meaningful message on this in the next release.