Search notes:

ORA-00932: inconsistent datatypes: expected … got …

An ORA-00932 is thrown when a data type cannot be converted to another.

In Oracle versions prior to 23c, the message is ORA-00932: inconsistent datatypes: expected … got …, with the «error message improvement program», the messages changed to ORA-00932: expression is of data type …, which is incompatible with expected data type ….

CHAR / NUMBER

ORA-00932: inconsistent datatypes: expected CHAR got NUMBER

select
   case '1'
      when  2  then 'two'
      when '1' then 'one'
   end
from
   dual;

ORA-00932: inconsistent datatypes: expected NUMBER got CHAR

select
   case  1
      when  2  then 'two'
      when '1' then 'one'
   end
from
   dual;

ORA-00932: inconsistent datatypes: expected CHAR got LONG

The data type of the column data_default in the data dictionary view user_tab_columns is long which essentially makes it impossible to formulate a query-condition on that column when selecting from user_tab_columns:

select *
from
   user_tab_columns
where
   data_default like '%sysdate%';

ORA-00932: inconsistent datatypes: expected - got CHAR

Without improved error messages, the following statement throw ORA-00932: inconsistent datatypes: expected - got CHAR:

select
   to_lob('xyz')
from
   dual;

With improved error messages, it throws ORA-24856: invalid use of TO_LOB operator

ORA-00932: inconsistent datatypes: expected - got CLOB

It's not possible to execute a group by on a clob (or blob) value. The following select statement raises a ORA-00932: inconsistent datatypes: expected - got CLOB error:

create table tq84_ora_00932_clob (
    val number,
    txt clob
);
 
select
   txt,
   max(val) max_val
from
   tq84_ora_00932_clob
group by
   txt;

Similarly, it's not possible to use aggregate functions on LOB values, the following statement raises the same error:

select
   count(txt)
from
   tq84_ora_00932_clob;

Cleaning up:

drop table tq84_ora_00932_clob;

Comparing a CLOB with a VARCHAR2/string

This error is also thrown when a clob is compared with a varchar2 value (see ORA-22848: cannot use … type as comparison key).

dbms_lob.compare can be used to compare CLOB values with strings without throwing an error.

ORA-00932: inconsistent datatypes: expected - got -

The following statement throws ORA-00932: inconsistent datatypes: expected - got - (23c: ORA-00932: expression is of data type CHAR, which is incompatible with expected data type XMLTYPE):

select
   extractValue(         '<e>hello world</e>'   , '/e') e
from
   dual;

Using XMLType is better:

select
   extractValue( XMLType('<e>hello world</e>')  , '/e') e
from
   dual;

ORA-00932: inconsistent datatypes: expected NUMBER got CLOB

Preparation for demonstration:

create table tq84_src_varchar2 (id integer, val varchar2(20));
create table tq84_src_clob     (id integer, val clob        );
create table tq84_dest         (id integer, val number (7,2));
 
 
begin
   insert into tq84_src_varchar2 values (1, 111);
   insert into tq84_src_clob     select * from tq84_src_varchar2;
   commit;
end;
/

The following insert statement runs without problems, i. e. the varchar2 of the source table is implicitely converted to a number:

insert into tq84_dest select * from tq84_src_varchar2;

However, this insert statement throws ORA-00932: inconsistent datatypes: expected NUMBER got CLOB (or ORA-00932: expression is of data type CLOB, which is incompatible with expected data type NUMBER with improved error messages).

Apparently, a clob is not implicitely converted:

insert into tq84_dest select * from tq84_src_clob    ;

Cleaning up:

drop table tq84_dest;
drop table tq84_src_clob;
drop table tq84_src_varchar2;

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

ceil cannot be applied on a date datatype:

select ceil(sysdate) from dual;

ORA-00932: inconsistent datatypes: expected DATE got DATE

If you get what you want but still complain…

select
   adj_date(sysdate)
from
   dual;

ORA-00932: inconsistent datatypes: expected UDT got NUMBER

The following snippet throws ORA-00932: inconsistent datatypes: expected UDT got NUMBER.

create type tq84_result_line as object (
   num     number(2),
   txt     varchar2(10)
);
/

create type tq84_result_table as table of tq84_result_line;
/


declare
   res tq84_result_table;
begin

   select
      tq84_result_table(level, rpad('x', level, 'x')) --- <== Change to tq84_result_line to fix problem
   bulk collect into res
   from
      dual connect by level <= 10;
      
end;
/

ORA_00932: inconsistent datatypes: expected NLS PARAMETER got NUMBER

select
   to_char(dbms_random.value, '0.0000', 10)
from
   dual;

ORA-00932: inconsistent datatypes: expected TIMESTAMP got TIMESTAMP WITH TIME ZONE

select
   from_tz(systimestamp, 'Europe/Zurich')
from
   dual;

ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

create table tq84_avg_interval (
    crt   varchar2(10),
    tim  interval day(3) to second(0)
);

begin
   insert into tq84_avg_interval values ('abc', '1 01:01:01');
   insert into tq84_avg_interval values ('abc', '3 03:03:03');
   insert into tq84_avg_interval values ('xyz', '2 02:02:02');
   commit;
end;
/

--
-- In version prior to 23ai, the following statement throws 
-- ORA-00932: inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND
--
select
   crt,
   avg(tim)
from
   tq84_avg_interval
group by
   crt;

-- Cleaning up:
drop table tq84_avg_interval purge;

Note: in 23ai, aggregation using avg and sum over interval data types has become possible.