Toad for Oracle-1

Oracle Database supports database objects in the relational model and uses object types to convey the concept of the objects. An object type definition consists of an object specification and an object body. The specification declares the attributes, subprograms (functions and procedures) and other object type elements, such as map order functions. The object types are stored in the database itself along with the data. Object types may be used just as any other data types. As an example, a database object type called CATALOG_TYPE may be used in a table definition as follows:

CREATE TABLE catalog (


  edition        VARCHAR2(25));

Or, an object type may be used to create an object table. An object table is the simplest use of an object type. Each row of data in an object table contains data modelled by the object type from which the object table is created.


Setting the environment

The following steps are required:

  1. Download and install Toad for Oracle 14 DBA Edition which includes the DB Admin module, as discussed inHow to download Toad for Oracle.
  2. Create an instance of Oracle Autonomous Database on a local machine or a cloud platform (e.g., Oracle Cloud, Oracle Autonomous Database or AWS RDS). Oracle Database 19c is used in the tutorial.
  3. Create a connection to the Oracle Database in Toad for Oracle.



How to create a database object

TheCREATE TYPE statement is used to create a database object type. A statement could be directly run in an Editor (SQL Worksheet), but we’ll use the Toad for Oracle built-in tools to model and create an object type. We’ll create an object type called CATALOG_TYPE, which models a journal catalog. The object type consists of attributesid, journal, publisher, a map function called get_id, and a member procedure called get_details. The object specification for the catalog_type object type is as follows:

