5 APEX_COLLECTION

About the APEX_COLLECTION API

Every collection contains a named list of data elements (or members) which can have up to 50 character attributes (VARCHAR2(4000)), five number attributes, five date attributes, one XML Type attribute, one large binary attribute (BLOB), and one large character attribute (CLOB). You insert, update, and delete collection information using the PL/SQL API APEX_COLLECTION.

The following are examples of when you might use collections:

• When you are creating a data-entry wizard in which multiple rows of information first need to be collected within a logical transaction. You can use collections to temporarily store the contents of the multiple rows of information, before performing the final step in the wizard when both the physical and logical transactions are completed.

• When your application includes an update page on which a user updates multiple detail rows on one page. The user can make many updates, apply these updates to a collection and then call a final process to apply the changes to the database.

• When you are building a wizard where you are collecting an arbitrary number of attributes. At the end of the wizard, the user then performs a task that takes the information temporarily stored in the collection and applies it to the database.

Beginning in Oracle Database 12c, database columns of data type VARCHAR2 can be defined up to 32,767 bytes. This requires that the database initialization parameter MAX_STRING_SIZE has a value of EXTENDED. If Application Express was installed in Oracle Database 12c and with MAX_STRING_SIZE = EXTENDED, then the tables for the Application Express collections will be defined to support up 32,767 bytes for the character attributes of a collection. For the methods in the APEX_COLLECTION API, all references to character attributes (c001 through c050) can support up to 32,767 bytes.

Naming Collections

When you create a collection, you must give it a name that cannot exceed 255 characters. Note that collection names are not case-sensitive and are converted to uppercase.

Once the collection is named, you can access the values in the collection by running a SQL query against the view APEX_COLLECTIONS.

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.

About the Parameter p_generate_md5

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

Accessing a Collection

You can access the members of a collection by querying the database view APEX_COLLECTIONS. Collection names are always converted to uppercase. When querying the APEX_COLLECTIONS view, always specify the collection name in all uppercase. The APEX_COLLECTIONS view has the following definition:

COLLECTION_NAME   NOT NULL VARCHAR2(255)
SEQ_ID            NOT NULL NUMBER 
C001              VARCHAR2(4000)
C002              VARCHAR2(4000)
C003              VARCHAR2(4000)   
C004              VARCHAR2(4000)   
C005              VARCHAR2(4000)  
...
C050              VARCHAR2(4000)
N001              NUMBER
N002              NUMBER
N003              NUMBER
N004              NUMBER
N005              NUMBER     
CLOB001           CLOB
BLOB001           BLOB  
XMLTYPE001        XMLTYPE
MD5_ORIGINAL      VARCHAR2(4000)  

Use the APEX_COLLECTIONS view in an application just as you would use any other table or view in an application, for example:

SELECT c001, c002, c003, n001, clob001
   FROM APEX_collections
 WHERE collection_name = 'DEPARTMENTS'

Merging Collections

You can merge members of a collection with values passed in a set of arrays. By using the p_init_query argument, you can create a collection from the supplied query.

Truncating a Collection

If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place.

Deleting a Collection

If you delete a collection, you delete the collection and all of its members. Be aware that if you do not delete a collection, it is eventually deleted when the session is purged.

Deleting All Collections for the Current Application

Use the DELETE_ALL_COLLECTIONS method to delete all collections defined in the current application.

Deleting All Collections in the Current Session

Use the DELETE_ALL_COLLECTIONS_SESSION method to delete all collections defined in the current session.

Adding Members to a Collection

When data elements (or members) are added to a collection, they are assigned a unique sequence ID. As you add members to a collection, the sequence ID is change in increments of 1, with the newest members having the largest ID.

You add new members to a collection using the ADD_MEMBER function. Calling this function returns the sequence ID of the newly added member.

You can also add new members (or an array of members) to a collection using the ADD_MEMBERS procedure. The number of members added is based on the number of elements in the first array.

About the Parameters p_generate_md5, p_clob001, p_blob001, and p_xmltype001

Use the p_generate_md5 flag to specify if the message digest of the data of the collection member should be computed. By default, this flag is set to NO. Use this parameter to check the MD5 of the collection member (that is, compare it with another member or see if a member has changed).

Use p_clob001 for collection member attributes which exceed 4,000 characters. Use p_blob001 for binary collection member attributes. Use p_xmltype001 to store well-formed XML.

Updating Collection Members

You can update collection members by calling the UPDATE_MEMBER procedure and referencing the desired collection member by its sequence ID. The UPDATE_MEMBER procedure replaces an entire collection member, not individual member attributes.

Use the p_clob001 parameter for collection member attributes which exceed 4,000 characters.

To update a single attribute of a collection member, use the UPDATE_MEMBER_ATTRIBUTE procedure.

Deleting Collection Members

You can delete a collection member by calling the DELETE_MEMBER procedure and referencing the desired collection member by its sequence ID. Note that this procedure leaves a gap in the sequence IDs in the specified collection.

