A while ago, I was in the middle of a live presentation about something or other (maybe Plug-ins) and I was complaining about my dislike of single-purpose hidden items. Before I could move on, I think it was Giliam that put his hand up and asked why I had so much dislike for hidden items. The truth is:

• I don’t mind hidden items when they are used as variables being set multiple times to solve problems

• What I object to, is using them as a workaround to bypass limitations when APEX already provides proper mechanisms to handle the situation.

The Problem

We have a page where we are creating a demonstration collection of 67 members (six,seven 😀) on a Before Header Process

BEGIN 
    APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY (
        p_collection_name => 'AUTO', 
        p_query => q'[SELECT level AS row_num
                    FROM dual
                    CONNECT BY level <= 67]', -- Six-Seven,
        p_generate_md5 => 'YES',
        p_truncate_if_exists => 'YES' );
END;

I want a Process button on the page which, when clicked, shows a confirmation alert like this…

We need a Process button that shows a confirmation alert displaying the collection count before proceeding:

The Basic Approach: Hidden Page Item

The straightforward solution involves:

1. Create a hidden Page Item P1_MEMBER_COUNT

2. Add a click Dynamic Action with:

   • A PL/SQL true action that sets P1_MEMBER_COUNT to the count:

   :P1_MEMBER_COUNT := 
     APEX_COLLECTION.COLLECTION_MEMBER_COUNT( p_collection_name => 'AUTO');
   

   • Followed by a Confirm Action

   • Followed by the processing action (I'm using an alert in this example - just to demonstrate)

The Dynamic Action structure looks like this:

Pros:

• Logical workflow

• Easy to implement

• Confirm action stops the chain if user clicks No

Cons:

• Requires a single-purpose page item 👿

• Needs explanation from me to to team members why I have a page Item called P1_MEMBER_COUNT

• Must be hidden to avoid user confusion

• Must be unprotected (triggers security tool warnings)

• Juggling between PL/SQL and Confirm actions

This works, but there are cleaner alternatives.

Alternative Solutions

Option 1: Set Value Over URL

Best for: When you have the collection count available on a previous page

If you have the value on a previous page, you can set a page item over the URL. Yeah - you still have to use a Page Item for this, however if you already have the value, no need to calculate it again.

Option 2: Use the Load JSON Object Process Plugin

Best for: When you know the value before page load.

Use the Load JSON Object Process Plugin in your After Footer placeholder - important:

This stores the count in a JSON object called window.autoCollection and then use a JavaScript to show the count as part of the confirm message like this.

Pros:

• Easy to implement

• No single-purpose page items 👿

Cons:

• Value is only available in JavaScript

• A Plug-in may not be for everyone

Option 3: AJAX Callback (Recommended)

Best for: When the collection count is dynamic and changes during page interaction

This is my preferred approach for most scenarios. It eliminates page items entirely and keeps your Dynamic Action clean.

Step 1: So first we have to create the AJAX Callback which writes the JSON to the buffer

Step 2: Use apex.server.process in your Dynamic Action to fetch the count and trigger the confirm:

Why this is best: This is generally my favorite approach because:

• No Page items

• Using an AJAX Callback as it was intended

• Clean Dynamic Action with just one action

• Handles dynamic values perfectly

• All native

Trade-off:

• Slightly higher learning curve with JavaScript

Option 4: Use the Load JSON Object Dynamic Action Plugin

Best for: When you need advanced features and prefer plugin functionality

This Dynamic Action Plug-in has a few more bells & whistles than the Process Plug-in of the same name

Step 1: It runs Source (e.g SQL, PL/SQL) which writes in to a page variable e.g. pageData

Step 2: It runs (optional) JavaScript Code which can directly use that page variable.

It works like this

Pros:

• Easy to implement

• No single-purpose page items 👿

Cons:

• Value is only available in JavaScript

• A Plug-in may not be for everyone

Bonus - Getting Page Objects back into PL/SQL

Options 2, 3 & 4 above work by writing the DB result into a Page Variable. If you need that value back into PL/SQL to run some further DB code then this can be problematic … fear not, I’ve got a solution to that.

Look a couple pictures up, there is this line….

window.myErrors = [1247, 3891, 5632];

… say for example your JavaScript spat out some errors in an array like that, you could go from JavaScript object to PL/SQL to process those errors with the Pretius JS Object to Collection Plug-In

In the above, the value from window.myErrors is added to the CLOB001 column of an APEX Collection named JS_OBJECT_COLLECTION with an index (C001) of myErrors. Check out the video

You can see in the above that D001 gets set with the current_timestamp

This way I can shoot a JS value (e.g Objects, variables, arrays, JSON, etc) to PL/SQL very easily and then loop around those values using something like this

SELECT jt.value
FROM APEX_COLLECTIONS ac
CROSS JOIN JSON_TABLE(ac.CLOB001, '$[*]'
     COLUMNS (value NUMBER PATH '$')
) jt
WHERE ac.collection_name = 'JS_OBJECT_COLLECTION'
  AND ac.C001 = 'myErrors';

I’ve put the above SQL into an Interactive Report to prove I can access them

Conclusion

The conclusion is simple. I want this blog to make you:

• reconsider using single-use hidden items in favour of AJAX Callbacks

• be aware of Plug-in alternatives that can help you in these situations

ENJOY!

Whats the picture? It’s the hay barn at the stables where we keep the horses.