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?
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.
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" ?
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>