MERGE_MEMBERS Procedure

Merges 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:

Rows in the collection and not in the arrays are deleted.
Rows in the collections and in the arrays are updated.
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 two members are merged.

If p_c001 is NULL, an application error occurs.

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

Note:

Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. Also, the number of members added is based on the number of elements in the first array.

Parameter	Description
`p_collection_name`	The name of the collection. Maximum length is 255 bytes. Collection names are not case-sensitive and are converted to upper case.
`p_c001 through p_c050`	Array of attribute values to be merged. Maximum length is 4,000 bytes. Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. The count of the `p_c001` array is used across all arrays. If no values are provided, then no actions are performed.
`p_c0xx`	Attribute of `NN` attributes values to be merged. Maximum length can be 4,000 bytes. The attribute value is truncated to 4,000 bytes if greater than this amount.
`p_seq`	Identifies the sequence number of the collection member to be merged.
`p_null_index`	If the element identified by this value is NULL, then treat this row as a NULL row. For example, if `p_null_index` is `3`, then `p_c003` is treated as a NULL row. The merge function then ignores this row. This results in removing NULL rows from the collection. The NULL index works with the NULL value. If the value of the `p_cXXX` argument is equal to the `p_null_value`, then the row is treated as NULL.
`p_null_value`	Used with the `p_null_index` argument. Identifies the NULL value. If used, this value must not be NULL. A typical value for this argument is `0`.
`p_init_query`	If the collection does not exist, the collection is created using this query.

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;