Nov 16, 2021 3:00:00 AM by Deepak Vohra
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.
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.
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 on Execute 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
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.
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;
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
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.
Need help managing data?
Quest Software is here to help you simplify complexity, reduce cost and risk, and drive performance. Maybe that’s why we’re consistently voted #1 by DBTA readers and have 95%+ customer satisfaction rate.
Toad® database management tools are cross-platform solutions from Quest® that can help new and veteran DBAs, and even “accidental” DBAs manage data in Oracle, SQL Server, DB2, SAP, MySQL, and Postgres environments.
Learn more about how Toad database management tools from Quest can help with database development, performance monitoring and database DevOps.
Visit Toad World® often for free advice via our blogs, free interactive help via our forums and free trial downloads.
Have questions about Toad Developer Tools? Click Start Discussion and this blog topic will be transferred to the Toad World Forums.
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
Tags: Toad for Oracle Tutorial
Written by Deepak Vohra
Deepak Vohra is an Oracle Certified Associate, Oracle Database 10g, and Sun Certified Java Programmer. Deepak has published on OTN and in Oracle Magazine.