ORA-00932: inconsistent data types: expected NUMBER received LONG

Question

I run the request in user_views. The TEXT column is of the LONG data type. Therefore, when I use this column in the where clause, I get an error that is expected.

Error: ORA-00932: inconsistent data types: NUMBER LONG expected

And request

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

How to solve this?

Answer 1

Create a table from user_views and query your requirement from a newly created new table.

 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%'; 

Thanks.

Answer 2

Since the TO_CLOB converter / constructor (LONG) requires a physical pointer to store (possibly 4 GB ...),

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'); 

Answer 3

Please refer to the following link:

ORA-00932

DESCRIPTION

If error ORA-00932 occurs, the following error message appears:

ORA-00932: inconsistent data types CAUSE

You tried to perform an operation between two different data types, but the data types are incompatible.

Decision

The parameter to solve this Oracle error:

OPTION number 1

Correct the operation so that the data types are compatible. You can use a conversion function such as: TO_DATE, TO_NUMBER or TO_CHAR. A complete list of our Oracle functions can be found on our Oracle functions web page.

One example of this error is that you are trying to use a LIKE condition with a LOTG data type.

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 in the provider_name column, which, as defined as the LONG data type:

 SELECT * FROM suppliers WHERE supplier_name LIKE 'IBM%'; 

Unfortunately, you cannot use the LIKE clause for the LONG data type.

To fix this error, you can do one of the following:

• Do not use the LIKE clause in your SQL (in the LONG datatype field).
• Consider modifying the table so that the provider_name field is a VARCHAR2 or CHAR field.
• Try writing a custom PLSQL function to convert LONG to VARCHAR2.

OPTION number 2

This error can also occur if you try to use the Oracle function for the LONG data type.

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 in the provider_name column, which is defined as the LONG data type:

 SELECT upper(supplier_name) FROM suppliers; 

You will receive an error message:

Unfortunately, you cannot use Oracle functions for the LONG data type.

To fix this error, you can do one of the following:

• Do not use Oracle functions in your SQL (in the LONG datatype field).
• Consider modifying the table so that the provider_name field is a VARCHAR2 or CHAR field.
• Try writing a custom PLSQL function to convert LONG to VARCHAR2.