Oracle / PLSQL: ORA-01448 Error Message

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

Description

When you encounter an ORA-01448 error, the following error message will appear:

• ORA-01448: index must be dropped before changing to desired type

Cause

You tried to execute a ALTER TABLE MODIFY attempting to change the data type of an indexed column to a LONG datatype. Columns with the LONG datatype can not be indexed.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Drop all indexes that reference the column that you wish to change to a LONG datatype.

Option #2

Select a different column to index.

For example, if you had a table called suppliers defined as follows:

CREATE TABLE suppliers
( supplier_name varchar2(50),
  city varchar2(35),
  state varchar2(2),
  zip varchar2(10),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_name) 
);

Then executed the following ALTER TABLE statement

ALTER TABLE suppliers
 MODIFY supplier_name LONG;

You would receive the following error message:

You could correct the error by removing the primary key from the suppliers table:

ALTER TABLE suppliers
 DROP CONSTRAINT suppliers_pk;

However, once you've modified to your column to a LONG datatype, you can no longer include this column in any index.