So I thought about wrapping it into pl/sql the way so that the merge would be re-tried after some time and then, if it failed again, the exception would be thrown to upper layer of application (here: shell). I came up with the following code below. I would appreciate your opinion, especcially if there should be something else incorporated to improve the code or maybe some other approach would be better, or maybe I should pay attention to other aspects.
ORA-08006: specified row no longer exists Cause: the row has been deleted by another user since the operation began Action: re-try the operation
create or replace package tst as procedure main; end tst; / create or replace package body tst as do_merge_cnt pls_integer := 0; ora08006 exception; pragma exception_init(ora08006, -08006); /* here the exception is simulated */ procedure throw8006 is begin raise ora08006; end; /* this procedure will include original merge statement */ procedure do_merge is begin do_merge_cnt := do_merge_cnt + 1; -- increase "failure" counter case when do_merge_cnt < 10 then throw8006; else null; end case; exception when ora08006 then case when do_merge_cnt < 2 then -- if counter < 2 then re-try after a nap dbms_lock.sleep(5); do_merge; else raise; end case; when others then raise; end; procedure main is begin do_merge; end; end tst; /
This way you don't need aditional logic to to end the recursion.
procedure do_merge is begin do_merge_cnt := do_merge_cnt + 1; -- increase "failure" counter if do_merge_cnt < 10 then throw8006; end if; end do_merge; procedure main is begin do_merge; exception when ora08006 then dbms_lock.sleep(5); do_merge; end main;
This is still APSA (a pretty stupid approach).
... for i in 1..10 loop begin do_merge_statement; exit; /* leave the loop when the merge was successfull */ exception when ora08006 then null; /* repeat the merge */ end; if i <10 then /* no need to wait 5 secs after the last merge */ dbms_lock.sleep(5); end if; end loop; ...
Sven W. wrote:No.
Sorry but this doesn't make any sense. You would throw an error even if there was no error,
just to be able to wait 5 seconds and run the merge again?This is what the OP wanted.
THis second merge would probably merge like.... 0 rows?Maybe, maybe not, depending on the cause of the exception.
TPD Opitz-Consulting com wrote:I've now seen that you copied this wrong logic from the op. However it is still wrong.
* If the first call to <tt>do_merge</tt> succeeds main exits normally.
1<10 => true => throw exception => sleep => do_merge again => raise exception again => error out with an exception.
if do_merge_cnt < 10 then throw8006; end if;
Sven W. wrote:The shown implementation of <tt>do_merge</tt> is (hopefully) only explaining the problem, not the real production code. So yes: it does not make sense (other than forcing an exception).
I've now seen that you copied this wrong logic from the op. However it is still wrong.