APEX

Hi,

I have a table with a clob column, and an APEX generated form for editing it. A textarea is used for editing the clob column. This setup was working fine until I added a trigger onto the table. APEX does the update (Automatic Row Processing (DML)) in an after-submit process, but within the trigger, the value of the CLOB column in :NEW is empty. The code below is a cut down version to illustrate my problem. The value of :NEW.clob_col is empty.

CREATE OR REPLACE TRIGGER mytrigger
  BEFORE INSERT OR UPDATE
  ON myschema.mytable
  FOR EACH ROW
DECLARE
BEGIN
  DBMS_OUTPUT.put_line ('Old value of CLOB column: ' || :OLD.clob_col);
  wwv_flow.DEBUG ('Old value of CLOB column: ' || :OLD.clob_col);
  DBMS_OUTPUT.put_line ('Proposed new value of CLOB column: ' || :NEW.clob_col);
  wwv_flow.DEBUG ('Proposed new value of CLOB column: ' || :NEW.clob_col);

  -- Do some processing with :NEW.clob_col.......
END mytrigger;

There are a few points to be aware of -
1. If I update the column by issuing a normal sql update statement in TOAD, SqlPlus, SQLDeveloper then the trigger works perfectly. All my processing works, the debug statements print out as you'd expect.
2. If I edit the value in a TOAD datagrid, it fails.
3. I can read all other columns in :NEW - only the CLOB column doesn't work.
4. The inserts and updates themselves work - the clob data is being correctly written to the table, it's just invisible as it passes through the trigger.

So, the trigger itself does work, it just doesn't work when APEX does the update. Also, interestingly it doesn't work if I edit it through TOAD's datagrid, but does work if I issue a SQL update statement from TOAD. Something about the way that both APEX and TOAD are doing updates is causing the clob data to be invisible to the trigger.

I'm using APEX 2.2

Hope you can help.

Mark