Patching to APEX 4.2.1

I found the patch for APEX 4.2.1 to be easy, as usual - but I've come out with one question.

When in the APEX builder, keep an eye out for this region on the right hand side - if you haven't heard about a patch through the copious amounts of blogs or social media, then this is on place you might see the prompt.

As described on the APEX download page, you can either download the entire product; for English only; or if you're already on 4.2.0, you can download the 66mb patch set from My Oracle Support - patch #14732511

The patch set notes can be found here, or as part of your download.

My first step to upgrade the instance on my Windows 7 laptop was to terminate APEX Listener with Ctrl-C

Now open a command prompt, change current folder to where the patch is, connected to SQL*Plus and ran the install script
cd \apex4.2\patch
sqlplus /nolog
conn sys as sysdba

Ten minutes later, I found no errors in the apxpatch.log although the log didn't capture the final output I saw in the command display.
It finished in the middle of
Application Express 4.2.1 patch
... lots of stuff
... Sample Access Control: Application ID 7600
... some more stuff
......region template 5047053830717713422
-- no more logs!

I opened f7600.sql, found the exact location but saw no reason why the log would stop.

Moving on, it came to updating the images directory when using Oracle APEX Listener, and for me it got a little obscure.

Ever since I can remember, this step involved copying the images folder to where the relevant web server sourced the documents, but the current documentation suggests creating a web archive.

If you are using the Oracle Application Express Listener, you need to create a Web Archive for Oracle Application Express images

I decided not to do this in my circumstance - stand alone APEX listener, with no WLS/Glassfish because the  documentation for this option didn't require it.

So I made a backup of my old images folder by renaming to /images4.2/

Then dragged /images from the patch folder to my apex4.2 directory.

Now time to restart the APEX Listener using my batch file on the desktop, which runs
cd c:\apex_listener2\
java -jar apex.war standalone --apex-images \apex4.2\images

I also checked my older post regarding APEX 4.1 upgrade to see if there was something I missed.

Opening the localhost:8080/i/apex_version.txt showed the patched version, so no harm done?

My question - under what circumstances would you need to create the web archive - and what advantage/functionality does it provide?

Take care when importing applications from 4.2 to 4.2.1 - you will need to re-apply the theme (see patch notes)
It will also pay to read notes regarding jQuery Mobile 1.2.0, which is part of this patch - there are some interesting widgets to play with.

Now I very much look forward to the new year, upgrading my current client from 4.1 so we can start a project building an application for a tablet.

Scott

Grassroots blog self reference map

I've published a page you'll find linked in the top menu to a list of articles I've posted on this blog.
grassroots-oracle.com/p/blog-reference-map.html

It's not all of them, and I can never promise it will be up-to-date - of course a google search also works
(or the little Search Me widget in the side-bar)

Let me google that for you

I will be using it as a reference page for myself - quick browser search to find content I know I have and need some syntax from.

I've had this drafted for a long time, but for some reason it wouldn't publish.  I had a moment of inspiration and just re-created the page.

Scott

Review: Oracle APEX Best Practices

November 2012 was the launch of a new APEX book from Packt publishing. Kudos to authors Learco, Iloon & Alex.

I'd like to offer the following review.

Oracle APEX Best Practices

Front page - title & byline to the point; three author contribution - nice to keep things balanced.  I'd probably buy it without looking at the contents. Sometimes it's a shame not to still discover these books for the first time on shelves.

Ubiquitous scenic photograph included - that I'm sure the authors had no influence on ;-)

Chapter 1, Prepare and Build

Not just your obligatory first chapter on setting up APEX - it covers plenty of best practices just in this first chapter. It doesn't go into a huge level of detail - it's not going to be the one-stop-shop for all information, but awareness is key. For this reason it is actually quite a large chapter!

Some highlights:
web server choices; multiple application framework; page design ideas based on table configuration

Chapter 2, Leveraging the database

Instrumentation is what every application should have - the APEX team includes some by default, and the database data dictionary views are also considered instrumentation. This chapter first covers Tyler Muth's logger package - although I would have liked to have read some use case scenarios to really seal the deal.

It then breaks down how to speed up your lookup tables with hash clusters and/or index organised tables - don't sound too scared. It does scratch the surface and explains it fairly well, but I recommend further research.

