Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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> create table ims_test(
  2  test_category varchar2(30),
  3  test_script clob,
  4  id varchar2(30)
  5  );
Table created.
SQL> insert into ims_test values ('test1','something','10000239');
1 row created.
SQL> 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
                Does your actual script have the Microsoft curly quotes that were present in your question?  Or was that merely an artifact of creating the question?  In the actual script, is the string 'something' actually longer than 4000 characters?
– Justin Cave
                Oct 19, 2012 at 18:14
                sorry, what do you mean by curly quotes? Are you suggesting the quotes are not  the right quotes ?  and the string s=in this example is just that. I might have larger strins in the real product but I am just trying to see if this simple query works.
– user1298925
                Oct 19, 2012 at 18:19
                @user1298925, this query will work but it will fail if you are trying to insert more than 4000 characters in clob field
– rs.
                Oct 19, 2012 at 18:20
                @user1298925 - In the question you posted, the quotes around the string just testing were Microsoft curly quotes ’ rather than normal single quotes '.  I corrected that in my edit but I'm not sure whether that is something that affects your original script or something that was introduced in the process of creating and posting your question.  Are you saying that in the example scripts the strings are really only 10-ish characters long?
– Justin Cave
                Oct 19, 2012 at 18:25
                yes they are really only 10-ish character and I made sure my single quotes are correctly typed too.
– user1298925
                Oct 19, 2012 at 18:33
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?
                If your values might be over 4K, you can use where dbms_lob.compare(testscript,:var2) = 0 see stackoverflow.com/a/12590223/1845672
– Roland
                Jan 11, 2016 at 13:03
                just another oracle's weird error message. there is nothing related among usage of clob on where clause and 'expected - got CLOB'. thank you by the way.
– yılmaz
                Aug 16, 2018 at 16:18
UPDATE IMS_TEST 
  SET TEST_Category           = 'just testing' 
WHERE to_char(substr(TEST_SCRIPT, 1, 9))    = 'something'
  AND ID                      = '10000239';
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.
                You can use LIKE without % wildcards. This makes sure you only match exact values (and should be faster but I don't know if it actually is in practice). WHERE TEST_SCRIPT    LIKE 'something'
– Scar
                Jun 17, 2021 at 11:44
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.
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 .
Generally to solve this problem, just use TO_CHAR, example if you convert convert date to 'DD/MM/RRR hh24:mi:ss' format.
TO_CHAR(date_value ,''DD/MM/RRRR hh24:mi:ss'') 
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.