You can also delete all members from a collection by when an attribute matches a specific value. Note that the DELETE_MEMBERS procedure also leaves a gap in the sequence IDs in the specified collection. If the supplied attribute value is null, then all members of the named collection are deleted where the attribute (specified by p_attr_number) is null.

Obtaining a Member Count

Use COLLECTION_MEMBER_COUNT to return the total count of all members in a collection. Note that this count does not indicate the highest sequence in the collection.

Resequencing a Collection

Use RESEQUENCE_COLLECTION to resequence a collection to remove any gaps in sequence IDs while maintaining the same element order.

Verifying Whether a Collection Exists

Use COLLECTION_EXISTS to determine if a collection exists.

Adjusting a Member Sequence ID

You can adjust the sequence ID of a specific member within a collection by moving the ID up or down. When you adjust a sequence ID, the specified ID is exchanged with another ID. For example, if you were to move the ID 2 up, 2 becomes 3, and 3 would become 2.

Use MOVE_MEMBER_UP to adjust a member sequence ID up by one. Alternately, use MOVE_MEMBER_DOWN to adjust a member sequence ID down by one.

Sorting Collection Members

Use the SORT_MEMBERS method to reorder members of a collection by the column number. This method sorts the collection by a particular column number and also reassigns the sequence IDs for each member to remove gaps.

Clearing Collection Session State

Clearing the session state of a collection removes the collection members. A shopping cart is a good example of when you might need to clear collection session state. When a user requests to empty the shopping cart and start again, you must clear the session state for a collection. You can remove session state of a collection by calling the TRUNCATE_COLLECTION method or by using f?p syntax.

Calling the TRUNCATE_COLLECTION method deletes the existing collection and then recreates it, for example:

APEX_COLLECTION.TRUNCATE_COLLECTION(
    p_collection_name => collection name);

You can also use the sixth f?p syntax argument to clear session state, for example:

f?p=App:Page:Session::NO:collection name

Determining Collection Status

The p_generate_md5 parameter determines if the MD5 message digests are computed for each member of a collection. The collection status flag is set to FALSE immediately after you create a collection. If any operations are performed on the collection (such as add, update, truncate, and so on), this flag is set to TRUE.

You can reset this flag manually by calling RESET_COLLECTION_CHANGED.

Once this flag has been reset, you can determine if a collection has changed by calling COLLECTION_HAS_CHANGED.

When you add a new member to a collection, an MD5 message digest is computed against all 50 attributes and the CLOB attribute if the p_generated_md5 parameter is set to YES. You can access this value from the MD5_ORIGINAL column of the view APEX_COLLECTION. You can access the MD5 message digest for the current value of a specified collection member by using the function GET_MEMBER_MD5.

ADD_MEMBER Procedure

Use this procedure to add a new member to an existing collection. An error is raised if the specified collection does not exist for the current user in the same session for the current Application ID. Gaps are not used when adding a new member, so an existing collection with members of sequence IDs (1,2,5,8) adds the new member with a sequence ID of 9.

Syntax

APEX_COLLECTION.ADD_MEMBER (
    p_collection_name IN VARCHAR2,
    p_c001 IN VARCHAR2 default null,
    ...
    p_c050 IN VARCHAR2 default null,
    p_n001 IN NUMBER default null,
    p_n002 IN NUMBER default null,
    p_n003 IN NUMBER default null,
    p_n004 IN NUMBER default null,
    p_n005 IN NUMBER default null,
    p_d001 IN DATE default null,
    p_d002 IN DATE default null,
    p_d003 IN DATE default null,
    p_d004 IN DATE default null,
    p_d005 IN DATE default null,
    p_clob001 IN CLOB default empty_clob(),
    p_blob001 IN BLOB default empty_blob(),
    p_xmltype001 IN XMLTYPE default null,
    p_generate_md5 IN VARCHAR2  default 'NO');

Parameters

Table 5-1 describes the parameters available in the ADD_MEMBER procedure.

Example

The following is an example of the ADD_MEMBER procedure.

APEX_COLLECTION.ADD_MEMBER(
        p_collection_name => 'GROCERIES'
        p_c001            => 'Grapes',
        p_c002            => 'Imported',
        p_n001            => 125,
        p_d001            => sysdate );
END;

ADD_MEMBER Function

Use this function to add a new member to an existing collection. Calling this function returns the sequence ID of the newly added member. An error is raised if the specified collection does not exist for the current user in the same session for the current Application ID. Gaps are not used when adding a new member, so an existing collection with members of sequence IDs (1,2,5,8) adds the new member with a sequence ID of 9.

Syntax

