ORA-00932 : inconsistent datatypes :

In my previous articles, I have given the proper idea of different oracle errors, which are frequently come. In this article, I will try to explain another common error, which has been searched on google approximately 9 k times per month. ORA-00932 is another simple error, which is commonly come in database when user tries to use inconsistent datatype. The datatype is most important in Oracle database management system. When user tries to execute the operation with two different datatypes, which are incompatible, this error will come.

Why this error will come?

The main cause of ORA-00932 error is using two different incompatible datatypes for executing query without converting it using function. I will try to produce this error using different scenarios.

Incompatible datatype:

As I explained that, this error will come because of use of incompatible datatype. Let’s take an example for the same

Example:

CREATE TABLE T_error

(creation_date DATE);

INSERT INTO T_error

values(125);

commit;

Output :

SQL Error: ORA-00932: inconsistent datatypes: expected DATE got NUMBER

00932. 00000 –  “inconsistent datatypes: expected %s got %s”

*Cause:

*Action:

Modify system tables:

When user tries to modify the system table then this error will come.

Using Long Datatype:

As you all know that user can use long datatype once in the table. When user tries to use like operator with long datatype column then this error will occur. Let’s try to produce this error

Queries :

CREATE TABLE t_long

(name long);

INSERT INTO t_long

VALUES(‘Amit’);

commit;

select * from t_long where name like ‘Ami%’;

Output :

ORA-00932: inconsistent datatypes: expected CHAR got LONG

00932. 00000 –  “inconsistent datatypes: expected %s got %s”

*Cause:

*Action:

Error at Line: 8 Column: 27

Using Long Datatype with upper function (any functions) :

When user tries to use the long datatype with upper function this error will come.

Query:

select upper(name) from t_long;

Output :

ORA-00932: inconsistent datatypes: expected CHAR got LONG

00932. 00000 –  “inconsistent datatypes: expected %s got %s”

*Cause:

*Action:

Error at Line: 8 Column: 13

Resolution of this error:

I have explained different scenarios of this error. In this section I will try to explain the solution of the error.

Use Conversion functions:

It is proposed to use the conversion functions while using the inconsistent datatype. Means if user is entering the number value to date function then TO_DATE function needs to be used by the user.Just like that user needs to try different Sql conversion functions like TO_NUMBER,TO_CHAR

Query should be :

INSERT INTO T_error

VALUES(to_date(’12-MAY-10′,’DD-MON-YYYY’));

Do not try to modify System tables :

Don’t try to modify the system tables as user can not modify oracle in built system tables.

 For Long datatype:

When user is using long datatype following needs to be done :

1.Do not use like operator in SQL with long datatype .

2.Do not use functions with long datatype.

3.Try to convert long datatype column to varchar2 type.

These are above possible solutions of resolving the error ORA-00932.Hope you like this article.