Can I drop/add attribute without consequences? Oracle Object Type

karel picture karel · Aug 27, 2012 · Viewed 7.3k times · Source

I have an OBJECT_TYPE with ATTRIBUTE varchar2(200). There many other objects referencing this one. I need to reduce the length of the attribute to varchar2(50). I know I cannot do it directly, so I found this way:

ALTER TYPE CUSTOMER DROP ATTRIBUTE name INVALIDATE;
ALTER TYPE CUSTOMER ADD ATTRIBUTE name varchar2(50) CASCADE;

The question is: is there anything that could be broken after the dropping/creating of the attribute? Is it correct to use the INVALIDATE option instead of CASCADE in the DROP statement? I don't want to loose anything - relations or data.

Answer

Stu picture Stu · Feb 24, 2013

Is there a reason the following is not used:

ALTER TYPE CUSTOMER MODIFY ATTRIBUTE name VARCHAR2(50);

I have run into compatibility problems when using types and dropping an attribute especially if there are tables and data defined using the types. This is one reason I avoid basing tables and views on the data types. The change in the length would invalidate the table and any rows where the associated data exceeds 50. If the other objects are just types and are not used in views and tables then the alter type above should work fine. And you may need to recompile any types. Keep in mind that the type is becoming more restrictive and how it may affect any associated data.