Re: :old.clob and :new.clob in triggers

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: Thu, 17 Jun 2010 05:04:42 -0700 (PDT)
Message-ID: <e84ccd4f-a1f6-458e-b63a-7ba3fd44d3c3_at_u7g2000yqm.googlegroups.com>

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 Received on Thu Jun 17 2010 - 07:04:42 CDT