FUNCTION LAST RETURN BINARY_INTEGER;
Example
IF my_list.EXISTS(my_list.FIRST)
THEN
my_list(my_list.FIRST) := 42;
ELSE
my_list.EXTEND;
my_list(my_list.FIRST) := 42;
END IF;
Returns
FIRST returns the lowest index in use in the collection; LAST returns the highest.
Applies to
Nested tables, index-by tables, VARRAYs.
Boundary considerations
FIRST and LAST return NULL when applied to initialized collections which have no
elements. For VARRAYs which have at least one element, FIRST is always 1, and LAST is
always equal to COUNT.
Exceptions possible
If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL
predefined exception.
19.6.6 LIMIT
Specification
FUNCTION LIMIT RETURN BINARY_INTEGER;
Example
IF my_list.LAST < my_list.LIMIT
THEN
my_list.EXTEND;
END IF;
Returns
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
The maximum number of elements that is possible for a given VARRAY.

Boundary considerations
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
If applied to initialized collections which have no elements, returns NULL. If i is greater than
or equal to COUNT, NEXT returns NULL; if i is less than or equal to FIRST, PRIOR returns
NULL. (Currently, if the collection has elements, and i is greater than COUNT, PRIOR
returns LAST; if i is less than FIRST, NEXT returns FIRST; however, do not rely on this
behavior in future Oracle versions.)
Exceptions possible
If applied to an uninitialized nested table or a VARRAY, raises COLLECTION_IS_NULL
predefined exception.
19.6.8 TRIM [ (n ) ]
Specification
PROCEDURE TRIM (n BINARY_INTEGER:=1);
Example
CREATE FUNCTION pop (the_list IN OUT List_t) RETURN
VARCHAR2
AS
l_value VARCHAR2(30);
BEGIN
IF the_list.COUNT >= 1
THEN
/* Save the value of the last element in the
collection
|| so it can be returned
*/
l_value := the_list(the_list.LAST);
the_list.TRIM;
END IF;
RETURN l_value;
END;

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Previous: 19.6 Collection
Built-Ins
Chapter 19
Nested Tables and
VARRAYs
Next: 19.8 Collections
Housekeeping

19.7 Example: PL/SQL-to-Server Integration
To provide an(other) demonstration of how collections can ease the burden of transferring data
between server and PL/SQL application program, let's look at a new example. The main entity in this
example is the "apartment complex." We use a nested table of objects in order to hold the list of
apartments for each apartment complex.
Each apartment is described by the following attributes:
CREATE TYPE Apartment_t AS OBJECT (
unit_no NUMBER,
square_feet NUMBER,
bedrooms NUMBER,
bathrooms NUMBER,
rent_in_dollars NUMBER
);
And we can now define the nested table type which will hold a list of these apartment objects:
CREATE TYPE Apartment_tab_t AS TABLE OF Apartment_t;
Using this type as the type of a column, here is the definition of our database table:
CREATE TABLE apartment_complexes
(name VARCHAR2(75),
landlord_name VARCHAR2(45),
apartments Apartment_tab_t)

/* Declare the cursor that will retrieve the
collection of
|| apartment objects. Since we know we're going to
update the
|| record, we can lock it using FOR UPDATE.
*/
CURSOR aptcur IS
SELECT apartments
FROM apartment_complexes
WHERE name = 'RIVER OAKS FOUR'
FOR UPDATE OF apartments;
/* Need a local variable to hold the collection of
fetched
|| apartment objects.
*/
l_apartments apartment_tab_t;
which INTEGER;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
BEGIN
/* A single fetch is all we need! */
OPEN aptcur;
FETCH aptcur INTO l_apartments;
CLOSE aptcur;
/* Iterate over the apartment objects in the
collection and
|| delete any elements of the nested table which meet
the
|| criteria
*/
which := l_apartments.FIRST;

