问题描述
我正在尝试运行以下查询以查找包含给定关键字的视图:
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%';
我收到以下错误消息:
ORA-00932: inconsistent datatypes: expected CHAR got LONG
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Error at Line: 4 Column: 13
如果我只是从 ALL_VIEWS 中选择，那么我会在 TEXT 字段中看到查询 (TEXT).
if I just select from ALL_VIEWS than I see the query (TEXT) in the TEXT field.
我在这里做错了什么?
推荐答案
您的问题是 TEXT 是 LONG 类型 - 尽管 Oracle 很久很久以前就弃用了这种类型，但他们仍在自己的视图中使用它:-(
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 :-(
要将 LONG 转换为(可搜索的)CLOB，您可以使用 TO_LOB() 函数(请参阅 TO_LOB() 的 Oracle 文档.
To convert a LONG to a (searchable) CLOB, you can use the TO_LOB() function (see Oracle documentation for TO_LOB().
不幸的是，这不适用于简单的 SELECT 语句.您必须创建一个中间表:
Unfortunately, this doesn't work for simple SELECT statements. You'll have to create an intermediary table:
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%';
这篇关于Oracle 错误“数据类型不一致:预期 CHAR 得到 LONG"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！