1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

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

Dieses Thema im Forum "Oracle" wurde erstellt von Ora et labora, 1 Februar 2018.

  1. Ora et labora

    Ora et labora Benutzer

    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.
     
  2. akretschmer

    akretschmer Datenbank-Guru

    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?
     
  3. Ora et labora

    Ora et labora Benutzer

    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?
     
  4. ukulele

    ukulele Datenbank-Guru

    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.
     
  5. Ora et labora

    Ora et labora Benutzer

    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
    Das werde ich gleich mal machen.
     
  6. Ora et labora

    Ora et labora Benutzer

    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?
     
  7. ukulele

    ukulele Datenbank-Guru

    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?
     
  8. drdimitri

    drdimitri Datenbank-Guru

    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.
     
    Walter gefällt das.
  9. ukulele

    ukulele Datenbank-Guru

    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.
     
    Walter gefällt das.
  10. Ora et labora

    Ora et labora Benutzer


    Hallo,

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

    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.
     
  11. drdimitri

    drdimitri Datenbank-Guru

    Ob Package oder Function ist egal. Es ist ein PLSQL Aufruf der ein neues SQL startet.
     
  12. drdimitri

    drdimitri Datenbank-Guru

    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).
     
  13. ukulele

    ukulele Datenbank-Guru

    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.
     
  14. drdimitri

    drdimitri Datenbank-Guru

    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.
     
  15. Ora et labora

    Ora et labora Benutzer

    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.
     
Die Seite wird geladen...
Ähnliche Themen - Oracle SELECT INSERT
  1. Kaladial
    Antworten:
    0
    Aufrufe:
    1.098
  2. OracleQest
    Antworten:
    10
    Aufrufe:
    1.308
  3. resuah
    Antworten:
    0
    Aufrufe:
    1.040
  4. Babsi
    Antworten:
    11
    Aufrufe:
    1.608
  5. FreeSepp
    Antworten:
    1
    Aufrufe:
    1.543

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden