Oracle, Trigger per Programm erstellen

Hallo,

ich führe folgenden SQL auf einer Oracle-DB aus:

MyCommand.CommandText = "CREATE OR REPLACE TRIGGER \"MYTRIGGER\" BEFORE INSERT ON \"THISTABLE\" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW";
MyCommand.CommandText += "BEGIN";
MyCommand.CommandText += "    INSERT INTO "MYTABLE" VALUES (\"MYSEQ\".nextval, :NEW.FIELD1, :NEW.FIELD2, 'INSERT');";
MyCommand.CommandText += "END;";

Der Trigger erscheint dann auch in der Datenbank, ist aber ungültig. Erst wenn ich diesen manuell im PL/SQL-Developer (nach Aufruf im Editor) compiliere wird er gültig. Am SQL selber kann es also nicht liegen. Werden evtl. irgendwelche unsichtbaren Sonderzeichen mit übertragen? Es ist mir ein Rätsel, zumal ich von OracleCommand auch keine Exception beim Ausführen bekomme. Nur wenn ich die Trigger recompilieren will, ohne sie vorher im PL/SQL-Developer Editor anzuzeigen bekomme ich folgende Meldungen:

Compilation errors for TRIGGER DBO.MYTRIGGER

Error: PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:

      begin case declare exit for goto if loop mod null pragma  
      raise return select update while with <an identifier>  
      <a double-quoted delimited-identifier> <a bind variable> <<  
      close current delete fetch lock insert open rollback  
      savepoint set sql execute commit forall merge  
      <a single-quoted SQL string> pipe  
   Das Symbol "" wurde ignoriert.  

Line: 2
Text: BEGIN

Error: PLS-00103: Fand das Symbol "" als eines der folgenden erwartet wurde:

      begin case declare end exception exit for goto if loop mod  
      null pragma raise return select update while with  
      <an identifier> <a double-quoted delimited-identifier>  
      <a bind variable> << close current delete fetch lock insert  
      open rollback savepoint set sql execute commit forall merge  
      <a single-quoted SQL string> pipe  
   Das Symbol "" wurde ignoriert.  

Line: 3
Text: INSERT INTO "MYTABLE" VALUES ("MYSEQ".nextval, :NEW.FIELD1, :NEW.FIELD2, 'INSERT');

Öhm, du kannst das nicht so abgesetzt haben...
Du hättest sonst "ROWBEGIN" darin gehabt.
Was du wohl gemacht hast ist eine CrLf Kombi einzufügen. Für Oracle ist Cr aber kein White space!

Da ich weiß, dass du den PL/SQL Deveoper nimmst, würde ich dir empfehlen mal in seinem SpecialCopy-Ordner eine TextDatei namens "StringBuilder C#.copy" anzulegen.
Da kopierst du das hier rein:

#define " = \"
#define \ = \\
#define char(9) = \t
StringBuilder sb := new StringBuilder();
sb.Append("<line_1>\n");
sb.Append("<line_*>\n");
sb.Append("<line_n>");

Wenn du jetzt sowas im Developer stehen hast...

create or replace trigger "MYTRIGGER"
  before insert on "THISTABLE"
  for each row
begin
  INSERT INTO "MYTABLE"
  VALUES
    ("MYSEQ".NextVal
     ,:New.Field1
     ,:New.Field2
     ,'INSERT');
end;

... macht er dir das draus:

StringBuilder sb := new StringBuilder();
sb.Append("create or replace trigger \"MYTRIGGER\"\n");
sb.Append("  before insert on \"THISTABLE\"\n");
sb.Append("  for each row\n");
sb.Append("begin\n");
sb.Append("  INSERT INTO \"MYTABLE\"\n");
sb.Append("  VALUES\n");
sb.Append("    (\"MYSEQ\".NextVal\n");
sb.Append("     ,:New.Field1\n");
sb.Append("     ,:New.Field2\n");
sb.Append("     ,'INSERT');\n");
sb.Append("end;");

