Tuesday, September 22, 2009

APEX Meetup @ OOW

Also this year : APEX Meetup @ OOW

Getting rid of the annoying popup help

Recently I stumbled upon two excellent blog posts about how to change the default pop up APEX Help functionality to something more user friendly. Martin explained how to transform the Help into a tooltip, while Piotr transformed the Help pop up into a DIV.
I will add my two cents to this discussion by adding another option...
The pop up is trigger by a call to the Javascript function "popupFieldHelp'. So I decided to locally overwrite that function with this one:

function popupFieldHelp(pItemId, pSessionId ){
// Construct the URL
vURL = "wwv_flow_item_help.show_help?p_item_id=" + pItemId + "&p_session=" + pSessionId;
// Call getRequest to get the HTML for the popupWindow
var popupWindow = getRequest(vURL);
// Get the Subject (item name) from the Window
var HelpSubject = $(popupWindow).find('.fieldtitlebold').html();
// Get the HelpText from the Window
var HelpText = $(popupWindow).find('.instructiontext').html();
// Show the results as an alert
showHelp( HelpSubject, HelpText );
This function calls two other functions:
a. showHelp - to show the Helptext in a jQuery Dialog box
b. getRequest - to retrieve the results of an URL by executing an AJAX call (I was hoping to find something like that available in the standard APEX Javascript lib, but couldn't find it - if you know a better way, please let me know!).
The result is a nice looking help (you can tweak the settings and/or presentation the way you like):

IMHO the main advantage for this approach is that you don't need to change anything in your templates (apart from loading the necessary Javascript files), so it is very easy to implement!
As usual there is an example on apex.oracle.com...

The code for "showHelp" is below:

function showHelp(pTitle, pText){
vText = '
bgiframe: true,
modal: true,
minHeight : 200,
width : 200,
close : function(){$("#info").remove();},
closeOnEscape : false,
buttons: {
Ok: function() {
The code for "getRequest" is here:

function getRequest( vURL ){
http_request = false;
if (window.XMLHttpRequest) { // Mozilla, Safari,…
http_request = new XMLHttpRequest();
if (http_request.overrideMimeType) {
// set type accordingly to anticipated content type
else if (window.ActiveXObject) { // IE
try {
http_request = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try {
http_request = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e) {}
if (!http_request) {
alert("Cannot create XMLHTTP instance");
return false;
http_request.open("GET", vURL, false);
return http_request.responseText;

Monday, September 21, 2009

Running APEX on 11gR2 using Sun's (Oracle's?) VirtualBox

Today I decided that I needed an environment to play with the latest and greatest Oracle RDBMS : 11gR2. This version is only available for Unix platforms, so I needed a virtual environment to get that working on my laptop - running on XP.
You can pick any kind of virtualization software, but, due to the recent take-over of Sun by Oracle, I decided to go for what's currently known as Sun's VirtualBox. I guess this product will re-branded to Oracle's VirtualBox somewhere soon.
So what are the steps:

1. Download VirtualBox from http://www.virtualbox.org/ and run the installer.

2. Start VirtualBox, click 'New' and enter a Name, Operating and Version for the Virtual machine. This is gonna be a 100% Oracle thing, so I installed Oracle Enterprise Linux (OEL).

3. Oracle 11gR2 needs 1Gb of RAM, so set the memory size to 1024 - you can change this afterwards.

4. Create a new boot harddisk with dynamically expanding storage.

5. On the next page you have to set the 'size of the virtual hard disk'. This is not the initial size, but the maximum to which the harddisk can be expanded. So don't take it too small, because adding or expanding a hard disk to a current installation is not so easy...(as I have experienced).

6. Next start the VM and install OEL from a DVD or ISO download. I installed it with all defaults, with support for Software Development and Webserver. Honestly I have no idea what is added by checking these features, but I checked it just because that's what I want to use it for.

7. Now take a break - or do something else - while OEL is being installed... it takes a while.

8. After installation you have to configure OEL. You can accept all defaults, I just changed the settings for the Firewall to accept HTTP request on port 8080 - the one we're going to use for running APEX.

9. Next add a Shared Folder in VirtualBox - a folder that can be used by both the host and the guest OS. To use it within the Linux environment: login as root, mount the VBoxGuestAdditons.iso (by pressing the Host-D key, whereby Host is the right Ctrl key by default), copy VBoxLinuxAddtions-x86.run to the Desktop and run it. Restart the VM when that's finished.

10. Mount the added shared drive: sudo mount -t vboxsf Shared /media/shared

11. In the Host OS: Download the two 11gR2 disks, copy them to c:\shared, unzip them - of course you can do that in the Guest system also, but not while you're installing it ;-). Install 11gR2 on the Guest OS following the steps in the documentation. Again some time for a good cup of coffee....

12. 11gR2 is shipped with APEX, so it just takes two steps to get it running:
Open up the EPG using port 8080 : Start SQLPlus, login as SYS, exec dbms_xdb.sethttpport(8080) and set a password for the APEX Admin user by executing the apxchpwd script (located in /home/oracle/app/oracle/product/11.2.0/dbhome_1/apex for a default installation). Now you can open up the browser, navigate to http://localhost:8080/apex/apex_admin and off you go.

13. Now where almost there...because now we want to use the database in the VM from the browser in the Host OS. Therefore we need to set up Port Forwarding in VirtualBox. Shutdown the Guest VM and VirtualBox. Run these three commands:
vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/HostPort" 8080
vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/GuestPort" 8080
vboxmanage setextradata OEL "VBoxInternal/Devices/pcnet/0/LUN#0/Config/Apex/Protocol" TCP
Startup VirtualBox and the VM. If the database doesn't start, login as the oracle user, set the environment (. /usr/local/bin/oraenv), start the listener (lsnrctl start) and start the database (sqlplus sys as sysdba and enter 'startup').
Now we can use the APEX on Oracle 11gR2 in a Oracle Enterprise Linux VirtualBox from an XP Host OS. And it runs very fast....!!

One final remark: If you've got an Oracle instance running on your Host OS, http://localhost:8080/apex will open up the login page from your Host. So you either have to shut that down, or forward another HostPort (like 8081).

Thursday, September 17, 2009

Easy Show/Hide items in a Form

For showing and hiding information in a Form you can use a default "Show and Hide Region". But what to do if you want to show/hide some details within a region? The answer is: with a Label Template, some cool jQuery UI stuff and "Stop and Start HTML Table" Items.

1. Create a new Label Template (From Scratch) for Template Class 'No Label'. Call it 'Show/Hide Next Image' or something else you fancy.
2. Edit the Label Template you've just created. Remove everything that's there by default.
3. Set the 'Before Label' to :
<img src="wwv_flow_file_mgr.get_file?p_security_group_id=<your workspace id>&p_fname=
4. Set the 'After Label' to :
" onload="$(this).next().hide();" onclick="$(this).next().toggle('blind');">
5. Now create a "Stop and Start HTML Table" Item just before the items you want to show/hide. Edit that Item and set the label property to the name of the image you want to show (like Info.png) and set the Template to the template you've just created.
6. Run your page... you should see something like this example.

Everything between the "Stop and Start HTML Table" and another "Stop and Start HTML Table" or until the end of your Form will be shown/hidden.

Now you can use this throughout your application to save space or hide information that should only be shown on request. For instance, it is perfect for hiding some detail audit info.
Of course you can tweak the Template and use some other kind of jQuery UI effect, or change it to show/hide on mouse over.

Tuesday, September 15, 2009

Transform ORA-xxxx messages into something more user friendly in APEX

When you delete a record that is referenced using a foreign key constraint you get a rather ugly error like this:
Of course you can prevent such a thing from happening by creating a Page process that checks if your delete is "Ok". But that's double coding...and dangerous when you have to maintain your database structure: You'll probably forget to update the processes as well. Wouldn't it be nice if you can 'catch' that error an present it to the end user in a more friendly way (and with a somewhat more understandable text)? I guess your users will be more enthusiastic about your application (and you) when you show them something like this:
So how do we achieve that?

1. Edit the "Error Page Template Control" region of your Application Default Page Template, because that's the one that is used for presenting errors. Set it to :

<script type="text/javascript">$(document).ready(function(){raiseErrorHandler();});</script>
2. Create the raiseErrorHandler function (I'll use some jQuery UI stuff to show the nice dialog box):

function raiseErrorHandler(){
vError = $(".ErrorPageMessage");
var get = new htmldb_Get( null, $v('pFlowId')
get.addParam( 'x01', vError.html());
gReturn = get.get();
get = null;
var errArray = gReturn.split("#",2);
showError( vError, errArray[0], errArray[1]);

function showError(pThis, pTitle, pText){
vText = '
bgiframe: true,
modal: true,
minHeight : 200,
width : 600,
closeOnEscape : false,
close : function(){window.history.go(-1)},
buttons: {
Ok: function() {
3. That Javascript function calls an Application Process GetErrorMessage:
p_error_title varchar2(32767);
p_error_text varchar2(32767);
p_ora_error varchar2(32767);
p_ora_error := wwv_flow.g_x01;
-- Get "translated" error message
get_error_message( p_ora_error, p_error_title, p_error_text );
htp.p( p_error_title||'#'|| p_error_text );
4. The procedure get_error_message is defined in the database. This procedure queries a table that contains the contraint name (the 'ORA Error') and a more user friendly description.

( p_ora_error IN VARCHAR2
, p_error_title OUT VARCHAR2
, p_error_text OUT VARCHAR2
) AS
SELECT code, description
FROM afk_message_properties
WHERE INSTR( p_ora_error, constraint_name ) > 0;
OPEN c_mps;
FETCH c_mps INTO p_error_title, p_error_text;
CLOSE c_mps;
p_error_title := 'No translation found for this error';
p_error_text := p_ora_error;
The Application Process spits out a concatenated text, that will be segregated again in the Javascript function. The result is presented to the user in a way that would make him happy....
Even if there is no error definition found in the table, the error will be shown in the dialog box - only the translation to a normal language is missing.

Kinda cool I think!
You can see a public demo when you click here.

Blackbird : Javascript logging 2.0

When debugging your APEX Javascript stuff you probably use Firefox with Firebug. By writing messages to the console, you can follow the flow of your Javascript. Incidentally you use - annoying - alerts in your code to find out where you messed up your code. But recently I stumbled upon BlackBird! With blackbird you can write different types of messages, like info, warning and error messages, to a little console window, that looks a bit like a Twitter client. You can show/hide the window using F2. In this little window you can toggle the type of messages you're interested in. And it works even on IE!
Blackbird consists just of 1 CSS, 1 JS and two image files, so it is very easy to install.
Just a little tip, rename the namespace from 'log' to something like 'bb', to avoid conflicts with the Firebug log.
You can find the code, demo and docs on http://www.gscottolson.com/blackbirdjs/.

Thursday, September 10, 2009

OOW 2009 Blogger Meetup

All you bloggers out there visiting Oracle Open World this year, let's get together at the traditional annual Blogger Meetup. This time the organizer is Pythian's Alex Gorbachev. Thank you Alex!!

Friday, September 04, 2009