Both are especially useful for closed system, in which application is hard to be modified to fully expose code path and error causes.
Note: Tested in Oracle 19c
1. Test Setup
We create a table with one unique constraint, then insert a few rows:
drop table ora1_test_tab;
create table ora1_test_tab(id number, text varchar2(10));
create unique index ora1_test_tab#u#idx on ora1_test_tab(id);
--truncate table ora1_test_tab;
insert into ora1_test_tab values(1, 'A1');
insert into ora1_test_tab values(2, 'A2');
insert into ora1_test_tab values(3, 'A3');
commit;
select * from ora1_test_tab;
ID TEXT
--- ----
1 A1
2 A2
3 A3
2. ORA-00001 Errorstack Trace
Run following test, in which ORA-00001 is cut away (Plsql callstack printed out in exception handler):
create or replace procedure ORA_00001_cut(p_id number) as
begin
insert into ora1_test_tab values(p_id, 'B3');
exception when others then
dbms_output.put_line('Error');
dbms_output.put_line(substr(dbms_utility.format_call_stack,1,2000));
end;
/
alter system set events='10046 trace name context forever, level 12';
alter system set events '1 trace name errorstack level 3';
begin
ORA_00001_cut(3);
end;
/
alter system set events '1 trace name errorstack off';
alter system set events='10046 trace name context off';
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 3
Detector: Check-2: ID = 3 (in Session_ID: 17), Exists by Already Committed TRX
Error
----- PL/SQL Call Stack -----
object line object
handle number name
0x8a5b1568 6 procedure K.ORA_00001_CUT
0x81dd4768 2 anonymous block
Errors in file /orabin/app/oracle/admin/test_db3/diag/rdbms/test_db3/test_db3/trace/test_db3_ora_650408.trc:
ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
=====================
PARSING IN CURSOR #140050023218008 len=44 dep=1 uid=49 oct=2 lid=49 tim=9261352709305 hv=515771508 ad='8db01638' sqlid='74zg41sgbw33n'
INSERT INTO ORA1_TEST_TAB VALUES(:B1 , 'B3')
END OF STMT
PARSE #140050023218008:c=170,e=170,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=9261352709305
BINDS #140050023218008:
Bind#0
oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0
kxsbbbfp=8b3cb490 bln=22 avl=02 flg=09
value=3
=====================
----- Error Stack Dump -----
ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
----- Current SQL Statement for this session (sql_id=74zg41sgbw33n) -----
INSERT INTO ORA1_TEST_TAB VALUES(:B1 , 'B3')
----- PL/SQL Call Stack -----
object line object
handle number name
0x8a5b1568 3 procedure K.ORA_00001_CUT
0x81dd4768 2 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst1()+95 call kgdsdst() 7FFF914C8680 000000002
7FFF914C28D0 ? 7FFF914C29E8 ?
000000000 000000000
......
kauerr()+522 call kxcser() 7FFF914CBB40 000000001
7FFF914CBAC0 000000013
000000001 000000000
qesltcLoadIndexList call kauerr() 0005AAA5D 000000001 ?
()+2215 7FFF914CBAC0 ? 000000013 ?
000000001 ? 000000000 ?
qerltcNoKdtBuffered call qesltcLoadIndexList 7F682EC89290 09FC19BA8
InsRowCBK()+370 () 000000000 000000000
000000001 ? 000000000 ?
qerltcSingleRowLoad call qerltcNoKdtBuffered 7F682EC89230 09FC1A220
()+293 InsRowCBK() 09FC1A750 000000000 ?
7F6835BACC08 000000000
qerltcFetch()+391 call qerltcSingleRowLoad 09FC1A750 7F682EC89230
() 09FC1A220 000000000 ?
7F6835BACC08 ? 000000000 ?
qerstFetch()+541 call qerltcFetch() 09FC1A750 7F682EC89230
009748EB0 7FFF914CD140
000007FFF 09FC1BC28
insexe()+733 call qerstFetch() 09FC1A6E8 7F682EC89568
00D0CBE20 07302917A 000007FFF
09FC1BC28 ?
opiexe()+7194 call insexe() 09FC1A6E8 ? 7F682EC89568 ?
00D0CBE20 ? 07302917A ?
000007FFF ? 09FC1BC28 ?
3. ORA-00001 Before Trigger
Here our test procedures:
3.1 ORA-00001 Detector
create or replace procedure debug_output(p_text varchar2) as
begin
dbms_output.put_line(p_text);
sys.DBMS_System.ksdwrt(2, p_text);
end;
/
create or replace procedure ora_00001_detector(p_id number) as
l_id number := p_id;
l_sid number;
l_trx_date date;
l_trx_ts varchar2(100);
l_stmt varchar2(1000);
l_id_found number;
l_ret_str varchar2(1000);
begin
select sid into l_sid from v$mystat where rownum = 1;
l_ret_str := 'ID = '||l_id||' (in Session_ID: '||l_sid||')';
select max(id) into l_id_found from ora1_test_tab where id = l_id;
if l_id_found is null then
--Insert ID not yet exist
l_ret_str := 'Check-1: '||l_ret_str||' Not Exist';
else
select max(start_date) into l_trx_date from v$session s, v$transaction t where s.taddr=t.addr and sid = l_sid;
if l_trx_date is null then
--Insert ID exists by Already Committed TRX
l_ret_str := 'Check-2: '|| l_ret_str||', Exists by Already Committed TRX';
else
l_trx_ts := to_char(l_trx_date, 'YYYY-MM-DD hh24:mi:ss');
l_ret_str := l_ret_str||', My TRX start_date: '||l_trx_ts ||', flashback query timestamp: '||l_trx_ts;
l_stmt := 'select max(id) from ora1_test_tab as of timestamp timestamp''' ||l_trx_ts||''' where id = '||l_id;
l_ret_str := l_ret_str||chr(10)||' Stmt:'||l_stmt||chr(10);
execute immediate l_stmt into l_id_found;
if l_id_found is null then
--Insert ID inserted twice in the same TRX: first insert not yet committed, and inserted again. flashback query not found it.
l_ret_str := 'Check-3: '||l_ret_str||' flashback query No Found, My SID Inserted, Not Yet Committed.';
else
--Insert ID inserted after TRX started by another DML, flashback query found it.
l_ret_str := 'Check-4: '||l_ret_str||' flashback query Found Committed ID : '||l_id_found;
end if;
end if;
end if;
debug_output('Detector: '||l_ret_str);
-- @TODO Printout Plsql call_stack:
--debug_output(substr(dbms_utility.format_call_stack,1,2000));
end;
/
3.2 Before Trigger
create or replace trigger ora_00001_test_tab_trig
before insert or update on ora1_test_tab referencing new as new old as old for each row
declare
--pragma autonomous_transaction;
tab varchar2(30) := 'ora1_test_tab: ';
action varchar2(30);
begin
case
when inserting then action := 'BEFORE INSERT';
when updating then action := 'BEFORE UPDATE';
end case;
debug_output('Trigger: '||action||' on '|| tab ||' :Old Val: '||:old.id||' :New Val: '||:new.id);
ora_00001_detector(:new.id);
exception when others then
debug_output('ora_00001_test_tab_trig: '||SQLERRM); --catch ORA-04091
--commit;
end;
/
3.3 Test Procedure
create or replace procedure test_ora_00001_detector(p_id number, p_mode varchar2 := 'Detect') as
l_id_tab t_id_tab := t_id_tab(-3, 3, 13);
begin
rollback;
------------- Case-1 No ORA-00001: Insert Not Exist: ID = -3 -------------
debug_output(chr(10)||'------------- Case-1 No ORA-00001: Insert Not Exist: ID = -3 -------------');
if p_mode = 'Detect' then
ora_00001_detector(-p_id);
else
insert into ora1_test_tab values(-p_id, 'C1');
end if;
debug_output('Case-1 No ORA-00001 Insert: ID = -3 Not Exist');
rollback;
------------- Case-2 ORA-00001: Insert Already Committed ID = 3 -------------
debug_output(chr(10)||'------------- Case-2 ORA-00001: Insert Already Committed ID = 3 -------------');
begin
if p_mode = 'Detect' then
ora_00001_detector(p_id);
else
insert into ora1_test_tab values(p_id, 'C2');
end if;
exception when others then
debug_output('Case-2 ORA-00001 Insert: '||SQLERRM);
end;
rollback;
------------- Case-3 ORA-00001: Insert Twice ID = 13 in the Same TRX -------------
debug_output(chr(10)||'-------------Case-3 ORA-00001: Insert Twice ID = 13 in the Same TRX -------------');
begin
insert into ora1_test_tab values(10+p_id, 'C3_1');
if p_mode = 'Detect' then
ora_00001_detector(10+p_id);
else
insert into ora1_test_tab values(10+p_id, 'C3_2');
end if;
exception when others then
debug_output('Case-3 ORA-00001 Insert: '||SQLERRM);
end;
rollback;
------------- Case-4 ORA-00001: Insert ID = 3 in one already started TRX, flashback query detect Committed ID = 3 -------------
debug_output(chr(10)||'------------- Case-4 ORA-00001: Insert ID = 3 in one already started TRX, flashback query detect Committed ID = 3 -------------');
begin
insert into ora1_test_tab values(10+p_id, 'C4_1');
if p_mode = 'Detect' then
ora_00001_detector(p_id);
else
insert into ora1_test_tab values(p_id, 'C4_2');
end if;
exception when others then
debug_output('Case-4 ORA-00001 Insert: '||SQLERRM);
end;
rollback;
------------- Case-5 ORA-00001 Update to ID = 3 from ID = 13, flashback query detect Committed ID = 3 -------------
debug_output(chr(10)||'------------- Case-5 ORA-00001 Update to ID = 3 from ID = 13, flashback query detect Committed ID = 3 -------------');
begin
if p_mode = 'Detect' then
ora_00001_detector(p_id);
else
insert into ora1_test_tab values(10+p_id, 'A13');
update ora1_test_tab set id = p_id where id = 10+p_id;
end if;
exception when others then
debug_output('Case-5 ORA-00001 Update: '||SQLERRM);
end;
rollback;
------------- Case-6 ORA-00001 merge when matched, update to an existing ID -------------
debug_output(chr(10)||'------------- Case-6 ORA-00001 merge when matched, update to an existing ID -------------');
begin
if p_mode = 'Detect' then
ora_00001_detector(p_id);
else
merge into ora1_test_tab t
using (select p_id id, 'A3' text from dual) s
on (t.text = s.text)
when matched then update set t.id = s.id - 1
when not matched then insert values (s.id, s.text);
end if;
exception when others then
debug_output('Case-6 ORA-00001 Update: '||SQLERRM);
end;
rollback;
------------- Case-7 ORA-00001 merge when not matched, insert one existing ID -------------
debug_output(chr(10)||'------------- Case-7 ORA-00001 merge when not matched, insert one existing ID -------------');
begin
if p_mode = 'Detect' then
ora_00001_detector(p_id);
else
merge into ora1_test_tab t
using (select p_id + 10 id, 'B3' text from dual) s
on (t.text = s.text)
when matched then update set t.id = s.id - 1
when not matched then insert values (s.id - 10, s.text);
end if;
exception when others then
debug_output('Case-7 ORA-00001 Update: '||SQLERRM);
end;
rollback;
end;
/
4. Test Run
Here our tests and output recorded in DB alert.log:
4.1 Smoking Test
begin
insert into ora1_test_tab values(13, 'A13');
ora_00001_detector(3);
end;
/
rollback;
2025-09-01T10:13:41.357832+02:00
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Detector: Check-4: ID = 3 (in Session_ID: 297), My TRX start_date: 2025-09-01 10:13:41, flashback query timestamp: 2025-09-01 10:13:41
Stmt:select max(id) from ora1_test_tab as of timestamp timestamp'2025-09-01 10:13:41' where id = 3
flashback query Found Committed ID : 3
4.2 Detect Test
exec test_ora_00001_detector(3, 'Detect');
2025-09-01T10:15:48.582676+02:00
------------- Case-1 No ORA-00001: Insert Not Exist: ID = -3 -------------
Detector: Check-1: ID = -3 (in Session_ID: 297) Not Exist
Case-1 No ORA-00001 Insert: ID = -3 Not Exist
------------- Case-2 ORA-00001: Insert Already Committed ID = 3 -------------
Detector: Check-2: ID = 3 (in Session_ID: 297), Exists by Already Committed TRX
-------------Case-3 ORA-00001: Insert Twice ID = 13 in the Same TRX -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Detector: Check-3: ID = 13 (in Session_ID: 297), My TRX start_date: 2025-09-01 10:15:48, flashback query timestamp: 2025-09-01 10:15:48
Stmt:select max(id) from ora1_test_tab as of timestamp timestamp'2025-09-01 10:15:48' where id = 13
flashback query No Found, My SID Inserted, Not Yet Committed.
------------- Case-4 ORA-00001: Insert ID = 3 in one already started TRX, flashback query detect Committed ID = 3 -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Detector: Check-4: ID = 3 (in Session_ID: 297), My TRX start_date: 2025-09-01 10:15:48, flashback query timestamp: 2025-09-01 10:15:48
Stmt:select max(id) from ora1_test_tab as of timestamp timestamp'2025-09-01 10:15:48' where id = 3
flashback query Found Committed ID : 3
------------- Case-5 ORA-00001 Update to ID = 3 from ID = 13, flashback query detect Committed ID = 3 -------------
Detector: Check-2: ID = 3 (in Session_ID: 297), Exists by Already Committed TRX
------------- Case-6 ORA-00001 merge when matched, update to an existing ID -------------
Detector: Check-2: ID = 3 (in Session_ID: 297), Exists by Already Committed TRX
------------- Case-7 ORA-00001 merge when not matched, insert one existing ID -------------
Detector: Check-2: ID = 3 (in Session_ID: 297), Exists by Already Committed TRX
4.3. Execute Test
exec test_ora_00001_detector(3, 'Execute');
2025-09-01T10:16:27.833564+02:00
------------- Case-1 No ORA-00001: Insert Not Exist: ID = -3 -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: -3
Detector: Check-1: ID = -3 (in Session_ID: 297) Not Exist
Case-1 No ORA-00001 Insert: ID = -3 Not Exist
------------- Case-2 ORA-00001: Insert Already Committed ID = 3 -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 3
Detector: Check-2: ID = 3 (in Session_ID: 297), Exists by Already Committed TRX
Case-2 ORA-00001 Insert: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
-------------Case-3 ORA-00001: Insert Twice ID = 13 in the Same TRX -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-3: ID = 13 (in Session_ID: 297), My TRX start_date: 2025-09-01 10:16:27, flashback query timestamp: 2025-09-01 10:16:27
Stmt:select max(id) from ora1_test_tab as of timestamp timestamp'2025-09-01 10:16:27' where id = 13
flashback query No Found, My SID Inserted, Not Yet Committed.
Case-3 ORA-00001 Insert: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
------------- Case-4 ORA-00001: Insert ID = 3 in one already started TRX, flashback query detect Committed ID = 3 -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 3
Detector: Check-4: ID = 3 (in Session_ID: 297), My TRX start_date: 2025-09-01 10:16:27, flashback query timestamp: 2025-09-01 10:16:27
Stmt:select max(id) from ora1_test_tab as of timestamp timestamp'2025-09-01 10:16:27' where id = 3
flashback query Found Committed ID : 3
Case-4 ORA-00001 Insert: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
------------- Case-5 ORA-00001 Update to ID = 3 from ID = 13, flashback query detect Committed ID = 3 -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 13
Detector: Check-1: ID = 13 (in Session_ID: 297) Not Exist
Trigger: BEFORE UPDATE on ora1_test_tab: :Old Val: 13 :New Val: 3
ora_00001_test_tab_trig: ORA-04091: table K.ORA1_TEST_TAB is mutating, trigger/function may not see it
Case-5 ORA-00001 Update: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
------------- Case-6 ORA-00001 merge when matched, update to an existing ID -------------
Trigger: BEFORE UPDATE on ora1_test_tab: :Old Val: 3 :New Val: 2
ora_00001_test_tab_trig: ORA-04091: table K.ORA1_TEST_TAB is mutating, trigger/function may not see it
Case-6 ORA-00001 Update: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
------------- Case-7 ORA-00001 merge when not matched, insert one existing ID -------------
Trigger: BEFORE INSERT on ora1_test_tab: :Old Val: :New Val: 3
ora_00001_test_tab_trig: ORA-04091: table K.ORA1_TEST_TAB is mutating, trigger/function may not see it
Case-7 ORA-00001 Update: ORA-00001: unique constraint (K.ORA1_TEST_TAB#U#IDX) violated
