Abort in Oracle

From: Pasi Matilainen <pasi_at_verso.st.jyu.fi>
Date: 2000/04/13
Message-ID: <8d3sl1$sd4$1@mordred.cc.jyu.fi>#1/1

Hi,

I'd like to find a correct and reliable way to abort current operation in Oracle and terminate a session. (Oracle8) I've tested BREAK (OCIBreak()/Ctrl-C/...) and KILL (Alter system kill session...)
but they don't seem to provide a reliable termination.

The action performed by BREAK and KILL vary from situation to situation. Here what I had:

1.

---

begin                 
  loop                 
    null;                  --BREAK
  end loop;              
end;                   


----------------------------

Both BREAK and KILL work.

2.

---

begin
  raise no_data_found;

exception              
when others then       
  loop                 
    null;                  --BREAK
  end loop;              
end;                   


----------------------------

While in exception handling block BREAK has no effect. KILL in this example can't terminate a session. In v$session it will be shown marked as "KILLED".

3.

---

begin   execute immediate 'begin loop null; end loop; end;'; -- BREAK end;

---

BREAK can't abort executing dynamic PL/SQL Only KILL can terminate such a session.

4.

---

begin   execute immediate 'begin loop null; end loop; end;'; -- BREAK

exception when others then                              
 loop null; end loop;                                   
end;                                                    


----------------------------

BREAK wouldn't work
KILL wouldn't terminate such a session. Once execution is in the exception handling block KILL has not effect. Session is being marked as "KILLED" but keeps on working.

Is there a way to get rid of these "KILLED" sessions.

Are there any rules of using BREAK and KILL?

I would appreciate any information on the subject, sample codes in PL/SQL and OCI.
Maybe you can point me to some site on the web where I can find such information.

Thanks in advance,
Denis

---NOTE: DO NOT REPLY DIRECTLY TO ME, CUZ I'M POSTING THIS FOR A FRIEND---

-- 


------

Pasi Matilainen             Student of Information Systems Science
tel. +xxx-yy-zzzzzzz        Faculty of Information Technology
http://www.jyu.fi/~pasi/    University of Jyväskylä, Finland
-- Sinustakin voi tulla Akateemikko @ http://akatemia.sby.abo.fi/ --

Received on Thu Apr 13 2000 - 00:00:00 CDT