... This came up in a 'when others' handler in the main event loop of a daemon job. The handler logs the error and continues - a good use of 'when others' if ever I saw one. ....
false, entirely false - an extremely, excessively BAD PRACTICEdo not, repeat: DO NOT use when others, that is a worst practice, a really "bad idea"(tm). The approach to never take.
do you understand the huge flaw in your logic, in your code? what you have done to the transactional capability of the database (You have lobotomized it, killed it, destroyed it)
I added to your example:
create sequence s;
create table t ( id number, msg varchar2(80) );
and changed all of your "dbms_output.put_line(" strings to "insert into t values ( s.nextval, "
Now, let's run your excessively fragile, faulty, unstable code:
ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set echo off
run @x 4
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select * from t order by id;
ID MSG
---------- --------------------------------------------------------------------------------
1 caller call number 1
2 gets_recompiled version 1, call number 1
3 caller call number 2
4 ORA-04061: existing state of package body "OPS$TKYTE.GETS_RECOMPILED" has been i
5 caller call number 1
6 gets_recompiled version 4, call number 1
6 rows selected.
Note, id=1,2 - success, transaction ran from start to finish.
Note, id=3,4 - ugh, we did half of our transaction - and it is still there! but the other half (UNBEKNOWN TO US) did not ever execute, we have no clue, no way to find out - data integrity = 0%
Note, id=5,6 - success, however, it falls on the heels of a half done transaction - we probably did the work of "caller" twice and didn't really realize it.
Now, if I FIX THE BUG IN YOUR CODE:
create or replace package body caller
as
procedure call_no_catch
is
begin
call_count := call_count + 1;
insert into t values ( s.nextval, 'caller call number ' || call_count);
gets_recompiled.do_stuff;
end;
procedure call_and_catch(do_reset boolean := false)
is
begin
call_count := call_count + 1;
insert into t values ( s.nextval, 'caller call number ' || call_count);
gets_recompiled.do_stuff;
<b> /*
exception
when others then
insert into t values (s.nextval, substr(DBMS_UTILITY.FORMAT_ERROR_STACK,1,80) );
if do_reset then
dbms_session.reset_package;
end if;
*/
</b>end;
end;
/
and re-execute:
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set echo off
run @x 4
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
BEGIN caller.call_and_catch(true); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OPS$TKYTE.GETS_RECOMPILED" has been invalidated
ORA-04065: not executed, altered or dropped package body "OPS$TKYTE.GETS_RECOMPILED"
ORA-06508: PL/SQL: could not find program unit being called: "OPS$TKYTE.GETS_RECOMPILED"
ORA-06512: at "OPS$TKYTE.CALLER", line 15
ORA-06512: at line 1
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select * from t order by id;
ID MSG
---------- --------------------------------------------------------------------------------
1 caller call number 1
2 gets_recompiled version 1, call number 1
4 caller call number 1
5 gets_recompiled version 4, call number 1
I get exactly, precisely, what I should get - what should happen.
id=1,2 - all is well, perfection
id=4,5 = all is well, perfection
failed transaction - the INVOKER, the client, the caller KNOWS "transaction failed". Not only do they know this, they also know "transaction failed, but database has be restored (rolled back) to a known stable state - exactly the state I put it in right before making the failed call"
Your when others - broke that all
Your when others - hate it, it is wrong, it is bad, it is inexcusable, it is extremely poor programming/error handling, it must be erased.
This is why I would like "when others" removed from the language specification altogether...
If you want that error logged on the server, FINE, but you MUST let the invoker know "I've died, you've been rolled back, all is well - we can recover - you just need to RETRY"
<b>
ops$tkyte%ORA10GR2> create table log( msg clob );
Table created.
ops$tkyte%ORA10GR2> create or replace procedure log_error
2 as
3 pragma autonomous_transaction;
4 begin
5 insert into log values ( dbms_utility.FORMAT_ERROR_BACKTRACE );
6 COMMIT;
7 end;
8 /
Procedure created.
</b>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace package body caller
2 as
3 procedure call_no_catch
4 is
5 begin
6 call_count := call_count + 1;
7 insert into t values ( s.nextval, 'caller call number ' || call_count);
8 gets_recompiled.do_stuff;
9 end;
10 procedure call_and_catch(do_reset boolean := false)
11 is
12 begin
13 call_count := call_count + 1;
14 insert into t values ( s.nextval, 'caller call number ' || call_count);
15 gets_recompiled.do_stuff;
16<b>
17 exception
18 when others then
19 log_error;
20 raise;
21 end;</b>
22 end;
23 /
Package body created.
and now:
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set echo off
run @x 4
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
BEGIN caller.call_and_catch(true); END;
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "OPS$TKYTE.GETS_RECOMPILED" has been invalidated
ORA-04065: not executed, altered or dropped package body "OPS$TKYTE.GETS_RECOMPILED"
ORA-06508: PL/SQL: could not find program unit being called: "OPS$TKYTE.GETS_RECOMPILED"
ORA-06512: at "OPS$TKYTE.CALLER", line 20
ORA-06512: at line 1
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec caller.call_and_catch(true);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select * from t order by id;
ID MSG
---------- --------------------------------------------------------------------------------
1 caller call number 1
2 gets_recompiled version 1, call number 1
4 caller call number 1
5 gets_recompiled version 4, call number 1
we have the error logged, you can capture whatever you want - but (and this is key) the CLIENT MUST BE INFORMED
and all is reset without invoking that extremely "brain killing dbms_session.reset_package"
This is the correct and proper way to deal with this
when others then null - NO