APEX_COLLECTION.ADD_MEMBER (
    p_collection_name IN VARCHAR2,
    p_c001 IN VARCHAR2 default null,
    ...
    p_c050 IN VARCHAR2 default null,
    p_n001 IN NUMBER default null,
    p_n002 IN NUMBER default null,
    p_n003 IN NUMBER default null,
    p_n004 IN NUMBER default null,
    p_n005 IN NUMBER default null,
    p_d001 IN DATE default null,
    p_d002 IN DATE default null,
    p_d003 IN DATE default null,
    p_d004 IN DATE default null,
    p_d005 IN DATE default null,
    p_clob001 IN CLOB default empty_clob(),
    p_blob001 IN BLOB default empty_blob(),
    p_xmltype001 IN XMLTYPE default null,
    p_generate_md5 IN VARCHAR2 default 'NO')
RETURN NUMBER;

Parameters

Table 5-2 describes the parameters available in the ADD_MEMBER function.

Example

DECLARE
    l_seq number;
BEGIN
    l_seq := APEX_COLLECTION.ADD_MEMBER(
                 p_collection_name => 'GROCERIES'
                 p_c001            => 'Grapes',
                 p_c002            => 'Imported',
                 p_n001            => 125,
                 p_d001            => sysdate );
END;

ADD_MEMBERS Procedure

Use this procedure to add an array of members to a collection. An error is raised if the specified collection does not exist for the current user in the same session for the current Application ID. Gaps are not used when adding a new member, so an existing collection with members of sequence IDs (1,2,5,8) adds the new member with a sequence ID of 9. The count of elements in the p_c001 PL/SQL table is used as the total number of items across all PL/SQL tables. For example, if p_c001.count is 2 and p_c002.count is 10, only 2 members are added. If p_c001 is null an application error is raised.

Syntax

APEX_COLLECTION.ADD_MEMBERS (
    p_collection_name IN VARCHAR2,
    p_c001 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
    p_c002 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
    p_c003 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
    ...
    p_c050 IN APEX_APPLICATION_GLOBAL.VC_ARR2 default empty_vc_arr,
    p_n001 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
    p_n002 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
    p_n003 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
    p_n004 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
    p_n005 IN APEX_APPLICATION_GLOBAL.N_ARR default empty_n_arr,
    p_d001 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
    p_d002 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
    p_d003 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
    p_d004 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
    p_d005 IN APEX_APPLICATION_GLOBAL.D_ARR default empty_d_arr,
    p_generate_md5 IN VARCHAR2 default 'NO');

Parameters

Table 5-3 describes the parameters available in the ADD_MEMBERS procedure.

Example

The following example shows how to add two new members to the EMPLOYEE table.

Begin
    APEX_COLLECTION.ADD_MEMBERS(
        p_collection_name => 'EMPLOYEE',
        p_c001 => l_arr1,
        p_c002 => 1_arr2);
End;

COLLECTION_EXISTS Function

Use this function to determine if a collection exists. A TRUE is returned if the specified collection exists for the current user in the current session for the current Application ID, otherwise FALSE is returned.

Syntax

APEX_COLLECTION.COLLECTION_EXISTS (
    p_collection_name IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 5-4 describes the parameters available in the COLLECTION_EXISTS function.

Example

The following example shows how to use the COLLECTION_EXISTS function to determine if the collection named EMPLOYEES exists.

Begin
    l_exists := APEX_COLLECTION.COLLECTION_EXISTS (
        p_collection_name => 'EMPLOYEES');
End;

COLLECTION_HAS_CHANGED Function

Use this function to determine if a collection has changed since it was created or the collection changed flag was reset.

Syntax

APEX_COLLECTION.COLLECTION_HAS_CHANGED (
    p_collection_name IN VARCHAR2)
RETURN BOOLEAN;

Parameters

Table 5-5 describes the parameters available in the COLLECTION_HAS_CHANGED function.

Example

The following example shows how to use the COLLECTION_HAS_CHANGED function to determine if the EMPLOYEES collection has changed since it was created or last reset.

Begin
    l_exists := APEX_COLLECTION.COLLECTION_HAS_CHANGED (
       p_collection_name => 'EMPLOYEES');
End;

COLLECTION_MEMBER_COUNT Function

Use this function to get the total number of members for the named collection. If gaps exist, the total member count returned is not equal to the highest sequence ID in the collection. If the named collection does not exist for the current user in the current session, an error is raised.

Syntax

APEX_COLLECTION.COLLECTION_MEMBER_COUNT (
    p_collection_name IN VARCHAR2)
RETURN NUMBER;

Parameters

Table 5-6 describes the parameters available in the COLLECTION_MEMBER_COUNT function.

Example

This example shows how to use the COLLECTION_MEMBER_COUNT function to get the total number of members in the DEPARTMENTS collection.

Begin
    l_count := APEX_COLLECTION.COLLECTION_MEMBER_COUNT( p_collection_name => 'DEPARTMENTS';
End;

CREATE_COLLECTION Procedure

Use this procedure to create an empty collection that does not already exist. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.CREATE_COLLECTION(
    p_collection_name IN VARCHAR2);

Parameters

Table 5-7 describes the parameters available in the CREATE_COLLECTION procedure.

Example

This example shows how to use the CREATE_COLLECTION procedure to create an empty collection named EMPLOYEES.

Begin
    APEX_COLLECTION.CREATE_COLLECTION(
        p_collection_name => 'EMPLOYEES');
End;

CREATE_OR_TRUNCATE_COLLECTION Procedure

Use this procedure to create a collection. If a collection exists with the same name for the current user in the same session for the current Application ID, all members of the collection are removed. In other words, the named collection is truncated.

Syntax

APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
    p_collection_name IN VARCHAR2);

Parameters

Table 5-8 describes the parameters available in the CREATE_OR_TRUNCATE_COLLECTION procedure.

Example

This example shows how to use the CREATE_OR_TRUNCATE_COLLECTION procedure to remove all members in an existing collection named EMPLOYEES.

Begin
    APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(
        p_collection_name => 'EMPLOYEES');
End;

CREATE_COLLECTION_FROM_QUERY Procedure

Use this procedure to create a collection from a supplied query. The query is parsed as the application owner. This method can be used with a query with up to 50 columns in the SELECT clause. These columns in the SELECT clause populates the 50 character attributes of the collection (C001 through C050). If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2,
    p_generate_md5 IN VARCHAR2 default 'NO');