btw:
Deine Referencing clause ist witzlos, da du keinen anderen Alias für :new und 😮ld deklarierst. (Braucht man heutzutage eh nicht mehr wirklich, da keine bind vars außer :new und 😮ld in Triggers zulässig sind. 😉

Du hattest letztlich in der DP gefragt, wann man Bezeichner in Quotes setzen sollte. Solange du keine kranken Namen hast, würde ich das sein lassen.
Du musst die sonst immer komplett groß schreiben, da sie sonst case sensitiv werden. Und das ist nun wirklich verflucht hässlich a) alles groß und b) in Quotes... 😕

Original von Robert G
Öhm, du kannst das nicht so abgesetzt haben...
Du hättest sonst "ROWBEGIN" darin gehabt.
Was du wohl gemacht hast ist eine CrLf Kombi einzufügen. Für Oracle ist Cr aber kein White space!

Nein, das hab ich so nicht, habe die "\r\n" am Ende natürlich vergessen. Ich habe eine eigene Klasse namens "SqlQuery" (brauche ich um alten Pascal Code kompatibel zu machen und automatisiert einiges). Die enthält einen Property namens SQL und da mache ich sonst einfach:

MyQuery.SQL.Add("CREATE OR REPLACE TRIGGER \"MYTRIGGER\" BEFORE INSERT ON \"THISTABLE\" FOR EACH ROW");
MyQuery.SQL.Add("BEGIN");
MyQuery.SQL.Add("    INSERT INTO "MYTABLE" VALUES (\"MYSEQ\".nextval, :NEW.FIELD1, :NEW.FIELD2, 'INSERT');");
MyQuery.SQL.Add("END;");

"SQL" ist eine erweiterte StringCollection und ich mache dann zum Schluss automatisch in der Klasse:

MyCommand.CommandText = SQL.Text.Trim();

Wobei SQL.Text die einzelnen Strings per "\r\n" separiert.

Original von Robert G
Da ich weiß, dass du den PL/SQL Deveoper nimmst, würde ich dir empfehlen mal in seinem SpecialCopy-Ordner eine TextDatei namens "StringBuilder C#.copy" anzulegen.
Da kopierst du das hier rein:

#define " = \"  
#define \ = \\  
#define char(9) = \t  
StringBuilder sb := new StringBuilder();  
sb.Append("<line_1>\n");  
sb.Append("<line_*>\n");  
sb.Append("<line_n>");  

Wenn du jetzt sowas im Developer stehen hast...

create or replace trigger "MYTRIGGER"  
  before insert on "THISTABLE"  
  for each row  
begin  
  INSERT INTO "MYTABLE"  
  VALUES  
    ("MYSEQ".NextVal  
     ,:New.Field1  
     ,:New.Field2  
     ,'INSERT');  
end;  

... macht er dir das draus:

StringBuilder sb := new StringBuilder();  
sb.Append("create or replace trigger \"MYTRIGGER\"\n");  
sb.Append("  before insert on \"THISTABLE\"\n");  
sb.Append("  for each row\n");  
sb.Append("begin\n");  
sb.Append("  INSERT INTO \"MYTABLE\"\n");  
sb.Append("  VALUES\n");  
sb.Append("    (\"MYSEQ\".NextVal\n");  
sb.Append("     ,:New.Field1\n");  
sb.Append("     ,:New.Field2\n");  
sb.Append("     ,'INSERT');\n");  
sb.Append("end;");  

btw:
Deine Referencing clause ist witzlos, da du keinen anderen Alias für :new und 😮ld deklarierst. (Braucht man heutzutage eh nicht mehr wirklich, da keine bind vars außer :new und 😮ld in Triggers zulässig sind. 😉

Du hattest letztlich in der DP gefragt, wann man Bezeichner in Quotes setzen sollte. Solange du keine kranken Namen hast, würde ich das sein lassen.
Du musst die sonst immer komplett groß schreiben, da sie sonst case sensitiv werden. Und das ist nun wirklich verflucht hässlich a) alles groß und b) in Quotes... 😕

