Building Hyperlinks in Oracle APEX Reports

Wednesday, January 21st, 2009

Oracle APEX is an incredibly easy to use and powerful development framework, however, just like any other framework when it comes to doing anything bespoke it can be an incredible pain to work with.

Consider the following piece of PL/SQL:

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT
query.EMAIL_ID,
apex_item.checkbox(1,query.EMAIL_ID) sel,
DECODE(query.READ_FLAG,'N','<span class="unread_email">'||query.SENDER||'</span>',query.SENDER) SDR,
DECODE(query.READ_FLAG,'N','<span class="unread_email"><a href="f?p='||:APP_ID||':40:'||:APP_SESSION||'::'||:DEBUG||'::P40_EMAIL_ID,P40_BRANCH:'||query.EMAIL_ID||'%2C4">'||query.SUBJECT||'</a></span><a href="f?p='||:APP_ID||':40:'||:APP_SESSION||'::'||:DEBUG||'::P40_EMAIL_ID,P40_BRANCH:'||query.EMAIL_ID||'%2C4">'||query.SUBJECT||'</a>') SUB,
DECODE(query.READ_FLAG,'N','<span class="unread_email">'||to_char(query.CREATED_ON, 'DD/MM/YYYY')||'</span>',TO_CHAR(query.CREATED_ON, 'DD/MM/YYYY')) E_DATE,
DECODE(query.READ_FLAG,'N','<span class="unread_email">'||query.EMAIL_SIZE||'</span>',query.EMAIL_SIZE) E_SIZE
FROM
EPS_EMAIL query
WHERE
EMAIL_OWNER = :APP_USER AND
EMAIL_LOCATION_ID = 1
ORDER BY CREATED_ON DESC

In the PL/SQL above, I’m using a decode to change the appearance of the results of the report, essentially making the text bold if the column READ_FLAG = ‘N’. This is accomplished by appending a span element with the class of ‘unread_email’ around the report data I am outputting. The corresponding CSS is shown below.

1
2
3

.unread_email {
    font-weight:bold;
}

I also wanted to make the subject column (aliased as ‘SUB’) link to another page in my APEX application. At first I tried doing it through APEX and adding in a link the easy way. That worked but it removed the styling I was trying to apply with my decode. The only solution was to insert the link directly into the PL/SQL which you can see in the PL/SQL above. It’s not the most elegant code but it works well and preserves formatting.

2 Comments

  • Joey says: July 26th, 2009 at 10:48 pm

    how does the decode function make the output to the screen bold – what is the command you are using?

  • Martin says: July 27th, 2009 at 12:44 pm

    Hi Joey,

    if you look at any of the decode statements in the code above, if the ‘READ_FLAG’ value from the query = ‘N’, I write out a span with the class of ‘unread_email’ around the data I am outputting in the report. This corresponds to the ‘unread_email’ class I have in my CSS which simply sets the font weight to bold. Apologies for not showing the CSS, I have now updated the article to show it.

Leave a comment