Parameters

Table 5-9 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY procedure.

Example

The following example shows how to use the CREATE_COLLECTION_FROM_QUERY procedure to create a collection named AUTO and populate it with data from the AUTOS table. Because p_generate_md5 is 'YES', the MD5 checksum is computed to allow comparisons to determine change status.

Begin
    l_query := 'select make, model, year from AUTOS';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'AUTO', 
        p_query => l_query,
        p_generate_md5 => 'YES');
End;

CREATE_COLLECTION_FROM_QUERY2 Procedure

Use this procedure to create a collection from a supplied query. This method is identical to CREATE_COLLECTION_FROM_QUERY, however, the first 5 columns of the SELECT clause must be numeric and the next 5 must be date. After the numeric and date columns, there can be up to 50 character columns in the SELECT clause. The query is parsed as the application owner. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2,
    p_generate_md5 IN VARCHAR2 default 'NO');

Parameters

Table 5-10 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY2 procedure.

Example

The following example shows how to use the CREATE_COLLECTION_FROM_QUERY2 procedure to create a collection named EMPLOYEE and populate it with data from the EMP table. The first five columns (mgr, sal, comm, deptno, and null) are all numeric. Because p_generate_md5 is 'NO', the MD5 checksum is not computed.

begin;
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 (
        p_collection_name => 'EMPLOYEE', 
        p_query => 'select empno, sal, comm, deptno, null, hiredate, null, null, null, null, ename, job, mgr from emp',
        p_generate_md5 => 'NO');
end;

CREATE_COLLECTION_FROM_QUERY_B Procedure

Use this procedure to create a collection from a supplied query using bulk operations. This method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY method. The query is parsed as the application owner. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised.

This procedure uses bulk dynamic SQL to perform the fetch and insert operations into the named collection. Two limitations are imposed by this procedure:

1. The MD5 checksum for the member data is not computed.

2. No column value in query p_query can exceed 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. In Oracle Database 11g Release 2 (11.2.0.1) or later, this column limit is 4,000 bytes.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2.
    p_names IN apex_application_global.vc_arr2 DEFAULT,
    p_values IN apex_applicatioN_globa.vc_arr2 DEFAULT,
    p_max_row_count IN NUMBER DEFAULT);

Parameters

Table 5-11 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY_B procedure.

Example

The following examples shows how to use the CREATE_COLLECTION_FROM_QUERY_B procedure to create a collection named AUTO and populate it with data from the AUTOS table.

Begin
    l_query := 'select make, model, year from AUTOS';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B (
        p_collection_name => 'AUTO', 
        p_query => l_query);
End;

CREATE_COLLECTION_FROM_QUERYB2 Procedure

Use this procedure to create a collection from a supplied query using bulk operations. This method offers significantly faster performance than the CREATE_COLLECTION_FROM_QUERY_2 method. The query is parsed as the application owner. If a collection exists with the same name for the current user in the same session for the current Application ID, an application error is raised. It is identical to the CREATE_COLLECTION_FROM_QUERY_B, however, the first five columns of the SELECT clause must be numeric and the next five columns must be date. After the date columns, there can be up to 50 character columns in the SELECT clause

This procedure uses bulk dynamic SQL to perform the fetch and insert operations into the named collection. Two limitations are imposed by this procedure:

1. The MD5 checksum for the member data is not computed.

2. No column value in query p_query can exceed 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. In Oracle Database 11g Release 2 (11.2.0.1) or later, this column limit is 4,000 bytes.

Syntax

APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
    p_collection_name IN VARCHAR2,
    p_query IN VARCHAR2.
    p_names IN apex_application_global.vc_arr2 DEFAULT,
    p_values IN apex_applicatioN_globa.vc_arr2 DEFAULT,
    p_max_row_count IN NUMBER DEFAULT);

Parameters

Table 5-12 describes the parameters available in the CREATE_COLLECTION_FROM_QUERYB2 procedure.

Example

The following example shows how to use the CREATE_COLLECTION_FROM_QUERYB2 procedure to create a collection named EMPLOYEES and populate it with data from the EMP table. The first five columns (mgr, sal, comm, deptno, and null) are all numeric and the next five are all date. Because p_generate_md5 is 'NO', the MD5 checksum is not computed.

Begin
    l_query := 'select empno, sal, comm, deptno, null, hiredate, null, null, null, null, ename, job, mgr from emp';
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERYB2 (
        p_collection_name => 'EMPLOYEES', 
        p_query => l_query,
        p_generate_md5 => 'NO');
End;

DELETE_ALL_COLLECTIONS Procedure

Use this procedure to delete all collections that belong to the current user in the current Application Express session for the current Application ID.

Syntax

APEX_COLLECTION.DELETE_ALL_COLLECTIONS;

Parameters

None.

Example

This example shows how to use the DELETE_ALL_COLLECTIONS procedure to remove all collections that belong to the current user in the current session and Application ID.

Begin
    APEX_COLLECTION.DELETE_ALL_COLLECTIONS;
End;

DELETE_ALL_COLLECTIONS_SESSION Procedure

Use this procedure to delete all collections that belong to the current user in the current Application Express session regardless of the Application ID.

Syntax

APEX_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;

Parameters

None.

Example

This example shows how to use the DELETE_ALL_COLLECTIONS_SESSION procedure to remove all collections that belong to the current user in the current session regardless of Application ID.

Begin
    APEX_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;
End;

DELETE_COLLECTION Procedure

Use this procedure to delete a named collection. All members that belong to the collection are removed and the named collection is dropped. If the named collection does not exist for the same user in the current session for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.DELETE_COLLECTION (
    p_collection_name IN VARCHAR2);

Parameters

Table 5-13 describes the parameters available in the DELETE_COLLECTION procedure.

Example

This example shows how to use the DELETE_COLLECTION procedure to remove the 'EMPLOYEE' collection.

Begin
    APEX_COLLECTION.DELETE_COLLECTION(
        p_collection_name => 'EMPLOYEE');
End;

DELETE_MEMBER Procedure

Use this procedure to delete a specified member from a given named collection. If the named collection does not exist for the same user in the current session for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.DELETE_MEMBER (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2);

Parameters

Table 5-14 describes the parameters available in the DELETE_MEMBER procedure.

Example

This example shows how to use the DELETE_MEMBER procedure to remove the member with a sequence ID of '2' from the collection named EMPLOYEES.

Begin
    APEX_COLLECTION.DELETE_MEMBER(
        p_collection_name => 'EMPLOYEES',
        p_seq => '2');
End;

DELETE_MEMBERS Procedure

Use this procedure to delete all members from a given named collection where the attribute specified by the attribute number equals the supplied value. If the named collection does not exist for the same user in the current session for the current Application ID, an application error is raised. If the attribute number specified is invalid or outside the range of 1 to 50, an error is raised.

If the supplied attribute value is null, then all members of the named collection are deleted where the attribute, specified by p_attr_number, is null.

Syntax

APEX_COLLECTION.DELETE_MEMBERS (
    p_collection_name IN VARCHAR2,
    p_attr_number IN VARCHAR2,
    p_attr_value IN VARCHAR2);

Parameters

Table 5-14 describes the parameters available in the DELETE_MEMBERS procedure.

Example

The following example deletes all members of the collection named 'GROCERIES' where the 5th character attribute is equal to 'APPLE'.

Begin
    apex_collection.delete_members( 
        p_collection_name => 'GROCERIES'
        p_attr_number     => 5,
        p_attr_value      => 'APPLE' );
    Commit;
End;

GET_MEMBER_MD5 Function

Use this function to compute and return the message digest of the attributes for the member specified by the sequence ID. This computation of message digest is equal to the computation performed natively by collections. Thus, the result of this function could be compared to the MD5_ORIGINAL column of the view wwv_flow_collections.

If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised.

Syntax

APEX_COLLECTION.GET_MEMBER_MD5 (
    p_collection_name IN VARCHAR2,
    p_seq IN NUMBER)
RETURN VARCHAR2;

Parameters

Table 5-16 describes the parameters available in the GET_MEMBER_MD5 function.

Example

The following example computes the MD5 for the 5th member of the GROCERIES collection.

declare
    l_md5 varchar2(4000);
begin
    l_md5 := apex_collection.get_member_md5( 
        p_collection_name => 'GROCERIES'
        p_seq             => 10 );
end;

MERGE_MEMBERS Procedure

