Re: Abort in Oracle

Martin,

It is not the answer I wanted to here. Which situation are you talking about? There are 4 different situations to deal with.

How can I terminate a session when it is in the exception handling block? Or break executing dynamic PL/SQL?

Denis

In article <38F5B56D.737EF305_at_0800-einwahl.de>,   Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de> wrote:
> The break is an exception ORA-01013 itself. So you catch and discard
 your own
> break.
>
> Martin
>
> Pasi Matilainen wrote:
> >
> > 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--- Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 14 2000 - 00:00:00 CDT