Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

"Handling" Killed Sessions

_Dylan_Apr 30 2009 — edited Apr 30 2009
I'm curious as to if anybody has some insight to killed sessions. I have a procedure that looks like this:
procedure x
  lv_result number;
  lv_msg varchar2(4000); 
  for x in cursor
  loop
    lv_result := NULL;
    lv_msg := NULL;
    begin
      execute immediate x.sql into lv_result;
    exception
      when others then
         lv_msg := dbms_utility.format_error_stack;
         lv_result = NULL;
    end;
    log_result;  --autonomous transaction that inserts to table and commits;
  end loop;
  print_summary;
end x;
What struck me as odd was that even though I'd kill the session while it was in the middle of processing rec 10 out of 30, it would still log the remaining 20 records with a "ORA-00028: your session has been killed" message. But it didn't print the results. So modified it a bit...
procedure x
  --
  SESSION_KILLED exception ;
  pragma exception_init(SESSION_KILLED, -28);
  --
  lv_result number;
  lv_msg varchar2(4000);
  lv_session_killed boolean := FALSE;
  for x in cursor
  loop
    lv_result := NULL;
    lv_msg := NULL;
    begin
      execute immediate x.sql into lv_result;
    exception
      when session_killed then
         lv_session_killed := TRUE;
         lv_msg := dbms_utility.format_error_stack;
         lv_result = NULL;
      when others then
         lv_msg := dbms_utility.format_error_stack;
         lv_result = NULL;
    end;
    log_result; --autonomous transaction that inserts to table and commits;
    exit when lv_session_killed;
  end loop;
  print_summary;
end x;
...which effectively stopped the loop at the first record in the cursor at which the session was killed, and still didn't print the summary. But it's obvious that processing still "continues" in the background even though the session was killed. Does killing the session just terminate communications between the client and the DB, letting the procedure to continue on it's own? Is there any way to get DBMS_OUTPUT calls to work before the procedure actually dies?
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 28 2009
Added on Apr 30 2009
3 comments
993 views