ORA-00932 inconsistent datatypes expected - got CLOB

Question

Considering  that TEST SCRIPT is a CLOB  why when I run this simple query from SQL PLUS on Oracle  I get the error   ORA-00932  inconsistent datatypes  expected - got CLOB   I have been reading a lot of questions about the same error but none of those is running a direct query from SQLPLUS       UPDATE IMS TEST         SET TEST Category     just testing         WHERE TEST SCRIPT       something         AND ID                10000239     Full example   SQL gt  create table ims test    2  test category varchar2 30     3  test script clob    4  id varchar2 30    5      Table created   SQL gt  insert into ims test values   test1   something   10000239     1 row created   SQL gt  UPDATE IMS TEST   2  SET TEST Category     just testing    3  WHERE TEST SCRIPT       something    4  AND ID                10000239   WHERE TEST SCRIPT       something          ERROR at line 3  ORA-00932  inconsistent datatypes  expected - got CLOB

User · Answer

The same error occurs also when doing SELECT DISTINCT       lt CLOB column gt         If this CLOB column contains values shorter than limit for VARCHAR2 in all the applicable rows you may use to char  lt CLOB column gt   or concatenate results of multiple calls to DBMS LOB SUBSTR  lt CLOB column gt

User · Answer

I found that selecting a clob column in CTE caused this explosion  ie  with cte as       select         mytable1 myIntCol          mytable2 myClobCol     from mytable1     join mytable2 on       select myIntCol  myClobCol from cte where       presumably because oracle can t handle a clob in a temporary table   Because my values were longer than 4K  I couldn t use to char    My work around was to select it from the final select  ie  with cte as       select         mytable1 myIntCol     from mytable1   select myIntCol  myClobCol from cte join mytable2 on     where       Too bad if this causes a performance problem

User · Answer

Take a substr of the CLOB and then convert it to a char   UPDATE IMS TEST    SET TEST Category              just testing   WHERE to char substr TEST SCRIPT  1  9         something    AND ID                         10000239

User · Answer

The problem may lie in selected null values   in combination with a  CLOB-type column   select valueVarchar c1          valueClob c2          valueVarchar c3          valueVvarchar c4 of Table 1 union select valueVarchar c1          valueClob c2          valueVarchar c3          null c4 of table 2   I reworked the cursor  The first cursor is composed of four non-null columns  The second cursor selects three non-null columns  The null values   were injected into the cursorForLoop

User · Answer

I just ran over this one and I found by accident that CLOBs can be used in a like query      UPDATE IMS TEST     SET TEST Category     just testing     WHERE TEST SCRIPT    LIKE   something      AND ID                10000239     This worked also for CLOBs greater than 4K  The Performance won t be great but that was no problem in my case

User · Answer

You can t put a CLOB in the WHERE clause   From the documentation   Large objects  LOBs  are not supported in comparison conditions  However  you can use PL SQL programs for comparisons on CLOB data   If your values are always less than 4k  you can use  UPDATE IMS TEST     SET TEST Category              just testing     WHERE to char TEST SCRIPT        something     AND ID                         10000239    It is strange to search by a CLOB anyways    could you not just search by the ID column

[oracle] ORA-00932: inconsistent datatypes: expected - got CLOB

Examples related to oracle

Examples related to clob