Re: Insert Update Delete CLOB in trigger

> 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....

That's correct, your autonomous transaction doesn't see uncommitted inserted row. Because CTX_DOC.FILTER attempts to fetch the document to be filtered from the base table the index is built on, and the document is not yet there, you get the error. Again, I should stress that
you do NOT need to

    dbms_lob.createtemporary(V_PLAIN, TRUE);     dbms_lob.append(V_PLAIN, V_DOC);

because FILTER truncates the output CLOB, and if it's NULL, then a temporary CLOB is allocated automatically.

Now, since you're on 9i, you can try to use CTX_DOC.IFILTER(). The only problem with it is that it accepts BLOB as input. However, you can write a function that will 'cast' your CLOB to a BLOB (or google for it, I recall posting an example to newsgroups some time ago.) You can't control the output with that function (a limitation 10g's POLICY_FILTER addresses,) so it probably will always be either plain text or HTML. But IFILTER doesn't need a text index and doesn't need access to the base table. If this is not what you need, the only solution I can think of is this:

CREATE GLOBAL TEMPORARY TABLE TMP$FILTER ( R VARCHAR2(16), C CLOB )
ON COMMIT DELETE ROWS
/
CREATE INDEX I$CTX_FILTER ON TMP$FILTER(C) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS 'FILTER CTXSYS.INSO_FILTER' /
CREATE OR REPLACE TRIGGER TRG$R_AI$ACTIVITY AFTER INSERT
ON ACTIVITY
FOR EACH ROW
DECLARE
    L_DOC CLOB;
BEGIN
    INSERT INTO TMP$FILTER VALUES (:NEW.R, :NEW.ACTIVITY_COMMENT);     CTX_DOC.FILTER('I$CTX_FILTER',:NEW.R, L_DOC, PLAINTEXT=>TRUE);     INSERT INTO GEAM_ACTIVITY VALUES(:NEW.R, L_DOC); END;
/

What this will achieve: you will insert the new document into your own temporary table, filter using its index and then insert filtered document into the target table. As soon as transaction commits, temporary table cleans up automatically (but information about new document will be probably retained in I$CTX_FILTER, so you will need to clean it up regularly using CTX_DDL.OPTIMIZE_INDEX - I am not sure about this, but it's very probable because Text puts its own triggers on indexed tables to keep track of what needs to be [re]indexed.)

Note that I didn't test this and am not sure this will work as I think it will. :)

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Thu May 25 2006 - 12:34:05 CDT