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.

3 thoughts on “How to query an APEX collection”

  1. @Drew if this post was supposed to be a complete tutorial, then I would agree with you, but it’s there as a reminder of how to query a collection. Often I have 90% of the solution in my head, but need a reminder of a missing piece. That’s what this is here, a reminder, not a full tutorial.

  2. Here be dragons (APEX 4.2.5.x): When you use a lowercase name for the collection in order to add members to it, the name of the collection is converted to uppercase internally. When you query it as mentioned in the post and use lowercase letters it does not work. You better use uppercase for the collection name all the time.

Leave a Reply