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  |
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 
Any clues?
Regards,
Mahi
|
|
|
|
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   |
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   |
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 #429174 is a reply to message #429170] |
Mon, 02 November 2009 07:26   |
 |
ramoradba
Messages: 2457 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   |
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 #429287 is a reply to message #429283] |
Tue, 03 November 2009 01:04   |
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;
|
|
|
|
Goto Forum:
Current Time: Tue Feb 11 02:45:57 CST 2025
|