Alternative
Gary, April 28, 2017 - 4:27 am UTC
Another option is use a deferred constraint to force the COMMIT to fail.
create or replace procedure test_dc_one is
begin
commit;
end;
/
create table defcon_one (id number(1));
alter table defcon_one add constraint defcon_one_ck check (id != 1) initially deferred;
insert into defcon_one(id) values (1);
exec test_dc_one
When the procedure tries to do a commit, it will need to check the deferred constraint. Because it is violated, the commit fails and raises an error.
This won't work if the procedure does a rollback of your pending insert. If the procedure handles the error silently, that's a separate problem :)
April 28, 2017 - 10:45 pm UTC
nice touch
many commits in AUTONOMOUS_TRANSACTION
Tymur Gubayev, April 28, 2017 - 6:57 am UTC
I was able to generate the tracefile, but there are lots of lines like following.
XCTEND rlbk=0, rd_only=0, tim=14473904665992
This is due to extensive logging done with
pragma AUTONOMOUS_TRANSACTION;
and also dbms_lock.
I tried using
TKPROF: Release 12.1.0.1.0 - Development on Fr Apr 28 08:35:09 2017
to get the trace in more readable form, but it just crashes in oraclient12.dll 12.1.0.1
timestamp 51cd5302 with exception code c0000005, offset 00000000004681ba.
April 28, 2017 - 10:49 pm UTC
Well, definitely tkprof shouldn't crash :-) The trace files are upward compatible, so you should be able to run tkprof in an 12.1.0.2 home or similar.
(should be a comment in a thread)
Tymur Gubayev, May 02, 2017 - 8:08 am UTC
(I'm sorry for a new "Review" -- I can't find a way to comment on a "Followup").
"Well, definitely tkprof shouldn't crash :-) The trace files are upward compatible, so you should be able to run tkprof in an 12.1.0.2 home or similar."
I've tried tkprof.exe from DB Versions 11.2, 12.1 and 12.2. The trace file is from 12.1. Every version of tkprof just crashed.
Either way, I'll use the "deferred constraint" suggested by Gary.
And, in case anyone is interested, the `commit` causing troubles was inside DBMS_LOCK.ALLOCATE_UNIQUE.