Home » SQL & PL/SQL » SQL & PL/SQL » ORA-30926: unable to get a stable set of rows in the source tables (Oracle 9i)
ORA-30926: unable to get a stable set of rows in the source tables [message #429147] Mon, 02 November 2009 05:53 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I am trying to merge some data into the target table named 'SMP_VENDOR_ITEM1' only for columns having FLAG = 'U' in table 'PARKING_SMP_VENDOR_ITEM'.

Below is the structure of the two tables.

CREATE TABLE PARKING_SMP_VENDOR_ITEM
(
  VENDOR_CODE         VARCHAR2(30 BYTE)         NOT NULL,
  ITEM_CODE           VARCHAR2(32 BYTE),
  VENDOR_ITEM_STATUS  NUMBER                    DEFAULT 0,
  FLAG                CHAR(1 BYTE)              DEFAULT 'U',
  UPDATE_STATUS       CHAR(1 BYTE)              DEFAULT 'N'
)

INSERT INTO PARKING_SMP_VENDOR_ITEM ( VENDOR_CODE, ITEM_CODE, VENDOR_ITEM_STATUS, FLAG,
UPDATE_STATUS ) VALUES ( 
'900', '10600103', 1, 'U', 'Y'); 
COMMIT;



CREATE TABLE SMP_VENDOR_ITEM1
(
  VENDOR_ITEM_ID      NUMBER,
  VENDOR_ID           VARCHAR2(30 BYTE),
  ITEMCODE            VARCHAR2(32 BYTE),
  VENDOR_ITEM_STATUS  NUMBER
)

INSERT INTO SMP_VENDOR_ITEM1 ( VENDOR_ITEM_ID, VENDOR_ID, ITEMCODE,
VENDOR_ITEM_STATUS ) VALUES ( 
74704, '900', '10600103', 0); 
COMMIT;


When I run the below Merge statement, I get error:-

MERGE INTO smp_vendor_item1 
                  USING parking_smp_vendor_item
                  ON (smp_vendor_item1.vendor_id ='900'                                          
                      AND smp_vendor_item1.itemcode = '10600103')
                  WHEN MATCHED THEN
                     UPDATE
                        SET smp_vendor_item1.VENDOR_ITEM_STATUS = 1                               
                  WHEN NOT MATCHED THEN
                     INSERT (vendor_item_id, vendor_id, itemcode,vendor_item_status)
                     values(125452,'900','10600103', 1  );




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


Not sure what is going wrong.....

If I run the same commands in a seperate schema for test...it runs fine but not in the schema where I am working Sad

Any clues?

Regards,
Mahi

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429154 is a reply to message #429147] Mon, 02 November 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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.

Add the appropriate pk or unique constraints.
There is no join condition in your ON clause, as soon as there are more than one row in parking_smp_vendor_item the result set is non deterministic.

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429167 is a reply to message #429154] Mon, 02 November 2009 06:44 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi Michel,
Even when I try to execute the Merge query independently, it gives the error. You can see that the query doesn't have any DML operation nor any where clause. Its a simple Merge Operation.

I am not sure how to use the Join Condition in Merge. It can be on two columns ( VENDOR_CODE & ITEM_CODE).

Please advice.

Mahi
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429169 is a reply to message #429167] Mon, 02 November 2009 07:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When I try it on 10g, it works.

You could try adding the join condition like this:
MERGE INTO smp_vendor_item1   s
USING parking_smp_vendor_item p
ON (s.vendor_id = p.vendor_id
AND s.itemcode  = p.itemcode)
WHEN MATCHED THEN UPDATE
                  SET smp_vendor_item1.VENDOR_ITEM_STATUS = 1                               
WHEN NOT MATCHED THEN
                 INSERT (vendor_item_id, vendor_id, itemcode,vendor_item_status)
                 values(125452,'900','10600103', 1  );
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429170 is a reply to message #429167] Mon, 02 November 2009 07:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Think about what I said.
For each row you retrieve from the first table you get ALL rows of the second table, so Oracle can't know in which order it has to apply them, this is what is called a "not stable set of rows".

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429174 is a reply to message #429170] Mon, 02 November 2009 07:26 Go to previous messageGo to next message
ramoradba
Messages: 2456
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
[quote]Before posting On Orafaq ...Please google for that ...[/quote]

SQL> CONN SRIRAM/SRIRAM
Connected.
SQL> SELECT BANNER FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
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

SQL> CREATE TABLE PARKING_SMP_VENDOR_ITEM
  2  (
  3    VENDOR_CODE         VARCHAR2(30 BYTE)         NOT NULL,
  4    ITEM_CODE           VARCHAR2(32 BYTE),
  5    VENDOR_ITEM_STATUS  NUMBER                    DEFAULT 0,
  6    FLAG                CHAR(1 BYTE)              DEFAULT 'U',
  7    UPDATE_STATUS       CHAR(1 BYTE)              DEFAULT 'N'
  8  );

Table created.

SQL> INSERT INTO PARKING_SMP_VENDOR_ITEM ( VENDOR_CODE, ITEM_CODE, VENDOR_ITEM_STATUS, FLAG,
  2  UPDATE_STATUS ) VALUES (
  3  '900', '10600103', 1, 'U', 'Y');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> CREATE TABLE SMP_VENDOR_ITEM1
  2  (
  3    VENDOR_ITEM_ID      NUMBER,
  4    VENDOR_ID           VARCHAR2(30 BYTE),
  5    ITEMCODE            VARCHAR2(32 BYTE),
  6    VENDOR_ITEM_STATUS  NUMBER
  7  )
  8  ;

Table created.

SQL> INSERT INTO SMP_VENDOR_ITEM1 ( VENDOR_ITEM_ID, VENDOR_ID, ITEMCODE,
  2  VENDOR_ITEM_STATUS ) VALUES (
  3  74704, '900', '10600103', 0);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> MERGE INTO smp_vendor_item1
  2                    USING parking_smp_vendor_item
  3                    ON (smp_vendor_item1.vendor_id ='900'
  4                        AND smp_vendor_item1.itemcode = '10600103')
  5                    WHEN MATCHED THEN
  6                       UPDATE
  7                          SET smp_vendor_item1.VENDOR_ITEM_STATUS = 1
  8                    WHEN NOT MATCHED THEN
  9                       INSERT (vendor_item_id, vendor_id, itemcode,vendor_item_status)
 10                       values(125452,'900','10600103', 1  );

1 row merged.

SQL>



SQL> ED
Wrote file afiedt.buf

  1* CREATE TABLE target(a NUMBER, b NUMBER, c NUMBER, d NUMBER,
  constraint pk_target primary key(a,b,c) using index)
SQL> /

Table created.

SQL> ED
Wrote file afiedt.buf

  1  CREATE TABLE source (a NUMBER, b NUMBER, c NUMBER, d NUMBER,
  2  constraint pk_source primary key(a,b,c)
  3*  using index)
SQL> /

Table created.

SQL> ED
Wrote file afiedt.buf

  1* INSERT INTO target values (1,1,1,1)
SQL> /

1 row created.



INSERT INTO target values (1,1,2,2)
/
1 row created.
INSERT INTO target values (1,1,3,3)
/
1 row created.
INSERT INTO source values (1,1,1,1)
/
1 row created.
INSERT INTO source values (1,1,2,2)
/
1 row created.
INSERT INTO source values (1,1,3,3)
/
1 row created.
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)
/

MERGE INTO target t1
           *
ERROR at line 1:
ORA-30926: unable to get a stable set of rows in the source tables


SQL>
SQL> ROLLBACK;

Rollback complete.

SQL>








SQL> INSERT INTO target values (1,1,1,1)
  2  /

1 row created.

SQL> INSERT INTO target values (2,2,2,2)
  2  /

1 row created.

SQL> INSERT INTO target values (3,3,3,3)
  2  /

1 row created.

SQL> INSERT INTO source values (1,1,1,1)
  2  /

1 row created.

SQL> INSERT INTO source values (2,2,2,2)
  2  /

1 row created.

SQL> INSERT INTO source values (3,3,3,3)
  2  /

1 row created.

SQL> MERGE INTO target t1
  2  USING (SELECT a, b, c, d from source) t2
  3  ON (t1.a = t2.a AND t1.b = t2.b)
  4  WHEN MATCHED THEN
  5  UPDATE SET t1.c = t2.c,t1.d = 2 * t2.d
  6  WHEN NOT MATCHED THEN
  7  INSERT (a, b, c, d)VALUES (t2.a, t2.b, t2.c, t2.d)
  8  /

3 rows merged.

SQL>
Read the Following article.
http://oraclequirks.blogspot.com/2005/12/ora-30926-and-merge.html

