Skip to content
accelerando

Haufenweise: Bulk Insert, Update, Delete

Bulk Methoden in PL/SQL sind praktisch und schnell. Auch recht einfach anzuwenden. Allerdings ist mir in Oracle 9.2.0.6 ein sehr komisches Verhalten aufgefallen:

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  objekt_ids id_table;
BEGIN
    SELECT id
    BULK COLLECT INTO objekt_ids
    FROM   irgendwas
    WHERE  irgendwasanderes = 'wasweißich';
 
    FORALL idx IN objekt_ids.first .. objekt_ids.last
      DELETE FROM table1 WHERE objekt_id = objekt_ids(idx);
 
    FORALL idx IN objekt_ids.first .. objekt_ids.last
      DELETE FROM table2 WHERE objekt_id = objekt_ids(idx);
 
    -- ...
END;

Das Beispiel ist jetzt natürlich erstmal sinnlos, ist aber auch egal. Der Haken an der Sache: Das BULK COLLECT INTO Statement wirft keine NO_DATA_FOUND Exception, wenn die Ergebnismenge leer ist (im Gegensatz zu einem normalen SELECT INTO). Nichts desto trotz ist object_ids danach eine korrekt initialisierte Zusammenstellung.

War die Ergebnismenge leer, gibt es trotzdem eine Exception. Und zwar eine INVALID_NUMBER. Aus irgendeinem Grund laufen die forall Schleifen, auch wenn die virtuelle Tabelle leer ist.

Deshalb gilt für BULK Methoden: Abfragen, ob die Indextabellen leer sind!

DECLARE
  TYPE id_table IS TABLE OF NUMBER;
  objekt_ids id_table;
BEGIN
  IF(objekt_ids.COUNT > 0)THEN
    SELECT id
    BULK COLLECT INTO objekt_ids
    FROM   irgendwas
    WHERE  irgendwasanderes = 'wasweißich';
 
    FORALL idx IN objekt_ids.first .. objekt_ids.last
      DELETE FROM table1 WHERE objekt_id = objekt_ids(idx);
 
    FORALL idx IN objekt_ids.first .. objekt_ids.last
      DELETE FROM table2 WHERE objekt_id = objekt_ids(idx);
 
    -- ...
  END IF;
END;

2 Comments

  1. Gast wrote:

    Bin durch zufall auf diese Seite gestoßen!
    Vielen Dank für den Hinweis, dass beim Bulk Collect keine NO_DATA_FOUND exception geworfen wird!

    Ich habe mich jetzt ca. 1h mit diesem dummen Problem aufgehalten… und habe endlich hier die Lösung gefunden :-)

    Schönen Tag noch!

    Posted on 14-May-07 at 8:03 pm | Permalink
  2. Michael wrote:

    Gerne! Vielen Dank für die nette Rückmeldung!

    Posted on 14-May-07 at 9:22 pm | Permalink

Post a Comment

Your email is never published nor shared. Required fields are marked *
*
*

*