I think analytic functions are a little like learning to ride a bike. The authors do fairly well in explaining what they're all about, then provide some good examples - especially a cool one combining row_number() and pivot, and another on the essential string aggregate listagg(). In the caveat section, they show an example of analytic overkill - using row_number() to identify duplicates when "group by" will do. However, there is an advantage in using analytics if you need to identify the primary keys as well.

The authors then cover group by features such as grouping & rollup/cube.
Quality goes on with describing how to use scheduled jobs to encapsulate longer running jobs the user does not need to wait for; then looks at another favourite of mine - pipelined functions.
The chapter finishes with a look at ordsys for image processing & context indexing for document content searching just like google.

Best. chapter. ever.

To illustrate how useful & ubiquitous this chapter is, I already used most of these features in a recent APEX project - instrumentation, pipelining, analytics, ordsys, jobs.

Some highlights (I think I knew what these were going to be just by reading the summary):
instrumentation, analytical functions, grouping, pipelined functions, image proccesing, context indexing

Chapter 3, Printing

The most common question with the least definitive answer.

It quickly turned into a reference manual installation guide, but why not? - it explains the process well. There are good nuggets of information in this chapter, just depends on what you're looking for: BI Publisher, Apache FOP, Oracle Reports, Cocoon, JasperReports & two plug-ins, Reports to PDF & Embedded PDF.

My only complaint - there was only a brief note on the 32k limit for report columns. This means images you source dynamically from the database must be less than 22k + message fluff. This is what I experienced in 4.1, and it would have been nice to have dedicated paragraph on the matter - but there is plenty on the web!

I spoke too soon!! Writing this as I went along, I came to a brilliant summary of the 32K limit.

Some highlights:
incorporating images, Print API, suggested alternatives

Chapter 4, Security

Good intro, and great quote to start "Oracle Application Express is secure, but developers can make it insecure"

It covers functional settings, wide-ranging technical aspects - The first half covers administration aspects that you won't find in my APEX Security presentation - they didn't really have the sizzle a presentation needs ;-)

The second half covers the tools a developer needs to be aware of. It has great coverage for cross site scripting (XSS). For added humour, the authors referenced an XKCD comic to illustrate SQL injection. A few authorisation aspects not normally covered were in there. A couple of third party suggestions added to the end.

Some highlights:
VPD, APEX Listener, XSS

Chapter 5, Debugging & Troubleshooting

Starts with good detail on debugging in APEX. Later some browser extensions are mentioned - but I prefer the inspection tools native to Chrome.
It has only a small section on error handling, but really covers or at least mentions everything an APEX developer should be aware of.

Some highlights:
remote debugging, JavaScript console wrapper

Chapter 6, Deploy and Maintain

Another topic that probably needs more good published content, and the authors again do a good job.

Given the quality of the other chapters, I was looking forward to a deeper discussion regarding application IDs, particularly related to Interactice Reports.
They cover feedback well, but miss migrating it between environments.
They also mention my favourite APEX activity report though - Weighted Page Performance.
In regard to pro-active monitoring, I think error handling should have been be covered - with this we could log errors and actively notify the development team before the user picks up the phone to support.

Some highlights:
Version control, Feedback

Appendix, Database cloud service and APEX 4.2

The appendix, while probably vestigial in humans, here includes some interesting supplementary information on the Oracle Cloud Service, RESTful web services & a super quick mention of the data load feature in SQL Workshop.

Summary

Buy this book. Perfect information if you are intermediate to advanced, but what should be a required read for all APEX technologists. Kudos to the authors & publisher.

Past reviews

In the past I've reviewed other Oracle APEX books
Oracle Application Express Forms Converter
Oracle APEX 4.0 Cookbook
I've been technical review for some others, which I should also write up a light review for the blog.

Scott

APEX 5 Desires

No doubt about it, APEX 5 could be the opportunity to really think number 5 is alive! If you missed it, I commented on the statement of direction. It's a good list, and I still think the APEX product team have their fingers on the pulse regarding what the community needs & wants.

The APEX 4.2 wish list was certainly a success when it came to encouraging the community to speak out with what they wanted. There were many features suggested, all with varying degrees of complexity in regard to fitting in with the current data model & infrastructure. I have drafted a retrospective post to analyse some hits & misses.

Oracle now provides a dedicated application to listen your APEX feature requests. There is a similar one for SQL Developer.

APEX Feature Request application

