Sometimes you want to show data without the user being able to change it. The easiest way in Apex to use reports and read only items. There are use cases however for which this is not good enough.
For example when you want to change from read only to editable item dynamically or vv. Or if you want to disable items in a tabular form selectively.
In these cases it is necessary to generate the editable items and to disable them afterwards. In this blogpost you can read how you can do this without compromising the functioning of your page. A special chapter describes the way to selectively disable items in a tabular form.

How to disable items correctly with JavaScript

So disabled is dangerous to use and readonly is limited in item types.

So the correct way to disable an item is applying the class apex_disabled and the attribute tabindex=”-1″. It takes care that the item cannot be reached by the user either by mouse or by keyboard.

function disable_item ( itemName )  
function enable_item ( itemName )  

How to selectively disable items in a tabular form

In the after refresh Dynamic Action JavaScript is used to loop along all the rows and perform the disabling of the items. You can use the code below:

function disable_tabform_items ( tabSelector )  
    // loop along all the rows with a DISABLE_ITEMS cell
      // make array of item names to be disabled
      list = $(this).val().split(':');
      if ( list.length > 0 ) 
        // identify the row
        var tr = $(this).closest('tr');

        // disable all the item in the list  
        for ( i=0 ; i < list.length ; i++ ) 

An example of this you can find in action here.
There you can also download the demo application in order to see how it works form the inside.

Happy apexing!

Source Article from

function reportToColumns ( tabSelector, numCols )  
{ var numRows  = 0;  
  var curtable = null;
  // calculate the number of rows per column
  var table = $(tabSelector).addClass('reportColumn'); 
  var rowsPerCol = Math.ceil( ( $(table).find(' tr').length-1 )/numCols);  
  var baseName = 'reportColumn';   
  // create table template for columns 
  var template = $(table).clone();

  for ( i = 2; i 

The parameter tabSelector should contain the JQuery selector of the table containing the report.
For Universal Theme this is:

'#report_report static id table.t-Report-report'

in which the report static id should be replaced by the static ID defined for your report region. Actually this code can be used for any HTML table. You can find the table selector using FireBug or Chrome Inspector.
I personally like to work with Firebug because of its multiline code pane. It is very easy to test JQuery selectors because the code pane remains unaltered while you can see and scroll the console output. I also use this to develop and test my JavaScript.

Add the class below to your CSS definitions. It makes sure that the columns are displayed next to each other with a distance of 30 px between them.

.reportColumn {
    display: block;
    float: left;
    margin-right: 30px;
    position: relative;

You can see it in action here.

Happy Apexing

Source Article from

You are limited by the columns in the responsive layout.

It is possible to position the items much closer and have much control over their position. For this you need to call JavaScript function with the names of the two columns as parameter:


You can download the JavaScript code here.

The JavaScript removes the col-x classes from the input container of the first item and the label and input container of the second item. This class determines the fixed width of the container. Without this class the container’s width is determined by the content, so it fits tightly around the input or the label.
After removing the classes the JavaScript positions the label and input containers of the second item behind the input container of the first item.

You can decrease the width of the items and they still stay together.

And there is control over the spacing applying left and right margins no the second label container.

#P3000_TO_LABEL {
   margin-left: 20px;
   margin-right: 20px;

In the result you see a larger spacing around the label:

The downside of this approach is that the responsive behavior is not optimal. Developing just for desktop this is no problem.
But if you really want a perfect responsiveness on all screen sizes you should stick to the column model of UT.

Happy Apexing

Source Article from

As many people I am used to use the link images that Apex proposes when creating Forms with reports. And I end up with a bit grey images like:

Well, we are all used to it, aren’t we?
Now would not it be much nicer if it would look like this:

This fits much better into the Universal Theme.

It is possible and in fact very easy. If you look at the properties of the link column in the report you see:

You just change the Link Text to   and define the class report-icon:

.report-icon { font-size: 1.6em; }

And you are done.
NB You can by the way use any font-awesome icon you want by changing the fa-xxx class. 

And if you want to use these icons in all your reports, you can define them once as a subsitution variable in the application properties:

 … and use them in all your reports:

Happy Apexing

Source Article from

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.

Source Article from

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

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

The founder of, 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 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 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 ( ).

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

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

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