[Updated on: Mon, 02 November 2009 07:29]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429268 is a reply to message #429174] Mon, 02 November 2009 23:19 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Thanks to all of you for your valuable inputs. The link was really helpful.

I was missing on some more joins so I did the joins and it merged.

              
               MERGE INTO smp_vendor_item s
                  USING (SELECT p.vendor_code, p.item_code, p.vendor_item_status 
                           FROM parking_smp_vendor_item p, smp_vendor v, smp_item i
                          WHERE p.vendor_code = v.vendor_id
                            AND p.item_code = i.itemcode 
                            AND p.update_status = 'Y')p
                  ON (s.vendor_id = p.vendor_code
                      AND s.itemcode = p.item_code
                      )
                  WHEN MATCHED THEN
                     UPDATE
                        SET s.VENDOR_ITEM_STATUS = p.vendor_item_status                               
                  WHEN NOT MATCHED THEN
                     INSERT (vendor_item_id, vendor_id, itemcode,vendor_item_status)
                     VALUES (smp_vendor_item_seq.NEXTVAL, p.vendor_code,
                             p.item_code,
                             p.vendor_item_status);


I have one more question that now the client want that I should maintain audit-trail for any update/inserts in the table SMP_VENDOR_ITEM1.

The Merge would do everything in one go. How can I insert data in audit table for every insert and update because I would need to insert the each merged itemcode,vendorcode and status in audit trail with old and new value.

I have to insert a row in audit table for every update or insert.
Can I do that in Merge statement somehow. Or will I have to take help of a loop and forget Merge for Audit trail.

Regards,
Mahi


[Updated on: Mon, 02 November 2009 23:37]

Report message to a moderator

Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429283 is a reply to message #429268] Tue, 03 November 2009 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want an audit for each row or only for each statement?

Regards
Michel
Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429287 is a reply to message #429283] Tue, 03 November 2009 01:04 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Audit for each record updated or inserted.

In the audit table, the text column would be inserted for updated record as

vTaskText := 'Updated Vendor Item Status: '||v_vendor_item_status_pre||' to '||
                                    cur_rec_vendor_item.vendor_item_status||
                                   ' for Vendor Item Id: '||v_vendor_item_id;


And for inserted record as :-
vTaskText:= 'Inserted into smp_vendor_item - ' ||
                                 ' VENDOR ITEM ID: '||v_vli_seq_pre ||
                                 ', Vendor Code '||v_vendor_code||                             
                                 ', Item Code: '||v_item_code||
                                 ', Vendor Item Status: '||cur_rec_vendor_item.vendor_item_status;


Re: ORA-30926: unable to get a stable set of rows in the source tables [message #429289 is a reply to message #429287] Tue, 03 November 2009 01:17 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case you have to either use a trigger or go to fine-grained auditing.

Regards
Michel

Previous Topic: queried data from column into rows
Next Topic: Writing file from random position using UTL_FILE
Goto Forum:
  


Current Time: Thu Mar 28 16:01:09 CDT 2024