Wednesday 28 November 2012

APEX 5.0 Statement of Direction

The Oracle Application Express statement of direction was just announce by David Peake, I thought I'd share some initial thoughts.
  1. Modal dialog - the use of Dan McGan's modal dialog plug-in has been an integral part of a recent project. For me the ability to declaratively define these would be akin to what dynamic actions has over writing JavaScript. I think it will important for workflow design particularly in tablet applications - but mobile/desktop will benefit in the reach-ability.
  2. Drag and Drop Layout Editor - Introduced in 3.0, removed in 4.2 (probably as a result of component view re-design) I didn't use this much, but with the advent of the grid layout, this feature will probably evolve into something quite useful.
  3. HTML5 Capabilities - many mobile presentations last year rightly commented on the importance of HTML5. The 4.2 release is a little under-cocked when it comes to native capabilities, so it will be interesting to see what they include. 
  4. PDF Printing - external reporting options have always been lean with APEX. It seems now the APEX Listener is evolving, so can the cohesiveness for FOP support.
  5. Web Services Support - another area that improves with the growth of the APEX Listener.
  6. Tablet User Interface - perhaps I was too forward thinking when I suggested TVs might be the next interface. Tablets are seemingly more ubiquitous than laptops these days, and it seems the three major devices sizes (in regard to screen/workflow design) are mobile; tablet; desktop - so it seems prudent to have a UI and features applicable to tablet devices.
  7. Packaged Applications - going hand-in-hand with the Oracle Cloud Service, of course this will improve. No doubt the APEX team also uses these applications as practice/alpha testing for future releases.
  8. New multi-row region type - wow. I'm racking my brain trying to imagine what this may look like - I have a few ideas, but I'll just keep re-reading their sentence: "Define a new region type with a modern UI for updating multiple rows of data and allow multiple regions on one page"
  9. Master / Detail / Detail - provid the ability declaratively define these - sure, why not. David seems to like them.
  10. Multiple Interactive Reports - I think this has been a long time coming for a few people, and it was only yesterday I imagined an a need that interested me - multiple smaller regions utilising only the Detail View.
  11. Application Builder Security - I heard David mention this at the APEXposed conference in Melbourne. I can see the yearn, but I don't particularly care. Though I'm sure I'd use it if it was there.
  12. Numerous functional improvements - they're usually the ones that quietly surprise us as we move around the builder.
Another thing to consider as David mentioned, if you have any particular features you would like in the next release - there is a APEX Feature Request application. Get in there - I've submitted a few myself. Maybe hit up the wish list forum thread for ideas.

If you read the PDF document linked on the SOD page, it also talks about Future Investment. Oracle APEX is the standard development tool included with Database Cloud Service, so APEX has an important future within Oracle. That an the ability for Larry to analyse his yachts...
BMW Oracle Racing Trimaran 
What happens when someone uses ADF on the boat </joke>
APEX is also a standard feature of the Oracle Database, therefore the support guidelines for APEX are similar to those for the DB.

Check out the support policy, too - for example, premier support for APEX 3.x expired in Feb 2012.

If you're interested, I've commented previously on this for 4.2 and 4.1, since this is now my predominant tool of choice. Maybe I'll look at those later and see how things have panned out.

Happy APEXing!

Scott

Tuesday 20 November 2012

Oracle Forms New Features

Oracle Forms is a product that has shown staying power, but because the product is aging, support is waning, and new products are coming to replace it - there is little need for new features.

Those that do come out are often esoteric - there to fit highly specific solutions, or help wire in to the newer systems.

I had to compile a list of features from 9i through 10g to 11g, and I thought I'd offer the summary here

11g New Features
External events (Queuing)
Integration with JavaScript in surrounding web page (documented)
Calling from surrounding webpage into Forms
Making use of proxy user functionality
Forms & grid control
Pluggable Java Components (PJC whitepaper)
Enhancement to Trace
Support for Oracle Diagnostic Logging
Integration with Reports