This PL/SQL fragment emulates the creating of a "client-side cache" of data, which is an essential
concept in many object-oriented and client-server architectures. Using this kind of approach with
collections can reduce network traffic and improve the quality of your code.
Previous: 19.6 Collection
Built-Ins
Oracle PL/SQL
Programming, 2nd Edition
Next: 19.8 Collections
Housekeeping
19.6 Collection Built-Ins
Book Index
19.8 Collections
Housekeeping
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: 19.7 Example:
PL/SQL-to-Server
Integration
Chapter 19
Nested Tables and
VARRAYs
Next: 19.9 Which
Collection Type Should I
Use?

19.8 Collections Housekeeping
Here are some not-so-obvious bits of information that will assist you in using nested tables and

the
Question...
Use This View As In
What
collection
types have
I created?
USER_TYPES
SELECT type_name
FROM user_types
WHERE type_code = 'COLLECTION';
What was
the
original
type
definition
of
collection
Foo_t?
USER_SOURCE
SELECT text
FROM user_source
WHERE name = 'FOO_T'
AND type = 'TYPE'
ORDER BY line;
What
columns
implement
Foo_t?
USER_TAB_COLUMNS

Previous: 19.7 Example:
PL/SQL-to-Server
Integration
Oracle PL/SQL
Programming, 2nd Edition
Next: 19.9 Which
Collection Type Should I
Use?
19.7 Example: PL/SQL-to-
Server Integration
Book Index
19.9 Which Collection Type
Should I Use?
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: 19.8 Collections
Housekeeping
Chapter 19
Nested Tables and
VARRAYs
Next: 20. Object Views

19.9 Which Collection Type Should I Use?
It's not altogether obvious how to choose the best type of collection for a given application. Here are
some guidelines:
●
If you intend to store large amounts of persistent data in a column collection, your only option

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.9 Which
Collection Type Should I
Use?
Chapter 20
Next: 20.2 INSTEAD OF
Triggers

20. Object Views
Contents:
Example: Using Object Views
INSTEAD OF Triggers
Syntax for Object Views
Differences Between Object Views and Object Tables
Not All Views with Objects Are Object Views
Schema Evolution
Object Views Housekeeping
Postscript: Using the BFILE Datatype
Although Oracle's object extensions offer rich possibilities for the design of new systems, few Oracle
shops with large relational databases in place will want to, or be able to, completely reengineer those
systems to use objects. To allow established applications to take advantage of the new object features
over time, Oracle8 provides object views. With object views, you can achieve the following benefits:
●
Efficiency of object access. In PL/SQL, and particularly in Oracle Call Interface (OCI)
applications, object programming constructs provide for the convenient retrieval, caching, and

