Re: Exception halting a FORALL

From: Marc Blum <marc_at_marcblum.de>
Date: Sat, 24 Nov 2001 20:04:57 GMT
Message-ID: <3bfffc24.5874166@news.online.de>

You may evaluate SQL%ROWCOUNT:

• SCRIPT ============================= set serveroutput on

DROP TABLE mytable;
DROP PROCEDURE myproc;

CREATE TABLE mytable
(mycolumn NUMBER PRIMARY KEY);

INSERT INTO mytable
VALUES
(2);

COMMIT; 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));

   DBMS_OUTPUT.PUT_LINE('Normal Rowcount: '||SQL%ROWCOUNT);

EXCEPTION    WHEN DUP_VAL_ON_INDEX THEN NULL;
   DBMS_OUTPUT.PUT_LINE('abnormal Rowcount: '||SQL%ROWCOUNT);

END;
/

EXECUTE MyProc;

SELECT * FROM MyTable;
====================== END OF SCRIPT ==============================

• OUTPUT ==============================

DROP TABLE mytable

           *
ERROR at line 1:
ORA-00942: table or view does not exist

DROP PROCEDURE myproc
*
ERROR at line 1:
ORA-04043: object MYPROC does not exist

Table created.

1 row created.

Commit complete.

Procedure created.

aNormaler Rowcount: 1

PL/SQL procedure successfully completed.

  MYCOLUMN

---

	 2
	 1




====================== END OF OUTPUT ==============================

Now compare SQL%ROWCOUNT and your "number-of-rows-to-be-processed".

You may feed your bulk insert again with the collection starting at SQL%ROWCOUNT+2. Think about a recursive call.

Greetings
Marc

On Fri, 23 Nov 2001 01:31:44 GMT,
SPAMBLOCK.Maxwell_Smart_at_ThePentagon.com.SPAMBLOCK (Brian Tkatch) 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

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sat Nov 24 2001 - 14:04:57 CST