Oracle-Prozedur

[janosch1972]

Hallo Zusammen,

habe eine Prozedur erstellt, welche einen gelöschten Datensatz in eine Log-Tabelle protokolliert. Die Prozedur wird von einem Delete-Trigger, der den zu behandelnden Tabellen zugeordnet ist, ausgeführt. Als Parameter werden der eindeutige Schlüssel (p_CADID), die Schlüsselspalte (p_PKColumn) und der Tabellenname (p_TableName) übergeben. In der Prozedur wird ein Cursor (c_Table) erstellt, welcher die Felder und deren Datentypen der zu behandelnden Tabelle enthält.

Danach geht man durch alle Felder (For-Schleife) und sammelt alle Werte, setzt diese in einen String (v_ColumnValue) zusammen und fügt danach die Werte in die Log-Tabelle ein.

Die Prozedur wurde ohne Fehlermeldung angelegt. Wenn ich die Parameter übergebe, führt er die Prozedur aus, aber es wird nichts in die Log-Tabelle geschrieben. Habe die Prozedur auch mit dem PLSQL-Developer debuggt. Bei der For-Schleife steigt er aus, da r_Table und c_Table Null-Werte haben. Möglicherweise ist beim Cursor der Bug, finden diesen aber nicht.

Anbei die Prozedur:

CREATE OR REPLACE PROCEDURE LogSachDat(

p_CADID VARCHAR2,

p_PKColumn VARCHAR2,

p_TableName VARCHAR2)

IS

v_Value VARCHAR2(4000);

v_DataType VARCHAR2(4000);

v_ColumnValue VARCHAR2(4000);

c_Statement INTEGER;

-- Cursor erstellen

CURSOR c_Table IS

SELECT column_name, data_type

FROM all_tab_columns

WHERE table_name = p_TableName

ORDER BY COLUMN_ID;

BEGIN

FOR r_Table IN c_Table LOOP

-- jeden einzelnen Wert der Spalte abfragen

c_Statement := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(c_Statement, 'SELECT '||r_Table.column_name||' FROM '||p_TableName||' WHERE '||p_PKColumn||' = '||p_CADID, DBMS_SQL.NATIVE);

v_Value := DBMS_SQL.EXECUTE (c_Statement);

DBMS_SQL.CLOSE_CURSOR(c_Statement);

-- die abgefragten Werte zusammenfassen

v_ColumnValue := v_ColumnValue||';'||v_Value;

END LOOP;

das erste Zeichen entfernen

v_ColumnValue := LTRIM(v_ColumnValue,';');

-- die Werte einfügen

INSERT INTO visdat_Log(CADID,TableName,LogUser,LogDate,FieldValues)

VALUES(p_CADID,p_TableName,user,sysdate,v_ColumnValue);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20348, 'Kein Wert');

END LogSachDat;

Würde mich freuen, wenn mir jemand helfen kann.

Schöne Grüße, janosch

[Ganymed]

Dumme Idee, aber ich hab noch nie so eine Verwendung eines Cursors gesehen... Versuch das Ganze mal so umzubauen, dass er die Open Methode benutzt...

Oracle PL/SQL Tutorial: SQL

[dr.dimitri]

Hi,

versteh ich das richtig? Du versuchst den Datensatz der gerade aus der Tabelle gelöscht wurde zu selektieren? Logik?

In einem Delete Trigger bekommst Du über die :OLD Variable immer alle Spalten und Werte automatisch geliefert - ein nachselektieren ist daher nicht nötig (und auch falsch). Machst Du soe twas im Trigger selbst, dann bekommst Du von Oracle direkt eine auf die Finger:

ORA-04091: Table xyz is mutating, trigger/function may not see it

Ausserdem halte ich es für extrem unperformant bei jedem delete aus all_tab_columns dynamisch ein SQL zusammenzubauen, welches dann wiederum nicht mal Bindvariablen verwendet.

Wir verwenden auch Trigger zur Historisierung allerdings generieren wir uns für jede Tabelle einen spezifischen Trigger, der dann tabellenbezogen genau das macht was er soll - und zwar hartcodiert (bzw. hartgeneriert ).

Dim

PS: EXECUTE IMMEDIATE ist deutlich einfacher zu lesen als die Sachen aus dem DBMS_SQL Package.

