APEX 20.1 report link with reserved character

Edit button navigation issue for db column name with # sign

Link stopped working and not passing on the data?

This is not post about naming standards but it will touch on it. Working on presentation material for this year I stumbled at this issue and wanted to share it here.

Let's say you create a report on classic DEMO_CUSTOMERS table like below.

Please notice that I added a column to it called CUST_ORG# defined as number

Ask now from business is to create a link to navigate to a page passing in CUST_ORG#. Let's see what happens.

After we created a page with classic report on it we define a link on first name column as

Perfect, save it and run the page click on the newly defined link. What would happen is we would get page 3 in this case and P3_CUST_ORG_NBR would have a value set.

Fine but then someone asked us to also include additional item P3_DUMMY setting it to value = 4434343. Again we go in we edit the link on first name same as before but guess what happens now

On the first this might look okay but there are two issues. If we were to run the page Modal dialog now instead would open like this

which your testers would report back to you that page is now broken. CUST_ORG# is not being set anymore? Or even your page might break.

Impossible all we have done is added another column to that link definition. There is no way second Save can be cause of that error.

Looking at that last edit link popup screen above, it is easy to notice that things got out of hand. Firstly value for P3_CUST_ORG_NBR is gone and secondly P3_DUMMY value is also wrong now as it contains 4434343 that we sent concatenated with 1 from CUST_ORG# plus APEX started adding Anchor details for us as well.

None of this is what we wanted so.... lesson learned here is try not to have #, $ and other special characters in your DB table column names and your SQL queries.

Emphasizing here, this issue is not caused by database column name itself but with query column alias when we did select * from DEMO_CUSTOMERS as this eventually messes up APEX builder wizard. # character is used internally for replacing data values with actual data from your reports in places like links or HTML expressions which in my case broke the link.

We could say it would be nice to see APEX handling this situation too but hey having naming standard in place would help here too. :)

On top of this, every save on this link we would do would simply it keep adding things to Anchor which might cause other problems on your page too that would not be this obvious. 

Remember if you do not have control over DB object naming standards make sure you bypass this problem with aliasing your query columns. In my example I could do

select...., cust_org# as cust_org_nbr from demo_customers

and then use this when defining my link attributes in APEX.

Alternative approach would be to use built in APIs like get_url and prepare_url:

SELECT APEX_PAGE.GET_URL (
        p_page   => 1,
        p_items  => 'P3_CUST_ORG_NBR,P3_DUMMY',
        p_values => cust_org#||','|| 4434343 ) get_url,      
APEX_UTIL.PREPARE_URL('f?p=&APP_ID.:1:&APP_SESSION.:::: P3_CUST_ORG_NBR,P3_DUMMY:' || cust_org#||','|| 4434343) prep_url
FROM DEMO_CUSTOMERS_LINO

and combine them with Column Formatting HTML EXPRESSION maybe. So simple and yet it can take some of your time to detect and fix it. 

Happy APEXing,
Lino