Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Exception halting a FORALL

Re: Exception halting a FORALL

From: Mladen Gogala <mgogala_at_earthlink.net>
Date: Fri, 23 Nov 2001 04:15:27 GMT
Message-ID: <pan.2001.11.22.23.15.18.96.2387@earthlink.net>


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 Gogala
Received on Thu Nov 22 2001 - 22:15:27 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US