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

Question

考虑到 TEST_SCRIPT 是一个 CLOB，为什么当我在 Oracle 上从 SQL*PLUS 运行这个简单查询时，会收到错误:

ORA-00932: inconsistent datatypes: expected - got CLOB

我一直在阅读很多关于相同错误的问题，但这些问题都没有从 SQLPLUS 运行直接查询

    UPDATE IMS_TEST 
       SET TEST_Category  = 'just testing'  
     WHERE TEST_SCRIPT    = 'something'
       AND ID             = '10000239' 

完整示例:

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

Answer 1

不能将 CLOB 放入 WHERE 子句中。来自 documentation :

Large objects (LOBs) are not supported in comparison conditions.However, you can use PL/SQL programs for comparisons on CLOB data.

如果您的值始终小于 4k，您可以使用:

UPDATE IMS_TEST 
   SET TEST_Category           = 'just testing'  
 WHERE to_char(TEST_SCRIPT)    = 'something'
   AND ID                      = '10000239';

无论如何，通过 CLOB 进行搜索很奇怪......你能不能只通过 ID 列进行搜索？