Oracle8i SQLJ Developer's Guide and Reference
Release 8.1.5

A64684-01

Library

Product

Contents

Index

Prev  Chap Top Next

User-Defined Types in the Database

This section contains examples of creating and using user-defined object types and collection types in Oracle8i. A full SQL script for all the user-defined types employed in the object and collection sample applications is in "Definition of Object and Collection Types".

For more information about any of the SQL commands used here, refer to the Oracle8i SQL Reference.

Creating Object Types

Oracle SQL commands to create object types are of the following form:

CREATE TYPE typename AS OBJECT
( 
  attrname1    datatype1,
  attrname2    datatype2,
  ...         ...
  attrnameN    datatypeN
);

Where typename is the desired name of your object type, attrname1 through attrnameN are the desired attribute names, and datatype1 through datatypeN are the attribute datatypes.

The rest of this section provides an example of creating user-defined object types in Oracle8i.

The following items are created using the SQL script below:


Note:

Use of a table alias, such as p above, is a recommended general practice in Oracle SQL, especially in accessing tables with user-defined types. It is required syntax in some cases where object attributes are accessed. Even when not required, it helps in avoiding ambiguities. See the Oracle8i SQL Reference for more information about table aliases.  


Creating Collection Types

There are two categories of collections you can define: variable-length arrays (VARRAYs) and nested tables.

Oracle SQL commands to create VARRAY types are of the following form:

CREATE TYPE typename IS VARRAY(n) OF datatype;

Where typename is the desired name of your VARRAY type, n is the desired maximum number of elements in the array, and datatype is the datatype of the array elements. You must specify the maximum number of elements in the array. For example:

CREATE TYPE myvarr IS VARRAY(10) OF INTEGER;

Oracle SQL commands to create nested table types are of the following form:

CREATE TYPE typename AS TABLE OF datatype;

Where typename is the desired name of your nested table type and datatype is the datatype of the table elements (this can be a user-defined type as well as a standard datatype). A nested table is limited to one column, although that one column type can be a complex object with multiple attributes. The nested table, like any database table, can have any number of rows. For example:

CREATE TYPE person_array AS TABLE OF person;

This creates a nested table where each row consists of a person object.

The rest of this section provides an example of creating a user-defined collection type (as well as object types) in Oracle8i.

The following items are created and populated using the SQL script below:




Prev

Top

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index