Probleme mit Update-Anweisung in stored procedure

[giflo]

Hallo.
Ich bin schon seit zwei Tagen auf der Suche nach dem Fehler und kann mir nicht so recht helfen:

Folgendes Problem: Ich habe die procedure punkte geschrieben und erzeuge einen cursor der alle objectid aufnimmt deren dimension <= 100 ist.

anschließend soll der cursor durch eine forschleife laufen
und für jeden datensatz mit der aktuellen cursor objectid einen wert aus der tabelle rb_rw_kriterium_dim holen und ueber update einfügen.

beim starten der procedure kommt es dazu, dass Oracle sql developer abschmiert ohne überhaupt eine fehlermeldung.

Kann mir evtl. jemand helfen...ein insert auf die tabelle lässt sich problemlos durchführen, warum klappt es bei update nicht...


create or replace
PROCEDURE PUNKTE IS

CURSOR cur_dat
IS

SELECT objectid
FROM CPOG_WAS_ABSCHNITT_L
WHERE dimendion <= '100';


BEGIN

FOR akt_dat IN cur_dat
LOOP

UPDATE rb_bw_bewertung SET

punkte = (
SELECT wert
FROM rb_rw_kriterium_dim
WHERE bedingung = '<= 100'
)
WHERE
objectid = akt_dat.objectid;



END LOOP;

END PUNKTE;

[helga_h]

du steckst in einer endlosschleife.

schau dir dein for-loop nochmal genau an.

http://rowa.giso.de/oracle/latex/Ein...00000000000000

lg helga

[giflo]

die schleife so wie ich sie geschrieben habe funktioniert aber bei einem insert

kann dann doch keine endlosschleife sein, oder irre ich?

Die FOR-CURSOR-LOOP

Hier ein anderes Beispiel für die Verarbeitung von Datensätzen. Wir führen jetzt die sogenannte FOR-CURSOR-LOOP ein:

DECLARE
CURSOR c_students IS
SELECT * FROM students;
BEGIN
FOR c_students_rec IN c_students
LOOP
NULL;
END LOOP;
END;
/

Nun, das ist die Schleifenvariante mit dem wenigsten Tippaufwand. Was passiert. Im FOR-LOOP Konstrukt erfolgt bereits das FETCH aus dem Cursor in den Record. Wir brauchen kein Ende-Kriterium, die Schleife terminiert, wenn keine Datensätze mehr im Cursor zur Verfügung stehen. Besonderheit: Der Cursor muß weder geöffnet noch geschlossen werden, das geschieht automatisch. Weitere Besonderheit: Der Record c_students_rec muß nicht deklariert werden. Auch dies geschieht automatisch. Warum hat die erste Variante dennoch ihre Existenzberechtigung, wenn es doch auch einfacher geht? Die einfache Schleife ist dann zu bevorzugen, wenn der Schleifenabbruch bereits erfolgen kann, ohne alle Datensätze verarbeitet haben zu müssen. Das hier vorgestellte Konstrukt verarbeitet erst alle Datensätze, dann terminiert die FOR-CURSOR-LOOP.

[dbwizard]

Hallo.

BEGIN

FOR akt_dat IN cur_dat
LOOP




END LOOP;

END PUNKTE;

- Darf ich fragen, warum du überhaupt das ganze mittels Slow-by-Slow Processing machst ? Warum fährst du den Update nicht gleich als ganzes mit einem SQL Statement ?


Gruss

[giflo]

- Darf ich fragen, warum du überhaupt das ganze mittels Slow-by-Slow Processing machst ? Warum fährst du den Update nicht gleich als ganzes mit einem SQL Statement ?
Gruss

hm, verstehe nicht ganz was du meinst? der cursor soll aus der datenbanktabelle erstmal alle datensätze ermitteln und anschlißend update durchführen.

[dbwizard]

hm, verstehe nicht ganz was du meinst? der cursor soll aus der datenbanktabelle erstmal alle datensätze ermitteln und anschlißend update durchführen.

