Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.tools -> Re: Oracle varchar problem - Difficult
If you want to risk undocumented features,
try the sys_op_map_nonnul() function.
It seems to return 0xFF if the parameter is null.
update blob
set dirty='Y',value=new_value
where sys_op_map_nonnull(value) <>
sys_op_map_nonnull(new_value);
-- Jonathan Lewis Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk Practical Oracle 8i: Building Efficient Databases Publishers: Addison-Wesley Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html Christopher Burke wrote in message <9040EFD5CCraznar_at_61.9.128.12>...Received on Fri Feb 09 2001 - 14:39:33 CST
>I have a table - say called 'blob', in it are two fields called 'value' and
>'dirty'.
>
>The field value holds an arbitrary string .. dirty holds a 'Y' or an 'N' -
>which determine if the record has been read and process by another program.
>
>Here is the problem ... how do I do the following :
>
>update blob
> set dirty='Y',value=new_value
> where value <> new_value;
>
>... but do it correctly.
>
>If value is currently '' (blank is a valid value) then it is treated as
>null, if new_value is '' then it is treated as a null.
>
>That means that I cannot update a '' with a new value, nor can I update a
>value with a ''.
>
>Why - because "null = xxx" and "null <> xxx" always both return false.
>
>I changed to
>
>update blob
> set dirty='Y',value=new_value
> where NVL(value,'askhda') <> NVL(new_value,'askhda');
>
>But this isn't good either - because the 'value' is arbitrary, and I need
>to be able to store any value. The above example would be unable to store
>'askhda'.