Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Janet.

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

Last updated: January 23, 2025 - 1:52 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

  (3 ratings)

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?

Unexpected behaviour, getting NULL instead of 0

Hans Olo, January 22, 2025 - 3:59 pm UTC

When CLOB_1 and CLOB_2 are both NULL then I get NULL instead of an integer as expected. But this is not even my core problem here. It also returns NULL, if CLOB_1 is NULL and CLOB_2 has some value...

Therefore I have to use an NVL for every CLOB. Knowing this it's all good, but the documention didnt give me a single hint about this weird behaviour.
Chris Saxon
January 22, 2025 - 4:57 pm UTC

Why do you expect anything different? Most conditions return NULL/UNKNOWN if either argument is NULL.

what? simple logic

Hans Olo, January 23, 2025 - 9:16 am UTC

Because they have different values and the function is called "compare" ?

Chris Saxon
January 23, 2025 - 1:52 pm UTC

When you compare NULL using = or <> the result is NULL:

select null = 1 equal, null <> 1 not_equal;

EQUAL  NOT_EQ
------ ------
<null> <null>

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