CREATE TYPE catalog_type AS OBJECT (

  id             SMALLINT,

  journal        VARCHAR2 (20),

  publisher      VARCHAR2 (25),



The object body for the catalog_type object type is as follows:

CREATE TYPE BODY catalog_type AS



    RETURN id;




    — use the PUT_LINE procedure of the DBMS_OUTPUT package to display details

    DBMS_OUTPUT.PUT_LINE(TO_CHAR(id) || ' ' || journal || ' ' || publisher);



When developing a new object type, the complete definition may not be known at the outset and may be modelled as discussed next. Select Database>Create>Schema>Object from the toolbar (Figure 1.).


Figure 1. Database>Create>Schema>Object

The Create Object Type dialog is displayed (Figure 2.).

Create Database Type

Figure 2. Create Database Type

First, rename the placeholder object type. Right-click on the NEWOBJECT dummy object type and select Rename (Figure 3).

Renaming dummy object type

Figure 3. Renaming dummy object type

Similarly, rename the first placeholder attribute with right-click>Rename to id (Figure 4).

Renamed attribute in object type CATALOG_TYPE

Figure 4. Renamed attribute in object type CATALOG_TYPE

Additional attributes may be added by selecting New Attribute (Figure 5).

New Attribute

Figure 5. New Attribute

Rename the placeholder attribute added by selecting Rename on a right-click, as before. An attribute’s properties may be set in the Properties tab. Set the publisher and journal attributes to Type VARCHAR2 (Figure 6).

Setting publisher attribute type to VARCHAR2

Figure 6. Setting publisher attribute type to VARCHAR2

Set the id attribute to type SMALLINT (Figure 7).

Setting id attribute to SMALLINT type

Figure 7. Setting idattribute to SMALLINT type

To add a map function, select New Map Function (Figure 8) from the drop-down in the toolbar.

Adding New Map Function

Figure 8. Adding New Map Function

A placeholder map function gets added. Rename the map function to get_id, and set return type to SMALLINT (Figure 9).

Map Function get_id

Figure 9. Map Function get_id

To add a member procedure, select New Procedure (Figure 10) from the toolbar.

New Procedure

Figure 10. New Procedure

A placeholder procedure gets added to start with. Rename procedure to get_details with right-click>Rename. Procedure parameters may be added with the Add button (Figure 11) .


Figure 11. Procedure

Click on Show SQL (Figure 12) to generate and show the SQL for the new object type.

Show SQL

Figure 12. Show SQL

The SQL for the database object type specification is displayed in the Specification tab. The Body tab displays the object type body. The complete SQL statement for the object type definition is displayed in a pop-up window when Show SQL is clicked. With a start script for the new object type, send the SQL statement to an Editor with Send to Editor (Figure 13). Click on OK in the Create Object Type window to close the wizard if no other object type attributes, functions or procedures need to be added.

Send to Editor

Figure 13. Send to Editor

Modify the SQL script sent to the Editor to make it as listed at the start of this section. Click on Execute Script (Figure 14) to run the script.

Execute Script

Figure 14. Execute Script

As the Output tab (Figure 15) indicates, the object type is created.

Object type is created

Figure 15. Object type is created

The new object type is stored in the database itself. To verify, select Database>Schema Browser from the toolbar (Figure 16).

Database>Schema Browser

Figure 16. Database>Schema Browser

SelectTypes from the drop-down in the Schema Browser. The new object type called CATALOG_TYPE that we created is listed (Figure 17).


Figure 17. Object Type CATALOG_TYPE

Click on the CATALOG_TYPE object type to list its details in various tabs. The Info tab (Figure 24) lists the basic information about the object type. The Attributes & Methods tab (Figure 18) lists the attributes and methods.

Attributes & Methods

Figure 18. Attributes & Methods

The Spec tab lists the object specification (Figure 19).

Spec tab

Figure 19. Spec tab

And the Body tab lists the object body (Figure 20).

Body tab

Figure 20. Body tab

Get Toad for Oracle Base Subscription today

Subscription / eStore: buy up to 10 licenses at a time, get auto update, support, announcements/invites to education.

Talk to our professionals: demos, custom solutions, volume discounts.

Not ready to buy? Get Toad for Oracle a 4th way … try it free for 30 days.

Try Toad for Oracle 


How to create an object table

Next, create an object table from the new object type CATALOG_TYPE. A new object table may be created with an SQL statement such as the following:


But, we’ll create an object table using the built-in tool. Right-click on CATALOG_TYPE in the Schema Browser, and select Create>Object Table (Figure 21).

CATALOG_TYPE>Create>Object Table

Figure 21. CATALOG_TYPE>Create>Object Table

Alternatively, select Create Object Table (Figure 22).

Create Object Table

Figure 22. Create Object Table

An Information dialog gets displayed to indicate that the script was run without any errors. Refresh the Schema Browser, such as by closing and reopening it. Select Tables from the drop-down. The object table CATALOG_TYPE_T gets listed (Figure 23).

New Object Table

Figure 23. New Object Table

The new object table does not have any data in it as shown by the Data tab (Figure 24).

Data tab

Figure 24. Data tab

To add data to the object table, copy the following PL/SQL script to a new SQL worksheet (Editor).



  INSERT INTO CATALOG_TYPE_T VALUES (CATALOG_TYPE(1, 'Oracle Magazine', 'Oracle Publishing'));

  INSERT INTO CATALOG_TYPE_T VALUES (CATALOG_TYPE(2, 'Java Magazine', 'Oracle Publishing'));


Click on Execute Script to run the script. The Output tab indicates that the PL/SQL procedure successfully completed (Figure 25).

PL/SQL procedure successfully completed

Figure 25. PL/SQL procedure successfully completed

To query the object table copy the following PL/SQL script to a SQL worksheet (Editor).


  catalog CATALOG_TYPE;





Click on Execute Script to run the script. The Output tab indicates that the PL/SQL procedure completed successfully. The DBMS Output tab (Figure 26) displays the query result.

DBMS Output tab

Figure 26. DBMS Output tab

In a continuation article we’ll discuss altering an object, and dropping an object.

What’s Next?

  • Explore and Select a Toad product at
  • Download Free Trial Version of Toad for Oracle from
  • Buy Online or Request Pricing for Toad for Oracle at
  • Get Product Support for Toad for Oracle at

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


Related information:

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

Data sheet: Toad for Oracle DB Admin Module.

Blog: Toad SQL Editor Code Templates

Blog: Toad Explain Plan More Advanced Options

Blog: Quick and Easy SQL Optimization with Toad for Oracle

Video: VIDEO TUTORIAL: Toad Advanced Explain Plan Tips and Techniques

Video: Video: 'Dan's Dozen' Quick Toad Tips – Tip #5 – Advanced Explain Plans

Blog and Video Demo: Why an Oracle Ace thinks Toad® has the best SQL editor in the business

Oracle Documentation: Explain Plan Content

Oracle Documentation: Explaining a SQL statement: Basic Steps

Webcast: Five Cool DBA Features in Toad for Oracle Base Edition

Webcast: Top Five Use Cases for Developers for Toad for Oracle Base Edition

Chapter 10 “Toad as a SQL Tuning Tool” in Toad for Oracle Unleashed.

Blog: DB performance: 120-point database health check across multiple databases


Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!

About the Author

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.

Start the discussion at