While developing the dashboard for the Apex Dashboard Competition I found the challenge of presenting large numbers in a readable form. If you want to display the population of countries in a report there is China and India with more than 1 billion inhabitants and Tuvalu does not reach 10,000.
With a straight forward report it would look like the left image. The application apply of thousands separator improves the readability, but there is still too much detail in the numbers to easily get the meaning. The right image, where the numbers are formatted using size prefixes is much more readable.

You can see this in a useful example in the Apex World Dashboard. Select a country and look at the ranking table.

In Oracle Apex you can implement such formatting in your query, using a PL/SQL function. This solution has several disadadvantages:

  • Column header sorting is impossible because the actual numbers are replace by a character representation
  • Calling a PL/SQL function in a query can degrade performance
  • the readability of your query gets less

The solution is to apply the formatting using JavaScript. This way the query is not affected and the formatting is done after the sorting.
The formatting is implemented in a After Refresh DA on the report in question:

format_numbers_in_table('#report_tablespaces .t-Report-report');

The function formats all the cells with numeric content.
The parameter to the function should be the selector to the HTML table containing the data to be formatted. In this case the UT Standard Region and Standard Report are used with a static ID tablespaces.

If the report contains a large number of columns or some columns should not be formatted the columns to be formatted can be limited by passing an array of column names as the second parameter:

format_numbers_in_table('#report_tablespaces .t-Report-report',['LAND_SURFACE','POPULATION']);

Only the mentioned columns will be formatted.

Store the following JavaScript functions on the page or seperately in a file. You can also download the code here:

 * Display numbers readable
