Oracle: "SELECT" vs. "INSERT INTO table SELECT" liefern unterschiedliche Tupel.

Ora et labora

Benutzer
Beiträge
6
Hallo,

ich bin neu hier und ich wende mich an Euch, weil ich ein Problem (siehe unten) habe und alleine nicht weiterkomme.


Mein Problem:

Oracle: "SELECT" vs. "INSERT INTO table SELECT" liefern unterschiedliche Tupel.


Istzustand:

Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Oracle SQL Developer IDE 17.4.0.355.2349
Kenntnisse in Oracle SQL und Oracle PL/SQL sind (möglicherweise zu wenig) vorhanden.

Ich habe eine PL/SQL-Funktion "getZeit" (mit ca. 700 Zeilen Code) geschrieben, die Fertigungszeiten ermittelt und aufsummiert.

Diese Funktion ruft sich unter Umständen rekursiv auf.

Diese Funktion enthält eine Menge von unserer Firmenlogik, deshalb möchte ich sie hier nicht zeigen.

Diese Funktion enthält den folgenden EXCEPTION-Block.

Code:
EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line('NO_DATA_FOUND = '    || '; Fehler = ' || sqlerrm);
            RETURN NULL;
        WHEN PROGRAM_ERROR THEN
            dbms_output.put_line('PROGRAM_ERROR = '    || '; Fehler = ' || sqlerrm);
            RETURN NULL;
        WHEN TOO_MANY_ROWS THEN
            dbms_output.put_line('TOO_MANY_ROWS = '    || '; Fehler = ' || sqlerrm);
            RETURN NULL;
        WHEN VALUE_ERROR THEN
            dbms_output.put_line('VALUE_ERROR = '    || '; Fehler = ' || sqlerrm);
            RETURN NULL;
        WHEN ZERO_DIVIDE THEN
            dbms_output.put_line('ZERO_DIVIDE = '    || '; Fehler = ' || sqlerrm);
            RETURN NULL;
        WHEN OTHERS THEN
            dbms_output.put_line('OTHERS = '        || '; Fehler = ' || sqlerrm);
            RETURN NULL;

Wenn ich diese PL/SQL-Funktion "getZeit" in einem SELECT-Statement in der WHERE-Klausel bzw. in der AND-Bedingung (mit oder ohne NVL) nutze, ist alles in Ordnung, d.h. es wird immer die korrekte Anzahl von Tupeln geliefert. Das SELECT-Statement gebe ich hier mal "neutralisiert" und auf das Wesentliche reduziert wieder.

Code:
SELECT
    t1.TeileID,
    1,
    2017
FROM
    kontext.tabelle1 t1,
    kontext.tabelle2 t2
WHERE
    t1.TeileID    = t2.TeileID (+)
AND    ...
AND    ...
AND    NVL (kontext.getZeit(t1.TeileID), 0) = 0        -- 0 Tupel: Mit NVL = Richtig, es sollten 0 Tupel sein.
--AND    kontext.getZeit(t1.TeileID) = 0                -- 0 Tupel: Ohne NVL = Richtig, es sollten 0 Tupel sein.
GROUP BY
    t1.TeileID;

