APEX URLs and HTML need not be in your SQL report queries

I've seen a few examples recently where people have been embedding URLs and HTML into their SQL queries. I tend to advocate keeping such things out of the query itself and using APEX functionality such as link target and html expressions (in SQL Query column attributes).

First example, a full page URL embedded into a column like so (conditionally based on some condition):

case when job = 'PRESIDENT'
then 'f?p='||:APP_ID||':2:'||:APP_SESSION||'::::P2_EMPNO:'||empno
else  'f?p='||:APP_ID||':3:'||:APP_SESSION||'::::P3_EMPNO:'||empno
end dest_url

Then on the column attributes, you make it a link by specifying the type as a link and the target as a URL specifying the target as #DEST_URL#




Taking a look at the URL, we can see there is only one bit changing, and that is the page number. So, we can avoid embedding the whole URL in our query by simply adding a column for our destination page:

case when job = 'PRESIDENT'
then 2
else  3
end dest_page

Then, when we specify our target, we use the page in this application option, and pass in the row value #DEST_PAGE#, like so:




Another example it is not uncommon to come across in samples is actually generating much more HTML in the query, for example:

'<button onclick="void(0);" type="button"'
        || ' class="t-Button t-Button--hot t-Button--small actionButton"'
        || ' data-empno="' || empno || '"'
        || ' data-job="' || job || '">'
        || ' <span class="t-Button-label">Click me!<span></button>'
    awesome_btn

After adding this, you also need to set security options to not escape special characters, in order for this to be rendered properly.

But again, it really is not necessary to add all this noise to your query. In a report, every column has a HTML Expression property where you can modify the column/row value to use custom HTML. So, from the previous example, we change the query just to add a new column - if you need a new field, use that, or just add an empty column to put your button in:


Then, in the column attributes, for HTML expression, it's just a matter of specifying that same HTML as from the previous example (less any PL/SQL and SQL bits - and using substitution strings where necessary).

<button 
    onclick="void(0);" 
    type="button" 
    class="t-Button t-Button--hot t-Button--small actionButton" 
    data-empno="#EMPNO#" 
    data-job="#JOB#"> 
    <span class="t-Button-label">
        Click me!(2)
    </span>
</button>

..

Obviously, this will come down to a personal preference on how you deal with such situations. I just prefer to remove unnecessary noise from my queries and use aspects of APEX that are available to me - rather than embedding as much as possible in the query source. 

Popular posts from this blog

Installing Oracle Instant Client on Ubuntu

Now that Oracle has enabled us to download instant client without any click through for accepting the license , I wanted to revisit a seamless install of the instant client on a new set up. Ubuntu has the documentation about installing the instant client here: https://help.ubuntu.com/community/Oracle%20Instant%20Client. First - because Oracle provides their releases in RPM archive format (or a tarball), in order to have an installer you need to create a DEB archive. There is a package in the archives, alien, which aids this process. This gives the start of the script: #!/bin/bash # Install dependencies sudo apt install alien The 3 packages the Ubuntu documentation tells us to retrieve are: - devel - basic (I opt for basiclite instead) - sqlplus So, over at the downloads page: https://www.oracle.com/database/technologies/instant-client/linux-x86-64-downloads.html we can grab the link. # Download files. Example specific to 19.3 # Some links were not correct on the do
Read more

Report row buttons firing a dynamic action

Image
A lot of the time on reports of data, we'll define a link column, and specify an image which is the beloved pencil icon. Which is good and all, but looking in the Sample Database Application (packaged application), they have taken a different approach to style links - a button that stretches the width of it's containing cell. So, the UI ends up looking like so: So, is this done? Well first, you define a new column for the placeholder. Suppose I want my link column to be Report, I define an extra column in my query: , 'Report' report_link Then I need to go to my new column's attributes, and change the column type to a link. And, in the link attributes, set the text to what you want displayed. Here, I'm just going to display the value of the new column and point to my desired page. So I set the target page, then also the Link text as #REPORT_LINK#. At this point the report will just show link text, as we expect. So, then to replicate t
Read more

Customising APEX built-in validation messages

Image
I was working on an application and when trying to save some data, I was getting these error messages despite the fact I had no validations on my page. And what I wanted to know, how do I change these message contents? APEX does some default checking based on entered data. A prime example of this is a date field. On a brand new application, I created a region and added a page item with the type as "Date Picker". If I enter text into the date input field that is not a date conforming to the date format, I will get the following error: "Date Field does not match format DS."   First of all, DS? What is that? This particular application was created by uploading a spreadsheet, and I did not set that. In fact, my spreadsheet had no date data to begin with. In saying that, a quick search in the globalization attributes, and somehow this application has the date format set to "DS". So first, let us change that - I like to use DD-MON-YYYY. So that fixes the error
Read more