[janosch1972]

Es ist mir schon bewusst, dass das Selektieren der Felder aus der all_Tables auf die Performance geht. Habe anfangs auch vorgehabt für die zu loggenden Tabellen jeweils einen eigenen Trigger zu erstellen. Es sind momentan an die 300 Tabelle die geloggt werden müssen. Bei einer Änderung der Triggerm muss ich 300mal ran. So ändere ich es einmalig in der Prozedur.

Danke für den Tipp mit EXECUTE IMMEDIATE. Das SQL-Package ist bei uns nicht installiert. Werde die Prozedur umbauen und berichten.

[dbwizard]

Hallo Zusammen,

Würde mich freuen, wenn mir jemand helfen kann.

Schöne Grüße, janosch

Hallo,

Kleine Frage zum vorgehen : Warum schreibst du die LOG Informationen nicht gleich in der Logik, welche des löschen an sich ausführt. Ich halte nicht viel davon, solche Sachen in Triggern zu "verstecken". Wenn du einen Codeteil hast, welcher die Daten löscht, schrieb dort auch gleich deinen Code für das Loggen...es gehört ja schliesslich zur derselben Transaktion

gruss

[janosch1972]

Habe die Prozedur umgebaut. Das Ergebnis findet Ihr weiter unter. Bei der Ausführung und der Übergabe der Parameter erscheint folgende Fehlermeldung:

FEHLER in Zeile 1:

ORA-00933: SQL command not properly ended

ORA-06512: at "VISMAN.LOGSACHDAT", line 31

ORA-06512: at line 1

Ich weiss wirklich nicht, wo er sich da aufhängt.

Und nun die korrigierte Prozedur. Vielleicht kann ein oder anderer was optimieren. Wäre sehr dankbar.

CREATE OR REPLACE PROCEDURE LogSachDat(

p_CADID VARCHAR2

p_PKColumn VARCHAR2,

p_TableName VARCHAR2),

IS

v_Value VARCHAR2(4000);

v_DataType VARCHAR2(4000);

v_ColumnValue VARCHAR2(4000);

v_Column VARCHAR2(30);

v_Statement VARCHAR2(4000);

-- Cursor erstellen

CURSOR c_Column IS

SELECT column_name

FROM all_tab_columns

WHERE table_name = p_TableName

ORDER BY column_id;

BEGIN

OPEN c_Column;

LOOP

FETCH c_Column INTO v_Column;

v_Statement := 'SELECT '||v_Column||' FROM '||p_TableName||' WHERE '||p_PKColumn||' = '||p_CADID;

EXECUTE IMMEDIATE v_Statement INTO v_Value;

-- die abgefragten Werte zusammenfassen

v_ColumnValue := v_ColumnValue||';'||v_Value;

END LOOP;

CLOSE c_Column;

-- das erste Zeichen entfernen

v_ColumnValue := LTRIM(v_ColumnValue,';');

-- die Werte einfügen

INSERT INTO visman.visdat_Log(CADID,TableName,LogUser,LogDate,FieldValues)

VALUES(p_CADID,p_TableName,user,sysdate,v_ColumnValue);

EXCEPTION

WHEN NO_DATA_FOUND THEN

RAISE_APPLICATION_ERROR(-20348, 'Kein Wert');

END LogSachDat;

[dr.dimitri]

Hi,

darum habe ich ja auch gesagt generieren und nicht einzeln schreiben. Wir haben ca. 200 Tabellen und alle Trigger werden generiert.

Aber seis drum ich muss ja nicht damit leben

Zu Deinem Fehler:

Du hast ein Komma zuviel:

CREATE OR REPLACE PROCEDURE LogSachDat(


    p_CADID VARCHAR2

    p_PKColumn VARCHAR2,

    p_TableName VARCHAR2), <-----


IS

Zum Execute immediate: Du solltest Bindvariablen verwenden:

v_Statement := 'SELECT '||v_Column||' FROM '||p_TableName||' WHERE '||p_PKColumn||' = :1';

        EXECUTE IMMEDIATE v_Statement INTO v_Value USING p_CADID;

Dim

PS: Das ändert natürlich nichts daran, dass Du, sofern Du keinen BEVORE Trigger verwendest nichts finden wirst. und es auch nicht notwendig ist, da Oracle schon alles liefert was Du brauchst.