Nutze ich obiges SELECT-Statement (mit der PL/SQL-Funktion "getZeit" in der AND-Bedingung) jedoch in Verbindung mit der "INSERT INTO table ... SELECT ..."-Syntax zum Einfügen mehrerer Datensätze in eine Tabelle, dann werden mehr Datensätze in die Tabelle eingefügt, als es sein sollten. ;-(

Code:
INSERT INTO kontext.tabelle2
(
    TeileID,
    Stundensatz,
    Jahr
)
SELECT
    t1.TeileID,
    1,
    2017
FROM
    kontext.tabelle1 t1,
    kontext.tabelle2 t2
WHERE
    t1.TeileID    = t2.TeileID (+)
AND    ...
AND    ...
AND    NVL (kontext.getZeit(t1.TeileID), 0) = 0        -- 40 Tupel: Mit NVL == Falsch, es werden 0 Tupel sein!
--AND    kontext.getZeit(t1.TeileID) = 0                --  0 Tupel: Ohne NVL = Richtig, es sollten 0 Tupel sein!
GROUP BY
    t1.TeileID;

Ich habe bereits die PL/SQL-Funktion "getZeit" ausführlich und mehrfach zu Fuß überprüft, debuggt und finde darin keinen Fehler.


Der Fehler:

Was mir aufgefallen ist, dass mit der AND-Bedingung "AND NVL (kontext.getZeit(t1.TeileID), 0) = 0" z.B. 40 Tupel fälschlicherweise in die Tabelle eingefügt werden, hingegen mit der AND-Bedingung "AND kontext.getZeit(t1.TeileID) = 0" richtigerweise keine Tupel in die Tabelle eingefügt werden.


Ich bin jetzt ratlos, wo mein Fehler liegt und hoffe auf Eure hilfreichen Tips, damit ich meinen Fehler korrigieren kann.
 
Werbung:
vorweg: ich kenne mich mit ORA nicht wirklich aus. Die NVL() - Funktion macht aber (nach kurzer Suche) wohl das, was auch coalesce() macht: sie liefert einen Ersatzwert (zweiter Parameter), wenn der erste Parameter NULL ist.

Code:
AND NVL (kontext.getZeit(t1.TeileID), 0) = 0 -- 40 Tupel: Mit NVL == Falsch, es werden 0 Tupel sein!
--AND kontext.getZeit(t1.TeileID) = 0 -- 0 Tupel: Ohne NVL = Richtig, es sollten 0 Tupel sein!

Kann es sein, daß Dir der Unterschied zwischen NULL und 0 nicht bekannt ist?
 
vorweg: ich kenne mich mit ORA nicht wirklich aus. Die NVL() - Funktion macht aber (nach kurzer Suche) wohl das, was auch coalesce() macht: sie liefert einen Ersatzwert (zweiter Parameter), wenn der erste Parameter NULL ist.

Code:
AND NVL (kontext.getZeit(t1.TeileID), 0) = 0 -- 40 Tupel: Mit NVL == Falsch, es werden 0 Tupel sein!
--AND kontext.getZeit(t1.TeileID) = 0 -- 0 Tupel: Ohne NVL = Richtig, es sollten 0 Tupel sein!

Kann es sein, daß Dir der Unterschied zwischen NULL und 0 nicht bekannt ist?

Doch, schon. NULL ist ein nicht definierter Zustand, 0 ist Null als Wert (und liegt als Integer zwischen -1 und +1).

Die von mir erstellte Datenbankfunktion GETZEIT liefert immer einen Wert zurück, ausser wenn sie in einen Fehler läuft. Der EXCEPTION-Block in der Funktion sollte solche Fehler abfangen und ausgeben. Da aber keine Fehler auftreten, sollte soweit immer ein Wert != NULL zurückgegeben werden.

Meine Frage ist: Warum funktioniert das Select alleine für sich mit oder ohne NVL, während es in Verbindung mit INSERT INTO TABLE nicht sauber funktioniert?
 
Ich bin auch kein ORA Kenner und das scheint mir tiefgereifend aber aus meinem Verständnis heraus kann das eigentlich nicht passieren mit dem oben gezeigten Bestandteilen. Was du allerdings tust ist in kontext.tabelle2 Datensätze einfügen die aus einem Select mit einem Join auf kontext.tabelle2 kommen. Die Funktion bezieht sich entweder auch auf Einträge in kontext.tabelle2 oder beeinflusst das Verhalten des Selects eventuell dahingehend das die Werte nicht alle auf einmal eingefügt werden und sich somit gegenseitig beeinflussen können.

Teste das mal mit einer Ersatztabelle in die du einfügst und schaue, ob sich dann am Ergebnis etwas ändert. Das dürfte in keinem Fall passieren.
 
...Was du allerdings tust ist in kontext.tabelle2 Datensätze einfügen die aus einem Select mit einem Join auf kontext.tabelle2 kommen. Die Funktion bezieht sich entweder auch auf Einträge in kontext.tabelle2 oder beeinflusst das Verhalten des Selects eventuell dahingehend das die Werte nicht alle auf einmal eingefügt werden und sich somit gegenseitig beeinflussen können....

Ja das ist mir bewusst. Ich bin auch davon ausgegangen, dass das SELECT zuerst ausgeführt wird, bevor das INSERT die Daten in die Tabelle schreibt. Leider schweigt sich die Oracle-Doku (online/offline) dazu aus. Den einzigen Hinweis dazu fand ich hier:
https://www.google.de/url?q=http://...PDGEQFggdMAI&usg=AOvVaw12i7tsHpfUoYYd7U5VYKfh
Stefan Brass: Datenbanken I, Universität Halle, 2006, Folie 10
Die Unteranfrage wird vollständig ausgewertet bevor die Ergebnistupel eingefügt werden.
Daher gibt es auch dann ein definiertes Ergebnis (und niemals Endlosschleifen), wenn die Tabelle, in die eingefügt wird, in der Unteranfrage selbst verwendet wird.

...Teste das mal mit einer Ersatztabelle in die du einfügst und schaue, ob sich dann am Ergebnis etwas ändert. Das dürfte in keinem Fall passieren.

Das werde ich gleich mal machen.
 
...Teste das mal mit einer Ersatztabelle in die du einfügst und schaue, ob sich dann am Ergebnis etwas ändert. Das dürfte in keinem Fall passieren.

Gerade eben habe ich eine identische Ersatztabelle angelegt und Du liegst mit Deiner Vermutung richtig und ich habe eine Erklärung, warum zuviele Tupel eingefügt werden.
Innerhalb meiner Funktion rufe ich unter bestimmten Umständen "kontext.tabelle2" auf und dadurch wird der Fehler ausgelöst. Jetzt stellt sich für mich die Frage, wie kann ich diesen Fehler umgehen?
 
Also eigentlich ist es ja wie in deinem Zitat, erst der Select und dann der Insert sozusagen als Block und erst dann müsste sich für die Funktion etwas ändern. Das ist aber weit mehr als ein "Syntaxproblem" und sehr tiefgreifend, da stecke ich nicht drin. Ich kann mir vorstellen das es von der Datenmenge im Select abhängig sein kann wie das DBMS hier vorgeht und womöglich kann man das Verhalten sogar beeinflussen.

Ich würde mich eher an der Tatsache stören das die Datensätze sich gegenseitig beeinflussen, das ist ja logisch betrachtet nur selten richtig. Du kannst jetzt natürlich ein Flag setzen und das irgendwie abfangen oder erst in eine andere Tabelle schreiben und dann in die Livedaten Tabelle überführen aber mir erscheint das nicht logisch. Natürlich kenne ich eure Business Logik nicht aber vielleicht ist da ein Denkfehler drin?
 
Moin,

also die Fehlerbehandlung ist schon mal verbesserungswürdig.
Davon abgesehen schreibst du in deinem INSERT ja in eine Tabelle rein, die etwas später erneut vom Package gelesen wird.

Read Consistency greift hier nicht, da es sich hier nicht um ein einzelnes SQL handelt das zum Zeitpunkt X startet und zum Zeitpunkt Y beendet ist.
Die diversen Selects im Package lesen natürlich auch die vorher neu eingefügten Datensätze.

The Problem with SQL Calling PL/SQL Calling SQL

Lösung: Die angesprochene Zwischentabelle.
 
Lösung: Die angesprochene Zwischentabelle.
Oder eben eine Verbesserung der Logik. Ich kann mir einfach nicht vorstellen das das Ergebnis einer Berechnung die Berechnung beieinflussen soll, das erschließt sich mir nicht. Und wenn das nicht gewollt ist sollte man tunlichst seinen Code reparieren und nicht noch mehr dran kleben.
 
also die Fehlerbehandlung ist schon mal verbesserungswürdig.


Hallo,

kannst Du zu der Fehlerbehandlung etwas ausführlicher werden? Meinst Du damit den EXCEPTION-Block, den ich weiter oben angegeben habe?

Davon abgesehen schreibst du in deinem INSERT ja in eine Tabelle rein, die etwas später erneut vom Package gelesen wird.
...
Die diversen Selects im Package lesen natürlich auch die vorher neu eingefügten Datensätze.

The Problem with SQL Calling PL/SQL Calling SQL

Kein Package, nur eine FUNCTION, in der mehrere verschiedene SELECTs aufgerufen werden. Allerdings rufe ich in der FUNCTION einige FUNCTIONS aus einem PACKAGE auf.



Danke für den Link und den Tip mit der Zwischentabelle.
 
Oder eben eine Verbesserung der Logik. Ich kann mir einfach nicht vorstellen das das Ergebnis einer Berechnung die Berechnung beieinflussen soll, das erschließt sich mir nicht. Und wenn das nicht gewollt ist sollte man tunlichst seinen Code reparieren und nicht noch mehr dran kleben.

Wenn das Ergebnis der vorherigen Berechnung die Grundlage der nächsten Berechnung sein soll, dann passt die Logik ja - nur das Vorgehen muss man etwas anpassen.

Möglich wär auch eine Einschränkung per Zeitstempel, laufende Nummer etc oder der Einsatz von Partitionierung (sofern vorhanden).
 
Wenn das nicht wirklich immer weiter rekursiv berechnet wird (was ich in einem Schritt mit CTE machen würde) sonderen eine feste Anzahl von Schritten ist dann würde ich das in Abschnitte unterteilen und für die Berechnung von z.B. Abschnitt 2 nur Datensätze nehmen die mit Abschnitt 1 markiert sind usw.
 
Etwa eleganter wäre es, wenn man Flashback Query mit der SCN verwendet. Einmalig folgenden PLSQL Block ausführen:
Code:
begin
DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER (DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER);
end;
Dann selektieren alles Statements in der Session automatisch den comitteten Datenstand mit der aktuellen SCN. Neuere Daten werden nicht gesehen.
Müsste man mal testen. Berechtigungen für das dbms_flashback Package müssen evtl. noch vergeben werden.
 
Werbung:
Vielen Dank für die konstruktiven Rückmeldungen. Ich arbeite mich gerade durch das verlinkte Dokument "The Problem with SQL calling PL/SQL calling SQL" und melde mich Morgen wieder.
 
Zurück
Oben