20. Mai 2008

Large Objects und Trigger ...

English title: LOBs and triggers ... some thoughts ...

Ein Trigger auf eine Tabelle ist ja nichts Besonderes - eine LOB-Spalte (CLOB, BLOB) auch nicht. Setzt man aber beides zusammen ein, so kann man ein seltsames Verhalten beobachten: Irgendwie feuert der Trigger niemals zur richtigen Zeit ...
Creating a trigger on a table is not a very complex thing to do - a lob column (CLOB, BLOB) also. But combining these can lead to some strange behaviour: In some way the trigger does never fire at the right time ...
Dazu (wie immer) ein Beispiel. Eine Tabelle mit einer LOB-Spalte und ein Trigger, welcher die Größe des LOB in eine andere Spalte schreibt, werden erzeugt. Zur Sicherheit wird im Trigger zusätzlich die Spalte TRIGGER_FIRED auf Y gesetzt.
An example illustrates this: We'll create a table with a CLOB column and a trigger which determines the LOB's length and stores this into another column: LAENGE. To be sure about the firing of the trigger we also set another column (TRIGGER_FIRED) explicitly to Y.
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class lobWriter {

  public static void main(String args[]) throws Exception {
    PreparedStatement pstmtInsert = null;
    PreparedStatement pstmtSelect = null;
    ResultSet rsLobLocator = null;
    CLOB lobLocator = null;

    int iLobId = 1;

    Writer lobWriter = null;


    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(
      "jdbc:oracle:thin:@192.168.2.140:1521:orcl",
      "scott",
      "tiger"
    );
    con.setAutoCommit(false);
   
    // LOB-Locator in die Tabelle einfügen
    // Insert a lob locator into the table
    pstmtInsert = con.prepareStatement ( 
      "insert into lobtable (id, dokument) values (?, empty_clob())"
    );

    // Den LOB-Locator abrufen 
    // Retrieve the lob locator
    pstmtSelect = con.prepareStatement (
      "select dokument from lobtable where id = ? for update"
    );
  
    pstmtInsert.setInt(1, iLobId); 
    pstmtInsert.execute();

    pstmtSelect.setInt(1, iLobId);
    rsLobLocator = pstmtSelect.executeQuery();
    if (rsLobLocator.next()) {
      lobLocator = ((OracleResultSet)rsLobLocator).getCLOB(1); 
    }

    // LOB befüllen
    // populate lob via streaming
    lobWriter = lobLocator.setCharacterStream(0L);
    for (int i=0;i<1000;i++) {
        lobWriter.write("Dies ist ein Text ... zum " + i + "ten!\n");
    } 
   
    // Ressourcen schließen
    // Close the handles
    lobWriter.flush();
    lobWriter.close();
    rsLobLocator.close();
    pstmtSelect.close();
    pstmtInsert.close();
    con.commit();
    con.close();
  }
}
Der Java-Code macht ein INSERT nur auf die Spalten ID und DOKUMENT. Schauen wir uns die Tabelleninhalte anschließend mal an ...
Have a look at the SQL INSERT being actually performed by the JDBC code. It only populates the column ID and LOB column DOKUMENT. All other columns are being populated by the trigger. After that we have a look at the table contents ...
select id, dokument, laenge len_table, dbms_lob.getlength(dokument) len_lob, trigger_fired 
from lobtable

        ID DOKUMENT                                  LEN_TABLE    LEN_LOB T
---------- ---------------------------------------- ---------- ---------- -
         1 Dies ist ein Text ... zum 0ten!                   0      33890 Y
           Dies ist ein Text ... zum 1ten!
           Dies ist ein Tex
