Tuesday, December 4, 2012

APEX Listener favicon bug issue

When developing an application in Oracle APEX, I usually have opened two browser tabs, one for the builder and one for the application I’m working on. This works fine, even though both tabs are using different sessions.

The other day I noticed a strange annoying behavior using this approach: Each time I opened the application I was working on in the new tab, my builder session was expired. This behavior wasn’t related to the APEX version: it happened with my 4.2 installation running on a VBox as well as with my local installation running 4.1. I was rather puzzled, as I didn’t change anything on my local installation.

Here is what caused it (thanks to Christian Neumüller for explaining):

I ran into the so-called APEX Listener favicon-bug: On my 4.1 installation I was working on a new theme, build entirely from scratch, and on my 4.2 installation, I was playing around with a mobile application. What both applications (or better: themes) had in common: the lack of a favicon reference in the page template. One other thing both environments had in common: an APEX Listener stand-alone installation. Looks like the Listener tries to resolve a favicon location each time, and when not found, redirection the favicon request to the “webservers” root (the favicons default location). With the APEX Listener the “root” gets redirected to yourserver:port/apex, which is causing a reset of the session id.

Adding a favicon to your page template(s) HEAD section solves the problem.


Make sure you've got an image file at the specified location. I could reproduce the problem with APEXListener versions 1.1.4 and 2.0.
I was promised, the issue will be addressed in one of the coming versions of the APEX Listener.

Friday, November 9, 2012

Cloudy Normal Button :hover fix

mouse_cursor_hoverMaybe you have noticed it, maybe not, but the hover effect of the Normal Button template in the APEX Cloudy theme does not  work in IE. It seems it hasn’t been implemented. So if you want the hover effect in IE, here is the CSS snippet you need to to add to your template:

