ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Question

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?

Answer 1

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

Answer 2

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.