- Ja, klar, dass habe ich schon verstanden :-)...Meine Frage war eher, warum du einen Cursor baust, Row für Row abarbeitest in einem Loop (=Slow-by-Slow) wenn du das ganze auch in einenm Rutsch als SQL machen kannst ? Das wäre :

- deutlich performanter
- Spart Ressoucen
- Skaliert besser

Gruss

[Falk Prüfer]

Hallo giflo,

deine Procedure sieht syntaktisch ok aus - der Programmabruch läßt sich so erstmal nicht erklären. Evtl. solltest du mal in den Logfiles vom Server nachsehen, ob dort irgendwelche "Ungereimtheiten" protokolliert wurden. Gibt es mglws. einen UPDATE-Trigger auf der Tabelle, der ein Problem verursachen könnte?

Allerdings wäre dein Code sehr "verbesserungswürdig". Falls du in deiner Schleife nicht tatsächlich noch mehr erledigen mußt, wäre - wie von dbwizard vorgeschlagen - ein einfaches Update-Statement die (sehr viel) bessere Variante.

Desweiteren verwendest du in deiner Cursor-Deklaration ...WHERE dimendion <= '100'. Ich weiß nicht von welchem Datentyp dimendion ist, aber fals es sich um ein NUMBER handelt, ist der Vergleich mit '100' natürlich mehr als fahrlässig. 99 ist nämlich z.B. größer als '100', da Oracle hier eine implizite Typumwandlung nach VARCHAR2 vornehmen wird und als String '99' > '100' ist.

Das Subselect in deinem Update-Statement - SELECT wert FROM rb_rw_kriterium_dim WHERE bedingung = '<= 100' - ist völlig unabhängig vom konkreten Schleifenwert und muß trotzdem für jeden Schleifendurchlauf gemacht werden. Selbst wenn du die PL/SQL-Schleifenlösung benötigst, wäre es hier günstiger den Wert einmal außerhalb der Schleife zu ermitteln, in einer Variablen zu speichern und beim Update die Variable zu verwenden.

Gruß Falk

[uminky]

was meinst du mit " dass Oracle sql developer abschmiert ". Von der Geschwindigkeit abgesehen. Kannst du bitte dein " create or replace PROCEDURE PUNKTE IS" durch ein "declare" und das "END PUNKTE;" durch ein "end;" ersetzen umd im SQL+ absetzen.
Danach die Meldung posten, die SQL+ dir zurück gibt.

[giflo]

Danke erstmal für eure zahlreichen Tipps, ich werde diese mir morgen auf der Arbeit mal zu Gemüte führen und euch berichten.

Gruß Flo

[giflo]

Meine Frage war eher, warum du einen Cursor baust, Row für Row abarbeitest in einem Loop (=Slow-by-Slow) wenn du das ganze auch in einenm Rutsch als SQL machen kannst ?

Ich nutze den Cursor, da noch weitere 3 weitere Bedingungen (Dimensionen) abgefragt werden.

Gibt es mglws. einen UPDATE-Trigger auf der Tabelle, der ein Problem verursachen könnte?

Es gibt einen Update Trigger, wie kann ich evtl herausfinden ob dieser Probleme bereitet

Desweiteren verwendest du in deiner Cursor-Deklaration ...WHERE dimendion <= '100'. Ich weiß nicht von welchem Datentyp dimendion ist, aber fals es sich um ein NUMBER handelt, ist der Vergleich mit '100' natürlich mehr als fahrlässig. 99 ist nämlich z.B. größer als '100', da Oracle hier eine implizite Typumwandlung nach VARCHAR2 vornehmen wird und als String '99' > '100' ist.

Der Datentyp für Dimension ist VARCHAR2(100), also ist es an so bei WHERE dimendion <= '100' richtig.

Selbst wenn du die PL/SQL-Schleifenlösung benötigst, wäre es hier günstiger den Wert einmal außerhalb der Schleife zu ermitteln, in einer Variablen zu speichern und beim Update die Variable zu verwenden.

