Oracle/PLSQL: ORA-00932 Error

Error:

Cause:

Action:

1. Correct the operation so that the datatypes are compatible. You may want to use a conversion function such as: to_date, to_number, or to_char. For a complete listing of our Oracle functions, go to our Oracle functions webpage.

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:

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.

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:

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.