function display_number(number)
  var sizes = [ {"base":1000,"symbol":"K"}
  var display = number.toString();

  if ( !isNaN(number) ) 
    for ( i in sizes )
      if ( number > sizes[i].base ) 
        num1 = number / sizes[i].base;
        if ( num1  0 && col.innerHTML != ' ' )
        // if column list has content only format columns for this list
        var header = col.headers;          
        if ( column_list == null || column_list.indexOf(header) >= 0 )
            col.innerHTML = display_number(col.innerHTML);        

Happy Apexing

Source Article from http://dickdral.blogspot.com/2016/09/displaying-large-numbers-in-human.html

While looking for a suitable icon for an organization item on the login page I examined the collection of Apex icons. The big difference with the Font Awesome icons is that the Apex icons are less heavy. Therefore they are more suitable to enlarge where Font Awesome icons tend to appear bulky over a certain font size.
I looked for the Workspace Icon ( a database cylinder ), but I could not find it. While inspection showed that it was part of the core.min.css. After an hour of searching I looked at the source files in Firebug. There are two versions of this css file, one in the apex_ui folder and one in the app_ui folder.
I have created a page where both collections of icons are listed:

Press the button above to visit the page.
You can toggle the list between the standard icons or all the icons.

The icons can be used in HTML by adding the classes a-Icon and the class of the specific icon (the text behind the icon on the page):

<span class=”a-Icon icon-find”></span>

The Apex Builder icons can be used by referencing the file apex_icons.css. Put this file on your server or in the static files and reference it on the page with the icons.

Have fun using the icons,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/06/hidden-treasure-apex-5-icons.html

The founder of apex.world, Juergen Schuster, had a question about using flag images in reports and select lists. As I had used flags in my Apex World Dashboard I responded and pointed him to the dashboard. But that was not an answer to the flags in the select list. I did some investigation and the answer was not so easy to find. But I found it in the end 🙂 and I want to share this solution with you.

Flags in reports

For the use in reports flag sprites are used. The advantage of using sprites is that you only need two files, one css file and one image file. The image file contains one large image with all the flags and in the css each country has a style pointing to the right location on the image file. So the HTML:

<div class=“flag flag-nl”></div>

results in the flag of the Netherlands:

To implement this in Apex you have to load the files flags.css and flags.png and upload them as static files. Refer to the CSS file on your page.
Now all you have to do is create a div in your report with the right country code. In an Oracle Apex report you can select the country code:

select lower(country_code)  as country_code , country_name from countries;

Then in the HTML section of the country_code column paste:

<div class="flag flag-#COUNTRY_CODE#"/>

The text #COUNTRY_CODE# is replaced by the value in the row.
NB This value should be lower case because the class names are in lower case.

This method has been used in the Apex World Dashboard of which a clip of the country report is shown:

Flags in select lists (only Firefox 🙁 )

The flag in the select list caused me more headaches. The option element is very limited with regard to styling. No child elements are allowed. The only possibility I found was to use an image as background.
Using the sprite technique above poses a problem. The size of the flag is limited by the size of the element. With the standard classes above you will only see the flag and not the rest of the content. When the width is increased, you can see the content but on the background are the other flags (from the one large image).
I looked in vain into Super LOV and select2 plugins for a solution.

So the only solution left was to collect separate flag images and use them as background for the option elements. Luckily you can find a collection of images easily on the internet. The images from http://flagpedia.net are used. With CSS3 these images are bound to the corresponding option value in the select list ( presuming it is the two character ISO country code that is returned ). A CSS file was created to bind the option values to the corresponding flags.

So all that is needed is to refer to https://www.speech2form.com/assets/img/flags/select_flags.css on your page:

…and add the class flag to your select item:

When you define the country code in lower case as the return value of your select list you will see the flags in front of the country names:

In the CSS files default values for the option element have been set. You can tweak them to get the best appearance for your application. With margin and padding you can influence the spacing of the option elements.
In this solution the files reside on a remote server ( http://www.speech2form.com ).

If you want the files in the database you can download the ZIP file here. You can import the file in your application. The option to unzip should be checked.

Reference the static file select_flags.css on your page and you can use the flag images stored in the database.

After a mornings work I find out that the background images for options only work in firefox! Sigh…
When a new solution is available I will get back to you.

Happy coding,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/05/flag-images-in-apex-reports-and-select.html

One of the options for solving performance issues is the use of pipelined functions. The performance of queries can in some cases be improved dramatically by the use of PL/SQL instead of pure SQL.
The setup of pipelined functions requires however the creation of two object types for the return values and a function in which these object type are filled with values. There is a lot of typing before you can start to create the actual logic.
For this purpose the Oracle Pipelined Function Generator is created. With this tool you can generate a skeleton for the pipelined function with the required types. You can start coding the logic immediately!
The basis of the generation is a table definition with column names and data types.
A script for the following objects is generated :

  • the pipelined function with a query based on the table definition
  • the type to be used in the PIPE ROW command
  • the type to be used as the return type of the function
  • the view to encapsulate the pipelined function

If your pipelined functions recieves parameters you can use the technique with package variables that is described in another blogpost.

The online generation of the objects is available here:

Happy coding,
Dick Dral

PS This generator is an overhauled version of my old generator. The formatting of the create script has been improved, the application’s theme is changed to UT and a Copy to Clipboard button has been added.

Source Article from http://dickdral.blogspot.com/2016/05/a-generator-for-pipelined-functions.html

The other day I noticed a problem in an time registration system built with Apex. One employee could register hours under the name of another employee. This was possible because the application had access to the table with all the registrations and the filtering to the employee was done on the page.
One way to prevent this is to use views refering to application items. On the EMP table we create a view restricting it to one department:

create view emp_for_dept as select * from emp where deptno = v('APP_DEPTNO');

Outside of Apex this view will return no rows. Within an Apex application with the application item APP_DEPTNO set to 10, 20 or 30 the view will return the rows for that specific department. This way it is possible to limit the visible rows in a report.

It gets even better when we create the view with check option:

create view emp_for_dept as select * from emp where deptno = v('APP_DEPTNO') with check option;

The check option prevents rows to be inserted or updated, that do not comply with the where clause of the view. If you have a form based on this view, you cannot change the department. If you try to do so, you will get an error:

So in the case of the time registration system mentioned above the Apex application could be based on views limited by an application item. On logon this application item can be filled with the ID of the employee and she will only see her registrations. Moreover she can only enter new registrations that match the views selection criteria, so only for her own hours.
It is an elegant and easy way of solving a security issue.

Happy apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/05/using-views-with-check-option-in-apex.html

For the Apex Dashboard Competition I created an World Dashboard. It is based on the demographical and geographical data on all countries of the world. It was a lot of fun to build it and also fun to use it. You can take a look at it using the URL shown below.


Using it you will discover amazing facts. The dashboard contains data on population, land use, energy use, health and transportation. You can find for example which are the countries with the most doctors or the highest percentage forest. Let yourself be surprised – at least I was. You can also see how your country compares to others in the areas afore mentioned.

In following post I will discuss some of the features I have built in like the Oracle JET Sunburst Chart or the comparison table.

Have fun!

Source Article from http://dickdral.blogspot.com/2016/04/apex-world-dashboard.html

Today I am investigating performance problems on an Oracle Apex page. On of the possible causes are JavaScript DA Actions that are Fired on page load. The following query helps to identify these DA Actions:

select distinct da.page_id
     , da.page_name
     , da.when_event_name
     , case when when_selection_type is not null then 
                    when_selection_type ||':' || when_element || when_region
            else null
       end    as  object_name
     , da.dynamic_action_name
from   apex_application_page_da_acts   act
       join apex_application_page_da   da  on ( da.dynamic_action_id = act.dynamic_action_id ) 
where  act.action_code = 'NATIVE_JAVASCRIPT_CODE'
  and  act.application_name = '[app_name]'
  and  act.page_id = [page_id]
  and  substr(act.execute_on_page_init,1,1) = 'Y'

Fill in the name of your application ([appname]) and the ID of the page ([page_id]) and run the query. You will get a list of teh relevant DA Actions that you can check.

By the way, if you are at this point you could also run the query for the DA Fire on Page Load settings and contribute the survey.

Happy Apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/03/identify-fire-on-page-load-da-actions.html

Do you recognize the situation that you have coded a nice JavaScript Dynamic Action to respond on user input, you run the page and you notice unwanted behaviour when loading the page?
Oh no, forgot to uncheck the Fire on page load option! It happens to me all the time!

In Oracle Apex JavaScript DA steps have the option Fire on page load checked by default, as you will all be aware. PL/SQL steps have it default unchecked.
I stumble regularly over forgetting to uncheck the option. Most of the DA’s I write are about responding on user actions so I normally do not want them to execute on page load. Not unchecking the option results in unwanted behaviour which is not always obvious. One time it has cost me a few hours to come to the conclusion I should uncheck it.

I would like to have the option default unchecked. This ranks high on my Apex enhancement requests list.

To back this I decided to examine how many Javascript DA steps still have the Fire on page load option active in the application I currently work on together with 5 other developers.
This is the result:

The ratio between unchecked and checked is 2,7! A good argument for me to have the option default unchecked.

What about the Apex builder? A query on the Apex repository for application ID’s between 4000 and 5000 results in :

Also in their own work it would be an advantage to uncheck by default!

But how about your application? I am curious what the ratio is in your application.
If you fire the query below you can find out.

select execute_on_page_init, count(*)
from   apex_application_page_da_acts
where  action_code = 'NATIVE_JAVASCRIPT_CODE'
  and  application_name = 'appname
group by execute_on_page_init;

You can fill in the application name at appname or omit this line to query the whole Apex repository.

Please you post the results on www.speech2form.com/da_survey.
If your results also show a majority for the unchecked steps I will try to convince the Apex team of unchecking the option by default for JavaScript DA steps.

#letswreckthistogether ;-).

Happy apexing,
Dick Dral

Source Article from http://dickdral.blogspot.com/2016/03/lets-wreck-default-fire-on-page-load.html

For long lists mobile applications the Apex Autocomplete Item is way more suited than a Select List Item.

You know what I mean, when you ever needed to choose a country from a Select List ( > 200 items) and your pick was near the end ( The Netherlands, Unites States ). You have to scroll for 10 or 20 seconds to reach to the desired entry, and all the time you need to pay attention whether or not you have already the desired entry.

In this case it is much more user friendly to deploy the good old Auto Complete Item. Within a few key strokes, usually not more than three, the list of possibilities has been reduced to less than ten entries, and you can easily pick the right value. The only drawback for the programmer is, that the Auto Complete Item returns the description instead of the code, so the code need to be looked up before submitting the form. Not a big price for the gain is user friendliness.

The Auto Complete item is not directly available from the list of item types, you need to choose the option Show unsupported to uncover the item type. On the right side the after pressing Show unsupported is visible.

After including the item and attaching a source query to it, it is already functional:

Okay, we can choose a value, but it does not look right, does it? We cannot read the text of some entries, the pick list is transparent and the item have list style bullets.
To fix this we need to apply a bit of CSS:

.ac_results {
   background-color: white;
   border: 1px solid #cccccc;
.ac_results ul {
   margin-top: 0;
   padding-left: 5px;
.ac_results li {
   line-height: 30px;
   list-style: outside none none;

You can put it as Inline CSS on the page or in the template page, if you want to use it on several pages.
Now the item looks a lot better:

PS Do not use the Date Picker Classic,  also a not supported item type, with JQM. When you put such an item on a JQM page, calling the page provides you an eternal load animation, the page is never shown. 

 Happy apexing,

Source Article from http://dickdral.blogspot.com/2015/11/using-apex-auto-complete-item-with.html