Adsense Ad

Wednesday, 24 May 2017

Oracle: ORA-01448 Error Message

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:
Oracle PLSQL
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.

No comments: