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

Creating a javascript popup window in APEX

Standard behaviour in APEX is for a branch to link to a page using the current window (or tab). There are times when you want the application to link to another page in a popup window.

To create the popup window I am going to use Javascript. This can be placed behind a button or text link, or it can be placed in an HTML region.

The code to create the popup is very simple

window.open (URL, windowName[, windowFeatures]);

windowName
This name is not visible by the user, and is only there for reference by us in our code. For the example below I have decided to use the name popup1. If window.open was called again, using “popup1” as the windowName, then the contents of the popup would be refreshed with this new call. That might be what you want, but I tend to prefer to use unique names.

URL
In the example code below I will simply call page 200 without passing in any page item values. If you wish to pass in values, or pass in a request value then read about URL parameters.

windowFeatures
This allows the developer to define specific features for the popup window. Eg no scrollbars, the size of the window, whether menu bars are visible.

The Javascript

window.open("f?p=&APP_ID.:200:&SESSION.","popup1");

The above code will open a new window (or tab depending on your specific browser settings) called popup1, containing page 200. Easy.

Manually set page process success message in APEX

You can set a process success or failure message via a setting in a page process, but you can also do it via PL/SQL using the following code

You can set a process success or failure message via a setting in a page process, but you can also do it via PL/SQL using the following code.

apex_application.g_print_success_message
    := 'Message you want to display';

Ensure that your branch has been set to show process success messages.

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.