The problem is that handling an error ORA-00028 is kinda tricky. Please, look at the code below.

If you run proc1 in session 1 and while it's still running you kill session 1 with ALTER SYSTEM KILL SESSION then you get ORA-00028 error message and no row in llog table. If you run proc1 and let it finish (1 min) then error handling works as expected and you get no error message and 1 row in llog table. But the funny thing is if after that you run proc1 again and kill that session you get no error-message (ORA-00028 handled) and one more row in llog table.

So for ORA-00028 to be handled in exception clause you need to catch some other error first. It seems to be a bug. Has anyone faced this problem?

/* creating simple table with logs */
create table llog(time timestamp, error varchar2(4000));
/
/* creating package */
create or replace package my_pack
is procedure proc1;
end;
/
/* creating package body*/
create or replace package body my_pack
is
e_session_killed EXCEPTION;
PRAGMA EXCEPTION_INIT(e_session_killed, -00028);
procedure error_log (time llog.time%type, error llog.error%type) is pragma autonomous_transaction;
begin insert into llog values (time, error); commit;
end;
procedure proc1 is
begin dbms_lock.sleep(60); raise too_many_rows; exception when e_session_killed then error_log(systimestamp, sqlerrm); when others then error_log(systimestamp, sqlerrm);
end;
end;

2

1 Answer

You can't catch a kill session. It interrupts the current operation (as mush as it can - there might be some low level operations that cause issues), rolling back the open transaction(s). Once the rollback is complete the client is told that it is disconnected (assuming the client is still there) and the process goes away.

There's a couple of variants of kill session that affect the order of those but you're not going to be able to insert anything into any table from a killed session.

The only exception might be through a database link or similar, where you actually have two separate sessions/processes going on at the same time.

3

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.