Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Janet.

Asked: October 03, 2018 - 7:15 pm UTC

Last updated: February 03, 2022 - 6:27 pm UTC

Version: Oracle 12c

Viewed 10K+ times! This question is

You Asked

Hello,
I'm trying within a trigger to compare two clobs to see if there is any change. I am trying to prevent any unnecessary writes.
Prior to writing to audit trail I compare two values.
 v_clob_compare := dbms_lob.compare( :old.clob_text, :new.clob_text );

IF (v_clob_compare <> 0 OR v_clob_compare IS NULL) and length(:old.clob_text) <> length(:new.clob_text) THEN

------------
at this point I write to audit trail. Does a length comparison on a clob produce anything of value or was it overkill? Currently it does write to audit trail when there is no change which is what I am trying to prevent (users hitting save when there are no changes is the issue.) Comments?

and Chris said...

dbms_lob.compare returns:

INTEGER: 0 if the comparison succeeds, nonzero if not.

NULL, if any of amount, offset_1 or offset_2 is not a valid LOB offset value. A valid offset is within the range of 1 to LOBMAXSIZE inclusive.


So yes, the length comparison is unnecessary. If you're getting false positives (a difference when there shouldn't be) please give us a complete test case show this happening. i.e.:

- create table
- inserts
- all the code needed to reproduce the problem.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Compare lob fields

Carlos, February 03, 2022 - 10:42 am UTC

I am using dbms_lob.compare in a trigger with :NEW and :OLD values and dbms_lob.length with the same variables. And when my process update the same BLOB value that exists previosuly, compare function returns 1 but length function returns the same value for :NEW and :OLD values.

Could be possible that dbms_lob.compare with :OLD values does not work?
Chris Saxon
February 03, 2022 - 6:27 pm UTC

If compare returns 1, the values are different - what is it you're expecting to happen?

Can you provide a test case?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library