Sql – ORA-00932: inconsistent datatypes: expected NUMBER got LONG

oraclesql

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?

Best Answer

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

Related Solutions

C# – ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Most likely reason for this error is that the order of the parameters in your query does not match the order you add them to the Parameters collection. Oracle Data Access pretends to bind them by name, but actually binds them by order.

Sql – ORA-00932 inconsistent datatypes expected char got number

You can't sum a character value and all the returned values in a CASE statement must be the same datatype.

If you transform your SUM to a character using TO_CHAR() this still won't work as you're not grouping correctly, see this SQL Fiddle.

The easiest way to do this would be to return a 0 rather than 'INVALID', sum over the entire case statement and change this back to 'INVALID' if it's 0. It's not quite the same... don't store numbers in character columns?

select case when a = 0 then 'INVALID'
            else to_char(a)
       end
  from ( select sum( case when upper(txt) <> lower(txt) then 0
                          else to_number(txt)
                     end ) as a
           from class )

Here's a SQL Fiddle to demonstrate.

Best Answer

Related Solutions

C# – ORA-00932: inconsistent datatypes: expected DATE got NUMBER

Sql – ORA-00932 inconsistent datatypes expected char got number

Related Topic