Und dieses SQL offenbart das ganze Dilemma. Dass der Trigger gefeuert wurde, ist an der Spalte TRIGGER_FIRED klar erkennbar. Allerdings wurde eine Länge von 0 geschrieben - wie die Funktion DBMS_LOB.GETLENGTH erkennen lässt, ist der LOB aber klar größer. Ganz offensichtlich hat der Trigger zum falschen Zeitpunkt gefeuert.
And this SQL shows the problem: The column TRIGGER_FIRED proves that the trigger has actually fired. But the column LAENGE contains zero (0), so it seems that the Trigger could not access to LOB contents. But the lob contents were written correctly, as the function DBMS_LOB.GETLENGTH indicates. Onviously, the trigger fired too early.
Tatsächlich feuert der Trigger unmittelbar nach dem DML-Kommando, also dem SQL Insert. Wenn man in den Java-Code schaut, wird dort jedoch zunächst ein leerer Lob (empty_clob()) eingefügt. Anschließend "holt" sich das Java-Programm den LOB-Locator mit einem SELECT ... FOR UPDATE und füllt ihn mittels Java Streaming. Für die Datenbank ist das "Füllen" des LOB mit Java Streaming allerdings kein DML mehr. Verfolgt man diesen Weg, um LOB-Inhalte in die Datenbank zu laden, so können vorhandene Trigger nicht auf die später per Streaming geladenen LOB-Inhalte zugreifen ...
The trigger actually fires directly after the DML issued by the JDBC code. Since that DML created an empty LOB using the empty_clob() function the trigger saw only this empty lob. The actual lob contents were then written via Java Streaming - but this streaming isn't DML for the database so no trigger fires any more. And for that reason the trigger has only seen the empty lob - therefore it has correctly determined a zweo length. When the actual contents reached the database the trigger has already finished.
Und wie löst man das Problem? Mit temporären LOBs - wir ändern den Java-Code ein wenig um:
And how to solve that problem? With temporary LOBs - just change the java code a little bit:
import java.sql.*;
import java.io.*;
import oracle.jdbc.*;
import oracle.sql.*;

public class lobWriter2 {

  public static void main(String args[]) throws Exception {
    PreparedStatement pstmtInsert = null;
    ResultSet rsLobLocator = null;
    CLOB lobLocator = null;

    int iLobId = 1;

    Writer lobWriter = null;

    DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
    Connection con = DriverManager.getConnection(
      "jdbc:oracle:thin:@192.168.2.140:1521:orcl",
      "scott",
      "tiger"
    );
    con.setAutoCommit(false);

    // temporären LOB erzeugen und befüllen
    // create temporary lob and populate it
   
    lobLocator = CLOB.createTemporary(con, true, CLOB.DURATION_SESSION);
    lobWriter = lobLocator.setCharacterStream(0L);
    for (int i=0;i<1000;i++) {
        lobWriter.write("Dies ist ein Text ... zum " + i + "ten!\n");
    } 
    lobWriter.flush();
    lobWriter.close();

    // LOB in die Tabelle einfügen
    // Insert the LOB into the table
    pstmtInsert = con.prepareStatement ( 
      "insert into lobtable (id, dokument) values (?, ?)"
    );

    pstmtInsert.setInt(1, iLobId); 
    ((OraclePreparedStatement)pstmtInsert).setCLOB(2, lobLocator);
    pstmtInsert.execute();

    // WICHTIG: temporären LOB freigeben
    // IMPORTANT: free the temporary lob
    lobLocator.freeTemporary();

    pstmtInsert.close();
    con.commit();
    con.close();
  }
}
Nun wird zunächst ein temporärer LOB erzeugt, dieser wird befüllt und erst dann findet das SQL INSERT statt. Zum DML-Zeitpunkt ist der LOB auf Datenbankseite also vollständig zusammengestellt; wenn der Trigger feuert, "sieht" er also die Inhalte und die Spalte LAENGE in der Tabelle wird richtig vom Trigger gesetzt. Wir machen die Testabfrage nochmal:
This code creates a temporary lob, populates it and after that the SQL INSERT takes place. Now the LOB contents are present at DML time; the trigger can see the whole LOB and determine its length correctly. To check we'll perform our test query once more:
select id, dokument, laenge len_table, dbms_lob.getlength(dokument) len_lob, trigger_fired 
from lobtable

        ID DOKUMENT                                  LEN_TABLE    LEN_LOB T
---------- ---------------------------------------- ---------- ---------- -
         1 Dies ist ein Text ... zum 0ten!               33890      33890 Y
           Dies ist ein Text ... zum 1ten!
Mehr zum Thema temporäre LOBs (welche übrigens im TEMP-Tablespace gespeichert werden) findet Ihr in der Dokumentation. Wichtig ist bei temporären LOBs vor allem, dass Ihr sie freigebt, wenn Ihr fertig seid - dazu hatte ich in einem früheren Posting auch schon ein wenig was geschrieben.
You'll find more information on temporary LOBs (which are acually stored in the TEMP tablespace) in the Oracle Documentation. Most important is the correct free'ing of temporary lobs - an earlier posting contains some information about this.

Keine Kommentare:

Beliebte Postings