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?