oracle ORA-00932: 不一致的数据类型：预期 - 得到 CLOB
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12980038/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-00932: inconsistent datatypes: expected - got CLOB
提问by user1298925
Considering that TEST_SCRIPTis a CLOBwhy when I run this simple query from SQL*PLUS on Oracle, I get the error:
考虑到这TEST_SCRIPT就是CLOB为什么当我在 Oracle 上从 SQL*PLUS 运行这个简单查询时，我收到错误消息：
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
我一直在阅读很多关于相同错误的问题，但没有一个是从 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
采纳答案by Craig
You can't put a CLOB in the WHERE clause. From the documentation:
您不能在 WHERE 子句中放置 CLOB。从文档：
Large objects (LOBs) are not supported in comparison conditions. However, you can use PL/SQL programs for comparisons on CLOB data.
比较条件不支持大对象 (LOB)。但是，您可以使用 PL/SQL 程序来比较 CLOB 数据。
If your values are always less than 4k, you can use:
如果您的值始终小于 4k，则可以使用：
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?
无论如何，通过 CLOB 进行搜索是很奇怪的......你不能只通过 ID 列搜索吗？
回答by Franc Drobni?
The same error occurs also when doing SELECT DISTINCT ..., <CLOB_column>, ....
执行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>, ...).
如果此 CLOB 列在所有适用行中包含短于 VARCHAR2 限制的值，您可以使用to_char(<CLOB_column>)或连接多次调用的结果DBMS_LOB.SUBSTR(<CLOB_column>, ...)。
回答by kirby
Take a substr of the CLOB and then convert it to a char:
取 CLOB 的 substr，然后将其转换为字符：
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE to_char(substr(TEST_SCRIPT, 1, 9)) = 'something'
AND ID = '10000239';
回答by Taeke
The problem may lie in selected null values ??in combination with a CLOB-type column.
问题可能在于选定的空值与 CLOB 类型的列结合使用。
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 .
我重新设计了光标。第一个游标由四个非空列组成。第二个游标选择三个非空列。空值被注入 cursorForLoop 。
回答by Turo
I just ran over this one and I found by accident that CLOBs can be used in a like query:
我刚跑过这个，我偶然发现 CLOB 可以用在一个类似的查询中：
UPDATE IMS_TEST
SET TEST_Category = 'just testing'
WHERE TEST_SCRIPT LIKE '%something%'
AND ID = '10000239'
This worked also for CLOBs greater than 4K
这也适用于大于 4K 的 CLOB
The Performance won't be great but that was no problem in my case.
性能不会很好，但在我的情况下这没有问题。
回答by Bohemian
I found that selecting a clobcolumn in CTE caused this explosion. ie
我发现clob在 CTE中选择一列导致了这种爆炸。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.
大概是因为 oracle 无法处理临时表中的 clob。
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
因为我的值超过 4K，所以我不能使用to_char().
我的工作是从决赛中选择它select，即
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.
如果这会导致性能问题，那就太糟糕了。
