What
database
objects are
dependent
on Foo_t?
USER_DEPENDENCIES
SELECT name, type
FROM user_dependencies
WHERE referenced_name = 'FOO_T';
18.6.2 SQL*Plus "Describe" Command
If you're like me and don't like to type any more than necessary, you'll appreciate a wonderful enhancement
that Oracle has provided for the describe command in SQL*Plus. It will report not only the attributes of an
object type, but also the methods and their arguments. To illustrate:
SQL> desc pet_t
Name Null? Type
------------------------------- -------- ----
TAG_NO NUMBER(38)
NAME VARCHAR2(60)
ANIMAL_TYPE VARCHAR2(30)
SEX VARCHAR2(1)
PHOTO BINARY FILE LOB
VACCINATIONS VACCINATION_LIST_T
OWNER REF OF PERSON_T
METHOD
------
MEMBER FUNCTION SET_TAG_NO RETURNS PET_T
Argument Name Type In/Out
Default?
------------------------------ ----------------------- ------
--------
NEW_TAG_NO NUMBER IN

OK, if you want to add an attribute, you're out of luck. What about methods? Oracle8.0 does include an
ALTER TYPE statement that allows you to recompile an object specification or body. It also allows you to
add new methods. It is extremely limited, however; it does not allow you to add or remove attributes, nor does
it allow you to modify the quantity or datatypes of existing method arguments. The basic syntax is:
Form I
ALTER TYPE [ BODY ] type_name COMPILE [ SPECIFICATION | BODY ];
which does not solve our problem, or:
Form II
ALTER TYPE [ BODY ] type_name REPLACE
<the entire new type or body definition>;
Using Form II, we can, in fact, add an entirely new method to an object type, even if there are dependencies on
the type.
In the case of changing a method's specification (or deleting a method) in object type Foo_t which is
implemented in table foo, you would think that export/import would work, using something like:
1. Export the foo table.
2. Drop the foo table.
3. CREATE OR REPLACE TYPE Foo_t with the new definition.
4. Import the foo table.
But alas, it doesn't work, because when you CREATE OR REPLACE the type, it actually assigns a new OID
to the type, and the import fails with IMP-00063 when it sees that the OID is different. Huh? What do you
mean, "assigns a new OID to the type?" For reasons apparently having to do with facilitating certain
operations in the Oracle Call Interface (OCI), object types themselves have an OID. See for yourself -- you can
easily retrieve them from the USER_TYPES data dictionary view.
Neither can you "CREATE new_object_table AS SELECT ... FROM old_object_table." Even if you could, the
REFs wouldn't match up to the OIDs of the new table.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
It's even worse if you want to make any serious modifications to an object type and you have a dependency on
the type from other types or tables. You cannot drop and recreate a parent object table unless you drop the
child object types and object tables first. So maybe you could:
1. Create new object types and tables.

Previous: 18.6 Object
Housekeeping
Chapter 18
Object Types
Next: 19. Nested Tables
and VARRAYs

18.7 Making the Objects Option Work
This stuff isn't designed to be easy for the beginner, and the complexities are more than syntax-deep.
In addition to the operational limitations we have discussed, the act of "thinking objects" is not a trait
that comes naturally to programmers schooled in database or structured approaches. But if you feel
intimidated, take heart from this advice: "There may be an OO revolution, but that does not mean you
have to make the change all at once. Instead, you can incorporate what you know worked before, and
bring in the best that OO has to offer, a little at a time as you understand it."[16]
[16] See Rick Mercer and A. Michael Berman, "Object-Oriented Technology and C++
in the First Year: Ten Lessons Learned." Presented at the Northeastern Small College
Computing Conference, April 18- 20, 1996, and on the web at />~berman/tenLessons/paper.htm.
If object technology is such a challenge, what is it that drives many organizations to consider object
approaches in the first place? The overriding interest of managers seems to be their desire to reuse
rather than reinvent the software needed to run their businesses.[
17] In industries whose automation
needs are not satisfied by off-the-shelf solutions, IS managers are continuously squeezed by the need
to deliver more and more solutions while maintaining their legacy code, all while attempting to keep
costs under control.
[17] See Ivar Jacobson, "Reuse in Reality: The Reuse-Driven Software-Engineering
Business." Presented at Object Expo Paris, available at />support/techpapers/objex_ivar.pdf.
It may not be obvious from our examples just how the objects option is going to facilitate reuse,
particularly given Oracle8.0's lack of inheritance and difficulties with schema evolution. Indeed, the
benefits of an object approach do not automatically accrue to the practitioner; large systems, in
particular, must exhibit other characteristics.[18] Achieving reuse requires careful planning and

19. Nested Tables and
VARRAYs
The Oracle Library
Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 18.7 Making the
Objects Option Work
Chapter 19
Next: 19.2 Creating the
New Collections

