Modifying Oracle9i Object Tables and Object Types

Here you have an interesting problem. When you make an object table, you identify what object type is to be used for each row in the object table. The attributes that you see when you look at the table in Schema Manager are actually the attributes of the object type.

You can modify a few characteristics of the object table, as shown in the following section. Adding or removing attributes in an object table, however, must be performed by modifying the underlying object type, which is discussed in the second section.

Modifying object tables

The only changes to the attributes that you can make to the object table are

• Changing NULL to NOT NULL

• Adding a default value

• Adding constraints, such as primary-key or check constraints

You handle these changes the same way that you do with relational tables.

Modifying object types

When you modify an object type, the changes are inherited by all the object tables and all the object types that use the changed object type.

If you're not sure which object type was used to create an object table, you can get some help from the table properties window in Schema Manager. To view this window, follow these steps:

1. Start Console, as described earlier in the chapter.

2. Click the object table that you're interested in.

A properties window for the table appears in the right window. For this example, select the BREAD_OBJ table in the BAKERY schema. Figure 1 shows the properties window. Notice that if you peer intently at the window, you can just make out the object type that defines the object table rows.

>

>

Figure 1: Make all the adjustments you need to your column definitions.

To modify an object type, use SQL*Plus. You can add, change, or remove an attribute in an object type. Following is the syntax for adding an attribute to an object type:

ALTER TYPE typename
ADD ATTRIBUTE (attributename datatype)
CASCADE;

Replace typename, attributename, and datatype with the names and datatype. The CASCADE parameter tells Oracle9i to make the change to all dependent objects and object types.

For example, to add a new attribute called MANUFACTURER to the INGREDIENT_TYPE object type owned by BAKERY, use this SQL command:

-- 18_addattribute
ALTER INGREDIENT_TYPE
ADD ATTRIBUTE (MANUFACTURER VARCHAR2(40))
CASCADE;

Modifying an attribute uses the same syntax as adding, except you replace ADD with MODIFY.

For example, to modify the MANUFACTURER attribute to be 60 characters long, use this command:

-- 19_changeattribute
ALTER INGREDIENT_TYPE
MODIFY ATTRIBUTE (MANUFACTURER VARCHAR2(60))
CASCADE;

When removing an attribute, use the following syntax:

ALTER TYPE typename
ADD ATTRIBUTE (attributename)
CASCADE;

Replace typename and attributename with the actual names. The CASCADE parameter tells Oracle9i to make the change to all dependent objects and object types. For example, to remove the MANUFACTURER attribute from the INGREDIENT_TYPE object type owned by BAKERY, use this SQL command:

-- 20_dropdattribute
ALTER INGREDIENT_TYPE
DROP ATTRIBUTE (MANUFACTURER)
CASCADE;

>
dummies

Source:http://www.dummies.com/how-to/content/modifying-oracle9i-object-tables-and-object-types.html