Oracle8i SQLJ Developer's Guide and Reference Release 3 (8.1.7) Part Number A83723-01 |
Library | Service | Contents | Index

## Oracle Objects and Collections

This section provides some background conceptual information about Oracle8i objects and collections.

For additional conceptual and reference information about Oracle objects, references, and collections, refer to the Oracle8i SQL Reference and the Oracle8i Application Developer's Guide - Fundamentals.

For information about how to declare objects and collections, see "User-Defined Types in the Database".

### Oracle Object Fundamentals

Oracle objects (SQL objects) are composite data structures that group related data items, such as facts about each employee, into a single data unit. An object type is functionally similar to a Java class--you can populate and use any number of individual objects of a given object type, just as you can instantiate and use individual objects of a Java class.

For example, you can define an object type `EMPLOYEE` that has the attributes `name` (type `CHAR`), `address` (type `CHAR`), `phonenumber` (type `CHAR`), and `employeenumber` (type `NUMBER`).

Oracle objects can also have methods--stored procedures associated with the object type. These methods can be either static methods or instance methods that can be implemented either in PL/SQL or in Java. Their signatures can include any number of input, output, or input-output parameters. All of this depends on how they are initially defined.

### Oracle Collection Fundamentals

There are two categories of Oracle collections (SQL collections):

- variable-length arrays (`VARRAY` types)

- nested tables (`TABLE` types)

Both categories are one-dimensional, although the elements can be complex object types. `VARRAY` type`s` are used for one-dimensional arrays; nested table types are used for single-column tables within an outer table. A variable of any `VARRAY` type can be referred to as a VARRAY; a variable of any nested table type can be referred to as a nested table.

A VARRAY, as with any array, is an ordered set of data elements, with each element having an index and all elements being of the same datatype. The size of a VARRAY refers to the maximum number of elements. Oracle VARRAYs are of variable size (thus the name), but the maximum size of any particular `VARRAY` type must be specified when the `VARRAY` type is declared.

A nested table is an unordered set of elements. Nested table elements within a table can themselves be queried in SQL, but not in SQLJ. A nested table, as with any table, is not created with any particular number of rows--this is determined dynamically.

Notes: The elements in a VARRAY or the rows in a nested table can be of a user-defined object type, and `VARRAY` and nested table types can be used for attributes in a user-defined object type. Oracle does not, however, support any nesting of collection types. The elements of a VARRAY or rows of a nested table cannot be of another `VARRAY` or nested table type, nor can these elements be of a user-defined object type that has VARRAY or nested table attributes.

### Object and Collection Datatypes

User-specified object and collection definitions in Oracle8i function as SQL datatype definitions. You can then use these datatypes, as with any other datatype, in defining table columns, SQL object attributes, and stored procedure or function parameters. In addition, once you have defined an object type, the related object reference type can be used as any other SQL reference type.

Once you have defined `EMPLOYEE` as an Oracle object, as described in "Oracle Object Fundamentals", it becomes an Oracle datatype, and you can have a table column of type `EMPLOYEE` just as you can have a table column of type `NUMBER`. Each row in an `EMPLOYEE` column contains a complete `EMPLOYEE` object. You can also have a column type of `REF EMPLOYEE`, consisting of references to `EMPLOYEE` objects.

Similarly, you can define a variable-length array `MYVARR` as `VARRAY(10)` of `NUMBER` and a nested table `NTBL` of `CHAR(20)`. The `MYVARR` and `NTBL` collection types become Oracle datatypes, and you can have table columns of either type. Each row of a `MYVARR` column consists of an array of up to ten numbers; each row of an `NTBL` column consists of 20 characters.

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index