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.

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

Use APEX authorisation scheme within PL/SQL

Check whether the logged in user passes one of the APEX authorization schemes with the following PL/SQL function call

Check whether the logged in user passes one of the APEX authorization schemes with the following PL/SQL function call

apex_util.public_check_authorization('AUTHORIZATION_NAME');

APEX URL parameters

The APEX URL is constructed as follows

http://www.SERVER_ADDRESS.com/f?p=1:2:3:4:5:6:7:8:9

The numbers are to be replaced with the following

1 – The number or alias of the application you wish to run. To access the current application id, use &APP_ID. (remember to include the final dot)

2 – The page number or page alias. The page alias can be set in the page attributes.

3 – The session. to access the current session, use &SESSION. (remember to include the final dot)

4 – The request that you want to pass to the destination page.

5 – DEBUG. Decide whether the page is run in debug mode or not. either YES or NO.

6 – This parameter can accept two values. RP = resets the pagination of the destination page followed by a comma separated list of pages where you wish to reset the cache. So for example if you want to reset pagination and reset the cache for pages 1 and 2 you would include “RP,1,2” for this parameter.

7 – Comma separated list of page items to assign values to.

8 – Comma separated list of values to assign to the page items specified at position 7. The values must be in the same order as the page items are.

9 – Printerfriendly. Determines if the page is being rendered in printer friendly mode. YES or blank.