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 the SELECT 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 the SELECT clause must be numeric. After the numeric columns, there can be up to 50 character columns in the SELECT 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 the SELECT clause must be numeric. After the numeric columns, there can be up to 50 character columns in the SELECT clause.