Apex 5 right side column

The new Universal Theme in Apex 5 includes a nice "right side column" feature that adds a sliding menu on the right side of the page, where you can place additional content, such as (for example) an audit trail, actions/links, comments, whatever.

Let me show you with a picture:

To add this to your page, simply change the page template to "Right Side Column", and note that a "Right Column" template position appears in the grid layout pane:

You can then place whatever you want in this template position (any region, with buttons and items as usual). Here, I have placed two different regions on top of each other:

That's all there is to it. Again, Apex 5 with the Universal Theme makes this very easy.

By the way, have you checked out the Universal Theme sample application? You can install it into your own workspace from the Packaged Applications gallery, and it is also available online at https://apex.oracle.com/ut

Using this application you can browse the various templates and template options built into the Universal Theme. For example, it showcases the "Right Side Column" template with thumbnails and sample pages, and also tells you how to implement it.

Easy tab regions in Apex 5

In Apex 4, if you wanted to implement tabbed regions, you had to use jQuery UI Tabs or some other third-party component.

With Apex 5 and the Universal Theme, tabs come built-in and setting it all up is extremely easy.

To add tabs, just add a Static Content region to the page, and change its template from "Standard" to "Tabs Container".

Then add Sub Regions to the region. In the component tree, any sub regions you add will appear under the "Tabs" node which has a folder icon.

Add as many sub regions as desired. For best results, set the template of these sub regions to "Blank With Attributes". Add content and items to these sub regions.

You can also set some template options for the tab container region, such as "Remember active tab" (between page views) and to set the visual style of the tabs.

That's it! Happy tabbing! :-)

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.

Sending SMS text messages from PL/SQL

Do you need to send SMS (Short Message Service) text messages from your Oracle database using PL/SQL? This is actually quite easy to do, the only hard part is deciding on which SMS gateway to use. You need to sign up with a gateway provider to get a username and password to use the gateway, and you typically pay some cents for each message you send.

Do a google search for "sms gateway api" to find a suitable gateway provider. I won't provide any specific recommendations or endorsements; for this blog post I just picked a random provider to use in the code example below. I have no idea if they offer a good/cheap/reliable service or not. (Leave a comment below if you have any recommendations that you would like to share.)

Most gateways offer an API that takes a HTTP(S) GET or POST request with some parameters in the query string to specify the recipient and the message itself, and then returns some kind of status back in some format (XML, JSON, plain text). The concept is usually the same, but the specifics vary.

I've added a new package called SMS_UTIL_PKG to the Alexandria PL/SQL Utility Library that provides a generic interface to any SMS gateway.

To use the package, you need to check the API documentation of your chosen gateway provider, and set up the URL template accordingly. You can use tags like #username# and #password# and #message#, etc. in the template, which will be replaced with actual values when sending a message.

Here is an example of using the package to set up a gateway and send a message:

There's also support for adding a callback to a custom error handler that you can use to determine if the gateway returns an error message. This should be a function that accepts a clob as an input parameter (this will contain the response message from the gateway) and returns a varchar2 with an error message, or return null if successful.

Remember that you might have to modify your database Network ACL settings to open traffic to the gateway's hostname. And if the SMS gateway requires HTTPS, you need to set up an Oracle wallet with the certificate and call the set_wallet procedure before you send the message.

Happy texting! :-)

My nominations for the Oracle Database Developer Choice Awards 2015

I'm honored to have been nominated for the Oracle Database Developer Choice Awards 2015, in no less than three (!) categories: PL/SQL, Application Express (Apex) and Oracle REST Data Services (ORDS).

Here's a short video that explains what the Oracle Database Developer Choice Awards are all about:

If you are a regular reader of my blog, you are probably familiar with my contributions to the Oracle Database Developer community, but here is a short recap of my work in the relevant categories:

• PL/SQL: I've been programming in PL/SQL for almost 20 years. I've written and released as open source the widely-acclaimed Alexandria PL/SQL Utility Library, which contains around 50 PL/SQL packages that deal with everything from general math and string utilities to Amazon web service integration, NTLM authentication, CSV parsing, MS Exchange integration, and much more. Jeffrey Kemp has a great guided tour of the library which highlights some of the packages. As of February 2015, the library had been downloaded more than 12,000 times. Furthermore, I've created a presentation called PL/SQL: The Good Parts, which in Steven Feuerstein's words is a "fast, entertaining high-level glimpse of what makes PL/SQL so great" (and Steven should know what he's talking about! :-).
• Apex: Since 2008 I've published numerous articles related to Oracle Application Express on this blog. Among other things, I've created popular Apex plugins, including a pivot table plugin and a plugin for generating on-demand dynamic PL/SQL content. I've created and open-sourced the jQGrid Integration Kit for PL/SQL. And I created ApexGen, a framework for generating Apex apps using PL/SQL code (which I may revisit and enhance in the future if the Apex team provides a supported API for it). I've also been active in the Apex community by asking and answering questions on the Apex forum on OTN, actively testing Apex early adopter (EA) releases for Apex 4 and 5, and reporting numerous technical issues and bugs back to the Apex development team. My coworkers know me as "the Apex guy" since I constantly promote the virtues of Apex for new development projects.
• ORDS: I've long been interested in the technology that connects the Oracle Database to the web, including the PL/SQL Web Toolkit (aka OWA) and the forerunners of ORDS such as mod_plsql and the Embedded PL/SQL Gateway. Using this knowledge I implemented and released as open source a mod_plsql alternative for Microsoft Internet Information Server (IIS) called the Thoth Gateway. I've published many articles on the use of JSON from PL/SQL, a key part of REST. Earlier this year I wrote a four-part blog post series with all the nitty-gritty details on installing and running Apex with ORDS that became very popular and widely distributed.

Now go and vote for me! :-)

Click here to vote in the PL/SQL category, here to vote in the Apex category, and here to vote in the ORDS category.

Thank you very much for your vote! :-)

Image credit.