Oracle Tips by Burleson
A collection is a way to work with a set of data
without committing it to the base database tables. The concept is
very much like a shopping cart. The data is collected, then, when all
the necessary data has been collected, it can be inserted into a
table, or tables, from the collection. Another way to put it is a
collection works like persistent session state for a multi-dimensional
array of data.
The data in a collection can only be seen by the
session that created it. Also, a session can have many collections.
For example, there may be one collection for books and another
collection for music CDs.
If you have downloaded and installed the EASY
Samples application from the code depot, you will be able reference it
with the explanations that follow. The collections page ID is 1132.
The explanation provided here will be a departure
from the way things have been done so far. The normal process walks
through the steps to create all the components, but since collections
are an intermediate to advanced topic, it is assumed you already know
how to create the components necessary.
A collection stores data in a character format.
It can store up to 50 elements plus a CLOB. The 50 columns are of
data type varchar2(4000).
The use of collections utilizes some of the HTML
DB API. The package for this process is htmldb_collection. This
package has several procedures and functions.
Creating a Collection
being created here is a subset of the CONFERENCE_RSVP table. The code
is in the Create Collection process on page 1132 of the EASY Samples
htmldb_collection.collection_exists( 'CONF') = TRUE then
htmldb_collection.delete_collection( 'CONF' );
p_collection_name => 'CONF',
p_query => 'select id, last_name, first_name, rsvp_date,
where payment = ''Check''',
p_generate_md5 => 'YES');
htmldb_collection.reset_collection_changed( 'CONF' );
While creating the collection above, the first
step is to check to see if the collection exists. If it does, it is
deleted. Then, the CONF collection can be created using a query to
The above book excerpt is from:
Oracle Application Express
Dynamic Web Pages with OAE
Michael Cunningham & Kent Crotty