Re: Aborting PL/SQL script in Exception handler

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/29
Message-ID: <338ee28a.22900779@newshost>#1/1

On 29 May 97 18:58:48 GMT, "Mike Hill_at_PW" <mike_hill_at_ibm.net> wrote:

>I have a PL/SQL script with a number of Begin...End blocks, each with its
>own
>exception handling.
>
>If an exception is encountered, I'm trying to figure out if there is a way
>to abort the entire script so that processing does not continue onto the
>next block.
>

You can use:
SQL> whenever sqlerror exit

That way, if a block raises an exception, it will exit sqlplus all together. Other then that, there is no way I am aware of to abort a script file.

You could also do something like:

SQL> variable n number;
SQL> exec :n := 0;
SQL> begin
   >     ......
   > exception
   >   when others then :n := 1;
   > end;
   > /

 SQL> begin

   >    if ( :n = 0 ) then
   >       ....
   >    end if;
   > exception
   >    when others then :n := 1;
   > end;
   > /

And so on. Each block would look at a sqlplus bind variable to see if it should execute or not...

[snip]

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 29 1997 - 00:00:00 CDT