I'm interested in something a little broader. I'm asking if the APEX community could have one major change applied to APEX 5, regardless of what the poor elves in Oracle development need to do under the hood to make it happen, what would it be? With the introduction of mobile applications with user interfaces, the bar has been raised.

What big change would you like to see in APEX 5?

Eight ideas to get your brain juices flowing:

1. A major restructure of Interactive Reports, offering more control with UI options, sharing reports, migration
2. Bigger, more dedicated leap into responsive web design for at least 3 devices, offering more features
3. A super easy to integrate, supported, WYSIWYG reporting engine/development tool
4. Remotely integrated migration engines operating over a database link
5. Built-in version control, perhaps with super-changed build option features
6. A simpler (than plug-ins) transparent porting facility to allow developers more easily integrate jQuery plug-ins, typically for menus & lists
7. More cross-application features, particularly the management of subscriptions
8. More integrated use of page aliasing, optionally applied "instead of" page numbers, and of course :APP_PAGE_ALIAS

As I did last time, I mentioned this on the OTN APEX forum - but I encourage you to use the Oracle's feature request application to manage these ideas, allow the community to help assess their viability & popularity, and help see some into fruition.

You'll get more awareness for these ideas using the application than relying on post 42 on 18 pages of posts, but it would be nice to see your big ideas repeated as comments/forum posts regardless to help seed activity.

The APEX development team are are listening.

Scott

Thursday Thought: Pacifism

Of course the many forms of internet media are rife with commentary on the tragedy of Sandy Hook, and this morning after reading a relatively objective article by someone I admire - Steve Novella, and I felt compelled to add an observation on the taboo, gun control.

I'll start with the reason I find Steve's opinions worthy, particularly those relating to his medical profession - the and the fact he seems to know facts about everything!
When he starts assessing gun control:

I just want to make the point that it is reasonable to address this issue, the discussion should be evidence-based, we currently need more and better evidence, meanwhile we can make some rational decisions based on the evidence we have.

I think it comes down to attitude. Another piece of the puzzle to my epiphany:

US attitude to gun control

