ORA-00932: inconsistent datatypes: expected - got CLOB

ora-00932 inconsistent datatypes expected - got clob group by
ora-00932 inconsistent datatypes expected - got clob distinct
ora-00932 inconsistent datatypes expected - got clob union
ora-00932 inconsistent datatypes expected - got clob xmlagg
ora-00932: inconsistent datatypes: expected - got clob xmltable
ora-00932 inconsistent datatypes expected - got clob minus
ora-00932: inconsistent datatypes: expected - got clob hibernate
ora-00932 inconsistent datatypes expected - got clob insert

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

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?

KB45918: “inconsistent datatypes: expected – got CLOB” error , ORA-00932: inconsistent datatypes: expected - got CLOB. The CLOB data type is mapped to LongVarChar in Oracle. Same report runs  ORA-00932: inconsistent datatypes: expected - got CLOB. 00000 - "inconsistent datatypes: expected %s got %s" I finally corrected the query and eliminated the duplicate records by moving the condition in the WHERE clause to the INNER JOIN like this: SELECT A.* FROM TABLE_A A INNER JOIN TABLE_B B ON A.YEAR = B.YEAR AND A.ID = B.ID

The same error occurs also when doing SELECT DISTINCT ..., <CLOB_column>, ....

If this CLOB column contains values shorter than limit for VARCHAR2 in all the applicable rows you may use to_char(<CLOB_column>) or concatenate results of multiple calls to DBMS_LOB.SUBSTR(<CLOB_column>, ...).

ORA-00932: inconsistent datatypes: expected, SELECT TO_CLOB(comment_desc) FROM sys_comment; Error occurs: ERROR at line 1: ORA-00932: inconsistent datatypes: expected - got  ORA-00932: inconsistent datatypes: expected - got CLOB One thing I must share is, I have explained what I was facing and that is explained below. If you are facing same problem then it is pleasure for me, my post is helpful for you. But if this is not the case then please visit following links may be you can find some thing for you.

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';

Ask TOM "Convert clob to char to get distinct", Hii ASKTOM, select distinct clob_column_name from table_name; I get following error:- ora-00932: inconsistent datatypes;expected-got CLOB ORA-00932: inconsistent datatypes: expected - got CLOB 00932. 00000 - "inconsistent datatypes: expected %s got %s" anybody can help me to fix this error? thanks oracle select join clob

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 .

'ORA-00932: Inconsistent Datatypes: Expected, Oracle Financial Services Analytical Applications Infrastructure - Version 7.3 and later: 'ORA-00932: Inconsistent Datatypes: Expected - Got  Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information. ORA-00932: inconsistent datatypes: expected DATE got

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.

23432, ORA-00932: inconsistent datatypes: expected - got CLOB. One thing I must share is, I have explained what I was facing and that is explained  Informatica cannot read CLOB, you need to convert CLOB to a varchar before data fed to Informatica. Use DBMS_LOB.SUBSTR(COL_NAME,250,1) to convert to varchar, COL_NAME is the CLOB column

ORA-00932: inconsistent datatypes: expected, "ORA-00932: inconsistent datatypes: expected - got CLOB" running Data Explorer profile on object with CLOB datatype column  I have table T1 ( id number, content CLOB ) and table T2 ( id number, content CLOB ) where T2 is snapshot of remote table. Both of them have identical structure. I want to select all the records from T1 and T2, thus I am trying, "select id, content from T1 UNION select id , content from T2 ;" This returns ORA-00932: inconsistent datatypes

ORA-00932: inconsistent datatypes: expected CLOB got CHAR, ORA-00932: inconsistent datatypes: expected - got NCLOB #255. Open. pikhovkin opened this issue on Jan 9, 2018 · 4 comments. Open  AS SELECT statement if you are creating an index-organized table" (before posting my answer, I tried it on 11gR2, and it worked as expected). – Frank Schmitt Dec 17 '14 at 11:09 Yes, i also tried and works, but why it's not possible do this on the fly, i mean in a simple sql without creating a table. i really don't understand this issue.

ORA-00932: inconsistent datatypes: expected - got CLOB, ORA-00932: inconsistent datatypes: expected CLOB got CHAR. in source qualifier the sql query is SELECT FIELD_A,FIELD_B,FIELD_C,.. FROM TABLEA; ORA-00932: inconsistent datatypes: expected - got CLOB . Hi Experts , Am facing same issue while executing below query select logs.countrycode,logs.endsystem,logs.merchantid from (select countrycode,endsystem,SUBSTR(part2, 1, INSTR(part2, ';') - 1)MerchantId from (select countrycode,endsystem,SUBSTR(part1, INSTR(part1,'=') + 1) part2 from (select countrycode,endsystem,SUBSTR(additionalinfo

Comments
  • 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?
  • 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, this query will work but it will fail if you are trying to insert more than 4000 characters in clob field
  • @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?
  • yes they are really only 10-ish character and I made sure my single quotes are correctly typed too.
  • CLOB field can not be used in MINUS operation as well.
  • If your values might be over 4K, you can use where dbms_lob.compare(testscript,:var2) = 0 see stackoverflow.com/a/12590223/1845672
  • @Jewels : I got a 404 too, then tried the linked again and it worked - weird
  • 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.
  • "Comparison Conditions" also include SELECT DISTINCT and GROUP BY
  • also applies to CLOB in GROUP BY clause. Same reason. :-)