merge_with_dbms_errlog

View All Scripts Login to Run Script

Script Name merge_with_dbms_errlog
Description test of merge behaviour in case of unstable source rowset and dml error logging
Area Oracle 12c
Contributor matteo
Created Monday April 16, 2018

Statement 1

drop table dest_testlog

ORA-00942: table or view does not exist

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

Statement 2

create table dest_testlog (c1 number, c2 varchar2(20))

Table created.

Statement 3

insert into dest_testlog 
select level , 'DEST'   from dual connect by level <= 5

5 row(s) inserted.

Statement 4

drop table src_testlog2

ORA-00942: table or view does not exist

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

Statement 5

create table src_testlog2(c1 number, c2 varchar2(20))

Table created.

Statement 6

insert into src_testlog2 
select level c1, 'SOURCE' as c2 from dual connect by level <= 5

5 row(s) inserted.

Statement 7

update src_testlog2 set c1 = 1, c2 = '????' 
where c1 = 2

1 row(s) updated.

Statement 8

select * from src_testlog2


C1 C2
1 SOURCE
1 ????
3 SOURCE
4 SOURCE
5 SOURCE

5 rows selected.

C1	C2
1	SOURCE
1	????
3	SOURCE
4	SOURCE
5	SOURCE

Statement 9

merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2

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

drop table err$_dest_testlog

ORA-00942: table or view does not exist

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

Statement 11

begin 
   dbms_errlog.create_error_log(dml_table_name => 'dest_testlog'); 
end; 
 
 
select * from err$_dest_testlog; 
 
-- retry merge with log errors into clause.... 
-- merge is now successful,  
merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2 
log errors into err$_dest_testlog ('have a look!!!') reject limit unlimited; 
 
-- controlla log riga in errore 
select * from err$_dest_testlog; 
 
select * from dest_testlog where rowid in (select elog.ora_err_rowid$ from err$_dest_testlog elog); 
select * from dest_testlog; 
 
--- WOW!! update has been done even for row with unstable source dataset 
 
--drop table dest_testlog; 
--drop table src_testlog2; 
---drop table err$_dest_testlog;

ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "SELECT"

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

Statement 12
```
drop table dest_testlog
```
```
Table dropped.
```

Statement 13

create table dest_testlog (c1 number, c2 varchar2(20))

Table created.

Statement 14

insert into dest_testlog 
select level , 'DEST'   from dual connect by level <= 5

5 row(s) inserted.

Statement 15
```
drop table src_testlog2 
```
```
Table dropped.
```

Statement 16

create table src_testlog2(c1 number, c2 varchar2(20))

Table created.

Statement 17

insert into src_testlog2 
select level c1, 'SOURCE' as c2 from dual connect by level <= 5

5 row(s) inserted.

Statement 18

update src_testlog2 set c1 = 1, c2 = '????' 
where c1 = 2

1 row(s) updated.

Statement 19

select * from src_testlog2


C1 C2
1 SOURCE
1 ????
3 SOURCE
4 SOURCE
5 SOURCE

5 rows selected.

C1	C2
1	SOURCE
1	????
3	SOURCE
4	SOURCE
5	SOURCE

Statement 20

merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2

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

drop table err$_dest_testlog

ORA-00942: table or view does not exist

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

Statement 22

begin 
   dbms_errlog.create_error_log(dml_table_name => 'dest_testlog'); 
end;

Statement processed.

Statement 23

select * from err$_dest_testlog

no data found

Statement 24

merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2 
log errors into err$_dest_testlog ('have a look!!!') reject limit unlimited

4 row(s) updated.

Statement 25

select * from err$_dest_testlog


ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ C1 C2
30926 ORA-30926: unable to get a stable set of rows in the source tables
AAGsUCAAPAAAAFWAAA U have a look!!! 1 ????

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	C1	C2
30926	ORA-30926: unable to get a stable set of rows in the source tables	AAGsUCAAPAAAAFWAAA	U	have a look!!!	1	????

Statement 26

select * from dest_testlog where rowid in (select elog.ora_err_rowid$ from err$_dest_testlog elog)


C1 C2
1 SOURCE

C1	C2
1	SOURCE

Statement 27

select * from dest_testlog


C1 C2
1 SOURCE
2 DEST
3 SOURCE
4 SOURCE
5 SOURCE

5 rows selected.

Statement 28
```
drop table dest_testlog
```
```
Table dropped.
```

C1	C2
1	SOURCE
2	DEST
3	SOURCE
4	SOURCE
5	SOURCE

Statement 29

create table dest_testlog (c1 number, c2 varchar2(20))

Table created.

Statement 30

insert into dest_testlog 
select level , 'DEST'   from dual connect by level <= 5

5 row(s) inserted.

Statement 31
```
drop table src_testlog2 
```
```
Table dropped.
```

Statement 32

create table src_testlog2(c1 number, c2 varchar2(20))

Table created.

Statement 33

insert into src_testlog2 
select level c1, 'SOURCE' as c2 from dual connect by level <= 5

5 row(s) inserted.

Statement 34

update src_testlog2 set c1 = 1, c2 = '????' 
where c1 = 2

1 row(s) updated.

Statement 35

select * from src_testlog2


C1 C2
1 SOURCE
1 ????
3 SOURCE
4 SOURCE
5 SOURCE

5 rows selected.

C1	C2
1	SOURCE
1	????
3	SOURCE
4	SOURCE
5	SOURCE

Statement 36

merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2

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

drop table err$_dest_testlog

Table dropped.

Statement 38

begin 
   dbms_errlog.create_error_log(dml_table_name => 'dest_testlog'); 
end;

Statement processed.

Statement 39

select * from err$_dest_testlog

no data found

Statement 40

merge into dest_testlog l 
using src_testlog2 ll 
on (l.c1 = ll.c1) 
when matched then update set 
 l.c2 = ll.c2 
log errors into err$_dest_testlog ('have a look!!!') reject limit unlimited

4 row(s) updated.

Statement 41

select * from err$_dest_testlog


ORA_ERR_NUMBER$ ORA_ERR_MESG$ ORA_ERR_ROWID$ ORA_ERR_OPTYP$ ORA_ERR_TAG$ C1 C2
30926 ORA-30926: unable to get a stable set of rows in the source tables
AAGsVTAAPAAAAE+AAA U have a look!!! 1 ????

ORA_ERR_NUMBER$	ORA_ERR_MESG$	ORA_ERR_ROWID$	ORA_ERR_OPTYP$	ORA_ERR_TAG$	C1	C2
30926	ORA-30926: unable to get a stable set of rows in the source tables	AAGsVTAAPAAAAE+AAA	U	have a look!!!	1	????

Statement 42

select * from dest_testlog where rowid in (select elog.ora_err_rowid$ from err$_dest_testlog elog)


C1 C2
1 SOURCE

C1	C2
1	SOURCE

Statement 43

select * from dest_testlog


C1 C2
1 SOURCE
2 DEST
3 SOURCE
4 SOURCE
5 SOURCE

5 rows selected.

C1	C2
1	SOURCE
2	DEST
3	SOURCE
4	SOURCE
5	SOURCE

Additional Information

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