Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Re: Exception halting a FORALL
In article <3bfda63a.298947875_at_news.alt.net>, "Brian Tkatch"
<SPAMBLOCK.Maxwell_Smart_at_thepentagon.com.spamblock> wrote:
> From the docs.
>
> "When any execution of the SQL statement raises an exception, the FORALL
> statement halts."
>
> Is there anyway around that, other than creating a normal loop.
>
> An example:
>
> CREATE TABLE MyTable(MyColumn NUMBER PRIMARY KEY); INSERT INTO MyTable
> VALUES (2);
>
> CREATE OR REPLACE PROCEDURE MyProc IS
>
> TYPE MyType IS TABLE OF NUMBER;
> MyVar MyType := MyType(1,2,3);
>
> BEGIN
>
> FORALL Moo IN 1..3 INSERT INTO MyTable VALUES(MyVar(Moo));
>
> EXCEPTION
>
> WHEN DUP_VAL_ON_INDEX THEN NULL;
>
> END;
> /
>
> EXECUTE MyProc;
>
> SELECT * FROM MyTable;
>
> Because of the PRIMARY KEY violation, 2 is not written, but the FORALL
> then exists, and 3 never gets a chance.
>
> Brian
Unfortunately, an exception always terminates an enclosing block. As you can not nest blocks in "FORALL", try with an ordinary "FOR" loop which do allow that. I'd like to see the solution too, if anybody else has it?
-- Mladen GogalaReceived on Thu Nov 22 2001 - 22:15:27 CST
![]() |
![]() |