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


>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'.