Oracle Database Tips by Donald Burleson

APEX's HTMLDB_COLLECTION

Collections were covered in an earlier chapter, but only a couple of procedures were shown there.  The following section provides a larger list of many of the procedures commonly used while managing collections.

create_collection( p_collection_name in varchar2 );

This procedure is used to create a new collection.  If the collection exists, the delete_collection must be used first.

create_or_truncate_collection( p_collection_name in varchar2 );

This procedure is used to delete all the data in the collection and leave the collection available.

create_collection_from_query(
         p_collection_name in varchar2,
         p_query in varchar2,
         p_generate_md5 ); 

This can be used to create a new collection and populate it with the results from the query provided.  The p_generate_md5 parameter will produce a checksum value that can be used to check if data has changed.

delete_collection ( p_collection_name in varchar2 );

This procedure deletes the specified collection and all data in the collection.

delete_all_collections;

If multiple collections are in used, they can all be deleted with this procedure.

collection_exists ( p_collection_name in varchar2 ) return boolean;

This procedure returns a Boolean if the collection exists.  It can be used to check if a collection exists before calling create_collection.  If it does exist, the delete_collection procedure should be used to delete it first.

add_member( p_collection_name in varchar2,
           p_c001 in varchar2 default null,
           …
          p_c050 in varchar2 default null,
          p_clob001 in clob default empty_clob( ),
          p_generate_md5 n varchar2 default 'NO' ) return number; 

This is used to add a new member to the collection.

This procedure returns the count of members in the collection.

collection_member_count( p_collection_name in varchar2 );

This returns the count of members in the collection.

The above book excerpt is from:

Easy HTML-DB Oracle Application Express

Create Dynamic Web Pages with OAE

ISBN 0-9761573-1-4   

Michael Cunningham & Kent Crotty

http://www.rampant-books.com/book_2005_2_html_db.htm