How to query an APEX collection

This example shows how to query a collection called TEST_COLLECTION.

SELECT c001, c002, c003
FROM APEX_collections
WHERE collection_name = 'TEST_COLLECTION';

In my example I am only selecting three columns, but the APEX_COLLECTIONS view has the following structure:

Column Name Data Type Null?
COLLECTION_NAME VARCHAR2 (255 Byte) N
SEQ_ID NUMBER N
C001 VARCHAR2 (4000 Byte) Y
C002 VARCHAR2 (4000 Byte) Y
C003 VARCHAR2 (4000 Byte) Y
C004 VARCHAR2 (4000 Byte) Y
C005 VARCHAR2 (4000 Byte) Y
C006 VARCHAR2 (4000 Byte) Y
C007 VARCHAR2 (4000 Byte) Y
C008 VARCHAR2 (4000 Byte) Y
C009 VARCHAR2 (4000 Byte) Y
C010 VARCHAR2 (4000 Byte) Y
C011 VARCHAR2 (4000 Byte) Y
C012 VARCHAR2 (4000 Byte) Y
C013 VARCHAR2 (4000 Byte) Y
C014 VARCHAR2 (4000 Byte) Y
C015 VARCHAR2 (4000 Byte) Y
C016 VARCHAR2 (4000 Byte) Y
C017 VARCHAR2 (4000 Byte) Y
C018 VARCHAR2 (4000 Byte) Y
C019 VARCHAR2 (4000 Byte) Y
C020 VARCHAR2 (4000 Byte) Y
C021 VARCHAR2 (4000 Byte) Y
C022 VARCHAR2 (4000 Byte) Y
C023 VARCHAR2 (4000 Byte) Y
C024 VARCHAR2 (4000 Byte) Y
C025 VARCHAR2 (4000 Byte) Y
C026 VARCHAR2 (4000 Byte) Y
C027 VARCHAR2 (4000 Byte) Y
C028 VARCHAR2 (4000 Byte) Y
C029 VARCHAR2 (4000 Byte) Y
C030 VARCHAR2 (4000 Byte) Y
C031 VARCHAR2 (4000 Byte) Y
C032 VARCHAR2 (4000 Byte) Y
C033 VARCHAR2 (4000 Byte) Y
C034 VARCHAR2 (4000 Byte) Y
C035 VARCHAR2 (4000 Byte) Y
C036 VARCHAR2 (4000 Byte) Y
C037 VARCHAR2 (4000 Byte) Y
C038 VARCHAR2 (4000 Byte) Y
C039 VARCHAR2 (4000 Byte) Y
C040 VARCHAR2 (4000 Byte) Y
C041 VARCHAR2 (4000 Byte) Y
C042 VARCHAR2 (4000 Byte) Y
C043 VARCHAR2 (4000 Byte) Y
C044 VARCHAR2 (4000 Byte) Y
C045 VARCHAR2 (4000 Byte) Y
C046 VARCHAR2 (4000 Byte) Y
C047 VARCHAR2 (4000 Byte) Y
C048 VARCHAR2 (4000 Byte) Y
C049 VARCHAR2 (4000 Byte) Y
C050 VARCHAR2 (4000 Byte) Y
CLOB001 CLOB Y
MD5_ORIGINAL VARCHAR2 (4000 Byte) Y

Although APEX collections can be useful for storing data temporarily, they do not perform well when used with hundreds, or thousands of rows.

Creating an APEX collection

APEX collections can be useful for storing temporary data in an array like structure.

This example shows how to create an APEX collection called TEST_COLLECTION. The name is unique to the user’s┬ásession.

apex_collection.collection_exists does exactly what you would expect it to do. There’s no point in creating it if it doesn’t exist, so check for it’s existence first. Also, you don’t want to truncate the collection if you’ve already stored data in there.

if not apex_collection.collection_exists(p_collection_name=>'TEST_COLLECTION') then
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'TEST_COLLECTION');
end if;

The next step after creating it would be to add rows to the collection.

Adding data to an APEX collection

This example shows how to add entries to a collection called TEST_COLLECTION. Before adding the entries, I ensure that the collection exists apex_collection.collection_exists.

begin

--only create a collection if it doesnt already exist
if not apex_collection.collection_exists(p_collection_name=>'TEST_COLLECTION') then
  APEX_COLLECTION.CREATE_OR_TRUNCATE_COLLECTION(p_collection_name => 'TEST_COLLECTION');
end if;

APEX_COLLECTION.ADD_MEMBER(p_collection_name => 'TEST_COLLECTION',
                                        p_c001          => :P1_TEST_ITEM_1,
                                        p_c002          => :P1_TEST_ITEM_2);

end;

That’s such a simplistic example that you may ask why you would even bother using a collection. Well, one use might be on a page where you want the user to build up a temporary set of inserts┬ábefore committing it to the database; a bit like a user might do within Oracle Forms.

Once you have added rows to the collection, the next logical step would be to query the APEX collection.

For reference, here is the structure of the underlying APEX_COLLECTIONS view

Column Name Data Type Null?
COLLECTION_NAME VARCHAR2 (255 Byte) N
SEQ_ID NUMBER N
C001 VARCHAR2 (4000 Byte) Y
C002 VARCHAR2 (4000 Byte) Y
C003 VARCHAR2 (4000 Byte) Y
C004 VARCHAR2 (4000 Byte) Y
C005 VARCHAR2 (4000 Byte) Y
C006 VARCHAR2 (4000 Byte) Y
C007 VARCHAR2 (4000 Byte) Y
C008 VARCHAR2 (4000 Byte) Y
C009 VARCHAR2 (4000 Byte) Y
C010 VARCHAR2 (4000 Byte) Y
C011 VARCHAR2 (4000 Byte) Y
C012 VARCHAR2 (4000 Byte) Y
C013 VARCHAR2 (4000 Byte) Y
C014 VARCHAR2 (4000 Byte) Y
C015 VARCHAR2 (4000 Byte) Y
C016 VARCHAR2 (4000 Byte) Y
C017 VARCHAR2 (4000 Byte) Y
C018 VARCHAR2 (4000 Byte) Y
C019 VARCHAR2 (4000 Byte) Y
C020 VARCHAR2 (4000 Byte) Y
C021 VARCHAR2 (4000 Byte) Y
C022 VARCHAR2 (4000 Byte) Y
C023 VARCHAR2 (4000 Byte) Y
C024 VARCHAR2 (4000 Byte) Y
C025 VARCHAR2 (4000 Byte) Y
C026 VARCHAR2 (4000 Byte) Y
C027 VARCHAR2 (4000 Byte) Y
C028 VARCHAR2 (4000 Byte) Y
C029 VARCHAR2 (4000 Byte) Y
C030 VARCHAR2 (4000 Byte) Y
C031 VARCHAR2 (4000 Byte) Y
C032 VARCHAR2 (4000 Byte) Y
C033 VARCHAR2 (4000 Byte) Y
C034 VARCHAR2 (4000 Byte) Y
C035 VARCHAR2 (4000 Byte) Y
C036 VARCHAR2 (4000 Byte) Y
C037 VARCHAR2 (4000 Byte) Y
C038 VARCHAR2 (4000 Byte) Y
C039 VARCHAR2 (4000 Byte) Y
C040 VARCHAR2 (4000 Byte) Y
C041 VARCHAR2 (4000 Byte) Y
C042 VARCHAR2 (4000 Byte) Y
C043 VARCHAR2 (4000 Byte) Y
C044 VARCHAR2 (4000 Byte) Y
C045 VARCHAR2 (4000 Byte) Y
C046 VARCHAR2 (4000 Byte) Y
C047 VARCHAR2 (4000 Byte) Y
C048 VARCHAR2 (4000 Byte) Y
C049 VARCHAR2 (4000 Byte) Y
C050 VARCHAR2 (4000 Byte) Y
CLOB001 CLOB Y
MD5_ORIGINAL VARCHAR2 (4000 Byte) Y