Re: :old.clob and :new.clob in triggers
From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Fri, 18 Jun 2010 12:10:05 -0700 (PDT)
Message-ID: <123d8357-d3e8-40ae-b3ac-52faf626dcc2_at_z8g2000yqz.googlegroups.com>
On Jun 18, 4:48 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> 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.com
Date: Fri, 18 Jun 2010 12:10:05 -0700 (PDT)
Message-ID: <123d8357-d3e8-40ae-b3ac-52faf626dcc2_at_z8g2000yqz.googlegroups.com>
On Jun 18, 4:48 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> 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.com
And in addition to what I just wrote consider this: to compute the hashes, you will inevitably need to fully read both LOBs. When doing byte-wise comparison, you stop reading and comparing at the first mismatch, which might be close to the beginning. So hashing and bytewise comparison are equally I/O-intensive only in the worst case when both LOBs are of the same length and the very last byte is different, in other cases byte-wise comparison is cheaper.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Fri Jun 18 2010 - 14:10:05 CDT