Adsense Ad

Thursday, 25 May 2017

Oracle: ORA-00932 Error Message

ORA-00932 Error Message

Learn the cause and how to resolve the ORA-00932 error message in Oracle.

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. 
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%';
You would receive the following error message:
Oracle PLSQL
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 following error message:
Oracle PLSQL
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.

No comments: