Tuesday, November 17, 2015

How to change apex_public_user password in ORDS

When you install Oracle REST Data Services (ORDS) as the gateway for your Oracle Application Express (APEX) applications, you run the java -jar ords.war command to configure ORDS, including specifying connection strings and passwords for the service accounts apex_public_user, apex_listener and apex_rest_public_user.

The first time you run this command, you get asked where to store the configuration. A file called defaults.xml gets created in the folder you specify. It looks like this:

There's also a subfolder called conf, which contains a configuration file for each of the three service accounts mentioned above. The one for apex_public_user is called conf/apex.xml and it looks like this:

You can change any of these settings by modifying the config file (remember to restart the web server, ie Tomcat, after you have saved the changes).

But what if you need to change the database password? As can be seen from the screenshot, the password value is encrypted, so you cannot simply modify this value directly. What do you do then? You need to re-run the ORDS config with the setup flag:

# to change the database connection config in ORDS:
su - root
# assuming this is where your ORDS config file is located

cd /u01/ords
# re-run the ORDS config

java -jar ords setup
# for an existing setup, should get message "INFO: Using configuration folder: /u01/ords/config/ords"
# follow the prompts to specify the database host, port, sid, and passwords
# should get confirmation "INFO: Updated configurations: apex, apex_al, apex_rt"

# now restart the web server to pick up changes
sudo service tomcat restart

That's all there is to it... simple when you know how! :-)

UPDATE: Kris Rice pointed out that you can also edit the password directly in the config file by placing an exclamation mark before the password, for example "!your_password" (without the quotes). When you restart Tomcat, ORDS will update the config file with an encrypted version of the password.

Tuesday, November 10, 2015

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.

Monday, November 2, 2015

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! :-)

Thursday, October 22, 2015

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.

Wednesday, October 14, 2015

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.

Tuesday, October 6, 2015

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


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.

Thursday, September 24, 2015

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! :-)