Sunday, January 3, 2010

How to Automatically Remove Items and Values in APEX URLs

A while ago I wrote about how to pass multi-select lists through the URL: This method works, however it will only support up to 4000 characters as a result of the functions it uses.

What happens when a user selects more than 4000 characters? You could update the processes to handle clobs etc. I took a different approach when faced with this problem. Instead of trying to update the process to handle clobs I asked: Why am I passing the values through the URL? Wouldn't it be easier if I didn't pass values through the URL?

When I first thought about this, I thought I could run a process on each page that would set the appropriate variables to the calling page. But then developers would have to look in the page branch and page processes to see what items were being passed to which pages. This sounded confusing and goes against the APEX framework. I really don't like having developers do extra work when they don't need to or go against standards already put in place.

I took a different approach to this problem. Instead of having a special page process to pass values from one page to another I was able to "intercept" the page branch and modify it before it was executed. I did this by taking the page branch and manually setting the item/value pairs, then removing them from the page branch. Here's an example of the final solution:

Here are some screen shots from the demo application:

Page Branch configuration:

Passing in values via the URL (note that I'd like "X" to be "abc:def", however it's just abc)

Running the page process to manually remove the item/value pairs from the URL:

Before reviewing the code, it's important to note the following assumptions. I made these assumptions to make the code demo easier to read.

- Only 1 branch is defined on the page with no conditions etc.
- The branch only contains at most 1 clear cache
- The branch passes as most 1 item/value pair to the other page

- Create page process: Remove URL Params
- Branch Point: On Submit After Processing

Note: You could easily turn this into an application process to be run throughout your application

-- NOTE: For the purpose of this example I'm assuming the following:
-- Only 1 branch is defined with no conditions etc.
-- The branch only contains at most 1 clear cache
-- The branch passes as most 1 item/value pair to the other page

v_branch_action apex_application_page_branches.branch_action%TYPE;
p_app_id apex_application_page_branches.application_id%TYPE := :app_id;
p_page_id apex_application_page_branches.page_id%TYPE := :app_page_id;
v_clear_cache NUMBER;
v_item_names VARCHAR2 (4000);
v_item_values VARCHAR2 (4000);
-- Get branch action
-- The branch action is the URL before it is parsed by APEX
SELECT branch_action
INTO v_branch_action
FROM apex_application_page_branches
WHERE application_id = p_app_id AND page_id = p_page_id;

-- Get the clear cache options
v_clear_cache :=
SUBSTR (v_branch_action,
INSTR (v_branch_action, ':', 1, 5) + 1,
INSTR (v_branch_action, ':', 1, 6)
- INSTR (v_branch_action, ':', 1, 5)
- 1

-- Manually clear the cache if required
IF v_clear_cache IS NOT NULL
apex_util.clear_page_cache (p_page_id => v_clear_cache);

-- Get Page Param values
v_item_names :=
SUBSTR (v_branch_action,
INSTR (v_branch_action, ':', 1, 6) + 1,
INSTR (v_branch_action, ':', 1, 7)
- INSTR (v_branch_action, ':', 1, 6)
- 1
v_item_values :=
SUBSTR (v_branch_action,
INSTR (v_branch_action, ':', 1, 7) + 1,
INSTR (v_branch_action, '&success_msg=', 1, 1)
- INSTR (v_branch_action, ':', 1, 7)
- 1

-- If item/value pairs exist, manually set the values
IF v_item_names IS NOT NULL
-- See: for more info on apex_application.do_substitutions
(p_name => v_item_names,
p_value => apex_application.do_substitutions (TRIM (v_item_values))

-- Modify the branch action
-- Remove the clear cache option if applicable
IF v_clear_cache IS NOT NULL
v_branch_action :=
SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 5))
|| SUBSTR (v_branch_action, INSTR (v_branch_action, ':', 1, 6));

-- Remove the item name/value pairs if applicable
IF v_item_names IS NOT NULL
-- Remove item Names
v_branch_action :=
SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 6))
|| SUBSTR (v_branch_action, INSTR (v_branch_action, ':', 1, 7));
-- Remove item values
v_branch_action :=
SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 7))
|| SUBSTR (v_branch_action,
INSTR (v_branch_action, '&success_msg=', 1, 1)

-- Set the new branch action
apex_application.g_branch_action (1) := v_branch_action;


  1. Maybe I'm missing something, but wouldn't checking the "Save State Before Branching" checkbox do exactly this? Save the state, sanitize the URL and then branch?

    What does this offer over the built-in functionality other than someone may be on a version that didn't have that option yet.

  2. Hi Buzz,

    I didn't realize this feature was available. Thanks for pointing it out to me.

    You're right, my solution wouldn't offer anything better than checking that option in the branch. I'll be sure to update this post tonight with your comments.

    Thank you,


  3. No problem. That was one of the features they quietly snuck into 3.2. I didn't know what it was until I was curious one day and read the documentation.

    But your method still is good for those who aren't yet on 3.2

  4. I was looking at your example, but could not implement it using 2-level tabs. I am using your same theme (20). I am looking to change the Tabs, not the Parent Tabs. what would the code be like in that case?

    Also, would it dramatically change the code if I try to pass some parameters on the generated tab link? What would it look like?

    Thanks for your help.


    Angel Ortiz

  5. Hi Angel,

    It depends on how the tabs are submitted. Are they calling the "doSubmit" function or is it a direct link to another page?