Das mit der Grossschreibung werd ich gleich rausschmeissen. Ich passe den Trigger-Code erstmal an, das dürfte aber wohl das Problem nicht lösen. 🙁

P.S.: Ich sehe gerade, dass Du selber gern Pascal verwendest. Evtl. willst Du die "Pascal kompatiblen" .NET Klassen (in C# geschrieben) auch haben, wie TQuery, TTable, TStringList uvm.? 😉

Original von tomaten
"SQL" ist eine erweiterte StringCollection und ich mache dann zum Schluss automatisch in der Klasse:

MyCommand.CommandText = SQL.Text.Trim();

Wobei SQL.Text die einzelnen Strings per "\r\n" separiert.

Probieren wir's nochmal, Ok? 😁
Für Oracle ist ein Cr kein White space! Die dbExpress Treiber, dbGo und auch DOA haben damals unter Delphi deshalb die CRs aus dem Statement entfernt. Das wird nicht bei den .Net Zugriffsklassen gemacht. Du hast also CRs drin und damit kann Oracle nunmal nix anfangen. (btw Cr == \r )

Original von Robert G

Original von tomaten
"SQL" ist eine erweiterte StringCollection und ich mache dann zum Schluss automatisch in der Klasse:

MyCommand.CommandText = SQL.Text.Trim();

Wobei SQL.Text die einzelnen Strings per "\r\n" separiert.
Probieren wir's nochmal, Ok? 😁
Für Oracle ist ein Cr kein White space! Die dbExpress Treiber, dbGo und auch DOA haben damals unter Delphi deshalb die CRs aus dem Statement entfernt. Das wird nicht bei den .Net Zugriffsklassen gemacht. Du hast also CRs drin und damit kann Oracle nunmal nix anfangen. (btw Cr == \r )

Aha, ich habe nämlich vorher mit dbExpress gearbeitet und da klappte das einwandfrei. Evtl. werden die nämlich vom PL/SQL-Developer Editor automatisch entfernt und deshalb funktioniert das kompilieren. Bisher hatte ich aber mit CR noch keine Probleme. Dann machen ich wohl mal:

MyCommand.CommandText = SQL.Text.Trim().Replace("\r", String.Empty);

Original von Robert G
(btw Cr == \r )

😜 😉

Heureka, es funktioniert! 8o Vielen Dank. Das hat man davon, wenn andere das vorher für einen automatisch erledigt haben. 🙁

@Robert

Du scheinst ja selbst die innersten Innnereien von Oracle zu kennen. Ich habe da eine (evtl. blöde) Frage. Gibt es eine Möglichkeit festzustellen, welche Anwendung auf eine Tabelle schreibt? Oder kann ich einen SQL in Oracle so modifizieren (Dummy-Felder) dass ich unterscheiden kann, welcher SQL geschrieben wird?

Ich erklär mal den Sinn der Aktion. Anwendung A schreibt in Tabelle A. Anwendung B soll die Änderung an Tabelle A mitbekommen und in Tabelle B aufbereitete Daten schreiben. Soweit so gut, das machen wir über eine Synctabelle die von einem Trigger an Tabelle A befüllt wird. Jetzt kommt aber der Fall, dass Anwendung B in Tabelle A schreibt und da es diese Daten schon kennt auch die aufbereiteten Daten in Tabelle B. Natürlich soll in diesem Fall der Trigger an Tabelle A nicht in die Synctabelle schreiben.

Eine Idee dazu?

Original von tomaten
@Robert

Du scheinst ja selbst die innersten Innnereien von Oracle zu kennen. Ich habe da eine (evtl. blöde) Frage. Gibt es eine Möglichkeit festzustellen, welche Anwendung auf eine Tabelle schreibt? Oder kann ich einen SQL in Oracle so modifizieren (Dummy-Felder) dass ich unterscheiden kann, welcher SQL geschrieben wird? Moin Thomas,
war zwischendurch im Urlaub und habe nur kurz hier vorbei geschaut....

Zur Frage:
Ja, das ist möglich.
Abhängig davon wieviele Infos du brauchst kommst du entweder mit DBMS_Application_Info aus oder du musst V$Session abfragen.
Das hier wäre DBMS_Application_Info:

begin
  DBMS_Application_Info.Read_Module(:Module, :Action);
  DBMS_Application_Info.Read_Client_Info(:ClientInfo);
end;

Das hier eine Abfrage auf V$Session um ein paar Infos über deine Session zu erfahren:

SELECT t.AUDSID
      ,t.SchemaName
      ,t.UserName
      ,t.Command
      ,t.Module
      ,t.Action
      ,t.Client_Info
      ,t.Client_Identifier
      ,t.OsUser
      ,t.Process
      ,t.Machine
      ,t.Terminal
      ,t.Program
      ,t.type
FROM   V$Session t
WHERE  t.AUDSID = UserEnv('SESSIONID')

Da V$Session ein virtueller View ist, kostet so eine Abfrage etwas mehr als es dir in einem Trigger lieb sein wird.
Das lässt sich sehr easy in ein Package verpacken, das dir pro Session eine Instanz eines Records im "Cache" hält:
Spec:

create or replace package SessionInfos is

  subtype TSid is v$session.AUDSID%type;

  type TSessionInfo is record(
    Sid              TSid,
    SchemaName       V$Session.SchemaName%type,
    UserName         V$Session.UserName%type,
    Command          V$Session.Command%type,
    Module           V$Session.Module%type,
    Action           V$Session.Action%type,
    ClientInfo       V$Session.Client_Info%type,
    ClientIdentifier V$Session.Client_Identifier%type,
    OsUser           V$Session.OsUser%type,
    Process          V$Session.Process%type,
    Machine          V$Session.Machine%type,
    Terminal         V$Session.Terminal%type,
    Program          V$Session.Program%type,
    SessionType      V$Session.type%type);

  -- gets cached session info
  -- if current sid differs from cached one, it will retrieve the new session info
  function getSessionInfo return TSessionInfo;

  -- forces retrieval of current session info
  function getCurrentSessionInfo return TSessionInfo;

end SessionInfos;

Body:

create or replace package body SessionInfos is

  -- cached session info
  fSessionInfo TSessionInfo;

  procedure FetchSessionInfo(sid in TSid) is
    cursor SessionInfosCursor(currentSid in TSid) return TSessionInfo is
      SELECT t.AUDSID
            ,t.SchemaName
            ,t.UserName
            ,t.Command
            ,t.Module
            ,t.Action
            ,t.Client_Info
            ,t.Client_Identifier
            ,t.OsUser
            ,t.Process
            ,t.Machine
            ,t.Terminal
            ,t.Program
            ,t.type
      FROM   V$Session t
      WHERE  t.AUDSID = currentSid;
  begin
    open SessionInfosCursor(sid);
    FETCH SessionInfosCursor
      INTO fSessionInfo;
    close SessionInfosCursor;
  end FetchSessionInfo;


  function Equals
  (
    left  in TSid,
    right in TSid
  ) return boolean is
  begin
    return(left is null and right is null) or nvl(left = right, false);
  end Equals;


  function getSessionInfo return TSessionInfo is
    sid constant TSid := UserEnv('SESSIONID');
  begin
    if not Equals(fSessionInfo.Sid, sid) then
      FetchSessionInfo(sid);
    end if;
  
    return fSessionInfo;
  end getSessionInfo;


  function getCurrentSessionInfo return TSessionInfo is
    sid constant TSid := UserEnv('SESSIONID');
  begin
    FetchSessionInfo(sid);
  
    return fSessionInfo;
  end getCurrentSessionInfo;

end SessionInfos;

Hehe, war mal wieder interessant mit PL/SQL zu spielen. g (komme ich kaum noch dazu...)