Oracle错误“数据类型不一致:预期的CHAR变长" [英] Oracle Error "inconsistent datatypes: expected CHAR got LONG"

问题描述

我正在尝试运行以下查询来查找包含给定关键字的视图:

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 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变长"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！