ORA-00932:数据类型不一致:预期-获得CLOB [英] ORA-00932: inconsistent datatypes: expected - got CLOB

问题描述

考虑到TEST_SCRIPT是CLOB，为什么当我在Oracle上从SQL * PLUS运行此简单查询时，出现错误:

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

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

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' 

完整示例:

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

推荐答案

您不能在WHERE子句中放置CLOB.从文档:

You can't put a CLOB in the WHERE clause. From the documentation:

在比较条件下不支持大对象(LOB). 但是，您可以使用PL/SQL程序对CLOB数据进行比较.

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

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

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

无论如何，通过CLOB进行搜索是很奇怪的..您不能仅通过ID列进行搜索吗?

It is strange to search by a CLOB anyways.. could you not just search by the ID column?

这篇关于ORA-00932:数据类型不一致:预期-获得CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！