Re: Insert Update Delete CLOB in trigger

If CTX_DOC.FILTER() accesses base table, how does 1st DML operation (after creating trigger) works fine and subsequent DML operation gives error? Any idea..??
We use Oracle 9i, POLICY_FILTER procedure is not available in CTX_DOC package.

I also tried creating seprate triggers for INSERT, UPDATE and DELETE. Now UPDATE and DELETE works fine for me, as in UPDATE trigger I use AUTONOMOUS TRANSACTION and in DELETE trigger there is no reference required to base table. Only problem is with INSERT. If I try AUTONOMOUS TRANSACTION in INSERT trigger, it gives me following error:
DRG-10826: no document with the specified textkey is found. It seems even if my trigger is of type AFTER INSERT the new record is not inserted (or transaction is not committed) into base table. Your comments pls....

Below is INSERT trigger code.

CREATE OR REPLACE TRIGGER CONVERT_TO_PLNTXT_INSERT AFTER INSERT
ON ACTIVITY
REFERENCING NEW AS NEW
FOR EACH ROW
DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;
    V_DOC CLOB;
    V_PLAIN CLOB;
    R VARCHAR2(16);
BEGIN     SELECT :NEW.STAMP2, :NEW.ACTIVITY_COMMENT     INTO R, V_DOC
    FROM DUAL;     dbms_lob.createtemporary(V_PLAIN, TRUE);     dbms_lob.append(V_PLAIN, V_DOC);
    ctx_doc.filter('RTF_ACT_COMMENT', R, V_PLAIN, plaintext => TRUE);

    COMMIT;
    dbms_lob.freetemporary(V_PLAIN);     

END; Thanks
Saggi Received on Thu May 25 2006 - 09:27:59 CDT