Creating Object Types

Similar to PL/SQL packages, an object type's specification is the public interface that developers use to work with the type when building applications. To create an object type's specification, you use the SQL command CREATE TYPE.

An object type's body is the private implementation of the type's methods; it's only necessary to create a body for an object type when the type's specification declares one or more methods. To create an object type's body, you use the SQL command CREATE TYPE BODY.

Examples of Creating and Using Object Types

The following examples demonstrate some common object types and how you might use them in your database designs. The examples illustrate the varying degrees to which you can use object types in an Oracle database-from augmenting a relational database design to building an object database.

Creating Custom Datatypes with Object Types

The most straightforward use of object types is for the creation of custom datatypes that you can then use to build relational database objects more easily. For example, the following statement creates a new ADDRESS-TYPE:

CREATE OR REPLACE TYPE pub.address-type AS OBJECT
street1 VARCHAR2 (50),
street2 VARCHAR2 (50),
city VARCHAR2 (50),
state VARCHAR2 (25),
zipcode VARCHAR2 (10),
country VARCHAR2 (50));

Because the specification of the ADDRESS-TYPE does not include any method specifications, a body is not necessary for the type.

When developers need to declare an address in a relational database table, they can use the ADDRESSTYPE and automatically comply with corporate guidelines.

CREATE TABLE sales.customers
id INTEGER
PRIMARY KEY,
last-name VARCHAR2 (50),
first-name VARCHAR2 (50),
company-name
VARCHAR2 (50),
address pub.Address-Type

Likewise, when a developer must declare address parameters for a stored procedure, it's easy with the new ADDRESS-TYPE:

CREATE OR REPLACE PROCEDURE sales.new-customer (
custid IN INTEGER,
last IN VARCHAR2,
first IN
VARCHAR2,
address IN pub.Address-Type)
BEGIN

To work with a column in a relational table that is declared using an object type, DML statements must reference column attributes with some special syntax. For example, to query the CUSTOMERS table that uses the custom datatype ADDRESS-TYPE, a SELECT statement must use an extended form of standard dot notation to reference specific attributes in the ADDRESS column:

SELECT

id, last-name, first-name,
address.streetl, address. street2, address.city,
address.state, address.zipcode, address.country
FROM sales. customers;

Domain Name Search