Oracle update CLOB with varchar

Question

I have a table TABLENAME with COLUMNNAME data type is CLOB. For example it's contains only 3 rows with 1st row of COLUMNNAME value is 123,456,789 and the 2nd is NULL and the 3rd is an empty string

And I have this query

UPDATE TABLENAME
   SET COLUMNNAME = COLUMNNAME || CASE 
                                    WHEN TRIM(COLUMNNAME) = '' OR COLUMNNAME IS NULL THEN  
                                         '098765'
                                    ELSE ',098765'
                                  END

when I run this query, I've got error message ORA-00932: inconsistent datatypes: expected - got CLOB

how to fix it?

I use Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit

Gary Myers Gary Myers · Accepted Answer · 2010-11-03T05:24:58

Firstly, TRIM(COLUMNNAME) = '' will always fail as '' is NULL and doesn't get matched by an equals.

Try using PL/SQL :

declare
  v_clob clob := ',098765';
begin
 update t
 set val = val || v_clob
 where val is not null; 
 --
 UPDATE T
 SET val =  '098765' 
 WHERE val is null;
 --
end;
/

A string literal is defined as a CHAR which converts nicely to a VARCHAR2 but through incompatibility errors when you try to treat it like a CLOB.

Oracle update CLOB with varchar

1 Answers