How to alter and drop database objects with Quest® Toad® for Oracle Pro DB Admin

The object model for developing database object types and object tables brings object-oriented data storage to Oracle Database. In a previous post, “How to create database objects with Toad for Oracle Pro DB Admin,” we discussed creating an object type, which consists of an object specification and an object body. Then, we created an object table from the object type. In this article we’ll discuss altering an object type and how to drop an object type from the database.

Why alter or drop a database object?

If an organization’s data requirements change, it can be necessary to modify database objects. Dropping a database object essentially deletes it, while altering a database object allows DBAs and developers to add or remove member attributes and methods. As a result of altering a database object, it changes the specification of the type.

Oracle Database provides the ALTER TYPE statement to alter a database object type. Note that when an object type’s specification is changed, it must be compiled again. If an object type’s body is updated, the type’s body must also be compiled again.

We’ll discuss how to navigate both situations.

How to alter a database object

First, add an attribute called edition to the object type catalog_type that we created in the first article. The ADD ATTRIBUTE clause is used to add an attribute. The new attribute must be unique in that its name must not be the same as an existing attribute, or even a method name. The new attribute is added at the end of the attribute list. If any dependent database object types and object tables exist, the change to an object type may be propagated to those dependent object types and tables by using the CASCADE clause. As we do have a dependent object table, we’ll use the following ALTER TYPE statement to add an attribute.

ALTER TYPE CATALOG_TYPE

   ADD ATTRIBUTE (edition VARCHAR2(20)) CASCADE;

Copy the ALTER TYPE statement to an Editor in Quest^®Toad^® for Oracle and click on Execute Script as shown in Figure 1.

Figure 1. Execute Script

As the Output tab output shown in Figure 2 indicates, the object gets altered.

Figure 2. Object type altered

In the Schema Browser, the new attribute EDITION gets listed as shown in Figure 3. The Schema Browser may need to be refreshed, such as by closing and reopening it.

Figure 3. New Attribute EDITION Added

A database object type’s body cannot be altered with an ALTER TYPE statement, and should be altered separately as we’ll discuss next. In the Schema Browser, right-click on the CATALOG_TYPE type and select Editor as shown in Figure 4.

Figure 4. ALTER_TYPE>Editor

In the get_details procedure add the edition attribute in the DBMS_OUTPUT.PUT_LINE statement as shown in Figure 5. Click on Execute Script to run the modified script.

Figure 5. New attribute edition added to get_details procedure

As the output (Figure 6) shows, the object type’s body gets altered.

Figure 6. Object type’s body altered

We have altered both the database object type’s specification and body. To verify what the new type and body are, select the corresponding tab in the Schema Browser. The Spec tab (Figure 7) shows the altered specification. The ALTER TYPE statement has been added to the specification, and the original CREATE TYPE statement is not altered directly.

Figure 7. Spec tab

The object type’s body is shown in the Body tab (Figure 8).

Figure 8. Body tab

After altering the database object type’s specification and body, compile the specification and body again using the ALTER TYPE statement with the COMPILE clause. The following statement compiles the specification of the catalog_type type.

ALTER TYPE catalog_type COMPILE SPECIFICATION;

The following statement compiles the body of the catalog_type body.

ALTER TYPE catalog_type COMPILE BODY;

The preceding statements are useful if only the specification or the body need to be compiled again. Because we altered both the specification and the body, we can use the following statement which compiles both the specification and the body.

ALTER TYPE catalog_type COMPILE;

Copy the statement to an Editor in Toad for Oracle, and click onExecute Script (Figure 9).

Figure 9. Compiling object type

We did not take into consideration that the database object type catalog_type has a dependent object table. To be able to alter a type with the COMPILE option, the type must not have any dependent types, or object tables. The ORA-02311 error gets generated, as shown in Figure 10.

Figure 10. Error compiling object type

The ORA-02311 error is also output in the Output tab.

We need to drop the object table to be able to compile the altered object type. Select Tables in Schema Browser, right-click on the CATALOG_TYPE_T object table and select Drop (Figure 11).

Figure 11. CATALOG_TYPE_T>Drop

Click on OK in the Confirm Drop Table dialog.

