9.3 Creating a Collection
Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)
), five number attributes, one XML Type attribute, one large binary attribute (BLOB
), and one large character attribute (CLOB
). You use the following methods to create a collection:
-
CREATE_COLLECTION
This method creates an empty collection with the provided name. An exception is raised if the named collection exists.
-
CREATE_OR_TRUNCATE_COLLECTION
If the provided named collection does not exist, this method creates an empty collection with the given name. If the named collection exists, this method truncates it. Truncating a collection empties it, but leaves it in place.
-
CREATE_COLLECTION_FROM_QUERY
This method creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. This method can be used with a query with up to 50 columns in the
SELECT
clause. These columns in theSELECT
clause populate the 50 character attributes of the collection (C001 through C050). -
CREATE_COLLECTION_FOM_QUERY2
This method creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. It is identical to the
CREATE_COLLECTION_FROM_QUERY
, however, the first 5 columns of theSELECT
clause must be numeric. After the numeric columns, there can be up to 50 character columns in theSELECT
clause. -
CREATE_COLLECTION_FROM_QUERY_B
This method offers significantly faster performance than the
CREATE_COLLECTION_FROM_QUERY
method by performing bulk SQL operations, but has the following limitations:-
No column value in the select list of the query can be more than 2,000 bytes. If a row is encountered that has a column value of more than 2,000 bytes, an error is raised during execution.
-
The MD5 checksum is not computed for any members in the collection.
-
-
CREATE_COLLECTION_FROM_QUERYB2
This method also creates a collection and then populates it with the results of a specified query. An exception is raised if the named collection exists. It is identical to the
CREATE_COLLECTION_FROM_QUERY_B
, however, the first five columns of theSELECT
clause must be numeric. After the numeric columns, there can be up to 50 character columns in theSELECT
clause.
Parent topic: APEX_COLLECTION