10g New Features
Runtime prestart
URL Security
SSO Upgrades
Enhanced EM support
WebUtil Patchset
New features
- GIF/JPG support
- Hide connection info from URL when testing

9i New Features
Forms Listener SErvlet
Single Sign-on Support
Improved translation
Support for character semantics
Timezone support
Development UI improvements
XML
Accessibility
Runtime improvements
Enhanced JavaBean Support
EM Integration
Obsolescence

In the interests of offering a pretty image, I thought the solution to integrating JavaScript was painted well
Oracle Fusion Middleware Documentation
I wonder what adjustments might be made to accommodate 12c? Probably something under the hood regarding pluggable databases.

So nope - not a post about new features of a product like APEX, but a consideration to a product that helped us get there.

Monday 19 November 2012

Webinar: A foray into APEX mobile

If you missed it, it's going to be ok!

For those unable to make it to the Perth conference this year, I will be presenting my session as a webinar on  the 18th December 2012 at
4pm - New York
9pm - UTC / London
(19th December)
5am - Perth (yes, I'm getting up early)
8am - Sydney/Melbourne
(timeanddate.com)

Register via ODTUG.

I've modified the abstract slightly considering the accompanying application was designed for the conference - feel free to have a play now - even play the buzzwords game.

A foray into APEX mobile

Mobile development had beginnings even prior to Oracle Application Express 4.x, but with 4.1 itching to be a true mobile development platform - only held back by the jQuery library - APEX 4.2 has arrived with it as standard armoury in the utility belt.

We asked delegates prior to the 2012 Perth AUSOUG Conference to visit the following site either on desktop or mobile device apex.oracle.com/pls/apex/f?p=SW2012 (via tinyurl.com/SW2012AUSOUG)

We encouraged them to register interest in presentations, then provide feedback on each session. It is essentially a prototype built specifically for the purpose of this presentation, but it became a valuable tool for the conference organisers, and will be improved & polished for next year.

Listen to this presentation to see how the sausage was made; what you may expect to see out of APEX mobile development in the future; and what you need to consider learning before putting on the stetson!
It will be interesting to see how the prezi style works as a webinar, because in person I find it's more engaging than powerpoint - as long as you don't make the movements too much.


Scott

Thursday 15 November 2012

Review: APEXposed Melbourne 2012

Thanks to ODTUG, we had the pleasure of part-time Aussie David Peake, who happens to be an Oracle  product manager for APEX, & Canadian ACE Director Martin Giffy D'Souza from ClariFit. They were here to conduct 2 rounds of APEXposed - one in Auckland, then Melbourne.

With thanks to Bambi Price and other user group committee members - visitors from many states were ushered into a room with 2 large screens, great room width but only 5 or so rows - it worked. Compliments also to Cliftons, I liked the venue. And those chocolate things on day 1 arvo tea - yummo.

I'd like to offer my take of each session, at least what I remember and garner from a few tweets that were about #APEXposed

1 - Intro to APEX
I was a little unsure what the level of detail would be, and the ODTUG team anticipated this an apologised in advance for not meeting everyone's needs & wants - which I thought was great. I think a few people suggested separate advanced session for next year.
For me this was mainly a repeat of recent conference material, and a little "yeah come on, get on with the good stuff". Although I did note that I should have a fresh look at the Collateral section on OTN.

2 - APEX page types
It was good to have a walk through of what APEX is currently offering with commentary from Martin. Audience feedback was pretty good too. I counted about 60 people, with perhaps a dozen double power outlets around the room... hmm
It was also good to hear David's displeasure for tabular forms matches with mine.

3 - Dynamic Actions
A nice show & tell comparison with Martin performing something with JavaScript, and David repeating the function with dynamic actions. I mainly picked up notes regarding use of jQuery.
Don't read this lightly - I think dynamic actions are arguably the best feature to come out of APEX 4.x, competing tightly with the next topic...

4 - Plug-Ins
I think it may be a while before I create any item or DA plug-ins, but Security & process plug-ins may be on the agenda. It was brilliant to have someone who wrote a book on plug-ins to describe the mechanics of these magical components.
Martin ready for the big reveal
For those that missed it, here is the link to the APEX Builder plug-in.

5 - SQL Developer & SQL Data Modeler
I'm a regular user of SQL Developer for mainly SQL tasks, and occasionally re-engineer data models from the database with SQL Data Modeler, but I was interested to see what David had to show. I got a little bored of mentions to the cloud, as interesting as it would be to have a piece.

Hands on labs
Brilliant that they used VirtualBox to deliver a pre-packaged working environment with APEX workshops ready to go - but for me a waste. Others found it useful. I took the time to catch up with a few people, drop my bag off at the hotel, then meet the depleted team for a beer or three at the local.
What I will take away from this is the discovery of VirtualBox.

(sleep)

6 - Oracle Database Cloud
A good session covering all things Oracle cloud, although at times a little too salesy.

7 - Using APEX to build mobile applications
Even though I just experimented with and built a mobile application with APEX, I was keen to see what they had to demonstrate. I learnt a few things, it's certainly a big learning curve to climb, but I already crossed many of the bridges mentioned.  I think it was a well received session from the crowd.

8 - Debugging tools & techniques
Coming into it I think this was going to be my favourite session - and it turned out to be the case. Not so much for the demonstration of the tools, although it was interesting to see how someone else utilises them - but some of the idea's Martin presented for using components in ways I never thought of. Two examples
1) Using the error page template to e-mail notification of the error - something I've done myself in a Forms environment in the past :-)
2) Using build options to display debugging information such as session state in a global page (page zero) region.

