Re: problems selecting clob field

On Jan 29, 12:24 pm, "Bruintje Beer" <m..._at_knoware.nl> wrote:
> Hi,
>
> I have the following query
>
> select * from orders where order_message = null
>
> The order_message is a clob field and stores the order as xml. Oracle does
> not allow me to do this query on a clob field

I presume you got the error:

    ORA-00932: inconsistent datatypes: expected - got CLOB (in the future, please post the actual ORA-nnnnn error as well as your Oracle
version, though it is not required for this Q'n)

The problem is not the CLOB, it is that you are doing a comparison operation (=) against NULL. All such operations will evaluate to "false"
including:

      'x' = null
      'x' != null
      null = null
      null != null

What you want is:

    select * from orders where order_message is null

This works for all data types including CLOB. If you have "= null" comparisons
in other (non-CLOB) columns in other queries, I'd highly recommend changing
them to IS NULL. All of them are almost certainly useless since they evaluate to false regardless of the data.

[You may be experiencing a sense of shock, confusion and/or dismay at this apparently counterintuitive behavior of comparison operators and the special NULL value, since it defies common notions of "(in)equality."
This behavior, second perhaps only to Oracle's choice of NULL to represent a "zero length string," is a common source of initial confusion -- as well as eternal holy war among database camps ;) ]

HTH, John Received on Mon Jan 29 2007 - 11:48:30 CST