Dezember 13, 2022

TOO_MANY_ROWS Exception

Wird in einem PL/SQL Block das Ergebnis einer Select Abfrage mittels INTO in eine Variable geschrieben, so muss das Statement EXAKT 1 Zeile zurück liefern. Wird keine Zeile zurückgegeben, so wird eine NO_DATA_FOUND Exception ausgelöst. Ist die Where-Klausel nicht exakt genug und es werden mehrere Zeilen zurück geliefert, so wird eine TOO_MANY_ROWS Exception ausgelöst. Der Unterschied zwischen diesen beiden Fehlern ist jedoch ein potentiell sehr relevanter:

Tritt der Fall einer NO_DATA_FOUND Exception auf, so bleibt der Wert der Variable unverändert – sprich wenn die Variable NULL ist, denn bleibt der Wert NULL, ist der Wert beispielsweise 2, so bleibt er 2 (siehe auch die Ergebnisse des Beispielskriptes).

Tritt der Fall einer TOO_MANY_ROWS Exception auf, so ändert sich der Wert der Variable – und zwar auf eventuell unvorhersehbare Weise. Der Wert der Variable wird auf den ersten gefundenen Wert der Abfrage gesetzt. Mit Order By kann das kontrolliert werden, aber ohne ist es nicht vorhersehbar, welcher Wert eingetragen wird.

Das wirklich wichtige an diesem Verhalten ist, dass bei einem größeren Block mit nachfolgenden Schritten berücksichtigt werden muss, dass bei einem TOO_MANY_ROWS Fehler die Variable einen Wert hat trotz der Exception.

Das folgende Skript verdeutlich diese Möglichkeiten:

declare
  v_value number := 2;
begin
  -- create no data found exception - v_value is NULL
  dbms_output.put_line('v_value am Ende = ' || v_value);
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where eindeutig = 11;
  exception
    when no_data_found then
      dbms_output.put_line('v_value bei no_data_found = ' || v_value);
  end;
  -- create too many rows exception - v_value is NOT NULL
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where mehrdeutig = 1;
  exception
    when too_many_rows then
      dbms_output.put_line('v_value bei too_many_rows ohne order = ' || v_value);
  end;
  -- create too many rows exception - v_value is NOT NULL
  begin
    with numbers as
     (select level eindeutig from dual connect by level <= 10),
    base as
     (select eindeutig, mod(eindeutig, 3) mehrdeutig from numbers)
    select eindeutig into v_value from base where mehrdeutig = 1 order by 1 desc;
  exception
    when too_many_rows then
      dbms_output.put_line('v_value bei too_many_rows mit order = ' || v_value);
  end;
end;