ich habe im declare eine var_bedingung vom Typ Varchar2 deklariert. Sehe ich das richtig, dass ich die Bedingung per Select nach dem Begin ermitteln muss?

create or replace
PROCEDURE PUNKTE IS

CURSOR cur_dat
IS

SELECT objectid
FROM CPOG_WAS_ABSCHNITT_L
WHERE DIMENSION <= '100';

var_bedingung VARCHAR2(4000 Byte);

BEGIN

SELECT bedingung INTO var_bedingung FROM Tabelle;

FOR akt_dat IN cur_dat
LOOP

UPDATE rb_bw_bewertung SET

punkte = (
SELECT wert
FROM rb_rw_kriterium_dim
WHERE bedingung = var_bedingung
)
where objectid = akt_dat.objectid;

END LOOP;

END PUNKTE;

was meinst du mit " dass Oracle sql developer abschmiert "

Bei ausführen der Procedure aus dem Oracle SQL Developer heraus führt zum Absturz des Developers nach einiger Zeit

Kannst du bitte dein " create or replace PROCEDURE PUNKTE IS" durch ein "declare" und das "END PUNKTE;" durch ein "end;" ersetzen umd im SQL+ absetzen.
Danach die Meldung posten, die SQL+ dir zurück gibt.

Habe deine Änderungen befolgt und im Oracle SQL Plus abgesetzt, leider bricht das Programm wie auch beim Oracle SQL DEVELOPER zusammen.

[dbwizard]

Habe deine Änderungen befolgt und im Oracle SQL Plus abgesetzt, leider bricht das Programm wie auch beim Oracle SQL DEVELOPER zusammen.

- Ich würde mal die Session in den Trace Modus setzten (gleich im SqlPLus, das Statement nochmals ausführen und den entstehenden Trace über tkprof mal angucken und hier posten. Was heisst eigentlich "zusammenbrechen" ? Bleibt er hängen ? Fehlermeldung ? lebt die Session noch ?


Gruss

[giflo]

- Ich würde mal die Session in den Trace Modus setzten (gleich im SqlPLus, das Statement nochmals ausführen und den entstehenden Trace über tkprof mal angucken und hier posten. Was heisst eigentlich "zusammenbrechen" ? Bleibt er hängen ? Fehlermeldung ? lebt die Session noch ?


Gruss

Wie setze ich dei Session in den Trace Modus?

"zusammenbrechen" - Sql Plus reagiert nicht mehr...Anwendung bricht ab"
Gruß

[dbwizard]

Wie setze ich dei Session in den Trace Modus?

"zusammenbrechen" - Sql Plus reagiert nicht mehr...Anwendung bricht ab"
Gruß

- In der Session :

alter session set sql_trace=true;
alter session set timed_statistics=true;

- nun kannst du deine Statements ausführen....

anschliessend die Session wieder zurücksetzen (=false)

- Auf dem Server findest du im Verzeichnis ...\\udump\ das entsprechende Trace File (auf das Datum achten, ansonsten ist die PID im Filename enthalten)

- Mittels TKPROF nun das Tracefile formatieren : (in der Schell)

tkprof ora015445.trc ausgabe.txt explain=schema/psswd sys=no
- ausgabe.txt enthält nun die ausgeführten Statements. Am besten postet du das Ergebnis hier


Gruss

[giflo]

beim tkprof befehl bekomme ich die meldung tracefile could not open tracefile...das tracefile ist im verzeichnis \udump vorhanden...

woran liegt es das ich das nicht offnen kann?

[uminky]

Bevor du ein Tracefile analysiest:-
Was heisst eigentlich "zusammenbrechen" ?
Gibt es eine Rückmeldung ?
Medet sich der Befehlspromt zurück ?
Gibt es (Fehler)meldung ?
Ist die Session noch aktiv ?

Du hast gesehen das in dem PL kein commit steht. Somit kannst du auch das Ergebnis nicht in einer anderen Session sehen.

Ich habe es noch nie erlebt, das ein SQL+ sich selber beendet.