Re: Howto do Whenever error in PL/SQL

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Wed, 23 May 2001 16:48:07 +0200
Message-ID: <9egij7$4lk$1@s1.read.news.oleane.net>

From PL/SQL User's Guide and Reference:

<quote>
In the example below, you calculate and store a price-to-earnings ratio for a company with ticker symbol XYZ. If the company has zero earnings, the predefined exception ZERO_DIVIDE is raised. This stops normal execution of the block and transfers control to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically.

DECLARE
   pe_ratio NUMBER(3,1);
BEGIN
   SELECT price / earnings INTO pe_ratio FROM stocks

      WHERE symbol = 'XYZ'; -- might cause division-by-zero error    INSERT INTO stats (symbol, ratio) VALUES ('XYZ', pe_ratio);    COMMIT;
EXCEPTION -- exception handlers begin

   WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error

      INSERT INTO stats (symbol, ratio) VALUES ('XYZ', NULL);
      COMMIT;

   ...
   WHEN OTHERS THEN -- handles all other errors

      ROLLBACK;
END; -- exception handlers and block end here </quote>

--
Have a nice day
Michel


"Amos" <amos_987_at_hotmail.com> a écrit dans le message news: 9eggs8$ql6$1_at_news.inter.net.il...


> Hi,

>

> I'm doing a lot of execute immediate a loop inside a procedure.

> My problem is that if one of the exec fails, the SP terminates.

> What I want to do is something like whenever error do ... insert the

> statement that failed into an exception table and continue to exec the next

> command.

>

> Is there a way to do it in PL/SQL ??

>

>

> Cheers,

> Amos

>

>

>

Received on Wed May 23 2001 - 09:48:07 CDT