The object table gets dropped as indicated by no table listed in the Schema Browser. Run the ALTER TYPE statement with the COMPILE option again.

The object type gets altered as indicated by the message in the Output tab.

The attributes and methods in the altered object type CATALOG_TYPE are listed in the Schema Browser in the Attributes & Methods tab (Figure 12).

Figure 12. Attributes and Methods in altered object type

How to create an object table from the new database object type

Because we dropped the object table associated with the object type, we should create it again. Right-click on the CATALOG_TYPE object type in the Schema Browser, and select Create>Object Table (Figure 13).

Figure 13. CATALOG_TYPE>Create>Object Table

Click on OK in the Information dialog that is displayed. An object table CATALOG_TYPE_T gets created as shown in Schema Browser (Figure 14).

Figure 14. Object Table CATALOG_TYPE_T

The object table has an additional column called EDITION (Figure 15) as compared to before altering the object type.

Figure 15. Object Table with an additional column

The object table has no data initially as indicated by the Data tab (Figure 16) in the Schema Browser.

Figure 16. Data tab

The following PL/SQL script adds data to the object table, and also queries the table to get and list the data added.

DECLARE

catalog CATALOG_TYPE;

BEGIN

INSERT INTO CATALOG_TYPE_T VALUES (CATALOG_TYPE(1, 'Oracle Magazine', 'Oracle Publishing','Jan/Feb 2021'));

INSERT INTO CATALOG_TYPE_T VALUES (CATALOG_TYPE(2, 'Java Magazine', 'Oracle Publishing','Jan/Feb 2021'));

SELECT VALUE(c) INTO catalog FROM CATALOG_TYPE_T c WHERE c.id = 2;

catalog.get_details();

END;

Copy the script to an Editor and click on Execute Script. The PL/SQL procedure runs as indicated by the output (Figure 17).

Figure 17. PL/SQL procedure successfully completed

The DBMS Output tab (Figure 18) shows the output from the SELECT statement in the PL/SQL script.

Figure 18. DBMS Output

How to drop a database object

To drop an object type, right-click on the object type in the Schema Browser and select Drop as shown for the CATALOG_TYPE object type in Figure 19.

Figure 19. CATALOG_TYPE>Drop

A Confirm Drop Type dialog prompts if the Force option should be used to drop the object type. If the Force option is used, any dependent types and tables also get dropped. Select Do Not Use Force Option (Figure 20) and click on OK.

Figure 20. Confirm Drop Type dialog

An error message dialog (Figure 21) displays the ORA-02303 error indicating that a type with dependencies cannot be dropped.

Figure 21. ORA-02303 Error message

To drop the object table, right-click on the CATALOG_TYPE_T table in Schema Browser and select Drop.

Click on OK in the Confirm Drop Table prompt. After the dependent object table has been dropped, the object type may be dropped if no other dependencies exist. Right-click on the object type CATALOG_TYPE in the Schema Browser and select Drop again.

The Confirm Drop Type dialog is displayed. Again select the Do Not Use Force Option and click on OK (refer Figure 24). The object type CATALOG_TYPE gets dropped and does not get listed in Schema Browser>Types (Figure 22).

Figure 22. Object Type CATALOG_TYPE Dropped

In these two articles we discussed creating an object type, creating an object table from the object type, altering the object type and dropping the object type – all from within Toad for Oracle Pro with the DB Admin module. The DBA Edition of Toad for Oracle has the DB Admin module pre-installed.

What’s Next?

1. Explore and Select a Toad product at https://support.quest.com/download-product-select
2. Toad for Oracle download Free Trial Versionfrom https://www.quest.com/register/54618/
3. Buy Online or Request Pricing for Toad for Oracle at https://www.quest.com/products/toad-for-oracle/ 
4. Get Product Supportfor Toad for Oracle at https://support.quest.com/

Have questions about Toad Developer Tools? Click Start Discussionand this blog topic will be transferred to the Toad World Forums.

Related information:

Datasheet: Toad for Oracle DB Admin Module 

Blog: DB performance: 6 benefits from maintaining database health and stability

Blog: 8 ways to increase your database power and flexibility