6 Replies Latest reply: Mar 27, 2012 2:41 AM by 873956 RSS

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

    873956
      hi,

      I am loading data from source table to target table in a interface.
      Using LKM incremental update .

      In the merge rows step , getting the below error.

      30926 : 99999 : java.sql.SQLException: ORA-30926: unable to get a stable set of rows in the source tables


      please help as what should be done to resolve this.
        • 1. Re: ORA-30926: unable to get a stable set of rows in the source tables
          Sutirtha Roy
          Hi,

          Seems to be some issue in the update/merge part .
          Check the query generated , run it from any sql client and see what is causing this .

          Thanks,
          Sutirtha
          • 2. Re: ORA-30926: unable to get a stable set of rows in the source tables
            873956
            Below is the query in the merge step...

            when i run from SQL also, same error

            SQL Error: ORA-30926: unable to get a stable set of rows in the source tables
            30926. 00000 - "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.
            --------------------------------------------------------------------------------------------------------------------------------------------------


            merge into     TFR.INVENTORIES T
            using     TFR.I$_INVENTORIES S
            on     (
                      T.ORGANIZATION_ID=S.ORGANIZATION_ID
                 and          T.ITEM_ID=S.ITEM_ID
                 )
            when matched
            then update set
                 T.ITEM_TYPE     = S.ITEM_TYPE,
                 T.SEGMENT1     = S.SEGMENT1,
                 T.DESCRIPTION     = S.DESCRIPTION,
                 T.LIST_PRICE_PER_UNIT     = S.LIST_PRICE_PER_UNIT,
                 T.CREATED_BY     = S.CREATED_BY,
                 T.DEFAULT_SO_SOURCE_TYPE     = S.DEFAULT_SO_SOURCE_TYPE,
                 T.MATERIAL_BILLABLE_FLAG     = S.MATERIAL_BILLABLE_FLAG,
                 T.LAST_UPDATED_BY     = S.LAST_UPDATED_BY
                 ,T.ID     = TFR.INVENTORIES_SEQ.NEXTVAL,
                 T.CREATION_DATE     = CURRENT_DATE,
                 T.LAST_UPDATE_DATE     = CURRENT_DATE
            when not matched
            then insert
                 (
                 T.ORGANIZATION_ID,
                 T.ITEM_ID,
                 T.ITEM_TYPE,
                 T.SEGMENT1,
                 T.DESCRIPTION,
                 T.LIST_PRICE_PER_UNIT,
                 T.CREATED_BY,
                 T.DEFAULT_SO_SOURCE_TYPE,
                 T.MATERIAL_BILLABLE_FLAG,
                 T.LAST_UPDATED_BY
                 ,T.ID,
                 T.CREATION_DATE,
                 T.LAST_UPDATE_DATE
                 )
            values
                 (
                 S.ORGANIZATION_ID,
                 S.ITEM_ID,
                 S.ITEM_TYPE,
                 S.SEGMENT1,
                 S.DESCRIPTION,
                 S.LIST_PRICE_PER_UNIT,
                 S.CREATED_BY,
                 S.DEFAULT_SO_SOURCE_TYPE,
                 S.MATERIAL_BILLABLE_FLAG,
                 S.LAST_UPDATED_BY
                 ,TFR.INVENTORIES_SEQ.NEXTVAL,
                 CURRENT_DATE,
                 CURRENT_DATE
                 )
            • 3. Re: ORA-30926: unable to get a stable set of rows in the source tables
              Sutirtha Roy
              So this indicates that your join based on the 2 key column is returning more that 1 row while updating 1 row.
              That is not possible . Now check why your key column defined is not able to return single row and probalbly you need to change/add key column .
              • 4. Re: ORA-30926: unable to get a stable set of rows in the source tables
                873956
                Thank You..

                Actually the problem was, when the interface fails, the drop I$ flow table step is not executed.

                So when the next time, it starts, the I$ table was not purged for some reason..

                so once i purged manually and executed, it went through fine.

                Edited by: 870953 on Mar 27, 2012 12:40 AM
                • 5. Re: ORA-30926: unable to get a stable set of rows in the source tables
                  Bhabani Ranjan
                  Well it should drop the I$ table. because in your IKM you can find a step drop flow table at the beginning.. Might be your table name changed from I$_0 to I$_1. not sure but something like that. If IKM part is not executing then you have to manually drop
                  • 6. Re: ORA-30926: unable to get a stable set of rows in the source tables
                    873956
                    yeah... Now that drop I$ step is executing perfectly...