I think many Australians (a culture I'm familiar with) think less guns all around, less chance of something horrible occurring. All firearms must be licensed for particular uses only and kept in a secure safe. Notably, with the exception of Northern Ireland, UK police force don't carry guns.

So I take this to a logical conclusion - each side always needs the bigger gun. You end up with a cold war, which lead to humanity realising it must stop or reach mutually assured destruction.
Conflict in the regions around the world continue, sustained often by revenge or vengeance, or other similar retaliation.

Call me a pacifist (a word I learnt watching the classic Hunt for the Red October), but can't we do this to the lowest denominator we can go? Trained properly, police could defend themselves from most crime incidents with a three or six foot wooden pole.

On a lighter note, if you are interested I encountered a small piece from PZ Myers inspired by Victoria Soto praising teachers around the world for their contribution to human interest.

Finally, both Steve & PZ are on distant wings ends on the spectrum of secularism. I dare not mention some of the horrid things I saw on the ends of other spectra, perhaps I just don't understand them.

What turned out to be a rant, over.

The problem with Interactive Reports

Migrating APEX applications between environments can be a real pain, especially when it comes to Interactive Reports.

Oracle gurus Joel, David & Martin have previously commented on this, links in references below - but I'd like to describe a situation we found that may help others.

Our problem

We have an APEX application that migrated periodically, and we didn't maintain a specific application ID during the migration process - a method that I find makes migration simpler.

At one point we had users begin to use the IR saved reports feature, so we had to now make sure these reports were preserved during migration.

When migrating an application to prod considering behaviours described in David's post, our save reports were still lost. This was ultimately due to the fact the application in development was copied (snapshots of old versions) since users started using saved reports - so our application ID and hence region IDs were different.

We considered the issues raised in Joel's post regarding the apex_application_install API and offsets for metadata IDs, but it didn't help in our case.

We got a little inspiration from Martin's post, but our situation seemed a little different.

Our solution

We imported the production application with the saved private reports to development.

Then I updated the core table to align the flow_id to match, similar to Martin's suggestion - and in hindsight I see were I crossed the wires.

update apex_040100.wwv_flow_worksheet_rpts
set flow_id   = 108 -- current version in dev
where flow_id = 200 -- from production
and page_id   = 301 -- IR page
and session_id is null
and report_alias is null;

Note: updating tables owned by the APEX user not supported by Oracle

When we migrated the application we were still losing the saved reports, so my SAGE colleague Kate Marshall found solution inspired by this OTN Forum post

Just like Martin said, we still had to match the interactive report region_id to the worksheet children.
Hopefully this screenshot paints the picture, where the IDs in green needed to match, as do the application ID.

Saved IR meta-data

We initially missed the region_id -> worksheet_id, and if I recall that left us with orphaned records after the migration.

Once updated, we then migrated the application from development, including the saved reports we copied from production.

So now when migrating subsequent updates to the application, we don't include private IR reports from development - and it doesn't overwrite/lose whatever is in production.

Wish list

I would like to see the APEX development team improve this process regarding saved reports, perhaps similar to the way questions are asked during import/export regarding supporting objects.

Our users would also like the ability to share saved reports to selected groups of people. I wonder if logistics would allow this for either authorisation schemes, APEX user groups, or some other mechanism.

If you have your own feature requests - there's an app for that

Good references

David Peake - Preserving saved interactive reports
Joel Kallman - Where did my saved interactive reports go?
Martin Giffy D'Souza - Saving saved interactive reports when updating application

Managing APEX using APIs

You can still manage much of your APEX workspace from the command line - whether that be SQL*Plus, SQL Developer, or some other tool of choice.

They key thing is, not everything needs to be point and click - that feels a little important.

I like to frequently blow away & re-create a bunch of users, workspaces & applications in one foul swoop. I'd like to share a little of what I've done since it was a topic of discussion recently at the Perth APEXposed.

To use the APIs below, your schema will require the APEX_ADMINISTRATOR_ROLE, described by Martin here.

To create & define all the training accounts, I use the following procedure in a package I compile in my parsing schema.
Privileges are a little different when encapsulated in a package, so direct EXECUTE access on APEX_INSTANCE_ADMIN would be required to avoid the infamous PLS-00201. Permissions such as CREATE USER would also be required.

PROCEDURE define_users
  (p_nbr_users  NUMBER)
IS
  lc_user  VARCHAR2(10);
BEGIN
  << define_users >>
  FOR i IN 1..p_nbr_users LOOP

    lc_user := 'train'||i;

    << drop_user >>
    DECLARE
      e_no_user exception;
      pragma exception_init(e_no_user, -1918);
    BEGIN
      EXECUTE IMMEDIATE 'DROP USER '||lc_user||' CASCADE';
    EXCEPTION WHEN e_no_user THEN
      NULL;
    END drop_user;
    EXECUTE IMMEDIATE 'CREATE USER '||lc_user||' IDENTIFIED BY '||lc_user;

    EXECUTE IMMEDIATE 'ALTER USER '||lc_user||' QUOTA 100M ON users';
    EXECUTE IMMEDIATE 'GRANT CONNECT TO '||lc_user ;

    EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TYPE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||lc_user;

  END LOOP define_users;
END define_users;

All my other procedures just encapsulate the following steps and example API calls. I highly recommend specifying formal parameter names as the signature to these APIs often between APEX versions.

My procedure to define my workspace makes calls

APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(lc_user,'N','N');

Create the workspace

APEX_INSTANCE_ADMIN.ADD_WORKSPACE
  (p_workspace_id   => ln_workspace_id
  ,p_workspace      => lc_user
  ,p_primary_schema => lc_user
  ,p_additional_schemas => '')

A new call required for 4.1.1, found by Dimitri, announced by Patrick

apex_instance_admin.enable_workspace;

Set the context regarding which workspace

apex_util.set_security_group_id(p_security_group_id => ln_workspace_id);

Create a number of users, with various developer privileges.

APEX_UTIL.CREATE_USER(
        p_user_name                     => 'ADMIN'
       ,p_web_password                  => /* my attempt to keep generic accounts passwords unmentionable */
       ,p_email_address                 => 'username@sample.com.au'
       ,p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'
       ,p_default_schema                => lc_user
       ,p_allow_access_to_schemas       => lc_user
       ,p_change_password_on_first_use  => 'N');

Then before installing an application, you need to set the workpace and schema

APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID(p_workspace_id);
APEX_APPLICATION_INSTALL.SET_SCHEMA(p_schema);

Then you can either nominate the application id or have one generated

APEX_APPLICATION_INSTALL.SET_APPLICATION_ID(p_app_id);
-- or
APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;

You may also choose to set such properties as application name and alias

APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME (p_app_name);
APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(p_app_alias);

Then you generate an offset for all the IDs in the export script

APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

If you're creating multiple applications for one workspace, all you need each time is

APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

To execute these APIs among calls to import previously exported applications in SQL*Plus (wasn't that a mouthful), I used

set define '^'
accept my_app DEFAULT 'C:\my_everything\f_123_example.sql'

-- set app context
exec apex_application_install.generate_application_id;
exec apex_application_install.generate_offset;
-- install app from export
@^my_app
-- rinse and repeat

And that's essentially how I do it, just bundled up to suit my needs. It'll be pretty easy for you to grab what you need and sort out your own scripts.

Scott

Webinar reminder - APEX Mobile

Just a quick reminder about a coming webinar on APEX mobile on 18th/19th December.

If the time zone suits you, it would be wonderful if you could join in

• US west coast - noon
• US east coast - arvo
• Europe/Africa - PM
• Australia east cost - AM
• Australia west coast - too early, and you probably saw it at the Perth Conference ;-)
• any other suitable regions I neglected to mention, my apologies

Register via ODTUG.

Scott

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

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.

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

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

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

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

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

Perth Conference Feedback

Attention all delegates & conference volunteers who saw sessions at the Perth 2012 AUSOUG Conference:

I would strongly encourage you to head here to provide feedback on any sessions you attended in the last two days
http://apex.oracle.com/pls/apex/f?p=SW2012

Now that it's likely you're back on your desktops - it's even quicker and easier to find your sessions and add feedback. The desktop version of the app has a "Sessions" button, which takes you to the session list

Click "Feedback" on the relevant sessions

Then you can provide star ratings and some comments if you wish.

I also created a special last session so you can provide any general feedback regarding the conference.

Thank you!

Scott Wesley
Program Chair
Perth 2012 AUSOUG Conference

OTDUG Australia / NZ APEX training

If you didn't get enough APEX at the Perth conference, then thanks to ODTUG and AUSOUG, you can supplement it further by welcoming ACE Director Martin D'Souza and Oracle Application Express Product Manager David Peake to Australia and New Zealand.

They will dive deep into the APEX world, including a hands-on lab.

• Auckland - November 5-6, 2012
• Melbourne - November 8-9, 2012

Further details can be found here: http://www.odtugapextraining.com/AU.html

I'll be attending the Melbourne event - I might see you there!

Scott

Easter egg added to mobile app

I've added an easter egg to the mobile application that's accompanying my presentation at the Perth #AUSOUG conference.

If you can find it you will see the answer to an eyebrow-raising trivia question I'll ask at the end of my session. I'll try find something to give away.

At the SAGE booth We'll also have a game you can play with a mobile web browser - more details to come!

Upgrading to APEX 4.2 in three steps

I found upgrading my laptop's 11gXE instance from APEX 4.1 to APEX 4.2 ridiculously easy:

1. Stop APEX Listener (5 seconds)
2. Run install script (19 minutes)
   @apexins.sql apex apex_files TEMP /i/
3. Restart APEX Listener with new images address (15 seconds)
   java -jar apex.war standalone --apex-images \apex4.2\images

Job done.

Further details & other optional steps found in APEX 4.2 Installation Guide

When I have time I must find out if we can still tweak the APEX login page.

Scott

Demo Mobile APEX application for AUSOUG

The 2012 Perth AUSOUG Conference is less than a week away and it's time for an announcement.

This year I'm presenting "A Foray into APEX mobile", which will be a look into the 4.2 release. Accompanying this will be a demonstration application (apex.oracle.com/pls/apex/f?p=SW2012) that allows you to explore the conference sessions from your desktop or mobile device.

My presentation will investigate how this sausage was made; then talk about some of the considerations you'll need to take when exploring the world of mobile development. It will also be powerpoint free :-)

The application isn't super beautiful, I only use the stock theme - and I won't guarantee the application is defect free - in fact you may even encounter some APEX 4.2 bugs. I will however do my best to ensure the session times are up-to-date.

Please feel free to explore the application before & during the conference - in fact, I encourage all of you attending the conference to do three things

1. explore the application to help decide which sessions to attend
2. use the links to indicate which sessions you are interested in (or not interested)
3. after you attend each session, use the links to provide feedback on what you thought

The conference committee will be interested in the results. We will be promoting the application during the conference & reminding delegates after each session to provide feedback. We plan to formalise & extend the application for next year and integrate it with other services. 

To those not fortunate enough to be in Perth for the conference this year, I welcome your ideas & hope to see you in the future.

Scott Wesley

Program Chair

2012 Perth AUSOUG Conference

Perth AUSOUG Conference - Day 3 options

This year's Perth AUSOUG conference doesn't end on day 2 - you are three options to extend your learning into day 3:

1. Oracle database technologists simply musn't miss the Real World Performance day with Tom Kyte, Graham Wood & Andrew Holdsworth. I attended this last year in Sydney and talk about value for money!
2. Oracle E-Business Reporting users might be interested in attending a breakfast thanks to LogicalTech.
3. Primavera users may be interested in an all day SIG to help them with a wide range of topics and find other Primavera users within Perth.

And if you haven't even registered for the conference yet, you have only a week to do so - this year is going to be a ripper! It's in the Perth CBD and we have a great line up, lots of exhibitors & I've heard about a few dinners being organised already ;-)

Scott

SAGE Green Pigs

SAGE have made an addition to the social media wagon in an attempt to promote our green pig meme:
sagecomputing.tumblr.com

Do you have a SAGE green pig?

If you do, we'd love to see some photos from anywhere in the world with the little piggy - they don't have to be as nerdy as this one in Las Vegas though...

Send them through to us, or tag it on Twitter with #sagegreenpig and you might just see it on the Tumblr!

If you don't, come by our booth at the conference and if you're lucky we might have one! Oh, and they double as a piggy bank!

Scott & the SAGE Computing Services team.

Are you still on APEX 3.x?

Oracle Application Express 4.2 is now available to the public, and I thought I'd throw in my 5 cents.

I know a few sites out there still on 3.x, so I thought I'd list a few features that have come out in the past 3 major releases that they're missing out on:

• Improved Error handling
• Enhanced Interactive Reports
• DML using ROWID instead of primary key
• End user CSV upload
• Websheets
• Tabular form improvements
• Plug-ins
• Dynamic Actions
• Team development module
• Accessibility improvements
• Application builder for Mobile
• Responsive Themes
• HTML5 Charts
• HTML5 Item types
• Mobile calendars
• Packaged applications
• RESTful Web Services

For me, I think the biggest two are Plug-ins and Dynamic Actions - they're hard to split.

Mobile development is not so much a feature, but a whole new ball game!

As for APEX 4.2, I notice the public release is 4.2.0.00.27, while apex.oracle.com is still 4.2.0.00.22 - so hopefully some of the small bugs I've been encountering have been rectified. I have had to refrain from installing 4.2 on my laptop as I need 4.1 for a side project a little while longer.

Design time bugs can pretty much be ignored - stuff like using previous through the wizards doesn't always behave as expected. Some template tweaks would also be handy, but David Peake mentioned in a recent webinar that this was already being looked at.
Runtime bugs are a little more annoying - for the first time I've been finding intermittent issues in Chrome when IE works fine - usually it's the other way around!
For example, I received a checksum issue on an APEX generated link in Chrome, but not IE.
I'm confident these will be quickly rectified, if not already with the fast uptake and use of this new release.

My presentation at the Perth AUSOUG conference will be talking about an APEX 4.2 application with a desktop & mobile interface - more details to come soon!

Create Mobile Accordion with APEX 4.2

It's been a while since I posted some developer content, and I've been playing with APEX 4.2 a fair bit recently - preparing for my conference presentation.

I thought I'd share how I implemented a mobile (smartphone/gadget) accordion sourced from SQL.

I say the because you can do it out of the box using region templates, but only compressing multiple regions together, not rows from a query

APEX 4.2 Mobile Region templates

There seems to be many ways to skin the cat in APEX 4.2, and this is one way to do it - I'm happy to learn easier ways - I'm sure I will do that for a few things along the mobile journey!

Samsung 3 Screenshot

Consider the following SQL as my list, where column am_info_vw.header shows the accordion heading, and am_info_vw.my_html is the content.

select 
  null lvl
 ,header label
 ,my_html target
 ,decode(rn,1,'YES') is_current
 ,header image
 ,my_html image_attrib
 ,null img_alt
FROM am_info_vw
order by rn

The List Template image shows me commandeering the image attributes, but the same result can be made with the #TEXT# & #LINK# substitution strings.

List Template

List Template Current:

<div data-collapsed="false" data-role="collapsible" data-collapsed="false">
<h3>
<code>#TEXT#</code></h3>
<code>#LINK#</code></div>

List template non-current (just excludes the data-collapsed):

<div data-collapsed="false" data-role="collapsible">
<h3>
<code>#TEXT#</code></h3>
<code>#LINK#</code></div>

Before List Entry:

<div data-role="collapsible-set">

After List Entry closes the </div>

Then I just had to define the region on my mobile page utilising that template for the list with a plain region template, job done.

I'm still learning various options in the 4.2 environment, but this may seem like an easier method to create dynamic content lists instead of using the Advanced Attributes option in the List View built-in plug-in.

To help me out with the required syntax, I used the jQuery Mobile Docs.

2012 Conference Program Announced

Today the program for the 2012 Perth AUSOUG Conference was announced.

First of all, I know it will be a Monday morning but there is a reason not to miss the opening address - we have invited ultra marathon runner Bernadette Benson to talk to us about the discipline it takes to stay focused.

Now  it's hard to limit to the just the highlights, but here goes:

Oracle

It's a conference talking about Oracle after all, and here are some of the strengths they're bringing to the table

• Tom Kyte - it doesn't matter what they are, and there's two of them!!! 
• Tom Kyte - did I mention he's doing two presentations?
• Chris Muir - with some JDeveloper 12c announcements and ADF new features
• Melanie Caffrey - talking DBA stuff on auditing with flashback and testing with edition based redefinition
• John Hansen - Fusion strategic roadmap
• Graham Wood - Active Session History (ASH)
• Keith Laker - settles the debate on Big Data
• Cliff Godwin - EBS strategy & GM update
• Chris Muir - Developing for smart devices - web & native 

Mobile

It seems the buzz-word this year is mobile development, and this offering won't drown you with it - but instead offers a great cross-examination of what's out there. These are on top of Chris Muir's smart-devices talk.

• Penny Cookson - with some help from Chris Noonan will impress us with JDeveloper ADF dashboards on iPads - yep, it'll be interactive 
• Scott Wesley (me) - another interactive foray into APEX mobile (powerpoint free)
• Kevin Payne - case study involving mobile development
• Mark Lancaster - bootstrapping APEX for all devices

Development

Are you a developer? I'm positive there is something for you in every time-slot - starting with these selections

• Matthew Carrigy - case study on government project converting Forms to JDeveloper ADF
• Jeff Kemp - guiding us through Alexandria, and another on some Oracle DB gotchas
• David Wilkes - beginner's guide to SQL, PL/SQL
• Connor McDonald - SQL Tuning 101
• Connor McDonald - Analytics
• Gabriel Ilarda - SQL Data Modeler

DBA

The community is strong in Perth, so we've got to cover many bases.

• Tim Middleton - Web Logic Server 12
• Yury Velikanov - Direct NFS
• Damon Grummet - Case study on ODA
• Barbara Rabinowicz - Oracle VM for beginners
• Marc Fielding - Large scale consolidation
• Arjen Visser - Replication concepts
• Gavin Soorma - 11g Optimizer plan stability

EBS

E-Business Suite and other Fusion applications are another must.

• Dr Patricia Dues - BI case study in the public sector; and EBS R12 upgrade
• Howard Ong - looking at the current state of Business Intelligence
• Tony Scholefield - R12 upgrade case study
• Valery Hickey - Cloning EBS
• Andrew Howard - Oracle Spatial case study
• Jags Jawalekar - Oracle iProcurement

And then some...

In addition to the standard schedule, we have some other events over the 3 day period from 29th-31st October

• Full day (day 3) - Real World Performance with Tom Kyte, Andrew Holdsworth & Graham Wood. I went this last year in Sydney and it's a must for DBAs and developers. Not only that, it's great value.
• Half day (day 2) - Sysadmin training workshop on Linux / Solaris 
• Primavera SIG (day 3)
• Exhibitors - we have regulars, plus Amazon Web Services will have a booth to answer your cloud questions
• Welcome reception (day 1) - Let of some steam and enjoy some drinks & nibbles with speakers, delegates, exhibitors & user group committee members.
• WIN - a trip to attend Collaborate 2013

There's even more, and no doubt I've forgotten some sessions that will be a highlight for you.

We'll close off day 2 with some thanks from Susan Rooney, Cancer Council CEO & the prize draws.

Register now! Early bird special finishes next week.

Scott Wesley

Program Chair

2012 Perth AUSOUG Conference