Help with CLOB

Ok, I have a table with a couple of CLOB columns and a package which inserts into that table.

That table also has an insert/update trigger on it which takes that same row and inserts it into a history table.

Here is what is happening:

On the initial INSERT into the base table, the record is stored, the trigger is fired and the row is stored into the history table.

However, if I perform an UPDATE to the base table, the row is updated in the base table, the trigger fires and puts a new row into the history table, except that the CLOB columns on the new record in the history table are empty.

The trigger is pretty simple:

CREATE OR REPLACE TRIGGER MESSAGES_HISTORY_TRG BEFORE INSERT OR UPDATE ON MESSAGES FOR EACH ROW DECLARE BEGIN
  INSERT INTO messages_history
  (message_id, variables, name, body, created, created_by, entry_date, body_new)   VALUES
   (NEW.message_id, :NEW.variables, :NEW.name, :NEW.body, :NEW.created, :NEW.created_by, SYSDATE, :NEW.body_new); END;
/

The code for the package is straight forward too:

  UPDATE messages

  SET sec_id       = NVL(p_sec_id, sec_id),
      variables    = NVL(p_variables, variables),
      name         = NVL(p_name, name),
      body         = p_body,
      last_changed = SYSDATE,
      changed_by   = NVL(p_changed_by, changed_by),
      status       = NVL(p_status, status),
      body_new     = p_body_new
  WHERE message_id = p_message_id;

So, I do not understand why the UPDATE is failing with regards to the CLOB columns. The base table is fine, so I know the values are there (unless it is not even updating the CLOB columns).

At any rate, does anyone see anything wrong, or perhaps a better way of doing this?

Many thanks in advance. Received on Wed Jun 05 2013 - 18:52:19 CEST