.ie6 a.uButton:hover span,
.ie6 a.uButtonLarge:hover span,
.ie6 a.uButtonSmall:hover span,
.ie7 a.uButton:hover span,
.ie7 a.uButtonLarge:hover span,
.ie7 a.uButtonSmall:hover span,
.ie8 a.uButton:hover span,
.ie8 a.uButtonLarge:hover span,
.ie8 a.uButtonSmall:hover span,
.ie9 a.uButton:hover span,
.ie9 a.uButtonLarge:hover span,
.ie9 a.uButtonSmall:hover span {
background: url(#IMAGE_PREFIX#themes/theme_24/images/app_theme.png) 0 -400px repeat-x #DEDEDE;

This will only affect IE and will load a slightly lighter gray gradient background on the hover state of the normal button.

Credits go to Shakeeb. Thanks!

Tuesday, November 6, 2012

Book Release: “APEX Best Practices” - A First Impression

Oracle APEX Best PracticesToday (nov. 6th 2012), the book ‘APEX Best Practices’ written by Learco Brizzi, Iloon Ellen-Wolff and Alex Nuijten was finally released. I was curious about the book, for several reasons:

  • I promised the book to the winners of my FifApex competition earlier this year
  • I know all three authors and heard some stories about the process of making it
  • As an APEX developer: what ‘Best Practices’ will the book advice

Here is my first impression:

There is more to APEX then just building pages, and this is where this book comes in handy. Installation, set-up, maintenance, versioning, deployment, printing, are all subjects elaborated in this book. ‘APEX Best Practices’ will help you planning and setting up your environment. Also the book talks about creating pages and reports as well, it’s not the usual step-by-step tutorial. It focuses on the structure of an application and the development process in general.

The book contains an extensive chapter (probably written by Alex) on how to leverage the Oracle database functionality in an APEX application. A must read, in my opinion. After all: the best thing about Oracle Application Express is the Oracle database it’s build on.

One advantage of the delayed release: the authors managed to add a chapter (well, actually an appendix) about Database Cloud Service, APEX 4.2 and RESTful Web Services .

It’s probably not the first book you would grab from the shelf when starting to learn building applications with Oracle Application Express, but you should definitely read the book before starting your first real APEX project.

Book Details

Language : English
Paperback : 298 pages
Release Date : November 2012
ISBN : 1849684006
ISBN 13 : 9781849684002

Friday, October 12, 2012

Remember Me - APEX Autologin

I promised to publish about this subject to one of the attendees during my presentation at KScope 12 in San Antonio. I used this functionality in my demo application (FifApex) and it seems there is interest in how to do this.

Most of the public websites like Facebook, LinkedIn, Twitter, Amazon or OTN do have it: an option to stay logged in, even if you closed the browser, so you do not have to authenticate each time you visit the site again. I’m pretty used to it and would be surprised if a website didn’t offer this convenient feature. OK, it’s not entirely secure, but, as I said, very convenient.
I’m working on a “consumer” site/application, I’m building it with APEX and I want to offer this “remember me” option too. I knew an APEX based website that does offer this feature (www.plsqlchallenge.com) and I had a chat with the developer that implemented it’s login mechanism, Paul Broughton from Apex Evangelists. So, here my thanks to him for the original inspiration.
Oracle Application Express has neither a build in functionality nor is it providing a “standard” Authentication Schema that does provide this mechanism. But, with just a little effort you can implement this Autologin in your application. My example uses a Custom Authentication schema, meaning I have a user table and a package, providing all the necessary functionality. At the bottom of this post, you will find links, where you can download all files you need to install the demo application in your own environment.

Let’s start

First of all, let me define the mechanism: the user gets an option, usually a checkbox, in login screen to stay signed in. Next time the use visits the site, he doesn’t have to provide his credentials and is automagically signed in.
The common technique to achieve this, is using a cookie that holds a token to identify the returning user. So we have to set a cookie when the user signs into the application and check for the cookie and validate the token when the user comes back.
Setting and reading a cookie is easy, using the OWA_COOKIE package, but how to integrate this into a APEX authentication schema?
The following example is build with a standard “empty” application with Custom Authentication. Prior I created a table to hold the user data and a package, containing the authentication logic. Note: this is a simplified example, so using this in your production application is at your own risk! All code is included in the demo application download.

CREATE TABLE my_custom_users(
   username   VARCHAR2(25 BYTE)
, password   VARCHAR2(250 BYTE)
, token      VARCHAR2(25 BYTE)

INSERT INTO my_custom_users(username, password, token)
VALUES ('DEMO', my_cust_auth.encodeit('DEMO', 'demo'), NULL);


Step 1: setting the cookie during loginremember_me_01

First I’m going to modify the generated Login Page (101), adding the “Remember me” checkbox.
Then I modify the “Set Username Cookie” process that already should exists and stores the APEX username in a cookie (I do not make any changes to this functionality): The code I add, checks, if the “Remember me” has been ticked by the user, the user actually is a valid user from my table and then sets a cookie, called “REMEMBER_ME”. The value of this cookie is generated by a function, producing a random string and storing this as a token with the user data or fetches the token from the user data, if already existing. In my example, I choose to set the expiration date of this cookie one to about a year.
Running the application, logging in with the checkbox ticked, you can exam the cookies of your APEX application by using a tool like the Developer Console of Chrome. Next to some cookies, set by APEX itself, you should find the “REMEMBER_ME” entry holding the token string and expire date of today + 365 days:

Step 2: using the cookie on return

Now, when do we need to read the “REMEMBER_ME” cookie again? Every time the visitor returns to your site/application, has not signed in yet (obviously, as we want to do this automagically) and the cookie is set and holds a token that is known in the user table (assuming that the user is the same again!). I want to perform the check, regardless of the page visited is a public page or a page that requires authentication. The event that should be triggered, if the conditions are met (cookie set and valid, user is public), is the a automatic login, similar to the original login. To perform the check, I will use “PAGE 0”, but lets first create the autologin functionality to be called:
remember_me_04To realize the autologin, I create a new page (103 in example application). This page only contains a “On Load - Before Header” process and one page item. The process only fires, when the request name is “AUTOLOGIN” and calls the build in standard APEX login procedure provided for custom authentication. It uses the page item to “P103_TOPAGE” as target page after successful login, which I will set on the triggering process/branch on page 0, to return to the page the user actually requested in the URL. The username is derived from the cookie (the token belonging to one unique user) using the call OWA_COOKIE.get ('REMEMBER_ME'); in a stored procedure.
    P_UNAME       => my_cust_auth.get_user_from_cookie,
    P_PASSWORD    => null,
    P_FLOW_PAGE   => :APP_ID||':'||:P103_TOPAGE
As you see, the password parameter is NULL. So I have to add logic to the Authentication Function (my_cust_auth.validate_user) of my custom Authentication Schema, that handles two cases: either a valid combination of username/password is given, or the “REMEMBER_ME” token is set and a corresponding user is found. Again: this is very basic and may not be sufficient for your production application.

Step 3: automagically do it

I already mentioned that I will use Page 0 to call the auto login. Doing so, the user should be singed into the application, regardless which page he opens first. I simply create a “After Header” Region of type “Dynamic PL/SQL”. The redirect will be done using the APEX build in procedure sys.owa_util.redirect_url, passing the relative URL of page 103 with request parameter “AUTOLOGIN”. This region is conditional, checking for: user is public, cookie is set and belongs to “a” user.
I added an extra condition checking for the current page ID to be less or equal to 101, to prevent page 103 from recursively calling itself. Page 102 will be this demo application’s special logout page (see next section). All “normal” pages in this application are assumed to have ID’s in the rage of 1 to 100. You definitely should modify this condition to meet your actual applications page ID ranges.remember_me_08
Note to myself: I first tried to use a “Before-Header” Branch in Page 0 to implement this redirect. But: Branches in Page 0 do not ever get executed. It’s not the first time I hit this pitfall. After all: the APEX 4.1 Application Builder Treeview of Page 0 suggests Branches are possible.

Step 4: forget me

remember_me_07We need to offer the user a way to logout and remove the cookie. I create a new page, 102, containing one “On Load - Before Header” process and a branch.
The process removes (expires and replaces value with empty string) the cookie and performs the actual logout using WW_FLOW_CUSTOM_AUTH_STD.LOGOUT. The branch will take the user to the (public) Home page (1).
I then specify to use page 102 as the Logout URL of my Authentication Schema.
The request name “LOGOUT” is provided by APEX itself, so you might check for it in a condition, rather then defining the process as unconditioned, just to prevent accidental logout.
So, that’s it. Just a few simple modifications to your custom authentication. What do you think? Does anybody have a more elegant solution (I’m sure there are)? I would appreciate it, if you would post your ideas as comments to this article. If you haven’t got a solution yet, but want to use mine: go ahead! Down here the links of the demo application on apex.oracle.com and the full download of it for you as a template.


Demo and Download

Try the demo at apex.oracle.com!
(login with demo/demo)
Download the DemoApp!
(just import and install incl. supporting object scripts;
min. version: 4.1;
default login: demo/demo)


Possible Enhancements:

The whole mechanism is a rather simple and naïve approach. I just wanted to explain the basic principle of it. There are lots of enhancements and improvements one could think of, and actually, while writing this post I thought of some myself:
  • using pre/post function call of Authentication Schema instead of page processes
  • integrate autologin logic from page 103 to page 101
  • cookie name application variable or dynamically generated
  • investigating the possibility of an autologin authentication plugin



Friday, September 28, 2012

Dynamic Date Range in APEX Datepicker - no Plugin required

The build in jQuery UI Datepicker of Application Express is a very versatile component and it is very easy to configure the basic settings with the Page Item properties. One of the properties you can set, is the range of selectable dates by entering a Minimum and Maximum Date value. The “syntax” of these values is based on the jQuery UI Datepicker components date format capabilities, so this might be looking a bit awkward to us Oracle folks at first sight. Next to giving a static value or one of these Datepicker calculations you can reference an APEX Page Item to set the range values, which is a nice thing as we are able to calculate a range dynamically, based on some database value/calculations. The bummer is: the calculation only gets executed during the rendering phase of the page and changing the referencing Page Item’s value won’t change the Datepicker range.
What you really want (yes, you do), is to be able to reference Page Items to control the Minimum and Maximum Date dynamically “real-time”. Think of all the examples where you have to enter a Start Date and an End Date, where the End Date, obviously, has to be larger then the Start Date. In my case, I had different “events” to choose from and, once having chosen, pick a day within the events date range. You could just solve this by validating the users input and notifying him after he entered the date value. I prefer to restrict the user input to valid values.

The Basics: jQuery Datepicker in APEX

I created this little demo page, containing 3 standard APEX date items. Searching for “datepicker” in the page’s source code (using the Chrome Developer Tool), I found 3 corresponding jQuery calls, one for each item, to initialize the datepicker objects. Nice thing is, the Datepicker objects are named after the original APEX page items. As those objects are created during initial rendering of the page, we should be able to access them with jQuery by using the console. If we are able to access these Datepicker objects, we also should be able to alter the options, using the methods defined for the jQuery UI Datepicker object.

Let’s Get

OK, lets have a look at the objects using the Chrome Console: I want to get the value of the page item and see some of the Datepicker option settings.
Querying the page items value is pretty straight forward using jQuery selectors with the elements ID’s:
  • $("#P1_MAX_DATE").val()to get the value of the second date item
  • $("#P1_DATE").datepicker( "option", "minDate")to read the minDate option of the third date item
As you see I already entered a min and max Date when defining the third date item, using “-7d” and “+7d” as values, So, now we know how to read the page item value and the Datepicker options.

Set it

Let’s try to alter some options, and obviously, I’m going to try to set the minDate and maxDate options with
$("#P1_DATE").datepicker( "option", "minDate", "04-09-2012")
$("#P1_DATE").datepicker( "option", "maxDate", "29-09-2012")

Well, it works! This is what the Datepicker looks like after setting the min- and maxDate.

Wrap it up

If now we combine getting the values of the Min./Max. Date page items and setting the the minDate/maxDate options of the “Actual Date”  item, put this into a Dynamic Action, lets say: “execute JavaScript on the Change events of the first two date fields”, we should have exactly the intended behavior.

Start the Dynamic Action Wizard by right-clicking on the P1_MIN_DATE item and choosing “Create Dynamic Action”:

Do the same for P1_MAX_DATE using the code:

$("#P1_DATE").datepicker( "option", "maxDate", $("#P1_MAX_DATE").val());

Just two statements! See for yourself and visit the demo page. Of course the variations of this approach are countless, but the basic two statements will remain: getting the value and setting the range options.

Have fun APEX-ing.


Wednesday, August 22, 2012

APEX Tabular Form: focus cursor on first element of new row

focus0I just got the question from a fellow developer, how to focus the cursor to the first input field of a newly added row using the ADD button in a standard APEX Tabular Form. Actually, I would expect APEX to do this automagically. But it doesn’t. To implement this behavior is actually really simple.



For this example I create a simple tabular form based on the demo_customers table:


This is what the form looks like when you click the “Add Row” button. As you can see, none of the input field has focus at this time. To focus the cursor on the first field of the new row (Cust First Name), I somehow have to change the behavior of the “Add Row” button. Let’s have a look at it’s definition:


The click-action of the button is actually a JavaScript call: addRow();. Naturally I want to keep this action, but after this, I want to start an additional action that should set the focus to the input field. To be able to have multiple actions performed when the button is clicked, I will change the Action definition to “Defined by Dynamic Action”:


Now I’m going to add a Dynamic Action to the “Add Row” button. First action will be the original JavaScript call to add the row, second action will be setting the focus. This is how the definition will look like after I added the actions:


Creating the Dynamic Action and the first True Action I will do with the Create Dynamic Action Wizard for the button:





Now I have implemented the original behavior.

Next I want to focus the cursor on the first text-field of the added row, once I click the button. I know there is a build-in Dynamic Action to set the focus on an element. I just need to know which element. Let’s have a look at the HTML code of the generated APEX page, especially the the text-field element we wish to target (I used the Chrome Developer Tool here):


As you can see, the name of this element is f02 and the id f02_0008. Because the ID can change depending on the number of rows displayed, I choose to work with the name attribute, which stays the same. The problem is, all “Cust First Name” fields do have the same name. Luckily jQuery offers a way to select the last element in an array of element with the same name and luckily, APEX Dynamic Actions support jQuery selector syntax. So here is, how I define the focus action for my “Add Row” button:



As you can see, I select all elements having the name attribute equal to f02 and :last allows me to select the last element of these. Here is a screenshot from the result (after clicking the “Add Row” button) with focus on the first text element of the last, newly added row:


Saturday, August 18, 2012

Running Standalone APEX Listener as Windows Service

When working with APEX you certainly want to use the APEX Listener. It’s a great piece of software and Oracle is committed to extend its functionality (Oracle recently release the beta version of the new APEX Listener 2.0).
When installing the APEX Listener, you basically have two choices:
  • run APEX Listener “Standalone”
  • deploy the Listeners WAR file to one of the supported webservers
The problem (at least, my problem): when using the APEX Listener on my laptop/desktop with my local APEX instance, I do not want to set up a whole webserver, just to be able to develop APEX applications. So I,’m choosing the lightweight option: Standalone! But the annoying thing about the standalone installation is, that I have to start the APEX Listener from the command line each time I want it to be available. It just would be so much easier to just have it as a Windows service, stating during system boot.
Seems that an APEX developer from Poland, Andrzej Nowakowski, solved this problem for us and blogged about it, but the reason I probably never read his post is that he wrote the post in Polish. I followed the (Google Chrome) translated instructions, and after a little (just a little) struggle, I have my APEX Listener running as a Windows service.
So here is my post, mostly translating Andrzej’s instructions, in English.
Usually you would install/start the APEX Listener standalone mode by issuing the following statement, as documented in Oracle’s Installation Guide:
java -jar apex.war
This will create my Listener’s configuration files in the Windows path C:\Users\christian\AppData\Local\Temp\apex by default, using ${java.io.tmpdir}/<Mount-Point>/apex-config.xml. To avoid using the environment variables, which could be different when starting APEX Listener during system startup as a service, I’m going to let the Listener create its configuration files in a less user-dependent path using the listeners command line parameter –Dapex.home:
java -Dapex.home=C:\oracle\apex_listener.\listener_conf -jar apex.war
C:\oracle\apex_listener. is the folder I installed/extracted the Listener software into. For the rest of the installation process, just follow the instructions in the installation guide. Now you should have a command line window open running the APEX Listener. Closing this window will shut down the listener.
To get rid of starting the Listener manually, we need to find a way to start the command as service. A little freeware program does the trick: NSSM - the Non-Sucking Service Manager. Starting NSSM, you just need to enter the <path>/command, its command line options and a name for the windows service. Make sure nssm.exe is installed somewhere where Windows can find it through the $PATH environment variable.
For my APEX Listener, I create a .bat file that I then call as service startup script, startlistener.bat, and put it into my Listener’s installation folder.
cd \
cd C:\oracle\apex_listener.
java -Dapex.home=C:\oracle\apex_listener.\listener_conf -jar apex.war

Now all I have to do is starting a command window (in Administrator mode) and call
C:> nssm install
I enter my .bat file and give my service a name in the dialog window popping up:


Friday, June 8, 2012

APEX + jQueryMobile + EURO 2012 Championship = Lots’s of Fun

euro-2012-official-logo-transp Tonight (CET) the European Football Championship kicks off. As with every EURO or World Championship I’m organizing a predicting game where you (Yes, you too!) can bet on match results FifApex. And again, the website is build with Oracle Application Express. New this year: I created a mobile version of FifApex (m.fifapex.net) with APEX and the jQuery Mobile framework. If you want to know how I’ve done this, come visit my presentation at the ODTUG KScope12 conference in San Antonio, Texas, end of this month. Meanwhile, register for FifApex, enter our predictions, and maybe you will be one of the winners of the prizes …




BTW: if you missed the first games: you still can enter the competition AND, from my experience, I can tell you: there is not a lot football/soccer knowledge required for a good ranking ;-).

Tuesday, April 3, 2012

"Who are you?" – client device Categorizr for APEX

This article describes a solution to determine the client device category (i.e. desktop, browser or tablet) your APEX application is accessed from.


Until recently, building a web application with Oracle Application Express usually meant to build a desktop browser application. But if you look at the growing number of mobile devices, this will change very soon, if not already has for some of you. Being able to develop applications, that can be accessed by tablets or smart phones offers lots of new possibilities. But it also comes with some challenges. Mobile devices are different. Interaction by touch screen, screen size and (non-) support of certain common web functionality (flash, for example), just to mention a few.

Oracles announcement to integrate the jQuery mobile framework into APEX 4.2 will help us to deal with many of the challenges, developing for multiple devices will bring. Until now, one of the biggest challenges is, to determine the actual client your application is accessed on. Is it a desktop browser, a tablet or a smart phone? In this post I want to offer a solution that will provide an answer to this question, and can be used in APEX.

When building a web application, as a developer, you want to be able anticipate on how your application is being accessed. You might want to redirect the user to a different page or entirely different application. Or you might want to hide (i.e. not render) or show certain portions of a page. To be able to do so, you need to know the type of client used, and, ideally, be able to use this information in (PL/SQL) conditions.

Getting the info you need to decide

Every page request comes with a HTTP header, containing some generic information about the browser client it has been issued from: The HTTP_USER_AGENT string. This string holds information about browser, platform and version. The string itself can easily be accessed in PL/SQL by using the OWA_UTIL package function call:

l_user_agent := owa_util.get_cgi_env ('HTTP_USER_AGENT');

Once you've got the user agent string, the hard work starts. There are all kind of values you can get returned to you in the user agent string. It can be messy - VERY messy. Sometimes browser even lie about who they are, and the format is not standardized.

There are many approaches trying to find a way through this mess, and you can find a few articles on this at the bottom of this post. Some use databases with almost all possible user agent string stored, others try to decipher the string programmatically. Most of the code is available in Java, PHP or .net. I didn't find any appropriate code for PL/SQL. So I had to write my own or port some code I can understand.


What I wanted to achieve, was to be able to categorize the client device rendering the page in my application. I found some PHP code doing just that: Categorizr. The solution has some advantages:

  • It is lightweight and has not too many lines of code, which makes it reasonably easy to maintain it. With the growing number of mobile devices, I will have to maintain the algorithm. The approach chosen by the developer assumes the device to be a mobile device and then checks for other categories. By assuming devices are mobile from the beginning, Categorizr aims to be more future friendly. When new phones come out, you don’t need to worry if their new user agent is in your device detection script since devices are assumed mobile from the start.
  • The algorithm uses Regular Expressions, which are supported by PL/SQL. This minimizes the changes I have to apply to the code (Year, I know: I’m lazy).
  • Due to the minimal codebase and not querying large databases or accessing a web service, it’s fast.

The PLSQL package (specification) I have written offers these functions that you can use in your (APEX) PL/SQL code:

      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      Based on:
      Categorizr Version 1.1
      Written by Brett Jankord - Copyright (c) 2011

      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30-3-2012   crokitta         Created this package.
   g_tablets_as_desktops   BOOLEAN := FALSE; --If TRUE, tablets will be categorized as desktops
   g_smarttv_as_desktops   BOOLEAN := FALSE; --If TRUE, smartTVs will be categorized as desktops
   g_user_agent            VARCHAR2 (2000); -- User Agent String used for detection
   g_device                VARCHAR2 (100);

   FUNCTION get_category

   FUNCTION isdesktop

   FUNCTION istablet

   FUNCTION istv

   FUNCTION ismobile

    The package is initialized automatically when called, trying to fetch the value of
    the HTTP_USER_AGENT, which naturally only succeeds when called through a web gateway.
    Additionally the package just offers a mean to test a user agent strings manually by
    passing the string with a procedure call

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL);
END categorizr;

If you are interested the package body, click the link below and it will show up:

      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30-3-2012   crokitta         Created this package.

   FUNCTION preg_match (pattern    VARCHAR2,
                        subject    VARCHAR2,
                        switch     VARCHAR2 DEFAULT NULL)
      l_pattern   VARCHAR2 (32767) := pattern;
      l_subject   VARCHAR2 (32767) := subject;
      IF LOWER (switch) = 'i'
         l_pattern := LOWER (l_pattern);
         l_subject := LOWER (l_subject);
      END IF;

      IF REGEXP_INSTR (l_subject, l_pattern) = 0
         RETURN FALSE;
         RETURN TRUE;
      END IF;

   PROCEDURE set_category
         -- Check if user agent is a smart TV - http://goo.gl/FocDk
         WHEN preg_match ('GoogleTV|SmartTV|Internet.TV|NetCast|NETTV|AppleTV|boxee|Kylo|Roku|DLNADOC|CE\-HTML', g_user_agent, 'i')
            g_device := 'tv';
         -- Check if user agent is a TV Based Gaming Console
         WHEN preg_match ('Xbox|PLAYSTATION.3|Wii', g_user_agent, 'i')
            g_device := 'tv';
         -- Check if user agent is a Tablet
         WHEN (preg_match ('iP(a|ro)d', g_user_agent, 'i')
               OR preg_match ('tablet|tsb_cloud_companion', g_user_agent, 'i'))
              AND (NOT preg_match ('RX-34', g_user_agent, 'i')
                   OR preg_match ('FOLIO', g_user_agent, 'i'))
            g_device := 'tablet';
         -- Check if user agent is an Android Tablet
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('Android', g_user_agent, 'i')
              AND (NOT preg_match ('Fennec|mobi|HTC.Magic|HTCX06HT|Nexus.One|SC-02B|fone.945', g_user_agent, 'i')
               --or preg_match ('GT-P1000', g_user_agent, 'i')
            g_device := 'tablet';
         -- Check if user agent is a Kindle or Kindle Fire
         WHEN preg_match ('Kindle', g_user_agent, 'i')
              OR preg_match ('Mac.OS', g_user_agent, 'i')
                AND preg_match ('Silk', g_user_agent, 'i')
            g_device := 'tablet';
         -- Check if user agent is a pre Android 3.0 Tablet
         WHEN preg_match (
              OR preg_match ('MB511', g_user_agent, 'i')
                AND preg_match ('RUTEM', g_user_agent, 'i')
            g_device := 'tablet';
         -- Check if user agent is unique Mobile User Agent
         WHEN preg_match ('BOLT|Fennec|Iris|Maemo|Minimo|Mobi|mowser|NetFront|Novarra|Prism|RX-34|Skyfire|Tear|XV6875|XV6975|Google.Wireless.Transcoder', g_user_agent, 'i')
            g_device := 'mobile';
         -- Check if user agent is an odd Opera User Agent - http:--goo.gl/nK90K
         WHEN preg_match ('Opera', g_user_agent, 'i')
              AND preg_match ('Windows.NT.5', g_user_agent, 'i')
              AND preg_match ('HTC|Xda|Mini|Vario|SAMSUNG\-GT\-i8000|SAMSUNG\-SGH\-i9', g_user_agent, 'i')
            g_device := 'mobile';
         -- Check if user agent is Windows Desktop
         WHEN preg_match ('Windows.(NT|XP|ME|9)', g_user_agent, 'i')
              AND NOT preg_match ('Phone', g_user_agent, 'i')
              OR preg_match ('Win(9|.9|NT)', g_user_agent, 'i')
            g_device := 'desktop';
         -- Check if agent is Mac Desktop
         WHEN preg_match ('Macintosh|PowerPC', g_user_agent, 'i')
              AND NOT preg_match ('Silk', g_user_agent, 'i')
            g_device := 'desktop';
         -- Check if user agent is a Linux Desktop
         WHEN preg_match ('Linux', g_user_agent, 'i')
              AND preg_match ('X11', g_user_agent, 'i')
            g_device := 'desktop';
         -- Check if user agent is a Solaris, SunOS, BSD Desktop
         WHEN preg_match ('Solaris|SunOS|BSD', g_user_agent, 'i')
            g_device := 'desktop';
         -- Check if user agent is a Desktop BOT/Crawler/Spider
         WHEN preg_match ('Bot|Crawler|Spider|Yahoo|ia_archiver|Covario-IDS|findlinks|DataparkSearch|larbin|Mediapartners-Google|NG-Search|Snappy|Teoma|Jeeves|TinEye', g_user_agent, 'i')
              AND NOT preg_match ('Mobile', g_user_agent, 'i')
            g_device := 'desktop';
         -- Otherwise assume it is a Mobile Device
            g_device := 'mobile';
      END CASE;

      -- Categorize Tablets as desktops
      IF g_tablets_as_desktops
         AND g_device = 'tablet'
         g_device := 'desktop';
      END IF;

      -- Categorize TVs as desktops
      IF g_smarttv_as_desktops
         AND g_device = 'tv'
         g_device := 'desktop';
      END IF;

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL)
      g_user_agent := http_user_agent_string;

      IF g_user_agent IS NULL
            g_user_agent := OWA_UTIL.get_cgi_env ('HTTP_USER_AGENT');
            WHEN OTHERS
               g_user_agent := NULL;
      END IF;

         g_user_agent := null;

   FUNCTION get_category
      RETURN g_device;

   -- Returns true if desktop user agent is detected
   FUNCTION isdesktop
      IF g_device = 'desktop'
         RETURN TRUE;
      END IF;


   -- Returns true if tablet user agent is detected
   FUNCTION istablet
      IF g_device = 'tablet'
         RETURN TRUE;
      END IF;


   -- Returns true if SmartTV user agent is detected
   FUNCTION istv
      IF g_device = 'tv'
         RETURN TRUE;
      END IF;


   -- Returns true if mobile user agent is detected
   FUNCTION ismobile
      IF g_device = 'mobile'
         RETURN TRUE;
      END IF;

END categorizr;

The functions can easily be used in APEX region conditions or any other PL/SQL code. Install the package in your workspace’s schema or in a separate schema with execute granted to public and a public synonym on it.

You can download the whole package HERE.

I tested the package against a list of. 11,000 common user agent strings. The algorithm is not failsafe, but reasonably accurate. Of course I cannot test every client possible. Try the demo page I prepared and drop me an email if you believe the outcome of the package is not correct.



Categorizr – A modern device detection script
User Agent String.Com
WURFL, the Wireless Universal Resource FiLe, is a Device Description Repository (DDR)

Tuesday, March 27, 2012

A Glimpse of APEX 4.2

Yesterdays OGh APEX day in Zeist (NL) was a great success. The probably largest APEX-only congress (almost 300 APEX enthusiasts) was packed with 3 parallel tracks of presentations covering a wide variety of APEX related subjects.

The OGh organization committee again managed to invite well known APEX experts as speakers: Dimitri Gielis and John Scott (the dynamic APEX Evangelists Duo), Roel Hartman, Alex Nuijten, Patrick Barel (all ACE or ACED so far). Iloon Ellen Wolff, from Oracle NL, gave us some previews on Oracle’s Cloud Service, mostly slides and video’s. A live demo wasn’t possible, because there is something going on at Oracle and we may can expect some exciting news very soon.
The highlight, in my eyes, was Patrick Wolf’s keynote presentation, talking about the recent enhancements of 4.1.1,and, much more exciting, giving us a glimpse of what will come in the long awaited 4.2 upgrade. After showing the usual “Oracle Save Harbor Statement” the conference participants were treated with some live demos of the Oracle Application Express teams work on the new version. As far as I can tell now, they are doing a great job, especially in integrating the jQuery Mobile framework into APEX in a way that perfectly fits the “declarative way” you would expect from building applications with APEX. To mention just a few highlights (well, you know: everything mentioned might OR might not be a feature in a future version of APEX):
Multiple theme support in one application: means you can create mobile and “standard” pages in one application. This enhancement would take away the need to build and deliver separate applications for targeting different devices.
  • HTML5 Form Input Type support
  • This is a really essential feature for mobile support. APEX 4.2 will (or may not) support sub types for (Text) input elements.
  • Declarative Configuration
  • If you have taken a look at the jQuery Mobile framework already, you know that its components configuration is done by adding additional “data-” HTML attributes. In the screens I saw, these attributes where translated and integrated as element/region properties, just as you are used to from other APEX builder screens.
  • List-Views
  • One of the main components in jQuery Mobile with respect to presenting data on a mobile device will be (or may not be) very well integrated and support (or may not) all the major features the jQuery Mobile framework has to offer in a declarative way, like List Dividers, Count bubble and Search Bar Filter.

Pity I didn’t take any photos of APEX Builder screens during Patrick’s presentation. I was too busy modifying my own presentation about jQuery Mobile usage in version 4.1 that was scheduled later that day. I just had to refer to his presentation somehow. Integration jQuery Mobile “manually” looks so clumsy, compared to what we can expect from Oracle Application Express 4.2. I’m looking forward to the first (EA) releases of 4.2. When will that be? That’s the one question Patrick didn’t answer, not even under “Save Harbor” conditions.

Tuesday, March 20, 2012

themes4apex - custom UI for your APEX application

This short article is actually a shameless plug for my new service and website. So, if you are looking for some technical info on APEX: do not read any further. But if you want to provide your APEX application with a custom UI design without having to dive into HTML, CSS scripting and creating graphical components yourself, pay a visit to my brand new website: themes4apex.com.


I offer to design and implement APEX themes and templates based on your specifications, whatever these are: an existing web page, concepts created in PowerPoint  or any other image reference or prototyping tool.


I like to keep things simple. So your custom APEX UI design will be based on HTML and CSS, DIV-based and guaranteed to tested and working in all current browser brands and versions.


You will receive your custom theme ready to import into your APEX environment together with a demo application using the theme.

Interested? Visit themes4apex and contact me.