Collections enable you to temporarily capture one or more nonscalar values. You can use collections to store rows and columns currently in session state so they can be accessed, manipulated, or processed during a user's specific session. You can think of a collection as a bucket in which you temporarily store and name rows of information.
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.
Topics:
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 will be 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
.
See Also:
"Accessing a Collection", "CREATE_COLLECTION Procedure Parameters", "CREATE_OR_TRUNCATE_COLLECTION Procedure Parameters"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 will 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 will be raised during execution.
The MD5 checksum will not be 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.
See Also:
"CREATE_COLLECTION Procedure", "CREATE_OR_TRUNCATE_COLLECTION Procedure", "CREATE_COLLECTION_FROM_QUERY Procedure", "CREATE_COLLECTION_FROM_QUERY2 Procedure", "CREATE_COLLECTION_FROM_QUERY_B Procedure", "CREATE_COLLECTION_FROM_QUERYB2 Procedure"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).
See Also:
"Determining Collection Status" for information about using theGET_MEMBER_MD5
function, "GET_MEMBER_MD5 Function"You can access the members of a collection by querying the database view APEX_COLLECTIONS
. 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'
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.
See Also:
"MERGE_MEMBERS Procedure"If you truncate a collection, you remove all members from the specified collection, but the named collection remains in place.
See Also:
"TRUNCATE_COLLECTION Procedure"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 will eventually be deleted when the session is purged.
See Also:
"DELETE_COLLECTION Procedure"Use the DELETE_ALL_COLLECTIONS
method to delete all collections defined in the current application.
See Also:
"DELETE_ALL_COLLECTIONS Procedure"Use the DELETE_ALL_COLLECTIONS_SESSION
method to delete all collections defined in the current session.
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 will 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.
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.
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
.
See Also:
"UPDATE_MEMBER Procedure", "UPDATE_MEMBERS Procedure", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 1", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 2", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 3", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 4", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 5"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 will be deleted where the attribute (specified by p_attr_number
) is null.
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.
See Also:
"COLLECTION_MEMBER_COUNT Function"Use RESEQUENCE_COLLECTION
to resequence a collection to remove any gaps in sequence IDs while maintaining the same element order.
See Also:
"RESEQUENCE_COLLECTION Procedure"Use COLLECTION_EXISTS
to determine if a collection exists.
See Also:
"COLLECTION_EXISTS Function"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.
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.
See Also:
"SORT_MEMBERS Procedure"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
See Also:
"TRUNCATE_COLLECTION Procedure"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
.
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) will add the new member with a sequence ID of 9.
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');
Table 3-1 describes the parameters available in the ADD_MEMBER
procedure.
Note:
Any character attribute exceeding 4,000 characters is truncated to 4,000 characters.Table 3-1 ADD_MEMBER Procedure Parameters
Parameter | Description |
---|---|
|
The name of an existing collection. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
Attribute value of the member to be added. Maximum length is 4,000 bytes. Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. |
|
Attribute value of the numeric attributes to be added. |
|
Attribute value of the date attribute. |
|
Use p_clob001 for collection member attributes that exceed 4,000 characters. |
|
Use p_blob001 for binary collection member attributes. |
|
Use p_xmltype001 to store well-formed XML. |
|
Valid values include |
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;
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) will add the new member with a sequence ID of 9.
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;
Table 3-2 describes the parameters available in the ADD_MEMBER
function.
Note:
Any character attribute exceeding 4,000 characters is truncated to 4,000 characters.Table 3-2 ADD_MEMBER Function Parameters
Parameter | Description |
---|---|
|
The name of an existing collection. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
Attribute value of the member to be added. Maximum length is 4,000 bytes. Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. |
|
Attribute value of the numeric attributes to be added. |
|
Attribute value of the date attribute to be added. |
|
Use p_clob001 for collection member attributes that exceed 4,000 characters. |
|
Use p_blob001 for binary collection member attributes. |
|
Use p_xmltype001 to store well-formed XML. |
|
Valid values include |
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;
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) will add the new member with a sequence ID of 9. The count of elements in the p_c001 PL/SQL table will be 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 will be added. If p_c001 is null an application error is raised.
APEX_COLLECTION.ADD_MEMBERS ( p_collection_name IN VARCHAR2, p_c001 IN APEX_APPLICATION_GLOBAL.VC_ARR2 defulat empty_vc_arr, p_c002 IN APEX_APPLICATION_GLOBAL.VC_ARR2 defulat empty_vc_arr, p_c003 IN APEX_APPLICATION_GLOBAL.VC_ARR2 defulat empty_vc_arr, ... p_c050 IN APEX_APPLICATION_GLOBAL.VC_ARR2 defulat empty_vc_arr, p_n001 IN APEX_APPLICATION_GLOBAL.N_ARR defulat empty_n_arr, p_n002 IN APEX_APPLICATION_GLOBAL.N_ARR defulat empty_n_arr, p_n003 IN APEX_APPLICATION_GLOBAL.N_ARR defulat empty_n_arr, p_n004 IN APEX_APPLICATION_GLOBAL.N_ARR defulat empty_n_arr, p_n005 IN APEX_APPLICATION_GLOBAL.N_ARR defulat empty_n_arr, p_generate_md5 IN VARCHAR2 default 'NO');
Table 3-3 describes the parameters available in the ADD_MEMBERS
procedure.
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 3-3 ADD_MEMBERS Procedure Parameters
Parameter | Description |
---|---|
|
The name of an existing collection. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
Attribute value of the member to be added. Maximum length is 4,000 bytes. Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. |
|
Valid values include |
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;
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.
APEX_COLLECTION.COLLECTION_EXISTS ( p_collection_name IN VARCHAR2) RETURN BOOLEAN;
Table 3-4 describes the parameters available in the COLLECTION_EXISTS
function.
Table 3-4 COLLECTION_EXISTS Function Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length is 255 bytes. The collection name is not case sensitive and is converted to upper case. |
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;
Use this function to determine if a collection has changed since it was created or the collection changed flag was reset.
APEX_COLLECTION.COLLECTION_HAS_CHANGED ( p_collection_name IN VARCHAR2) RETURN BOOLEAN;
Table 3-5 describes the parameters available in the COLLECTION_HAS_CHANGED
function.
Table 3-5 COLLECTION_HAS_CHANGED Function Parameters
Parameter | Description |
---|---|
|
The name of the collection. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
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;
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.
APEX_COLLECTION.COLLECTION_MEMBER_COUNT ( p_collection_name IN VARCHAR2) RETURN NUMBER;
Table 3-6 describes the parameters available in the COLLECTION_MEMBER_COUNT
function.
Table 3-6 COLLECTION_MEMBER_COUNT Function Parameters
Parameter | Description |
---|---|
|
The name of the collection. |
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;
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.
APEX_COLLECTION.CREATE_COLLECTION( p_collection_name IN VARCHAR2);
Table 3-7 describes the parameters available in the CREATE_COLLECTION
procedure.
Table 3-7 CREATE_COLLECTION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session. |
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;
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.
APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION( p_collection_name IN VARCHAR2);
Table 3-8 describes the parameters available in the CREATE_OR_TRUNCATE_COLLECTION
procedure.
Table 3-8 CREATE_OR_TRUNCATE_COLLECTION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. All members of the named collection are removed if the named collection exists for the current user in the current session. |
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;
Use this procedure to create a collection from a supplied query. The query will be 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 will populate 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.
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY ( p_collection_name IN VARCHAR2, p_query IN VARCHAR2, p_generate_md5 IN VARCHAR2 default 'NO');
Table 3-9 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY
procedure.
Table 3-9 CREATE_COLLECTION_FROM_QUERY Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session. |
|
Query to execute in order to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor. |
|
Valid values include |
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 will be 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;
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 will be 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.
APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY2 ( p_collection_name IN VARCHAR2, p_query IN VARCHAR2, p_generate_md5 IN VARCHAR2 default 'NO');
Table 3-10 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY2
procedure.
Table 3-10 CREATE_COLLECTION_FROM_QUERY2 Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session. |
|
Query to executed in order to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor. |
|
Valid values include |
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;
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 will be 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:
The MD5 checksum for the member data will not be computed.
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 will be raised during execution. In Oracle Database 11gR2 11.2.0.1 or later, this column limit is 4,000 bytes.
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);
Table 3-11 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY
_B procedure.
Table 3-11 CREATE_COLLECTION_FROM_QUERY_B Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session. |
|
Query to executed in order to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor. |
|
Array of bind variable names used in the query statement. |
|
Array of bind variable values used in the bind variables in the query statement. |
|
Maximum number of rows returned from the query in p_query which should be added to the collection. |
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;
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 will be 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:
The MD5 checksum for the member data will not be computed.
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 will be raised during execution. In Oracle Database 11gR2 11.2.0.1 or later, this column limit is 4,000 bytes.
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);
Table 3-12 describes the parameters available in the CREATE_COLLECTION_FROM_QUERY
B2 procedure.
Table 3-12 CREATE_COLLECTION_FROM_QUERYB2 Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection. The maximum length is 255 characters. An error is returned if this collection exists with the specified name of the current user and in the same session. |
|
Query to executed in order to populate the members of the collection. If p_query is numeric, it is assumed to be a DBMS_SQL cursor. |
|
Array of bind variable names used in the query statement. |
|
Array of bind variable values used in the bind variables in the query statement. |
|
Maximum number of rows returned from the query in p_query which should be added to the collection. |
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;
Use this procedure to delete all collections that belong to the current user in the current Application Express session regardless of the current Application ID.
APEX_COLLECTION.DELETE_ALL_COLLECTIONS;
None.
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;
Use this procedure to delete all collections that belong to the current user in the current Flow session regardless of the Application ID.
APEX_COLLECTION.DELETE_ALL_COLLECTIONS_SESSION;
None.
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;
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.
APEX_COLLECTION.DELETE_COLLECTION ( p_collection_name IN VARCHAR2);
Table 3-13 describes the parameters available in the DELETE_COLLECTION
procedure.
Table 3-13 DELETE_COLLECTION Procedure Parameters
Parameter | Description |
---|---|
|
The name of the collection to remove all members from and drop. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
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;
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.
APEX_COLLECTION.DELETE_MEMBER ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2);
Table 3-14 describes the parameters available in the DELETE_MEMBER
procedure.
Table 3-14 DELETE_MEMBER Parameters
Parameter | Description |
---|---|
|
The name of the collection to delete the specified member from. The maximum length is 255 characters. Collection names are not case sensitive and are converted to upper case. An error is returned if this collection does not exist for the current user in the same session. |
|
This is the sequence ID of the collection member to be deleted. |
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;
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.
APEX_COLLECTION.DELETE_MEMBERS ( p_collection_name IN VARCHAR2, p_attr_number IN VARCHAR2, p_attr_value IN VARCHAR2);
Table 3-14 describes the parameters available in the DELETE_MEMBERS
procedure.
Table 3-15 DELETE_MEMBERS Parameters
Parameter | Description |
---|---|
|
The name of the collection to delete the specified members from. The maximum length is 255 characters. Collection names are not case sensitive and are converted to upper case. An error is returned if this collection does not exist for the current user in the same session. |
|
Attribute number of the member attribute used to match for the specified attribute value for deletion. Valid values are 1 through 50 and null. |
|
Attribute value of the member attribute used to match for deletion. Maximum length can be 4,000 bytes. The attribute value will be truncated to 4,000 bytes if greater than this amount. |
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;
This function is used 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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised.
APEX_COLLECTION.GET_MEMBER_MD5 ( p_collection_name IN VARCHAR2, p_seq IN NUMBER) RETURN VARCHAR2;
Table 3-16 describes the parameters available in the GET_MEMBER_MD5
function.
Table 3-16 GET_MEMBER_MD5 Parameters
Parameter | Description |
---|---|
|
The name of the collection to add this array of members to. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
p_seq |
Sequence ID of the collection member. |
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;
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 will be 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 will be merged. If p_c001 is null an application error is raised.
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);
Table 3-17 describes the parameters available in the MERGE_MEMBERS
procedure.
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 3-17 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 in conjunction with the null value. If the value of the |
p_null_value |
Used in conjunction 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. |
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;
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 will be 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.
APEX_COLLECTION.MOVE_MEMBER_DOWN ( p_collection_name IN VARCHAR2, p_seq IN NUMBER);
Table 3-19 describes the parameters available in the MOVE_MEMBER_DOWN
procedure.
Table 3-18 MOVE_MEMBER_DOWN 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. An error is returned if this collection does not exist with the specified name of the current user in the same session. |
|
Identifies the sequence number of the collection member to be moved down by one. |
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;
See Also:
"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 will be 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.
APEX_COLLECTION.MOVE_MEMBER_UP ( p_collection_name IN VARCHAR2, p_seq IN NUMBER);
Table 3-19 describes the parameters available in the MOVE_MEMBER_UP
procedure.
Table 3-19 MOVE_MEMBER_UP 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. An error is returned if this collection does not exist with the specified name of the current user in the same session. |
|
Identifies the sequence number of the collection member to be moved up by one. |
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;
See Also:
"MOVE_MEMBER_DOWN 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 will be raised.
APEX_COLLECTION.RESEQUENCE_COLLECTION ( p_collection_name IN VARCHAR2);
Table 3-20 describes the parameters available in the RESEQUENCE_COLLECTION
procedure.
Table 3-20 RESEQUENCE_COLLECTION Parameters
Parameter | Description |
---|---|
|
The name of the collection to resequence. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
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;
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 will be raised.
APEX_COLLECTION.RESET_COLLECTION_CHANGED ( p_collection_name IN VARCHAR2);
Table 3-21 describes the parameters available in the RESET_COLLECTION_CHANGED
procedure.
Table 3-21 RESET_COLLECTION_CHANGED Parameters
Parameter | Description |
---|---|
|
The name of the collection to reset the collection changed flag. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
This example shows how to reset the changed flag for the DEPARTMENTS
collection.
BEGIN; APEX_COLLECTION.RESET_COLLECTION_CHANGED ( p_collection_name => 'DEPARTMENTS'); END;
See Also:
"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.
APEX_COLLECTION.RESET_COLLECTION_CHANGED_ALL; (
None.
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;
See Also:
"RESET_COLLECTION_CHANGED Procedure".Use this procedure to reorder the members of a given collection by the column number specified by p_sort_on_column_number. This will sort the collection by a particular column/attribute in the collection and reassign 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 will be raised.
APEX_COLLECTION.SORT_MEMBERS ( p_collection_name IN VARCHAR2, p_sort_on_column_number IN NUMBER);
Table 3-22 describes the parameters available in the SORT_MEMBERS
procedure.
Table 3-22 SORT_MEMBERS Parameters
Parameter | Description |
---|---|
|
The name of the collection to sort. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
The column number used to sort the collection. |
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;
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 will be raised.
APEX_COLLECTION.TRUNCATE_COLLECTION ( p_collection_name IN VARCHAR2);
Table 3-23 describes the parameters available in the TRUNCATE_COLLECTION
procedure.
Table 3-23 TRUNCATE_COLLECTION Parameters
Parameter | Description |
---|---|
|
The name of the collection to truncate. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
This example shows how to remove all members from the DEPARTMENTS collection.
BEGIN; APEX_COLLECTION.TRUNCATE_COLLECTION( p_collection_name => 'DEPARTMENTS'); END;
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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error is raised.
Note:
Using this procedure sets the columns identified and nullifies any columns not identified. If you need to update specific columns, without affecting the values of other columns, use "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 1".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_blob IN BLOB DEFAULT empty-blob(), p_xmltype001 IN XMLTYPE DEFAULT NULL);
Table 3-24 describes the parameters available in the UPDATE_MEMBER
procedure.
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 3-24 UPDATE_MEMBER Parameters
Parameter | Description |
---|---|
|
The name of the collection to update. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
Attribute value of the member to be added. Maximum length is 4,000 bytes. Any character attribute exceeding 4,000 characters is truncated to 4,000 characters. |
|
Attribute value of the numeric attributes to be added or updated. |
|
Attribute value of the date attributes to be added or updated. |
|
Use p_clob001 for collection member attributes that exceed 4,000 characters. |
|
Use p_blob001 for binary collection member attributes. |
|
Use p_xmltype001 to store well-formed XML. |
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');
See Also:
"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 will be raised. The count of elements in the p_seq PL/SQL table will be 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 will be updated. 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.
APEX_COLLECTION.UPDATE_MEMBERS ( p_collection_name IN VARCHAR2, p_seq IN wwv_flow_global.VC_ARR2 DEFAULT empty_vc_arr, p_c001 IN wwv_flow_global.VC_ARR2 DEFAULT empty_vc_arr, p_c002 IN wwv_flow_global.VC_ARR2 DEFAULT empty_vc_arr, p_c003 IN wwv_flow_global.VC_ARR2 DEFAULT empty_vc_arr, ... p_c050 IN wwv_flow_global.VC_ARR2 DEFAULT empty_vc_arr, p_n001 IN wwv_flow_global.N_ARR DEFAULT empty_n_arr, p_n002 IN wwv_flow_global.N_ARR DEFAULT empty_n_arr, p_n003 IN wwv_flow_global.N_ARR DEFAULT empty_n_arr, p_n004 IN wwv_flow_global.N_ARR DEFAULT empty_n_arr, p_n005 IN wwv_flow_global.N_ARR DEFAULT empty_n_arr, p_d001 IN wwv_flow_global.D_ARR DEFAULT empty_d_arr, p_d002 IN wwv_flow_global.D_ARR DEFAULT empty_d_arr, p_d003 IN wwv_flow_global.D_ARR DEFAULT empty_d_arr, p_d004 IN wwv_flow_global.D_ARR DEFAULT empty_d_arr, p_d005 IN wwv_flow_global.D_ARR DEFAULT empty_d_arr)
Table 3-25 describes the parameters available in the UPDATE_MEMBERS
procedure.
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 3-25 UPDATE_MEMBERS Parameters
Parameter | Description |
---|---|
|
The name of the collection to update. Maximum length is 255 bytes. Collection names are not case sensitive and are converted to upper case. |
|
Array of member sequence IDs to be updated. The count of the p_seq array is used across all arrays. |
|
Array of attribute values to be updated. |
|
Attribute value of numeric |
|
Array of date attribute values to be updated. |
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;
See Also:
"UPDATE_MEMBER Procedure"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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the range 1-50, an error will be raised. Any attribute value exceeding 4,000 bytes will be truncated to 4,000 bytes.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_attr_number IN VARCHAR2, p_attr_value IN VARCHAR2);
Table 3-26 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 1.
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 3-26 UPDATE_MEMBER_ATTRIBUTE Signature 1 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the member attribute to be updated. Valid values are 1 through 50. Any number outside of this range will be ignored. |
|
Attribute value of the member attribute to be updated. |
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 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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for CLOB), an error will be raised.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_clob_number IN NUMBER, p_clob_value IN CLOB);
Table 3-27 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 2.
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 3-27 UPDATE_MEMBER_ATTRIBUTE Signature 2 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the CLOB member attribute to be updated. Valid value is 1. Any number outside of this range will be ignored. |
|
Attribute value of the CLOB member attribute to be updated. |
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 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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for BLOB), an error will be raised.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_blob_number IN NUMBER, p_blob_value IN BLOB);
Table 3-28 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 3.
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 3-28 UPDATE_MEMBER_ATTRIBUTE Signature 3 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the BLOB member attribute to be updated. Valid value is 1. Any number outside of this range will be ignored. |
|
Attribute value of the BLOB member attribute to be updated. |
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 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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the valid range (currently only 1 for XMLTYPE), an error will be raised.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_xmltype_number IN NUMBER, p_xmltype_value IN BLOB);
Table 3-29 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 4.
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 3-29 UPDATE_MEMBER_ATTRIBUTE Signature 4 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the XMLTYPE member attribute to be updated. Valid value is 1. Any number outside of this range will be ignored. |
|
Attribute value of the XMLTYPE member attribute to be updated. |
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 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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the valid range (currently only 1 through 5 for NUMBER), an error will be raised.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_attr_number IN NUMBER, p_number_value IN NUMBER);
Table 3-30 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 5.
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 3-30 UPDATE_MEMBER_ATTRIBUTE Signature 5 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the NUMBER member attribute to be updated. Valid value is 1 through 5. Any number outside of this range will be ignored. |
|
Attribute value of the NUMBER member attribute to be updated. |
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;
See Also:
"UPDATE_MEMBER_ATTRIBUTE Procedure Signature 1", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 2", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 3", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 4", "UPDATE_MEMBER_ATTRIBUTE Procedure Signature 6", "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 will be raised. If the member specified by sequence ID p_seq does not exist, an application error will be raised. If the attribute number specified is invalid or outside the valid range (currently only 1 through 5 for DATE), an error will be raised.
APEX_COLLECTION.UPDATE_MEMBER_ATTRIBUTE ( p_collection_name IN VARCHAR2, p_seq IN VARCHAR2, p_attr_number IN NUMBER, p_number_value IN DATE);
Table 3-30 describes the parameters available in the UPDATE_MEMBER_ATTRIBUTE
procedure signature 6.
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 3-31 UPDATE_MEMBER_ATTRIBUTE Signature 6 Parameters
Parameter | Description |
---|---|
|
The name of the collection. Maximum length can be 255 bytes. Collection_names are case-insensitive, as the collection name will be converted to upper case. An error is returned if this collection does not exist with the specified name of the current user and in the same session. |
|
Sequence ID of the collection member to be updated. |
|
Attribute number of the DATE member attribute to be updated. Valid value is 1 through 5. Any number outside of this range will be ignored. |
|
Attribute value of the DATE member attribute to be updated. |
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_number_value => 100 ); END;