SQL Oracle 错误“不一致的数据类型：预期的 CHAR 得到了 LONG”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27523530/
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
Oracle Error "inconsistent datatypes: expected CHAR got LONG"
提问by developer82
I'm trying to run the following query to find views containing a given keyword:
我正在尝试运行以下查询来查找包含给定关键字的视图：
select *
from ALL_VIEWS
where OWNER = 'SALESDBA'
and TEXT like '%rownum%';
I'm getting the following error message:
我收到以下错误消息：
ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 4 Column: 13
if I just select from ALL_VIEWS than I see the query (TEXT) in the TEXT field.
如果我只是从 ALL_VIEWS 中选择而不是在 TEXT 字段中看到查询 (TEXT)。
What am I doing wrong here?
我在这里做错了什么？
回答by Frank Schmitt
Your problem is that TEXT is of type LONG - although Oracle deprecated this type a long, long time ago, they're still using it in their own views :-(
您的问题是 TEXT 的类型为 LONG - 尽管 Oracle 很久很久以前就弃用了这种类型，但他们仍在自己的视图中使用它:-(
To convert a LONG to a (searchable) CLOB, you can use the TO_LOB()function (see Oracle documentation for TO_LOB().
要将 LONG 转换为（可搜索的）CLOB，您可以使用该TO_LOB()函数（请参阅TO_LOB() 的 Oracle 文档。
Unfortunately, this doesn't work for simple SELECTstatements. You'll have to create an intermediary table:
不幸的是，这不适用于简单的SELECT语句。您必须创建一个中间表：
create table search_all_views as
select av.owner, av.view_name, to_lob(text) as text_clob
from ALL_VIEWS av;
Then, you can search using that table:
然后，您可以使用该表进行搜索：
select *
from search_all_views
where text_clob like '%rownum%';
