| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Abort in Oracle
Pasi,
you loop by your exception. You hit Ctrl-C or Ctrl-Break which raises a ORA-1013 in Unix. The problem is a bit different. See the following example:
SQL> begin
2 raise no_data_found;
3 exception
4 when others then 5 loop 6 raise no_data_found; -- null; 7 end loop;
ORA-01403: no data found ORA-06512: at line 6 ORA-01403: no data found
where I just substituted the null by the raise. This shows that exceptions are not deactivated in exception areas. Your problem is that no exception is generated when you press the break key. This is not the case in Unix.
Of course you can catch GENERATED exceptions in exception blocks:
SQL>
SQL> set serveroutput on size 1000000
SQL> begin
2 raise no_data_found;
3 exception
4 when others then
5 begin
6 loop
7 raise no_data_found; -- null;
8 end loop;
9 exception
10 when others then
11 dbms_output.put_line ('caught something');
12 return;
13 end;
PL/SQL procedure successfully completed.
Martin
sinegoubko_at_my-deja.com wrote:
>
> 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
 |
 |