Re: Insert Update Delete CLOB in trigger

Saggi wrote:
> hmm....game is not yet over.
> All operations INSERT/UPDATE/DELETE works fine only when performed
> through backend using SQL tool (I am using DBArtisan). When I tried
> testing through front end application only DELETE operation worked
> fine. For INSERT and UPDATE operations on base table ACTIVITY, we got
> blank data in GEAM_ACTIVITY table for CLOB object ACTIVITY_COMMENT.
> I wonder if DB user which application is using is different than what I
> use to perform same operation thorugh SQL tool. But again trigger once
> set should work fine for all DB users having appropriate permissions
> and also DELETE operation works fine through front end.
> Any ideas what can be possible reasons..??
>
> Also I see lot of new lines inserted in ACTIVITY_COMMENT field. So if I
> have 1 line in base table for CLOB object then I get 9 lines of output
> of which first and last 4 lines are blank new lines. And middle line
> contains actual plain text format of original CLOB object. How to get
> rid of these new lines?
>

Please post your triggers. Are you using autonomous transactions in them? (you shouldn't) Any idea what DML is issued by your front-end application? Note that if DBMS_LOB or other LOB API is used in the front-end app, triggers will not see the changes. For example, the following sequence:

insert into activity values (:id, EMPTY_CLOB()); select activity_comment into :comment_var from activity
where r=:id FOR UPDATE;
dbms_lob.writeappend(:comment_var, 8, 'whatever');

will cause the trigger to fire on insert, but it will see empty CLOB, and subsequent calls to DBMS_LOB subprograms that actually populate it will not cause the insert trigger as well as any other triggers to fire again. If your application does something like this, your only option seems to be this: sweep the activity table for new/updated rows on regular intervals with a server-side job and filter new/updated comments into plaintext copy table. Now, if you could upgrade to 10g, you could use POLICY_FILTER() to filter online without an index and scrap that copy table altogether...

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Wed May 31 2006 - 01:58:46 CDT