Re: :old.clob and :new.clob in triggers
From: Mladen Gogala <gogala.mladen_at_gmail.com>
Date: Fri, 18 Jun 2010 12:48:14 +0000 (UTC)
Message-ID: <hvfpud$8hr$2_at_news.eternal-september.org>
On Thu, 17 Jun 2010 05:04:42 -0700, Vladimir M. Zakharychev wrote:
>> Hi,
>>
>> I've a table with a clob column. In a trigger that I need to write, I
>> need to compare the old and new values for this column.
>>
>> The code inside the trigger body looks like this:
>>
>> CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR
>> DELETE
>> ON tbl_clob
>> REFERENCING NEW AS NEW OLD AS OLD
>> FOR EACH ROW
>>
>> IF UPDATING THEN
>> IF :NEW.clob_col <> :OLD.clob_col
>> THEN
>> INSERT INTO ...
>> END IF;
>> END IF;
>> .....
>>
>> END;
>> /
>>
>> Is this the right way of comparing the old and new values for a clob,
>> inside the trigger? Please suggest.
>>
>> --
>> Thanks
>> DeB
Date: Fri, 18 Jun 2010 12:48:14 +0000 (UTC)
Message-ID: <hvfpud$8hr$2_at_news.eternal-september.org>
On Thu, 17 Jun 2010 05:04:42 -0700, Vladimir M. Zakharychev wrote:
> On Jun 17, 12:58 pm, Deb <debashish.majum..._at_gmail.com> wrote:
>> Hi,
>>
>> I've a table with a clob column. In a trigger that I need to write, I
>> need to compare the old and new values for this column.
>>
>> The code inside the trigger body looks like this:
>>
>> CREATE OR REPLACE TRIGGER tr_clob_test AFTER INSERT OR UPDATE OR
>> DELETE
>> ON tbl_clob
>> REFERENCING NEW AS NEW OLD AS OLD
>> FOR EACH ROW
>>
>> IF UPDATING THEN
>> IF :NEW.clob_col <> :OLD.clob_col
>> THEN
>> INSERT INTO ...
>> END IF;
>> END IF;
>> .....
>>
>> END;
>> /
>>
>> Is this the right way of comparing the old and new values for a clob,
>> inside the trigger? Please suggest.
>>
>> --
>> Thanks
>> DeB
>
> Comments embedded in the code below.
>
> ...
> -- this is to limit the scope of the following IF - we don't want -- to
> waste CPU cycles and I/O bandwidth comparing contents of old -- and new
> CLOB values when it definitely was not touched by the -- update and thus
> didn't change.
> IF UPDATING('CLOB_COL') THEN
> -- this is the right way of comparing LOBs. When you compare like --
> clob1 <> clob2, Oracle implicitly converts your CLOBs to -- VARCHAR2s
> (cutting off everything past the first 32k bytes) and -- compares
> strings. If the difference is somewhere past the first -- 32k bytes,
> this comparison will yield equality which is -- obviously not correct.
> DBMS_LOB.COMPARE() compares LOBs fully -- and returns 0 if the are
> equal. It does not throw exceptions -- for BLOB and CLOB comparisons,
> so it's pretty safe to use in -- triggers. Note that it can return
> NULL for some non-equal LOBs, -- so we need to use a variable and test
> if it's 0 or NULL - both -- cases indicate the LOBs are different.
> DECLARE
> RES INTEGER := DBMS_LOB.COMPARE(:NEW.CLOB_COL, :OLD.CLOB_COL);
> BEGIN
> IF RES IS NULL OR RES != 0 THEN
> -- do your stuff, they are different
> ...
> END;
> ...
>
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm) http://www.dynamicpsp.com
The "right way" of comparing CLOB values depends of how long the CLOB values are. Comparison is usually byte-wise, doing something like this:
char *p,*q;
while (*p++) {
if (*p != *q++) {
return(-1);
}
}
return(0);
If CLOB fields are large, creating MD5 sums and comparing them will actually be much faster and yet equally reliable as the comparison of the CLOB variables themselves.
-- http://mgogala.byethost5.comReceived on Fri Jun 18 2010 - 07:48:14 CDT