SR_3-21137080651

View All Public Scripts Login to Run Script

Script Name SR_3-21137080651
Visibility Unlisted - anyone with the share link can access
Description Starting from 12.2 merge does not explicitly raise ORA-30926: unable to get a stable set of rows in the source tables when duplicate records for the same key exist in the source data
Area Oracle 19c
Contributor martin.zahrubsky@gmail.com
Created Tuesday February 11, 2020

Statement 1

drop table target purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

drop table source purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 3

create table target ( a number, b number )

Table created.

Statement 4

create table source ( a number, b number )

Table created.

Statement 5

insert into target values ( 1, 1 )

1 row(s) inserted.

Statement 6

insert into source values ( 1, 1 )

1 row(s) inserted.

Statement 7

insert into source values ( 1, 2 )

1 row(s) inserted.

Statement 8

insert into source values ( 1, 3 )

1 row(s) inserted.

Statement 9

merge into target 
using (select * from source order by b desc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 10

merge into target 
using (select * from source order by b asc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 11

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 12
```
drop table target purge
```
```
Table dropped.
```
Statement 13
```
drop table source purge
```
```
Table dropped.
```

Statement 14

create table target ( a number, b number )

Table created.

Statement 15

create table source ( a number, b number )

Table created.

Statement 16

insert into target values ( 1, 1 )

1 row(s) inserted.

Statement 17

insert into source values ( 1, 1 )

1 row(s) inserted.

Statement 18

insert into source values ( 1, 2 )

1 row(s) inserted.

Statement 19

insert into source values ( 1, 3 )

1 row(s) inserted.

Statement 20

merge into target 
using (select * from source order by b desc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 21

merge into target 
using (select * from source order by b asc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 22

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 23

delete source where b= 3

1 row(s) deleted.

Statement 24

merge into target 
using (select * from source order by b desc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 25

merge into target 
using (select * from source order by b asc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

Statement processed.

Statement 26

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 27
```
drop table target purge
```
```
Table dropped.
```
Statement 28
```
drop table source purge
```
```
Table dropped.
```

Statement 29

create table target ( a number, b number )

Table created.

Statement 30

create table source ( a number, b number )

Table created.

Statement 31

insert into target values ( 1, 1 )

1 row(s) inserted.

Statement 32

insert into source values ( 1, 1 )

1 row(s) inserted.

Statement 33

insert into source values ( 1, 2 )

1 row(s) inserted.

Statement 34

insert into source values ( 1, 3 )

1 row(s) inserted.

Statement 35

merge into target 
using (select * from source order by b desc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 36

merge into target 
using (select * from source order by b asc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 37

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 38

delete source where b= 3

1 row(s) deleted.

Statement 39

merge into target 
using (select * from source order by b desc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 40

merge into target 
using (select * from source order by b asc) source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

Statement processed.

Statement 41

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

ORA-30926: unable to get a stable set of rows in the source tables ORA-06512: at "SYS.DBMS_SQL", line 1721

More Details: https://docs.oracle.com/error-help/db/ora-30926

Statement 42
```
rollback
```
```
Statement processed.
```

Statement 43

merge into target 
using source 
on (target.a = source.a) 
when matched then 
update set target.b = source.b

Statement processed.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database