SQL ORA-00932: 不一致的数据类型：预期的 NUMBER 变得很长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21158559/
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 NUMBER got LONG
提问by user3181672
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.
我正在对 user_views 运行查询。“TEXT”列是 LONG 数据类型。因此，当我在 where 子句中使用此列时，我收到了预期的错误。
Error: ORA-00932: inconsistent datatypes: expected NUMBER got LONG
错误：ORA-00932：不一致的数据类型：预期 NUMBER 得到了 LONG
And the query is
查询是
SELECT view_name, text
FROM user_views
WHERE lower(text) LIKE '%company%'
How to solve this?
如何解决这个问题？
回答by DBO
Because TO_CLOB(LONG) converter/constructor needs physical pointer for storage (potentially 4GB...),
因为 TO_CLOB(LONG) 转换器/构造函数需要物理指针来存储（可能是 4GB...），
This should work for you (tested on 11gR2):
这应该对你有用（在 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');
回答by Fr_nkenstien
Please refer the below link:
请参考以下链接：
DESCRIPTION
描述
When you encounter an ORA-00932 error, the following error message will appear:
当您遇到 ORA-00932 错误时，将出现以下错误消息：
ORA-00932: inconsistent datatypes CAUSE
ORA-00932: 不一致的数据类型
You tried to perform an operation between two different datatypes, but the datatypes are not compatible.
您尝试在两种不同的数据类型之间执行操作，但数据类型不兼容。
RESOLUTION
解析度
The option(s) to resolve this Oracle error are:
解决此 Oracle 错误的选项是：
OPTION #1
选项1
Correct the operation so that the datatypes are compatible. You may want to use a conversion function such as: TO_DATE function, TO_NUMBER function, or TO_CHAR function. For a complete listing of our Oracle functions, go to our Oracle functions webpage.
更正操作以使数据类型兼容。您可能希望使用转换函数，例如：TO_DATE 函数、TO_NUMBER 函数或 TO_CHAR 函数。如需 Oracle 函数的完整列表，请访问我们的 Oracle 函数网页。
One example of this error is if you try to use the LIKE condition with a LONG datatype.
此错误的一个示例是，如果您尝试将 LIKE 条件与 LONG 数据类型一起使用。
For example, if you created the following table:
例如，如果您创建了下表：
CREATE TABLE suppliers
( supplier_id numeric(10) not null,
supplier_name long not null,
contact_name varchar2(50)
);
And then you tried to use the LIKE condition on the supplier_name column which as defined as a LONG data type:
然后您尝试在供应商名称列上使用 LIKE 条件，该列定义为 LONG 数据类型：
SELECT *
FROM suppliers
WHERE supplier_name LIKE 'IBM%';
Unfortunately, you can not use the LIKE condition on a LONG data type.
不幸的是，您不能在 LONG 数据类型上使用 LIKE 条件。
To correct this error, you can do one of the following:
要更正此错误，您可以执行以下操作之一：
- Not use the LIKE condition in your SQL (against the LONG datatype field).
- Consider modifying your table so that the supplier_name field is either a VARCHAR2 or CHAR field.
- Try writing a custom PLSQL function to convert a LONG to a VARCHAR2.
- 不要在 SQL 中使用 LIKE 条件（针对 LONG 数据类型字段）。
- 考虑修改您的表，以便供应商名称字段是 VARCHAR2 或 CHAR 字段。
- 尝试编写自定义 PLSQL 函数将 LONG 转换为 VARCHAR2。
OPTION #2
选项#2
This error can also occur if you try to use an Oracle function on a LONG datatype.
如果您尝试对 LONG 数据类型使用 Oracle 函数，也会发生此错误。
For example, if you created the following table:
例如，如果您创建了下表：
CREATE TABLE suppliers
( supplier_id numeric(10) not null,
supplier_name long not null,
contact_name varchar2(50)
);
And then you tried to use the TO_CHAR function on the supplier_name column which as defined as a LONG data type:
然后您尝试对被定义为 LONG 数据类型的供应商名称列使用 TO_CHAR 函数：
SELECT upper(supplier_name)
FROM suppliers;
You would receive the error message:
您将收到错误消息：
Unfortunately, you can not use Oracle functions on a LONG data type.
不幸的是，您不能在 LONG 数据类型上使用 Oracle 函数。
To correct this error, you can do one of the following:
要更正此错误，您可以执行以下操作之一：
- Not use Oracle functions in your SQL (against the LONG datatype field).
- Consider modifying your table so that the supplier_name field is either a VARCHAR2 or CHAR field.
- Try writing a custom PLSQL function to convert a LONG to a VARCHAR2.
- 不要在 SQL 中使用 Oracle 函数（针对 LONG 数据类型字段）。
- 考虑修改您的表，以便供应商名称字段是 VARCHAR2 或 CHAR 字段。
- 尝试编写自定义 PLSQL 函数将 LONG 转换为 VARCHAR2。
回答by user3181672
Create a table out of the user_views and query your requirement from the recently created new table.
从 user_views 创建一个表，并从最近创建的新表中查询您的需求。
create table my_tab as
select view_name myview,to_lob(text) mytext from user_views;
then
然后
select * from my_tab
where mytext like '%company%';
Thank you.
谢谢你。