[janosch1972]

Wie generiert Ihr die Trigger?

Würde mich schon an Deine Tipps halten, hänge aber momentan ein bisschen in der Luft :-(

Habe die Bindvariablen eingebaut, bekommen trotzdem einen Fehler: Missing expression.

[dr.dimitri]

Wir verwenden einen eigenen Parser und Velocity Templates. Allerdings kann man damit auch noch ein bissl mehr machen.

Für deine Zwecke kann man das mit einfachem PL/SQL generieren:

CREATE OR REPLACE procedure create_triggerddl is

 l_triggerDDL VARCHAR2(32000);

 CURSOR c_tables is 

    SELECT table_name 

      FROM user_tables;

 CURSOR c_columns(p_tablename VARCHAR2) IS 

    SELECT column_name 

      FROM user_tab_cols 

     WHERE table_name=p_tablename;

 l_tables c_tables%ROWTYPE;

 l_pkName varchar2(32);

 l_trigName VARCHAR2(30);

BEGIN

 FOR  l_tables IN c_tables LOOP   

   --Name des PK herausfinden

   SELECT column_name INTO l_pkName

     FROM user_constraints a,

          user_cons_columns b

    WHERE a.constraint_name=b.constraint_name          

      AND a.table_name=l_tables.table_name

      AND a.constraint_type='P';

   --Den Trigger Stringenerieren

   l_trigName:='TRG_'||SUBSTR(l_tables.table_name,1,26);

   l_triggerDDL:='CREATE OR REPLACE TRIGGER '||l_trigName||CHR(13) ||

                 'BEFORE DELETE ON '||l_tables.table_name||CHR(13) ||'FOR EACH ROW '||CHR(13)||'DECLARE' ||CHR(13) ||

                 'l_histData varchar2(4000); '||CHR(13)||'BEGIN'||CHR(13);              

   FOR l_columns IN c_columns(l_tables.table_name) LOOP

     l_triggerDDL:=l_triggerDDL||'l_histData:=l_histData||:OLD.'||l_columns.column_name||';'||CHR(13);          

   END LOOP;

     l_triggerDDL:=l_triggerDDL||'INSERT INTO visman.visdat_log(cadid,tablename,loguser,logdate, fieldvalues)'||

                                 'VALUES(:OLD.'||l_pkName||','''||l_tables.table_name||''',USER,sysdate,l_histData);'||CHR(13)||

                                 'END '||l_trigName||';'||CHR(13)||'/';

 END LOOP;

 --dbms_output.put_line(l_triggerDDL); --Geht nur ab 10g. Vorher ist die Länge auf 255 Zeichen beschränkt 

END;

/

Das sollte schon mal ein Grundgerüst sein, auf dem Du aufbauen kannst.

Dim

[janosch1972]

Danke sehr Dim.

Das schaut schon mal professionell aus. D.h. bei Ausführung der Prozedur wird allen abgefragten Tabellen der Trigger hinzugefügt.

Werde es mal bei mir testen und Feedback geben.

Schöne Grüße,

janosch

[dr.dimitri]

Hinzugefügt wird nichts - in diesem Script wird nur die DDL generiert aber nicht ausgeführt.

Dim

[janosch1972]

Guten Morgen Dim,

habe die Prozedur auf meine Bedürfnisse angepasst und angelegt. Hat problemlos funktioniert. Sinnvoll wäre es, wenn man die erstellten Trigger in eine Textdatei schreibt, oder diese direkt ausführen lässt.

Gibt es eine Möglichkeit, dass man eine Textdatei erstellt (zur Kontrolle) und eine Möglicheit, dass die Trigger aus der Prozedur automatisch angelegt werden? Das wäre das Tüpfelchen auf dem "i".

Danke schon mal im voraus.

Schöne Grüße, janosch

[dr.dimitri]

Schreib den generierten String doch in eine Tabelle.

Dann kannst z.B. für jeden Tigger eine eigene Zeile anlegen und dir ggf. für genau einen bestimmten den String selektieren.

Das CREATE kannst Du per execute immediate ausführen:

execute immediate dein_trigger_string;

Beachte aber, dass execute immediate Befehle maximal 32K lang werden dürfen.

Dim