encapsulation. Insofar as old applications apply INSERT, UPDATE, and DELETE statements
directly to table data, they will subvert the benefits of encapsulation normally provided by an object
approach. As I discussed in Chapter 18, object-oriented designs typically prevent free-form access
directly to object data.
Despite this intrusion of reality, if you do choose to layer object views on top of a legacy system,
your future applications can employ object abstractions and enjoy many benefits of encapsulation and
information hiding. And your legacy systems are no worse off than they were before!
Figure 20.1
illustrates this use of object views.
Figure 20.1: Object views allow you to "bind" an object type definition to (existing)
relational tables
This chapter discusses the nuances of creating and, to a lesser extent, using object views. The
discussion of PL/SQL-specific aspects of object views is rather terse, for two reasons:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
1. Object views are substantially similar to regular object types, which are covered in a Chapter
18.
2. As a topic, object views are closer to SQL than to PL/SQL.
However, PL/SQL developers who are interested in fully exploiting Oracle's object features must
understand object views. This chapter pays close attention to the areas of difference between object
tables and object views.
20.1 Example: Using Object Views
In our first example, let's look at how object views might be used at Planetary Pages, a fictitious firm
that designs Web sites. Their existing relational application tracks JPEG, GIF, and other images that
they use when designing client Web sites. These images are stored in files, but data about them are
stored in relational tables. To help the graphic artists locate the right image, each image has one or
more associated keywords, stored in a straightforward master-detail relationship.
Our legacy system has one table for image metadata:
CREATE TABLE images (
image_id INTEGER NOT NULL,
file_name VARCHAR2(512),

bytes INTEGER,
keywords Keyword_tab_t,
MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
new_file_type IN VARCHAR2, new_bytes IN INTEGER)
RETURN Image_t,
MEMBER FUNCTION set_keywords (new_keywords IN
Keyword_tab_t)
RETURN Image_t,
PRAGMA RESTRICT_REFERENCES (DEFAULT, RNDS, WNDS,
RNPS, WNPS)
);
Here is the body:
CREATE TYPE BODY Image_t
AS
MEMBER FUNCTION set_attrs (new_file_name IN VARCHAR2,
new_file_type IN VARCHAR2, new_bytes IN INTEGER)
RETURN Image_t
IS
image_holder Image_t := SELF;
BEGIN
image_holder.file_name := new_file_name;
image_holder.file_type := new_file_type;
image_holder.bytes := new_bytes;
RETURN image_holder;
END;
MEMBER FUNCTION set_keywords (new_keywords IN
Keyword_tab_t)
RETURN Image_t
IS
image_holder Image_t := SELF;

define the same number of columns as there are attributes in the object type Image_t. The datatype of
each retrieved column matches the datatype of its corresponding object attributes.
You can use the CAST clause shown in the example in any view, not just in object views (but it does
require the presence of the Oracle objects option). This subquery performs an "on-the-fly" conversion
of the detail records into a collection type. For more details about the CAST and MULTISET
operators, refer to
Section 19.5, "Collection Pseudo-Functions" in Chapter 19.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
OK, now that we've created it, what can we do with it? Well, we can retrieve data from it just as if it
were an object table. First, let's put some data into the underlying tables:
INSERT INTO images VALUES (100001, 'smiley_face.gif',
'GIF', 813);
INSERT INTO images VALUES (100002, 'peace_symbol.gif',
'GIF', 972);
INSERT INTO KEYWORDS VALUES (100001, 'SIXTIES');
INSERT INTO KEYWORDS VALUES (100001, 'HAPPY FACE');
INSERT INTO KEYWORDS VALUES (100002, 'SIXTIES');
INSERT INTO KEYWORDS VALUES (100002, 'PEACE SYMBOL');
INSERT INTO KEYWORDS VALUES (100002, 'JERRY RUBIN');
Now, from SQL*Plus, you can make a query like the following:
SELECT image_id, file_name, keywords
FROM images_v;
Which yields:
IMAGE_ID FILE_NAME KEYWORDS
-------- ----------------
-------------------------------------------------------
100001 smiley_face.gif KEYWORD_TAB_T('HAPPY FACE',
'SIXTIES')
100002 peace_symbol.gif KEYWORD_TAB_T('JERRY RUBIN',
'PEACE SYMBOL', 'SIXTIES')

Keywords: HAPPY FACE SIXTIES
See
Chapter 19 for more examples of retrieving data from an object table.
Other things you can do with object views include the following:
●
Define REFs that point to "virtual" objects (discussed in detail in Section 20.4, "Differences
Between Object Views and Object Tables" later in this chapter).
●
Encapsulate an object view (more or less) using object methods and/or PL/SQL packages
(discussed in-depth in Chapter 18).
●
Write INSTEAD OF triggers that will allow direct manipulation of the view's contents
(discussed in the next section).
Previous: 19.9 Which
Collection Type Should I
Use?
Oracle PL/SQL
Programming, 2nd Edition
Next: 20.2 INSTEAD OF
Triggers
19.9 Which Collection Type
Should I Use?
Book Index
20.2 INSTEAD OF Triggers
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.