9 - Securing APEX 
What I picked up from this session was delivery tips from Martin. I know APEX security fairly well, and Martin joked that I should have been delivering that session. From my perspective in the crowd as a presenter & part-time trainer - I took the opportunity to see how Martin delivered this topic.

10 - Administering & Deploying APEX
What I should have expected, but not what I was hoping for. David talked about the mechanics of infrastructure & delivery - again involving the cloud, and at the end of day 2 it was getting a little dry. I was hoping for more real-world scenarios of how people manage version control of APEX applications, for instance.
Common problems people face moving & versioning applications between environments. In saying that, David suggested maybe perhaps a fairy said in future there may be something going on with Editioning.

Do look out for a post coming up from me regarding APEX_APPLICATION_INSTALL - there were a few questions relating to this, and I have some content to share.

11 - APEX best practices
Ended up being a little short, I think everyone was tired & their brains were full. While the questions were good, there didn't get too deep since we were all tired. I look forward, however, to reading the best practices book.

Summary

For beginners it would have been a wealth of information, there was even fair call occasionally to the DBAs in the room. Intermediates would have had a number of good sessions each day.

I'm happy with my output - picked up a dozen good tips, and assert the understanding & opinions in other areas. I also paid note to the way everything was delivered, a few things caught my eyes & ears.

For all it should have been a valuable networking experience.

I do hope if the event were to be replicated in some form in future, I would like to see time dedicated to more advanced. I imagine the topic lineup may differ in future, the release of 4.2 almost wrote the agenda itself.

My suggested future event

I think Melbourne location was good for everybody, but I'd always invite people to Perth.

Same standard of speakers. Martin & David were very good. I don't think it would hurt to involve locals in some sessions. I feel people are more attentive when more speakers are present.

Major logistical improvement would be to increase the amount of power outlets - it doesn't need to be 1 for 1, but increase the ratio. People get resourceful.
Unless of course batteries undertake massive improvements in the next 6 months - or these ultrabooks with SSDs truly hard power friendly.
Or maybe there'll be a wireless charging pad along the desks, charging everybody's devices? I digress...

One day dedicated to the basic-intermediate skill level, talking about latest new features & developments, perhaps some standard topics & problems featured in detail - enough to keep everybody interested.

