13 Replies Latest reply: Feb 23, 2013 10:18 AM by Peter Gjelstrup RSS

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

    959406
      hi all


      transactional table issue both equal condition(ie on clause common row not any) not avilable what can i do_+ in this merge statement CORM_IRT_INV_RESOURCE_Tmp and PEG6_WRDT_RES_DTS_TMP (both temp/transaction table) .

      error are display oracle - ORA-30926: unable to get a stable set of rows in the source

      i ask both table common any thing AVAILABLE USING on clause

      ANY SOLUTION PLZ PROVIDE ME


      MERGE INTO CORM_IRT_INV_RESOURCE_Tmp A
      USING (SELECT DISTINCT * FROM PEG6_WRDT_RES_DTS_TMP) B
      ON ( B.WRDT_GUID = A.IRT_GUID
      AND B.WRDT_DOCUMENT_NO = A.IRT_ORDER_NO
      AND B.WRDT_DOCUMENT_OU = A.IRT_ORDER_OU
      AND B.WRDT_REF_LINE_NO = A.IRT_REF_LINE_NO
      AND B.WRDT_TASK_LINE_NO = A.IRT_LINE_NO
      AND B.WRDT_RESOURCE_NO = A.IRT_RESOURCE_CODE
      AND B.WRDT_RES_TYPE = A.IRT_RESOUCE_TYPE
      AND A.IRT_GUID = v_Guid_Tmp
      AND A.IRT_ORDER_NO = v_cusorderno_Tmp
      AND A.IRT_ORDER_OU = v_COrderOU_Tmp)
      WHEN MATCHED THEN UPDATE SET A.IRT_NORMALRATE_PERHR = B.WRDT_RATE_PER_HR,
      A.IRT_OTRATE_PERHR = B.WRDT_OVERTIME_RATE_PER_HR,
      A.irt_facilityrate_perhr = b.wrdt_std_fac_rate,
      A.irt_resource_price = --sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_NO, 0) * NVL(B.WRDT_RATE_PER_HR, 0), v_pamt_tmp) + sqlserver_utilities.round_(NVL(IRT_BILLABLE_HR_OT, 0) * NVL(B.WRDT_OVERTIME_RATE_PER_HR, 0), v_pamt_tmp),
      round(NVL(IRT_BILLABLE_HR_NO, 0) * NVL(B.WRDT_RATE_PER_HR, 0), v_pamt_tmp) + round(NVL(IRT_BILLABLE_HR_OT, 0) * NVL(B.WRDT_OVERTIME_RATE_PER_HR, 0), v_pamt_tmp),
      A.IRT_SERVICE_PRICELIST = B.WRDT_SER_PRLIST,
      A.IRT_REVISION_NO = B.WRDT_SER_PRL_REVNO,
      A.IRT_EFFECTIVE_TILLDATE = B.WRDT_SER_PRL_EFF_DATE,
      A.IRT_BILLABLE_MISCPRICE = B.wrdt_misc_cost;
      EXCEPTION WHEN OTHERS THEN NULL;*/

      Edited by: 956403 on 21 Feb, 2013 5:55 AM
        • 1. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
          Karthick_Arp
          Welcome to the forum!!

          From the document

          <pre>
          ORA-30926: 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.
          </pre>

          This error occurs when the query in the using clause returns more than 1 row for the joining condition
          in on clause.

          Following example demonstrates that.
           
          SQL> create table target(id number, name varchar2(10)); 
          
          Table created. 
          
          SQL> insert into target values (1, 'karthick'); 
          
          1 row created. 
          
          SQL> commit; 
          
          Commit complete. 
          
          SQL> create table source(id number, name varchar2(10)); 
          
          Table created. 
          
          SQL> insert into source values (1, 'sql'); 
          
          1 row created. 
          
          SQL> insert into source values (1, 'oracle'); 
          
          1 row created. 
          
          SQL> commit; 
          
          Commit complete. 
          
          SQL> select * from target; 
          
                  ID NAME 
          ---------- ---------- 
                   1 karthick 
          
          SQL> select * from source; 
          
                  ID NAME 
          ---------- ---------- 
                   1 sql 
                   1 oracle 
          
          SQL> merge into target t 
          using (select * from source) s   
            on (t.id = s.id) 
          when matched then update set t.name = s.name;  2    3    4   
          merge into target t 
                     * 
          ERROR at line 1: 
          ORA-30926: unable to get a stable set of rows in the source tables 
          
          SQL> 
          {message:id=9360002} will help you to ask question in this forum in a better way. So please read.
          • 2. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
            Chanchal Wankhade
            Hi,

            Check out whether there are any duplicate records in your CORM_IRT_INV_RESOURCE_Tmp table. it seems you have duplicate entries hence the error.
            • 3. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
              Peter Gjelstrup
              Karthick_Arp wrote:
              This error occurs when the query in the using clause returns more than 1 row for the joining condition
              in on clause.
              Yes, but it's more like the merge can result in at most one update per join condition.
              SQL> insert into source values (1, 'sql');
              
              1 row created.
              
              SQL> insert into source values (1, 'oracle');
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> select * from target;
              
              no rows selected
              
              SQL> select * from source;
              
                      ID NAME
              ---------- ----------
                       1 sql
                       1 oracle
              
              SQL> merge into target t
                2  using (select * from source) s
                3    on (t.id = s.id)
                4  when matched then update set t.name = s.name
                5  when not matched then insert(id, name) values (s.id, s.name);
              
              2 rows merged.
              
              SQL>
              SQL> select * from target;
              
                      ID NAME
              ---------- ----------
                       1 oracle
                       1 sql
              
              SQL>
              Sometimes fairly confusing that a non-unique source set can be merged successfully.


              Regards
              Peter

              Regards
              Peter
              • 4. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                ora1001
                Yes. There is a problem with the merge statement (oracle bug). Recently i have encountered the same issue. Sometimes a non-unique source set can be merged successfully and if you just run the same merge again, it will throw the error second time.
                The only way is to make sure that already updated records shouldnt be considered for merge or in other words de-duplicate the source data.
                • 5. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                  Paul  Horth
                  Peter Gjelstrup wrote:
                  Karthick_Arp wrote:
                  This error occurs when the query in the using clause returns more than 1 row for the joining condition
                  in on clause.
                  Yes, but it's more like the merge can result in at most one update per join condition.
                  SQL> insert into source values (1, 'sql');
                  
                  1 row created.
                  
                  SQL> insert into source values (1, 'oracle');
                  
                  1 row created.
                  
                  SQL> commit;
                  
                  Commit complete.
                  
                  SQL> select * from target;
                  
                  no rows selected
                  
                  SQL> select * from source;
                  
                  ID NAME
                  ---------- ----------
                  1 sql
                  1 oracle
                  
                  SQL> merge into target t
                  2  using (select * from source) s
                  3    on (t.id = s.id)
                  4  when matched then update set t.name = s.name
                  5  when not matched then insert(id, name) values (s.id, s.name);
                  
                  2 rows merged.
                  
                  SQL>
                  SQL> select * from target;
                  
                  ID NAME
                  ---------- ----------
                  1 oracle
                  1 sql
                  
                  SQL>
                  Sometimes fairly confusing that a non-unique source set can be merged successfully.


                  Regards
                  Peter

                  Regards
                  Peter
                  It's not confusing to me: it's inserting not updating. If it was updating, it wouldn't know which name to pick
                  to do the update.
                  • 6. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                    Solomon Yakobson
                    ora1001 wrote:
                    Sometimes a non-unique source set can be merged successfully and if you just run the same merge again, it will throw the error second time.
                    Are you sure nothing happens to table in between merges? One diff between MERGE and UPDATE (I don't know if it is a bug, but I like it) is number of matching rows can be more than one if new value is same as old value:
                    SQL> drop table tbl1 purge
                      2  /
                    
                    Table dropped.
                    
                    SQL> drop table tbl2 purge
                      2  /
                    
                    Table dropped.
                    
                    SQL> create table tbl1(
                      2                    id number,
                      3                    val number
                      4                   )
                      5  /
                    
                    Table created.
                    
                    SQL> insert
                      2    into tbl1
                      3    select  level,
                      4            0
                      5      from  dual
                      6      connect by level <= 3
                      7  /
                    
                    3 rows created.
                    
                    SQL> create table tbl2(
                      2                    id number,
                      3                    val number
                      4                   )
                      5  /
                    
                    Table created.
                    
                    SQL> insert
                      2    into tbl2
                      3    select  ceil(level / 2),
                      4            1
                      5      from  dual
                      6      connect by level <= 6
                      7  /
                    
                    6 rows created.
                    
                    SQL> commit
                      2  /
                    
                    Commit complete.
                    
                    SQL> select  *
                      2    from  tbl1
                      3  /
                    
                            ID        VAL
                    ---------- ----------
                             1          0
                             2          0
                             3          0
                    
                    SQL> select  *
                      2    from  tbl2
                      3  /
                    
                            ID        VAL
                    ---------- ----------
                             1          1
                             1          1
                             2          1
                             2          1
                             3          1
                             3          1
                    
                    6 rows selected.
                    
                    SQL> update tbl1
                      2     set val = (select val from tbl2 where tbl2.id = tbl1.id)
                      3  /
                       set val = (select val from tbl2 where tbl2.id = tbl1.id)
                                  *
                    ERROR at line 2:
                    ORA-01427: single-row subquery returns more than one row
                    
                    
                    SQL> merge
                      2    into tbl1
                      3    using tbl2
                      4    on (tbl1.id = tbl2.id)
                      5    when matched
                      6      then update
                      7              set tbl1.val = tbl2.val
                      8  /
                      using tbl2
                            *
                    ERROR at line 3:
                    ORA-30926: unable to get a stable set of rows in the source tables
                    
                    
                    SQL> drop table tbl1 purge
                      2  /
                    
                    Table dropped.
                    
                    SQL> drop table tbl2 purge
                      2  /
                    
                    Table dropped.
                    
                    SQL> create table tbl1(
                      2                    id number,
                      3                    val number
                      4                   )
                      5  /
                    
                    Table created.
                    
                    SQL> insert
                      2    into tbl1
                      3    select  level,
                      4            0
                      5      from  dual
                      6      connect by level <= 3
                      7  /
                    
                    3 rows created.
                    
                    SQL> create table tbl2(
                      2                    id number,
                      3                    val number
                      4                   )
                      5  /
                    
                    Table created.
                    
                    SQL> insert
                      2    into tbl2
                      3    select  ceil(level / 2),
                      4            0
                      5      from  dual
                      6      connect by level <= 6
                      7  /
                    
                    6 rows created.
                    
                    SQL> commit
                      2  /
                    
                    Commit complete.
                    
                    SQL> select  *
                      2    from  tbl1
                      3  /
                    
                            ID        VAL
                    ---------- ----------
                             1          0
                             2          0
                             3          0
                    
                    SQL> select  *
                      2    from  tbl2
                      3  /
                    
                            ID        VAL
                    ---------- ----------
                             1          0
                             1          0
                             2          0
                             2          0
                             3          0
                             3          0
                    
                    6 rows selected.
                    
                    SQL> update tbl1
                      2     set val = (select val from tbl2 where tbl2.id = tbl1.id)
                      3  /
                       set val = (select val from tbl2 where tbl2.id = tbl1.id)
                                  *
                    ERROR at line 2:
                    ORA-01427: single-row subquery returns more than one row
                    
                    
                    SQL> merge
                      2    into tbl1
                      3    using tbl2
                      4    on (tbl1.id = tbl2.id)
                      5    when matched
                      6      then update
                      7              set tbl1.val = tbl2.val
                      8  /
                    
                    6 rows merged.
                    
                    SQL> 
                    SY.
                    • 7. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                      ora1001
                      One way to debug the issue is
                      SELECT DISTINCT * FROM PEG6_WRDT_RES_DTS_TMP;
                      Just write the same join in terms of a query and export the results to an excel or you can work the same result set to find out the duplicates. In this case, it would be something like below
                      SELECT DISTINCT * FROM PEG6_WRDT_RES_DTS_TMP B, CORM_IRT_INV_RESOURCE_Tmp A
                      where  B.WRDT_GUID = A.IRT_GUID
                      AND B.WRDT_DOCUMENT_NO = A.IRT_ORDER_NO
                      AND B.WRDT_DOCUMENT_OU = A.IRT_ORDER_OU
                      AND B.WRDT_REF_LINE_NO = A.IRT_REF_LINE_NO
                      AND B.WRDT_TASK_LINE_NO = A.IRT_LINE_NO
                      AND B.WRDT_RESOURCE_NO = A.IRT_RESOURCE_CODE
                      AND B.WRDT_RES_TYPE = A.IRT_RESOUCE_TYPE
                      AND A.IRT_GUID = v_Guid_Tmp 
                      AND A.IRT_ORDER_NO = v_cusorderno_Tmp
                      AND A.IRT_ORDER_OU = v_COrderOU_Tmp;
                      I generally just export to excel and find out the duplicates with respect to the columns that you have used for join condition.
                      • 8. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                        ora1001
                        Hi Solomon- I have tried this example
                        SQL> create table a
                          2  (key number
                          3  ,val varchar2(1));
                        
                        Table created.
                        
                        SQL> 
                        SQL> create table b
                          2  (key number
                          3  ,val varchar2(1));
                        
                        Table created.
                        
                        SQL> 
                        SQL> insert into a values(1,'A');
                        
                        1 row created.
                        
                        SQL> 
                        SQL> insert into b values(1,1);
                        
                        1 row created.
                        
                        SQL> 
                        SQL> insert into b values(1,2);
                        
                        1 row created.
                        
                        SQL> 
                        SQL> commit;
                        
                        Commit complete.
                        
                        SQL> 
                        SQL> select * from a;
                        
                               KEY V
                        ---------- -
                                 1 A
                        
                        SQL> select * from b;
                        
                               KEY V
                        ---------- -
                                 1 1
                                 1 2
                        SQL> 
                        ORA-30926 expected, but it’s not:
                        SQL> select * from a;
                        
                               KEY V
                        ---------- -
                                 1 A
                        
                        SQL> 
                        SQL> merge into a
                          2  using (select b.val
                          3         ,      b.key
                          4         from   b) b
                          5  on (a.key = b.key)
                          6  when matched then 
                          7   update
                          8   set a.val = decode(b.val,'1','A','B');
                        
                        2 rows merged.
                        
                        SQL> 
                        SQL> select * from a;
                        
                               KEY V
                        ---------- -
                                 1 B
                        
                        SQL> 
                        now if i run it again..
                        SQL> select * from a;
                        
                               KEY V
                        ---------- -
                                 1 B
                        
                        SQL> merge into a
                          2  using (select b.val
                          3         ,      b.key
                          4         from   b) b
                          5  on (a.key = b.key)
                          6  when matched then 
                          7   update
                          8   set a.val = decode(b.val,'1','A','B');
                        merge into a
                                   *
                        ERROR at line 1:
                        ORA-30926: unable to get a stable set of rows in the source tables
                        
                        
                        
                        SQL> rollback;
                        
                        Rollback complete.
                        
                        SQL> select * from a;
                        
                               KEY V
                        ---------- -
                                 1 A
                        
                        SQL> 
                        • 9. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                          BluShadow
                          ora1001 wrote:
                          Yes. There is a problem with the merge statement (oracle bug). Recently i have encountered the same issue. Sometimes a non-unique source set can be merged successfully and if you just run the same merge again, it will throw the error second time.
                          The only way is to make sure that already updated records shouldnt be considered for merge or in other words de-duplicate the source data.
                          If you're going to start stating things as Oracle Bugs, please indicate the bug number and the specific database version(s) it effects. Just generally saying it's a database bug doesn't help if it really is a bug and has been fixed in later versions. for all we know you could be using a 9i database or something.
                          • 10. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                            ora1001
                            I agree with you. Shouldn't have stated that way. I am using Oracle 10g. How the check ORA-30926 is implemented is possibly not documented. There is an article on metalink that provides information on this issue and also two possible work arounds. But i guess this particular merge oddity is not explained or documented.
                            • 11. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                              Solomon Yakobson
                              ora1001 wrote:
                              ORA-30926 expected, but it’s not:
                              Post all statem ents so we can reuse them.

                              SY.

                              Edited by: Solomon Yakobson on Feb 21, 2013 1:41 PM
                              • 12. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                                Christine Schnittker
                                >
                                I generally just export to excel and find out the duplicates with respect to the columns that you have used for join condition.
                                >

                                I wanna see you do that with a resultset of a couple of million rows.

                                You have heard of "count" and "having"..?

                                scnr
                                //Tine
                                • 13. Re: MERGE STATEMENT ORA-30926: unable to get a stable set of rows in the source
                                  Peter Gjelstrup
                                  Hi ora1001,

                                  I have re-produced your example in
                                  BANNER
                                  ----------------------------------------------------------------
                                  Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
                                  PL/SQL Release 10.2.0.1.0 - Production
                                  CORE    10.2.0.1.0      Production
                                  TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
                                  NLSRTL Version 10.2.0.1.0 - Production
                                  Agreed, this looks dodgy. At least I cannot come up with any reasonable explanation.

                                  Someone reported someting similar in Bug 8923964. No public details, so relevance is unknown.

                                  But it could be worthwhile checking with Oracle Support. Unless of course, other members come up with a reasonable explanation.

                                  Regards
                                  Peter