13.42 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:
-
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 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
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.
Table 13-19 MERGE_MEMBERS Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
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. |
|
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. |
|
Identifies the sequence number of the collection to be merged. |
|
That is 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. In other words, tell the merge function to ignore this row. This results in the null rows being removed from the collection. The null index works with the null value. If the value of the |
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;
Parent topic: APEX_COLLECTION