Use this procedure to merge members of the given named collection with the values passed in the arrays. If the named collection does not exist one is created. If a p_init_query is provided, the collection is created from the supplied SQL query. If the named collection exists, the following occurs:

1. Rows in the collection and not in the arrays are deleted.

2. Rows in the collections and in the arrays are updated.

3. Rows in the arrays and not in the collection are inserted.

The count of elements in the p_c001 PL/SQL table is used as the total number of items across all PL/SQL tables. For example, if p_c001.count is 2 and p_c002.count is 10, only 2 members are merged. If p_c001 is null an application error is raised.

Syntax

APEX_COLLECTION.MERGE_MEMBERS (
    p_collection_name IN VARCHAR2,
    p_seq  IN APEX_APPLICATION_GLOBAL.VC_ARR2 DEFAULT empty_vc_arr,
    p_c001 IN APEX_APPLICATION_GLOBAL.VC_ARR2 DEFAULT empty_vc_arr,
    p_c002 IN APEX_APPLICATION_GLOBAL.VC_ARR2 DEFAULT empty_vc_arr,
    p_c003 IN APEX_APPLICATION_GLOBAL.VC_ARR2 DEFAULT empty_vc_arr,
    ...
    p_c050 IN APEX_APPLICATION_GLOBAL.VC_ARR2 DEFAULT empty_vc_arr,
    p_null_index  IN NUMBER DEFAULT 1,
    p_null_value  IN VARCHAR2 DEFAULT null,
    p_init_query  IN VARCHAR2 DEFAULT null);

Parameters

Table 5-17 describes the parameters available in the MERGE_MEMBERS procedure.

Example

The following example creates a collection on the table of employees, and then merges the contents of the local arrays with the collection, updating the job of two employees.

DECLARE
    l_seq   APEX_APPLICATION_GLOBAL.VC_ARR2;
    l_c001  APEX_APPLICATION_GLOBAL.VC_ARR2;
    l_c002  APEX_APPLICATION_GLOBAL.VC_ARR2;
    l_c003  APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
    l_seq(1)  := 1;
    l_c001(1) := 7369;
    l_c002(1) := 'SMITH';
    l_c003(1) := 'MANAGER';
    l_seq(2)  := 2;
    l_c001(2) := 7499;
    l_c002(2) := 'ALLEN';
    l_c003(2) := 'CLERK';
 
    APEX_COLLECTION.MERGE_MEMBERS(
        p_collection_name => 'EMPLOYEES',
        p_seq => l_seq,
        p_c001 => l_c001,
        p_c002 => l_c002,
        p_c003 => l_c003,
        p_init_query => 'select empno, ename, job from emp order by empno');
END;

MOVE_MEMBER_DOWN Procedure

Use this procedure to adjust the sequence ID of specified member in the given named collection down by one (subtract one), swapping sequence ID with the one it is replacing. For example, 3 becomes 2 and 2 becomes 3. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the member specified by sequence ID p_seq is the lowest sequence in the collection, an application error is NOT returned.

Syntax

APEX_COLLECTION.MOVE_MEMBER_DOWN (
    p_collection_name IN VARCHAR2,
    p_seq IN NUMBER);

Parameters

Table 5-19 describes the parameters available in the MOVE_MEMBER_DOWN procedure.

Example

This example shows how to a member of the EMPLOYEES collection down one position. After executing this example, sequence ID '5' becomes sequence ID '4' and sequence ID '4' becomes sequence ID '5'.

BEGIN;
    APEX_COLLECTION.MOVE_MEMBER_DOWN( 
        p_collection_name => 'EMPLOYEES',
        p_seq => '5' );
END;

MOVE_MEMBER_UP Procedure

Use this procedure to adjust the sequence ID of specified member in the given named collection up by one (add one), swapping sequence ID with the one it is replacing. For example, 2 becomes 3 and 3 becomes 2. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the member specified by sequence ID p_seq is the highest sequence in the collection, an application error is not returned.

Syntax

APEX_COLLECTION.MOVE_MEMBER_UP (
    p_collection_name IN VARCHAR2,
    p_seq IN NUMBER);

Parameters

Table 5-19 describes the parameters available in the MOVE_MEMBER_UP procedure.

Example

This example shows how to a member of the EMPLOYEES collection down one position. After executing this example, sequence ID '5' becomes sequence ID '6' and sequence ID '6' becomes sequence ID '5'.

BEGIN;
    APEX_COLLECTION.MOVE_MEMBER_UP( 
        p_collection_name => 'EMPLOYEES',
        p_seq => '5' );
END;

RESEQUENCE_COLLECTION Procedure

For a named collection, use this procedure to update the seq_id value of each member so that no gaps exist in the sequencing. For example, a collection with the following set of sequence IDs (1,2,3,5,8,9) becomes (1,2,3,4,5,6). If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.RESEQUENCE_COLLECTION (
    p_collection_name IN VARCHAR2);

Parameters

Table 5-20 describes the parameters available in the RESEQUENCE_COLLECTION procedure.