And then a second day dedicated to the intermediate-advanced developers, looking at
  • jQuery
  • CSS
  • using attributes
  • dynamic actions
  • scripting deployment
  • responsive design
  • jQuery Mobile
  • jQuery plug-ins (non-Oracle)
  • plug-in consumption case studies
  • JSON
  • Web-services
I'd go to see that, and tell my friends.

And if I had it my way, I think that content could fill three days, not two.

Scott

Wednesday 14 November 2012

New APEX book - best practices

Today I picked up my copy of Oracle APEX Best Practices.

There have been some quality APEX books come by over time, but I'm a little excited about this one. The contents looks interesting, and it will be interesting to see how the authors talk about best practices now that APEX is really starting to mature.

I'll be sure to provide a more thorough review later, thanks to authors
Learco Brizzi
Iloon Ellen-Wolff
Alex Nuijten

all of whom I had to be very careful with spelling :-)

Scott

2012 Best New Speaker - AUSOUG

Congratulations to to the 2012 Perth AUSOUG Conference Best New Speaker: Matthew Carrigy - Department of Finance.

Despite being sick from food poisoning from the day before, he still came in to deliver his components of an interesting case study on an Oracle Forms to ADF conversion project.

He was clear, presentable & he covered the technical aspects quite well.

I certainly don't want to take anything away from the other new presenters we had at the conference - a few to choose from. There were some well though out presentations, but Matthew shone a little brighter.

Well done.

I know the news is a little late, but I was trying to acquire a photo to accompany the announcement.

Scott

ps - this year my managing director, Penny Cookson, mentored a number of speakers - some of whom presented at the conference as well as events on the lead up. Next year we will continue this mentoring program - contact us if you have any interest at all in presenting. It doesn't have to be at a conference, we have a number of smaller events held most months. New speakers are always welcome

Wednesday 7 November 2012

SQL tip: List days of the week

This one's real quick - something handy for later.

I've done some similar before for all days/weekends of the year, or the ubiquitous months of the year.

Here I wanted to dynamically define days of the week:
WITH data AS 
 (SELECT NEXT_DAY(sysdate, 'MON') dt
  FROM dual
  CONNECT BY LEVEL <= 7)
SELECT TO_CHAR(dt+ROWNUM-1,'DY'), TO_CHAR(dt+ROWNUM-1,'Day')
FROM data;
Scott

Sunday 4 November 2012

Presentation : A foray into APEX mobile

Here is the link for presentation I did at the 2012 Perth AUSOUG Conference - A foray into APEX mobile.

It will link you to Prezi, where you just click the arrow through.
(single frame from my Prezi)
It works better with a voice to go with, I might screen-cast it over YouTube sometime. I received some great feedback, thank you.

With this new style, I have more ideas to come - not limited to purely Oracle.

Scott

Categorizr for APEX - extended

Software evolves.

And I think it's going on right now. In April, Christian Rokitta blogged about Categorizr.

He adapted some PHP code from Brett Jankord and suited it to PL/SQL.

When I was building the mobile application for my AUSOUG presentation, I found it useful for statistics gathering... but I wanted more.

I added a few more functions, and included an object type to allow queries shown further below.
These functions broke/butchered the agent string further into
  • OS 
  • OS Version
  • Browser
  • Browser version
I think I've done well to cover the major data sets.
CREATE TYPE categorizr_agent_details IS OBJECT (
     agent                 VARCHAR2 (2000)
    ,device                VARCHAR2 (2000)
    ,os                    VARCHAR2 (2000)
    ,os_version            VARCHAR2 (2000)
    ,browser               VARCHAR2 (2000)
    ,browser_version       VARCHAR2 (2000)
   );
/

