ORA-00932:数据类型不一致:预期的数字长 [英] ORA-00932: inconsistent datatypes: expected NUMBER got LONG

问题描述

我正在对user_views进行查询. "TEXT"列为LONG数据类型.因此，当我在where子句中使用此列时，出现了预期的错误.

I am running a query on user_views. The "TEXT" column is of LONG datatype. So, when I use this column in where clause, I am getting error which is expected.

错误:ORA-00932:数据类型不一致:预期的NUMBER个很长

Error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG

查询为

SELECT view_name, text
FROM user_views 
WHERE lower(text) LIKE '%company%'

如何解决这个问题?

推荐答案

因为TO_CLOB(LONG)转换器/构造函数需要物理指针进行存储(可能为4GB ...)，

Because TO_CLOB(LONG) converter/constructor needs physical pointer for storage (potentially 4GB...),

这应该对您有效(已在11gR2上测试):

This should work for you (tested on 11gR2):

CREATE TABLE DBO.MY_ALL_VIEWS AS 
SELECT DV.owner, DV.view_name, TO_LOB(DV.text) AS text
FROM ALL_VIEWS DV;

SELECT count(*) 
FROM DBO.MY_ALL_VIEWS 
WHERE REGEXP_LIKE(TEXT,'(company)+','i');

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