How to fix the Oracle error ORA-22339: cannot alter to not final since its attribute column is substitutable?

In this post, you’ll learn more about the Oracle ErrorORA-22339: cannot alter to not final since its attribute column is substitutable with the details on why you receive this error and the possible solution to fix it.

Oracle Error Description

ORA-22339: cannot alter to not final since its attribute column is substitutable

Reason for the Error ORA-22339: cannot alter to not final since its attribute column is substitutable

An attempt was made to alter a type to not final when its embedded attribute is defined as substitutable in some tables. Note, this is a restriction in 9.0 version because when a type is altered to not final, column of that type is set to not substitutable at all levels; thus, it is an error if one of its embedded attribute column is already marked substitutable.

How to fix the Error ORA-22339: cannot alter to not final since its attribute column is substitutable ?

You can fix this error in Oracle by following the below steps

Recreate the table and specify NOT SUBSTITUTABLE AT ALL LEVELS for all columns of non final type. Then resubmit the ALTER TYPE statement.