CREATE OR REPLACE PACKAGE categorizr
AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      Based on:
      Categorizr Version 1.1
      http://www.brettjankord.com/2012/01/16/categorizr-a-modern-device-detection-script/
      Written by Brett Jankord - Copyright © 2011

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      0.1        30- 3-2012  crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
                                              With inspiration from http://barakhshan.wetpaint.com/page/detecting+os+and+browser+pl-sql
                                              and help from my own dataset. Doubtful all scenarios considered
   ******************************************************************************/
   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_agent_details  categorizr_agent_details;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details;

   FUNCTION get_category
      RETURN VARCHAR2;

   FUNCTION get_browser
      RETURN VARCHAR2;

   FUNCTION get_os
      RETURN VARCHAR2;

   FUNCTION isdesktop
      RETURN BOOLEAN;

   FUNCTION istablet
      RETURN BOOLEAN;

   FUNCTION istv
      RETURN BOOLEAN;

   FUNCTION ismobile
      RETURN BOOLEAN;

   /*
    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're interested in the package body, click as instructed
CREATE OR REPLACE PACKAGE BODY categorizr AS
   /******************************************************************************
      NAME:       categorizr
      PURPOSE:    detect web user agent device type

      REVISIONS:
      Ver        Date        Author           Description
      ---------  ----------  ---------------  ------------------------------------
      1.0        30-3-2012   crokitta         Created this package.
      0.2        24-10-2012  swesley          Included browser/os gets
   ******************************************************************************/


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

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

   PROCEDURE set_category
   IS
   BEGIN
      CASE
         -- 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')
         THEN
            g_agent_details.device := 'tv';
         -- Check if user agent is a TV Based Gaming Console
         WHEN preg_match ('Xbox|PLAYSTATION.3|Wii', g_user_agent, 'i')
         THEN
            g_agent_details.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'))
         THEN
            g_agent_details.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')
               )
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.device := 'tablet';
         -- Check if user agent is a pre Android 3.0 Tablet
         WHEN preg_match (
                 'GT-P10|SC-01C|SHW-M180S|SGH-T849|SCH-I800|SHW-M180L|SPH-P100|SGH-I987|zt180|HTC(.Flyer|\_Flyer)|Sprint.ATP51|ViewPad7|pandigital(sprnova|nova)|Ideos.S7|Dell.Streak.7|Advent.Vega|A101IT|A70BHT|MID7015|Next2|nook',
                 g_user_agent,'i')
              OR preg_match ('MB511', g_user_agent, 'i')
                AND preg_match ('RUTEM', g_user_agent, 'i')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.device := 'desktop';
         -- Check if user agent is a Solaris, SunOS, BSD Desktop
         WHEN preg_match ('Solaris|SunOS|BSD', g_user_agent, 'i')
         THEN
            g_agent_details.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')
         THEN
            g_agent_details.device := 'desktop';
         -- Otherwise assume it is a Mobile Device
         ELSE
            g_agent_details.device := 'mobile';
      END CASE;

      -- Categorize Tablets as desktops
      IF g_tablets_as_desktops
      AND g_agent_details.device = 'tablet'
      THEN
         g_agent_details.device := 'desktop';
      END IF;

      -- Categorize TVs as desktops
      IF g_smarttv_as_desktops
      AND g_agent_details.device = 'tv'
      THEN
         g_agent_details.device := 'desktop';
      END IF;
   END;

   PROCEDURE set_browser
   IS
   BEGIN
      IF preg_match ('Opera', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Opera';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Opera/')+6);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('MSIE', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Internet Explorer';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'MSIE ')+5);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Chrome', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Chrome';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Chrome/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Firefox', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Firefox';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Firefox/')+8);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSIF preg_match ('Safari', g_user_agent, 'i') THEN
        g_agent_details.browser := 'Safari';
        g_agent_details.browser_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Safari/')+7);
        g_agent_details.browser_version := SUBSTR(g_agent_details.browser_version, 1, INSTR(REGEXP_REPLACE(g_agent_details.browser_version,'[.;]',')'), ')')-1);

      ELSE
        g_agent_details.browser := 'Other';
        g_agent_details.browser_version := NULL;
      END IF;
   END set_browser;

   PROCEDURE set_os
   IS
   BEGIN
      IF preg_match ('iPad|iPod|iPhone', g_user_agent, 'i') THEN
        g_agent_details.os := 'iOS';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, ' OS ')+4);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(g_agent_details.os_version, ' ')-1);

      ELSIF preg_match ('Windows', g_user_agent, 'i') THEN
        g_agent_details.os := 'Windows';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Windows ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Mac OS', g_user_agent, 'i') THEN
        g_agent_details.os := 'Macintosh';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Mac OS X ')+9);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('RIM Tablet', g_user_agent, 'i') THEN
        g_agent_details.os := 'RIM';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'RIM Tablet OS ')+13);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Android', g_user_agent, 'i') THEN
        g_agent_details.os := 'Android';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Android ')+8);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSIF preg_match ('Linux', g_user_agent, 'i') THEN
        g_agent_details.os := 'Linux';
        g_agent_details.os_version := SUBSTR(g_user_agent, INSTR(g_user_agent, 'Linux ')+6);
        g_agent_details.os_version := SUBSTR(g_agent_details.os_version, 1, INSTR(REPLACE(g_agent_details.os_version,';',')'), ')')-1);

      ELSE
        g_agent_details.os := 'Other';
        g_agent_details.os_version := NULL;
      END IF;
   END set_os;

   FUNCTION get_agent_details(p_user_agent  VARCHAR2)
      RETURN categorizr_agent_details IS
   BEGIN
     -- override package initialisation
     set_user_agent(p_user_agent);
     RETURN g_agent_details;
   END get_agent_details;

   PROCEDURE set_user_agent (http_user_agent_string VARCHAR2 DEFAULT NULL)
   IS
   BEGIN
     g_agent_details := NEW categorizr_agent_details(null,null,null,null,null,null);
      g_user_agent := http_user_agent_string;

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

      set_category;
      set_os;
      set_browser;
      g_agent_details.agent := g_user_agent;
   /*EXCEPTION
      WHEN OTHERS
      THEN
         g_user_agent := null;*/
   END;

   FUNCTION get_category
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.device;
   END;

   FUNCTION get_browser
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.browser;
   END;

   FUNCTION get_os
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_agent_details.os;
   END;

   -- Returns true if desktop user agent is detected
   FUNCTION isdesktop
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'desktop'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if tablet user agent is detected
   FUNCTION istablet
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tablet'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if SmartTV user agent is detected
   FUNCTION istv
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'tv'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;

   -- Returns true if mobile user agent is detected
   FUNCTION ismobile
      RETURN BOOLEAN
   IS
   BEGIN
      IF g_agent_details.device = 'mobile'
      THEN
         RETURN TRUE;
      END IF;

      RETURN FALSE;
   END;
BEGIN
   set_user_agent;
   null;
END categorizr;
I've created an interactive report on visits data from that application: http://apex.oracle.com/pls/apex/f?p=SW2012:CATEGORIZR


This query used to generate that report
select 
   cnt
  ,categorizr.get_agent_details(v.agent).browser browser
  ,categorizr.get_agent_details(v.agent).browser_version b_version
  ,categorizr.get_agent_details(v.agent).os os
  ,categorizr.get_agent_details(v.agent).os_version os_version
  ,categorizr.get_agent_details(v.agent).device device
  ,categorizr.get_agent_details(v.agent).agent the_agent
from 
  (select agent, count(*) cnt from am_visits group by agent) v

If you were to utilise this package for production code, I would first check it for defects - then question your need to set the user agent in the package body.

I might also suggest that as this code matures, it could be added to the Alexandra PL/SQL Library, administered by Morten, expanded/updated by the masses, encouraging other utilities to come visiting.

Let me know if you find it handy or have any feedback. Thank you Christian & Brett for the boost.

Scott