19. Nested Tables and VARRAYs
Contents:
Types of Collections
Creating the New Collections
Syntax for Declaring Collection Datatypes
Using Collections
Collection Pseudo-Functions
Collection Built-Ins
Example: PL/SQL-to-Server Integration
Collections Housekeeping
Which Collection Type Should I Use?
In PL/SQL Version 2, Oracle introduced the TABLE datatype as a way of storing singly dimensioned
sparse arrays in PL/SQL. Known as the "PL/SQL table," this structure is thoroughly documented in
Chapter 10, PL/SQL Tables. PL/SQL8 introduces two new collection structures that have a wide
range of new uses. These structures are nested tables and variable-size arrays (VARRAYs). Like PL/
SQL tables, the new structures can also be used in PL/SQL programs. But what is dramatic and new
is the ability to use the new collections as the datatypes of fields in conventional tables and attributes

example, the employee table used by the HR department could store the date of birth for each
employee's dependents in a single column, as shown in
Table 19.1.
Table 19.1: Storing a Nonatomic Column of Dependents in a Table of Employees
Id (NUMBER) Name (VARCHAR2) Dependents_ages (Dependent_birthdate_t)
10010 Zaphod Beeblebrox 12-JAN-1763
4-JUL-1977
22-MAR-2021
10020 Molly Squiggly 15-NOV-1968
15-NOV-1968
10030 Joseph Josephs
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
10040 Cepheus Usrbin 27-JUN-1995
9-AUG-1996
19-JUN-1997
10050 Deirdre Quattlebaum 21-SEP-1997
It's not terribly difficult to create such a table. First we define the collection type:
CREATE TYPE Dependent_birthdate_t AS VARRAY(10) OF DATE;
Now we can use it in the table definition:
CREATE TABLE employees (
id NUMBER,
name VARCHAR2(50),
...other columns...,
Dependents_ages Dependent_birthdate_t
);
We can populate this table using the following INSERT syntax, which relies on the type's default
constructor to transform a list of dates into values of the proper datatype:
INSERT INTO employees VALUES (42, 'Zaphod
Beeblebrox', ...,
Dependent_birthdate_t( '12-JAN-1765', '4-JUL-1977',

benefits of the new collection types, index-by tables have one important unique feature: initial
sparseness.
Table 19.2 illustrates many of the additional differences among index-by tables and the
new collection types.
Table 19.2: Comparing Oracle Collection Types
Characteristic Index-By Table Nested Table VARRAY
Dimensionality Single Single Single
Usable in SQL? No Yes Yes
Usable as column
datatype in a table?
No Yes; data stored
"out of line" (in
separate table)
Yes; data stored "in
line" (in same table)
Uninitialized state Empty (cannot be null);
elements undefined
Atomically null;
illegal to reference
elements
Atomically null;
illegal to reference
elements
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Initialization Automatic, when declared Via constructor,
fetch, assignment
Via constructor,
fetch, assignment
In PL/SQL, elements
referenced via

maximum
EXTEND (or
TRIM), but only up
to declared
maximum size
Can be compared for
equality?
No No No
Retains ordering and
subscripts when
stored in and
retrieved from
database?
N/A No Yes
The inevitable question is: Which construct should I use? This chapter reviews some examples of the
new collections and offers some suggestions in this area. The short answer:
●
Nested tables are more flexible than VARRAYs for table columns.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
●
VARRAYs are best when you need bounded arrays that preserve element order.
●
Index-by tables are the only option that allows initial sparseness.
●
If your code must run in both Oracle7 and Oracle8, you can use only index-by tables.
We'll revisit these suggestions in more detail at the end of the chapter. Before diving in, though, let's
review a few of the new terms:
Collection
A term which can have several different meanings:
❍

19.2 Creating the New
Collections
The Oracle Library
Navigation

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.1 Types of
Collections
Chapter 19
Nested Tables and
VARRAYs
Next: 19.3 Syntax for
Declaring Collection
Datatypes

19.2 Creating the New Collections
There are two different ways of creating the new user-defined collection types:
1. You can define a nested table type or VARRAY type "in the database" using the CREATE
TYPE command, which makes the datatype available to use for a variety of purposes:
columns in database tables, variables in PL/SQL programs, and attributes of object types.
2. You can declare the collection type within a PL/SQL program using TYPE ... IS ... syntax.
This collection type will then be available only for use within PL/SQL.
Let's look at a few examples that illustrate how to create collections.
19.2.1 Collections "In the Database"
Before you can define a database table containing a nested table or VARRAY, you must first create
the collection's datatype in the database using the CREATE TYPE statement. There is no good
analogy for this command in Oracle7; it represents new functionality in the server. If we wanted to
create a nested table datatype for variables that will hold lists of color names, we'll specify:

CREATE TYPE Doc_ref_array_t AS TABLE OF REF Doc_t;
This statement says "create a user-defined type to hold lists of pointers to document objects." You
can use a nested table of REFs as you would any other nested table: as a column, as an attribute in an
object type, or as the type of a PL/SQL variable.
NOTE: While Oracle 8.0.3 allows you to create homogeneous collections, in some
cases we might want to build heterogeneous collections. It would be useful to be able
to define a type like the following:
CREATE TYPE Generic_ref_t AS TABLE OF REF ANY;
-- not in 8.0.3
This could allow you to make collections that hold references to more than one type of
object in your database ... or, if OID's are globally unique, each REF could point to any
object in any database on your entire network!1
19.2.1.1 Collection as a "column" in a conventional table
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
In the following case, we are using a nested table datatype as a column. When we create the outer
table personality_inventory, we must tell Oracle what we want to call the "out of line" store table:
CREATE TABLE personality_inventory (
person_id NUMBER,
favorite_colors Color_tab_t,
date_tested DATE,
test_results BLOB)
NESTED TABLE favorite_colors STORE AS favorite_colors_st;
The NESTED TABLE ... STORE AS clause tells Oracle that we want the store table for the
favorite_colors column to be called favorite_colors_st.
You cannot directly manipulate data in the store table, and any attempt to retrieve or store data
directly into favorite_colors_st will generate an error. The only path by which you can read or write
its attributes is via the outer table. (See
Section 19.5, "Collection Pseudo-Functions" for a few
examples of doing so.) You cannot even specify storage parameters for the store table; it inherits the
physical attributes of its outermost table.

says that you want the available_colors column to have a store table named available_colors_st.
19.2.2 Collections in PL/SQL
Whether you use a predefined collection type or declare one in your program, using it requires that
you declare a variable in a separate step. This declare-type-then-declare-variable motif should be
familiar to you if you have ever used an index-by table or a RECORD type in a PL/SQL program.
19.2.2.1 Collection variables
Using the collection types we've declared above, the following shows some legal declarations of PL/
SQL variables:
DECLARE
-- A variable that will hold a list of available font
colors
font_colors Color_tab_t;
/* The next variable will later hold a temporary copy
of
|| font_colors. Note that we can use %TYPE to refer to
the
|| datatype of font_colors. This illustrates two
different
|| ways of declaring variables of the Color_tab_t type.
*/
font_colors_save font_colors%TYPE;
-- Variable to hold a list of paint colors
paint_mixture Color_array_t;
But there is no reason you must use only types you have created in the database. You can declare
them locally, or mix and match from both sources:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
DECLARE
/* As with Oracle7 index-by tables, you can define
|| a table datatype here within a declaration
section...

my_favorite_colors(2) := 'BLUE'; -- changes 2nd
element to BLUE
my_favorite_numbers(1) := 3.14159; -- changes first
element to pi
END;
This code also illustrates default constructors, which are special functions Oracle provides whenever
you create a type, that serve to initialize and/or populate their respective types. A constructor has the
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
same name as the type, and accepts as arguments a comma-separated list of elements.
19.2.2.2 Collections as components of a record
Using a collection type in a record is very similar to using any other type. You can use VARRAYs,
nested tables, or index-by tables (or any combination thereof) in RECORD datatypes. For example:
DECLARE
TYPE toy_rec_t IS RECORD (
manufacturer INTEGER,
shipping_weight_kg NUMBER,
domestic_colors Color_array_t,
international_colors Color_tab_t
);
RECORD types cannot live in the database; they are only available within PL/SQL programs.
Logically, however, you can achieve a similar result with object types. Briefly, object types can have
a variety of attributes, and you can include the two new collection types as attributes within objects;
or you can define a collection whose elements are themselves objects.
19.2.2.3 Collections as module parameters
Collections can also serve as module parameters. In this case, you cannot return a user-defined type
that is declared in the module itself. You will instead use types that you have built outside the scope
of the module, either via CREATE TYPE or via public declaration in a package.
/* This function provides a pseudo "UNION ALL" operation
on
|| two input parameters of type Color_tab_t. That is, it

which := first_colors.NEXT(which);
END LOOP;
which := second_colors.FIRST;
LOOP
EXIT WHEN which IS NULL;
working_colors(element) := second_colors(which);
element := element + 1;
which := second_colors.NEXT(which);
END LOOP;
superset := working_colors;
END;
As a bit of an aside, let's take a look at the loops used in the code. The general form you can use to
iterate over the elements of a collection is as follows:
1 which := collection_name.FIRST;
2 LOOP
3 EXIT WHEN which IS NULL;
4 -- do something useful with the current element...
5 which := collection_name.NEXT(which);
6 END LOOP;
This works for both dense and sparse collections. The first assignment statement, at line 1, gets the
subscript of the FIRST element in the collection; if it's NULL, that means there are no elements, and
we would therefore exit immediately at line 3.
But if there are elements in the collection, we reach line 4, where the program will do "something
useful" with the value, such as assign, change, or test its value for some purpose.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The most interesting line of this example is line 5, where we use the NEXT method on the collection
to retrieve the next-higher subscript above "which" on the right-hand side. In the event that a
particular subscript has been DELETEd, the NEXT operator simply skips over it until it finds a non-
deleted element. Also in line 5, if NEXT returns a NULL, that is our cue that we have iterated over
all of the collection's elements, and it's time to exit the loop when we get back to line 3.

compatible with the collection's elements)
The first option is easy. Notice, by the way, that this is another circumstance where you don't have to
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.