CODEWITHSUNDEEP
sqloracle-database
user3181672
user3181672

Reputation: 119

ORA-00932: inconsistent datatypes: expected NUMBER got LONG

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.

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

And the query is 

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

How to solve this?

Upvotes: 7

Views: 55607

Answers (3)

DBO
DBO

Reputation: 31

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

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

Upvotes: 3

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

Please refer the below link:

ORA-00932

DESCRIPTION

When you encounter an ORA-00932 error, the following error message will appear:

ORA-00932: inconsistent datatypes CAUSE

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:

OPTION #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.

One example of this error is if you try to use the LIKE condition with a LONG datatype.

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:

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

Unfortunately, you can not use the LIKE condition on a LONG data type.

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.

OPTION #2

This error can also occur if you try to use an Oracle function on a LONG datatype.

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:

SELECT upper(supplier_name)
FROM suppliers;

You would receive the error message:

Unfortunately, you can not use Oracle functions on a LONG data type.

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.

Upvotes: 2

user3181672
user3181672

Reputation: 119

Create a table out of the user_views and query your requirement from the recently 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%';

Thank you.

Upvotes: 2

Related Questions