Re: Update table with CLOB condition

You are getting the error because CLOBS are not strings, even though you can insert/update small ones by supplying them as strings.

You may need to look into the dbms_lob package. e.g.

UPDATE CUST

        SET customer_id=99
        WHERE dbms_lob.substr(ATTENTION_MESSAGE,17,1) = 'test message back'

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Paul wrote in message ...


>Hi,

>I want to update table which contain CLOB column. I am able to update

>table with conditions, which not including CLOB column. But, when I put

>CLOB column in the condition column, it shows me ORA-00932: inconsistent

>datatypes. The detail information attached,

>

>Thanks for your help.

>

>SQL> desc cust;

> Name                            Null?    Type

> ------------------------------- -------- ----

> CUSTOMER_ID                     NOT NULL VARCHAR2(25)

> STATUS                          NOT NULL NUMBER(6)

> ATTENTION_MESSAGE                        CLOB

>

>SQL> select * from cust where customer_id=47;

>

>CUSTOMER_ID                  STATUS ATTENTION_MESSAGE

>------------------------- --------- ---------------------------------------

>47                                0 test message changed

>

>SQL> UPDATE CUST SET ATTENTION_MESSAGE='test message back'

>  2  WHERE CUSTOMER_ID='47';

>

>1 row updated.

>

>SQL> commit;

>

>Commit complete.

>

>SQL>  select * from cust where customer_id=47;

>

>CUSTOMER_ID                  STATUS ATTENTION_MESSAGE

>------------------------- --------- ---------------------------------------

>47                                0 test message back

>

>SQL> UPDATE CUST SET customer_id=99

>  2  WHERE ATTENTION_MESSAGE='test message back';

>SQL>

>WHERE ATTENTION_MESSAGE='test message back'

>      *

>ERROR at line 2:

>ORA-00932: inconsistent datatypes

>

>

>

>

>

>--

>Posted via CNET Help.com

>http://www.help.com/