Passing Item Values that Contain Colons to an Apex Page

One of the most helpful features of Oracle Application Express (APEX) applications that I've been able to exploit is the ability to drill down into a chart item.  I've been doing this here for applications without issue for a couple of years UNTIL I tried to drill down by passing a #LABEL# value that is a DATE that includes time (and a colon!).

We are currently using APEX 4.2, and according to the oracle docs the URL syntax for linking to pages is as follows:

f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

I want to focus on the itemNames and itemValues components of the URL.

itemNames	Comma-delimited list of item names used to set session state with a URL.
itemValues	List of item values used to set session state within a URL. Item values cannot include colons, but can contain commas if enclosed with backslashes. To pass a comma in an item value, enclose the characters with backslashes. For example: \123,45\

In my case, I need to pass a premise(99999), service type(Electric), channel(1), start date(24-AUG-2014 00:00), and an end date(25-AUG-2014 00:00) value in the url.

For example:

fp=108:4:112608898900151::NO::P4_PREMISE,P4_SERVICE_TYPE,P4_CHANNEL,P4_START_DATE,P4_END_DATE:99999,Electric,1,24-AUG-2014%2000:00,25-AUG-2014%2000:00

You'll notice that APEX encodes the spaces with %20.

The problem is that any colon after the last item (itemNames) is treated as an item delimiter!  So, the first date value of 24-AUG-2014 00:00 gets passed to page 4 as 24-AUG-2014 (no time) and the rest of the itemValues are ignored (25-AUG-2014 00:00 is not passed at all).

Hmm.  So Apex escapes the spaces but not the "non delimiting colons".  So I try to manually manipulate the URL to encode the colons with %3A:

fp=108:4:112608898900151::NO::P4_PREMISE,P4_SERVICE_TYPE,P4_CHANNEL,P4_START_DATE,P4_END_DATE:47000,Electric,1,24-AUG-2014%2000%3A00,25-AUG-2014%2000%3A00

No dice, same result as before with the escaped colon still being treated as a delimiter.  This means that using utl_https.escape on the second half of the URL would not work.  As a matter of fact it was worse as I would pass escaped characters to apex when building my URL and APEX would re-escape them on transmission(escape the % signs for example).

I do some googling around and see lots of activity on the subject with some folks hinting around at substituting the colons and spaces prior to passing to the new page, then replacing the values with spaces and colons.  So I decided to write a function to do this.

CREATE OR REPLACE FUNCTION AMFC.escape_url (V_URL_BASE      VARCHAR2,

                                            V_URL_SUFFIX    VARCHAR2)

   RETURN VARCHAR2

IS

   encoded_url   VARCHAR2 (4000);

BEGIN

   encoded_url :=

         v_url_base

      || replace(replace (v_url_suffix,' ','_SPACE_'),':','_COLON_');

   RETURN (encoded_url);

END escape_url;

/

I then call this function from my series sql to populate the link column and set the 

Here is what the URL that gets passed to page 4 looks like now.  You'll notice that all of the colons and spaces have been replaced on the second part of the URL (variable values) only as a result of the function call.

fp=108:4:112608898900151::NO::P4_PREMISE,P4_SERVICE_TYPE,P4_CHANNEL,P4_START_DATE,P4_END_DATE:47000,Electric,1,28-AUG-2014_SPACE_00_COLON_00,29-AUG-2014_SPACE_00_COLON_00

Since there are no colons being treated as delimeters, these values make it to their destination items on page 4.

The next step was to decode the values of _SPACE_ and _COLON_ to their correct values before the loading of the regions page 4.

I created two computations to handle this task:

Each computations is the same with the exception of the item referenced in the code.  Here is P4_START_DATE:

Success!