KPI Icon item plugin for Apex

Here's a very simple item plugin that you might find useful. The item plugin displays a Key Performance Indicator (KPI) icon based on the item value.

You can set the threshold values for green and red (and everything in-between will be yellow). Also, you choose whether high values are good or bad.

Since the item itself is a regular Apex item, you can assign a value to it using a computation, process, PL/SQL expression, etc.

The icon is displayed using Font Awesome (bundled with Apex). The value is displayed as a tooltip when you hover over the icon.

Download the plugin here.

Mockup Table region plugin for Apex

Today's post is about a new plugin that I developed for Oracle Application Express (Apex).

The plugin was inspired by a product called Balsamiq, which is a tool for creating mockups (or "wireframes") of web pages and web applications. Balsamiq is used as a drawing tool to quickly sketch out your user interface before building it using whatever technology.

Balsamiq is a nice tool, but so is Apex! :-) Why spend time mocking up screens in Balsamiq when you can use Apex to quickly lay out pages and regions? With Balsamiq, the end result is a set of fake, static wireframes. With Apex, you actually get a real, live, responsive web application that you can iterate and build upon until it is ready to use. You don't have to throw away anything.

However, that doesn't mean that we can't steal any good ideas from Balsamiq! :-)


Balsamiq has a nice Data Grid/Table widget that allows you to quickly mock up a table.

The plugin I created for Apex, called the Table Mockup Region plugin, allows you to do just that -- to quickly mock up table data on an Apex page without having to create database tables and write queries.

Which means you can quickly throw in some data, show it to your potential users, and then create tables and write queries later, when the design has been settled. Using the Apex 5 page designer, it is really easy to change the region type from the Table Mockup type to a classic or interactive report.

I made a short video (my first screencast ever, yay!) to demonstrate how easy it is to use:

Hope you find this useful, I had fun creating it. (The plugin code is actually less than 100 lines of PL/SQL! That's because all the hard work of parsing the delimited data is done using the CSV parsing package from the Alexandria Library.)

Download the plugin here.

ORDS Java heap space OutOfMemoryError

I recently ran into a problem with an Apex application running on ORDS on Tomcat. The application has a page with a custom tabular form (built using the apex_item package). When this page is submitted, the form values are stored in the "g_fxx" arrays (g_f01, g_f02, etc).

The problem was that when the number rows (and therefore the number of elements in the arrays) got too big, the server would respond with a HTTP 500 - Internal Server Error message.

The first thing to do in this case is to check the Tomcat logs at /usr/share/tomcat7/latest/logs to see what the real error message is. Tomcat generates one log file per day, and the log files have the naming format catalina.yyyy-mm-dd.log.

Opening the relevant log file, I found several occurrences of this error message (truncated for brevity):

oracle.dbtools.rt.web.WebErrorResponse internalError
SEVERE: Java heap space
java.lang.OutOfMemoryError: Java heap space
at oracle.jdbc.driver.T4CCallableStatement.doOall8(...)


So, Tomcat/ORDS was basically running out of memory when processing a page submission with dozens or hundreds of values (even though in my case the values were all contained in a few g_fxx arrays).

I googled a bit and found a recent post from the Apex forums that deals with the same issue. In my case I'm using ORDS 2.0.10, but it appears that the same issue can be experienced with ORDS 3.0.1.

Given that in this particular case my server only has 1 GB of memory, perhaps it's not that strange that the Java process would run out of memory (after all, the server is also running Oracle XE, Tomcat and Apache...).

To increase the memory available to Tomcat, you need to create a file called setenv.sh in the Tomcat bin directory, and make it executable.

nano /usr/share/tomcat7/latest/bin/setenv.sh
# paste the below text and save
chmod +x /usr/share/tomcat7/latest/bin/setenv.sh
service tomcat restart

Here is the content I put in the setenv.sh file (based on this):

#! /bin/sh

export CATALINA_OPTS="$CATALINA_OPTS -Xms128m"
export CATALINA_OPTS="$CATALINA_OPTS -Xmx512m"
export CATALINA_OPTS="$CATALINA_OPTS -server"

This sets the initial memory to 128MB and the maximum memory to 512MB (more info). The -server flag is to optimize execution when Java runs as a server (more info).

Remember to restart Tomcat for the new settings to take effect.