The Oracle database error ORA-22858: Invalid Alteration of Datatype occurs when attempting to modify a column’s datatype in a way that is not supported. This error commonly appears when working with LOB (Large Object) columns, collections, or user-defined types (UDTs).
In this topic, we will discuss the causes, solutions, and best practices to handle ORA-22858 efficiently.
Understanding ORA-22858: Invalid Alteration of Datatype
What Does ORA-22858 Mean?
This error occurs when trying to ALTER a column’s datatype in a way that violates Oracle’s datatype modification rules. Some datatypes, such as CLOB, BLOB, NCLOB, VARRAY, and object types, have strict limitations on how they can be altered.
When Does ORA-22858 Occur?
-
Changing a LOB (CLOB, BLOB, NCLOB) column to another datatype.
-
Modifying a VARRAY or NESTED TABLE type column.
-
Changing the structure of a User-Defined Type (UDT) incorrectly.
-
Attempting to alter a column from RAW to another type.
Causes of ORA-22858 and How to Fix Them
1. Altering a LOB Column to Another Datatype
LOB columns cannot be directly altered to VARCHAR2, NUMBER, DATE, or other scalar types.
Example (Causing ORA-22858):
ALTER TABLE employees MODIFY resume VARCHAR2(4000);If resume is a CLOB column, Oracle will throw ORA-22858 because you cannot directly change a LOB to a different datatype.
Solution:
To change a CLOB column to VARCHAR2, create a new column, copy the data, and drop the old column.
ALTER TABLE employees ADD new_resume VARCHAR2(4000);UPDATE employees SET new_resume = DBMS_LOB.SUBSTR(resume, 4000, 1);ALTER TABLE employees DROP COLUMN resume;RENAME new_resume TO resume;2. Altering a VARRAY Column
VARRAY types cannot be altered directly. If you need to modify a VARRAY, you must redefine the type and reassign data.
Example (Causing ORA-22858):
ALTER TYPE emp_projects MODIFY LIMIT 50;If emp_projects is a VARRAY(30), trying to modify its limit directly will result in an error.
Solution:
The correct approach is to create a new type with the updated limit and update the table.
CREATE OR REPLACE TYPE emp_projects_new AS VARRAY(50) OF VARCHAR2(100);ALTER TABLE employees MODIFY COLUMN project_list emp_projects_new;3. Altering a NESTED TABLE Column
If a table contains a NESTED TABLE, its datatype cannot be altered directly.
Example (Causing ORA-22858):
ALTER TABLE orders MODIFY order_items orders_nested_table_new;Oracle does not allow direct modification of nested table columns.
Solution:
-
Create a new nested table type.
-
Add a new column with the new type.
-
Transfer the data and drop the old column.
CREATE OR REPLACE TYPE orders_nested_table_new AS TABLE OF VARCHAR2(200);ALTER TABLE orders ADD new_order_items orders_nested_table_new NESTED TABLE new_order_items STORE AS new_items_table;UPDATE orders SET new_order_items = order_items;ALTER TABLE orders DROP COLUMN order_items;RENAME new_order_items TO order_items;4. Modifying a User-Defined Type (UDT) Incorrectly
If a table column is based on a user-defined type, direct modification is not allowed.
Example (Causing ORA-22858):
ALTER TYPE employee_type ADD ATTRIBUTE (email VARCHAR2(100));Oracle does not allow direct modifications like adding new attributes to a UDT that is already in use.
Solution:
To modify a UDT, create a new version and apply an edition-based redefinition approach.
CREATE OR REPLACE TYPE employee_type_v2 AS OBJECT (id NUMBER,name VARCHAR2(100),email VARCHAR2(100));ALTER TABLE employees MODIFY COLUMN emp_details employee_type_v2;How to Avoid ORA-22858: Best Practices
1. Plan Schema Changes Carefully
-
Before modifying column datatypes, check Oracle’s datatype alteration rules.
-
Use temporary tables for data migration if needed.
2. Use Workarounds Instead of Direct ALTER Statements
-
For LOBs, VARRAYs, and nested tables, create a new column or type instead of altering the existing one.
-
Use data migration techniques (copy data, drop old column, rename new column).
3. Test Changes in a Development Environment
-
Run schema modifications in a test database before applying changes to production.
-
Use Oracle Data Pump or DBMS_METADATA.GET_DDL to back up existing structures.
4. Use Edition-Based Redefinition (EBR) for UDT Changes
- Oracle supports edition-based redefinition to allow changes in user-defined types without breaking existing applications.
Common ORA-22858 Related Errors and Fixes
| Error Code | Cause | Solution |
|---|---|---|
| ORA-22858 | Invalid datatype alteration | Use a workaround like creating a new column and migrating data. |
| ORA-01439 | Cannot modify column with constraints | Drop constraints before altering the column. |
| ORA-00932 | Inconsistent datatypes | Ensure compatible datatypes before altering. |
| ORA-22830 | Nested table storage error | Use NESTED TABLE STORE AS correctly. |
The ORA-22858: Invalid Alteration of Datatype error occurs when trying to alter a column’s datatype in an unsupported way. This is common with LOBs, VARRAYs, NESTED TABLES, and user-defined types.
Key Takeaways:
✔ LOB columns cannot be altered directly—use a new column and migrate data.
✔ VARRAY and NESTED TABLE types must be redefined before changing their structure.
✔ User-defined types (UDTs) should be modified using edition-based redefinition.
✔ Always test changes in a development environment before applying them to production.
By following these best practices, you can effectively resolve and prevent ORA-22858 errors in Oracle databases.