Example

This example shows how to resequence the DEPARTMENTS collection to remove gaps in the sequence IDs.

BEGIN;
    APEX_COLLECTION.RESEQUENCE_COLLECTION (
        p_collection_name => 'DEPARTMENTS');
END;

RESET_COLLECTION_CHANGED Procedure

Use this procedure to reset the collection changed flag (mark as not changed) for a given collection. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.RESET_COLLECTION_CHANGED (
    p_collection_name IN VARCHAR2);

Parameters

Table 5-21 describes the parameters available in the RESET_COLLECTION_CHANGED procedure.

Example

This example shows how to reset the changed flag for the DEPARTMENTS collection.

BEGIN;
    APEX_COLLECTION.RESET_COLLECTION_CHANGED (
        p_collection_name => 'DEPARTMENTS');
END;

RESET_COLLECTION_CHANGED_ALL Procedure

Use this procedure to reset the collection changed flag (mark as not changed) for all collections in the user's current session.

Syntax

APEX_COLLECTION.RESET_COLLECTION_CHANGED_ALL; (

Parameters

None.

Example

This example shows how to reset the changed flag for all collections in the user's current session.

BEGIN;
    APEX_COLLECTION.RESET_COLLECTION_CHANGED_ALL;
END;

SORT_MEMBERS Procedure

Use this procedure to reorder the members of a given collection by the column number specified by p_sort_on_column_number. This sorts the collection by a particular column/attribute in the collection and reassigns the sequence IDs of each number such that no gaps exist. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.SORT_MEMBERS (
    p_collection_name IN VARCHAR2,
    p_sort_on_column_number IN NUMBER);

Parameters

Table 5-22 describes the parameters available in the SORT_MEMBERS procedure.

Example

In this example, column 2 of the DEPARTMENTS collection is the department location. The collection is reorder according to the department location.

BEGIN;
    APEX_COLLECTION.SORT_MEMBERS (
        p_collection_name => 'DEPARTMENTS',
        p_sort_on_column_number => '2';
END;

TRUNCATE_COLLECTION Procedure

Use this procedure to remove all members from a named collection. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised.

Syntax

APEX_COLLECTION.TRUNCATE_COLLECTION (
    p_collection_name IN VARCHAR2);

Parameters

Table 5-23 describes the parameters available in the TRUNCATE_COLLECTION procedure.

Example

This example shows how to remove all members from the DEPARTMENTS collection.

BEGIN;
    APEX_COLLECTION.TRUNCATE_COLLECTION(
        p_collection_name => 'DEPARTMENTS');
END;

UPDATE_MEMBER Procedure

Use this procedure to update the specified member in the given named collection. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER (
    p_collection_name IN VARCHAR2,
    p_seq  IN VARCHAR2 DEFAULT NULL,
    p_c001 IN VARCHAR2 DEFAULT NULL,
    p_c002 IN VARCHAR2 DEFAULT NULL,
    p_c003 IN VARCHAR2 DEFAULT NULL,
    ...
    p_c050 IN VARCHAR DEFAULT NULL,
    p_n001 IN NUMBER DEFAULT NULL,
    p_n002 IN NUMBER DEFAULT NULL,
    p_n003 IN NUMBER DEFAULT NULL,
    p_n004 IN NUMBER DEFAULT NULL,
    p_n005 IN NUMBER DEFAULT NULL,
    p_d001 IN DATE DEFAULT NULL,
    p_d002 IN DATE DEFAULT NULL,
    p_d003 IN DATE DEFAULT NULL,
    p_d004 IN DATE DEFAULT NULL,
    p_d005 IN DATE DEFAULT NULL,   
    p_clob001 IN CLOB DEFAULT empty_clob(),
    p_blob001 IN BLOB DEFAULT empty-blob(),
    p_xmltype001 IN XMLTYPE DEFAULT NULL);

Parameters

Table 5-24 describes the parameters available in the UPDATE_MEMBER procedure.

Example

Update the second member of the collection named 'Departments', updating the first member attribute to 'Engineering' and the second member attribute to 'Sales'.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_c001 => 'Engineering',
        p_c002 => 'Sales');

UPDATE_MEMBERS Procedure

Use this procedure to update the array of members for the given named collection. If a collection does not exist with the specified name for the current user in the same session and for the current Application ID, an application error is raised. The count of elements in the p_seq PL/SQL table is used as the total number of items across all PL/SQL tables. That is, if p_seq.count = 2 and p_c001.count = 10, only 2 members are updaapex_application_globalted. If p_seq is null, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBERS (
    p_collection_name IN VARCHAR2,
    p_seq  IN apex_application_global.VC_ARR2 DEFAULT empty_vc_arr,
    p_c001 IN apex_application_global.VC_ARR2 DEFAULT empty_vc_arr,
    p_c002 IN apex_application_global.VC_ARR2 DEFAULT empty_vc_arr,
    p_c003 IN apex_application_global.VC_ARR2 DEFAULT empty_vc_arr,
    ...
    p_c050 IN apex_application_global.VC_ARR2 DEFAULT empty_vc_arr,
    p_n001 IN apex_application_global.N_ARR DEFAULT empty_n_arr,
    p_n002 IN apex_application_global.N_ARR DEFAULT empty_n_arr,
    p_n003 IN apex_application_global.N_ARR DEFAULT empty_n_arr,
    p_n004 IN apex_application_global.N_ARR DEFAULT empty_n_arr,
    p_n005 IN apex_application_global.N_ARR DEFAULT empty_n_arr,
    p_d001 IN apex_application_global.D_ARR DEFAULT empty_d_arr,
    p_d002 IN apex_application_global.D_ARR DEFAULT empty_d_arr,
    p_d003 IN apex_application_global.D_ARR DEFAULT empty_d_arr,
    p_d004 IN apex_application_global.D_ARR DEFAULT empty_d_arr,
    p_d005 IN apex_application_global.D_ARR DEFAULT empty_d_arr)

Parameters

Table 5-25 describes the parameters available in the UPDATE_MEMBERS procedure.

Example

DECLARE
    l_seq   apex_application_global.vc_arr2;
    l_carr  apex_application_global.vc_arr2;
    l_narr  apex_application_global.n_arr;
    l_darr  apex_application_global.d_arr;
BEGIN
    l_seq(1)  := 10;
    l_seq(2)  := 15;
    l_carr(1) := 'Apples';
    l_carr(2) := 'Grapes';
    l_narr(1) := 100;
    l_narr(2) := 150;
    l_darr(1) := sysdate;
    l_darr(2) := sysdate;
 
    APEX_COLLECTION.UPDATE_MEMBERS (
        p_collection_name => 'Groceries',
        p_seq  => l_seq,
        p_c001 => l_carr,
        p_n001 => l_narr,
        p_d001 => l_darr);
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 1

Update the specified member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the range 1-50, an error is raised. Any attribute value exceeding 4,000 bytes are truncated to 4,000 bytes.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_attr_number IN VARCHAR2,
    p_attr_value  IN VARCHAR2);

Parameters

Table 5-26 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 1.

Example

Update the second member of the collection named 'Departments', updating the first member attribute to 'Engineering' and the second member attribute to 'Sales'.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_attr_number => '1',
        p_attr_value => 'Engineering');
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 2

Update the specified CLOB member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for CLOB), an error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_clob_number IN NUMBER,
    p_clob_value  IN CLOB);

Parameters

Table 5-27 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 2.

Example

The following example sets the first and only CLOB attribute of collection sequence number 2 in the collection named 'Departments' to a value of 'Engineering'.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_clob_number => '1',
        p_clob_value => 'Engineering');
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 3

Update the specified BLOB member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for BLOB), an error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_blob_number IN NUMBER,
    p_blob_value  IN BLOB);

Parameters

Table 5-28 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 3.

Example

The following example sets the first and only BLOB attribute of collection sequence number 2 in the collection named 'Departments' to a value of the BLOB variable l_blob_content.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_blob_number => '1',
        p_blob_value => l_blob_content);
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 4

Update the specified XMLTYPE member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for XMLTYPE), an error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_xmltype_number IN NUMBER,
    p_xmltype_value  IN BLOB);

Parameters

Table 5-29 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 4.

Example

The following example sets the first and only XML attribute of collection sequence number 2 in the collection named 'Departments' to a value of the XMLType variable l_xmltype_content.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_xmltype_number => '1',
        p_xmltype_value => l_xmltype_content);
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 5

Update the specified NUMBER member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the valid range (currently only 1 through 5 for NUMBER), an error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_attr_number IN NUMBER,
    p_number_value  IN NUMBER);

Parameters

Table 5-30 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 5.

Example

The following example sets the first numeric attribute of collection sequence number 2 in the collection named 'Departments' to a value of 3000.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_attr_number => '1',
        p_number_value => 3000);
END;

UPDATE_MEMBER_ATTRIBUTE Procedure Signature 6

Update the specified DATE member attribute in the given named collection. If a collection does not exist with the specified name for the current user in the same session for the current Application ID, an application error is raised. If the member specified by sequence ID p_seq does not exist, an application error is raised. If the attribute number specified is invalid or outside the valid range (currently only 1 through 5 for DATE), an error is raised.

Syntax

APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
    p_collection_name IN VARCHAR2,
    p_seq IN VARCHAR2,
    p_attr_number IN NUMBER,
    p_date_value  IN DATE);

Parameters

Table 5-30 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE procedure signature 6.

Example

Update the first attribute of the second collection member in collection named 'Departments', and set it to a value of 100.

BEGIN;
    APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE (
        p_collection_name => 'Departments',
        p_seq => '2',
        p_attr_number => '1',
        p_date_value => 100 );
END;