Small SQL puzzle - discrete top 3

Being the APEX geek I am I've created a page that lists all the entrants so far to the Packt Publishing Phonegap ebook giveway

Check it out here
http://apex.oracle.com/pls/apex/f?p=73000:22

I'll post more about how I've extrapolated records from derived column values, but I am looking for suggestions to improve the 'prize?' column

This is the current calculation - basically highlighting first three records in output.
case when row_number() over (order by dbms_random.value()) <= 3 then 'maybe?' end prize
But what if two of those records share the same name?

Here is some test data, where socrates, plato & yeats need to win the prize

with data as
 (select 'socrates'  name, 1 entry, 1 ord from dual union all
  select 'plato'     name, 1 entry, 2 ord from dual union all
  select 'socrates'  name, 2 entry, 3 ord from dual union all
  select 'yeats'     name, 1 entry, 4 ord from dual union all
  select 'aristotle' name, 1 entry, 5 ord from dual)
select case when row_number () over (order by ord)  <= 3 then 'maybe?' end prize
  ,name
from data
order by ord

Go nuts

Win a free ebook of PhoneGap 3 by Packt

PhoneGap 3 Beginner's Guide

Packt publishing have generously given me the opportunity to give away 3 copies of their e-book PhoneGap 3 Beginner's Guide, by Giorgio Natili.

Details on how to enter are further below.

Update (2013-12-19): Created APEX page displaying current entrants and puzzle regarding selection process.

You don't think you need to be an APEX developer to appreciate this book, but I will be reviewing this book in greater detail over the new year as I plan to use it to help me deploy Oracle APEX applications as a hybrid mobile application.

Looking through the contents, I see plenty opportunities to improve your APEX applications by using PhoneGap - a free, open licensed distribution of Apache Cordova.

Chapter 1: Getting Started with PhoneGap
Exactly that - understand what PhoneGap is and what you need depending on your device.

Chapter 2: Building and Debugging on Multiple Platforms
A little deeper preparation for how we might debug issues, even desktop debugging since APEX developers are building hybrid applications.

Chapter 3: Getting Started with Mobile Applications
A good look at some web development basics everyone should be familiar with - JavaScript, CSS.

Chapter 4: Architecting Your Mobile App
Might be a bit heavy for us APEX developers, but we may end up being able to hook in our APEX landing page using this information.

Chapter 5: Improving the User Interface and Device Interaction
I think some lessons in here will be deeper than we might get out of an Oracle-centric book.

Chapter 6: Using Device Storage and the Contacts API
Even though we're database developers, I think we're going to be able to provide some clever ideas with HTML5 storage. The Contacts API looks like a great lead in to communicating with the device.

Chapter 7: Accessing Device Sensors
The clear advantage your hybrid APEX applications will have over web deployments - access to hardware sensors.

Chapter 8: Using Location Data with PhoneGap
I think this is more functional than current HTML5 capabilities.

Chapter 9: Manipulating Files
As interesting as this looks - don't jump the gun, I think the chapter you're really interested in is next up.

Chapter 10: Capturing and Manipulating Device Media
Another marquee features for APEX applications - eg: easily uploading photos taken with the device.

Chapter 11: Working with PhoneGap Plugins
Just like APEX has some awesome plugins, so does Phonegap.
They'll probably feed your APEX application ideas even further.

Appendix A: Localizing Your App
We may get away with not needing this, but handy nonetheless. Besides, I've had limited exposure to localisation requirements.

Appendix B: Publishing Your App
No point having an app you can't publish, right? Luckily with our APEX hybrid, this may be a process that can be avoided after initial release.
All versioning can be done within your APEX environment.

Appendix C: Pop Quiz Answers
Pop quiz, hot shot - have you been comprehending what you're reading?

For your chance to win

1) Please post a comment below and mention which chapter you think would help you the most.

2) Double your chances by tweeting about this giveaway.

3) If you include a link to your tweet in the comments, I'll give you three entries.

I'll feed all entries into an Oracle table and run some SQL to randomly determine the three winners
- as oppopsed to using Random.org - come on, I'm an Oracle geek!
I'll post the method I will use to deduce the winners sometime soon, possibly over the new year.

Note: the closing date for this giveaway is 31st Dec 2013

Don't forget to include some form of contact information so I can contact you if you've won the prize.

Good luck!

3 product releases from Oracle Dec 2013

So far this December Oracle has released version upgrades for three of their products:

1. Joel Kallman announced Oracle APEX 4.2.4 is now available. It appears to be mainly fixes & enhancements to Package Applications, so maybe it's time to give them all a go & see if they may be useful to you. [bugs fixed]
   
   I'd also like to revisit a few and see if templates have been made out of some of their cooler dynamic PL/SQL regions. I've reverse engineered their hover functionality using a template & a list, but it would be nice to have these available in themes.
   
   Bring on APEX5
   
2. Jeff Smith gave use 10 reasons to upgrade to SQL Developer 4.0. I haven't used it much so far, but it seems many of the hiccups from the early adopter downloads have been addressed - plus a gazillion new features.
   
3. At the start of the month Jeff also announced Oracle SQL Developer Data Modeler 4.0 is available.

Do good things come in threes? No, they do not, but in this case we have a correlated co-incidence that we should certainly take advantage of ;-)

Count number of rows returned in APEX report

Often I want to configure an APEX page based on how many rows have been returned in a (typically classic) report region. There are a number of methods for determining this row count, and some have advantages over others.

1) Computation while rendering the page.

This often isn't best for performance & maintenance since you effectively need to do a count(*) over the SQL you already have. 

Analytics can deliver this value within the same SQL. Add the following column to your query

,count(*) over (order by null) cnt

And remove the column from sight of the user with the following CSS.

#emp thead th#CNT
,#emp tbody td[headers=CNT]
  {display:none;}

You can't declaratively set it hidden otherwise you cannot reference it with jQuery.

You can then use this jQuery to return the count.

$('#p1_notes tbody td[headers=CNT]:first').text()

It returns the text value of the CNT column from the first row.

2) Add substitution strings to Region Footer

If you're using pagination on a classic report you can include substitution strings such as #ROWS_FETCHED#.

You don't necessarily want to see this displayed on the page, so you could use

<div id="emp_count" style="display:none;">#TOTAL_ROWS#</div>

Then interrogate this value using

$('#emp_count').text()

Trouble is these don't get updated as you paginate through your results, and are usually only handy when operating with one page of results (or no results). Then the "No data found" and "More data found" message attributes can come into play.

3) jQuery function call

Length() returns the number of elements matching your selector. Note size() is deprecated as of jQuery 1.8

This method will work regardless of pagination usage. All you need is to identify something in your report you can count.

In this sample page there are a number of options. There is one button per record, so you could count the number of .uButtonGreen

A safer and faster method would be to nominate a column within my table. So in this case I defined a static region ID for my report region (emp)
, and nominated to count the number of employee name cells.

$s('P6_ROW_COUNT', $('table#report_emp td[headers="ENAME"]').length);

To help decide on my selector I used the browser's inspect element tool

Firefox inspect element

Press the "Show count" button on my sample page to see notification of row count. Change pagination to see varying results.

I'm really starting to love jQuery.

Build option use case - replace condition never

I first blogged about build options three years ago while still using APEX 3.x - I can tell by the green ;-)

I still use them regularly, but not often for versioning as they're allocated at a component level and not for attribute modifications - perhaps in later releases we'll see an extension of their use.

What I do see all the time is components with a condition type of "never". This is probably because it's the quickest way to turn off features that are broken or those you're not ready to completely remove for whatever reason.

The issue with this is they get forgotten. Future maintainers see these components in limbo and wonder if they should be removed or enabled, particularly if they lack comments regarding their status.

I have encountered valid circumstances when a report region is purposely set to "never" - where a link is available to export the relevant data as CSV.

Instead I would suggest creating a build option that looks like this:

Build option - to be removed

The 'Status' of 'Exclude' means I don't see the components at runtime - just like a condition of "never".
'Default on Export' being the same as current status means it will stay excluded as the application moves between environments.

One major advantage I find is the accompanying utilisation report. At some point during the project I went through everything I've allocated 'to be removed' and decided if it truly was time to sent these components to /dev/null.

Build option utilisation report

Do you have any interesting uses for build options?

Build option use case - experiments

I find build options as nifty tools to not only categorise components for the very purpose build options are documented for, but to help inform developers & testers of applications.

Here I've added a region on the global page to inform users of the status of the page - based on it's allocation to a build option.

Build option in action

I created the following classic report in my global page using region template DIV region with ID and report template One Column Unordered List, with some styling on the columns.
This shows detail of the build option allocated to the page (via page attributes).

select b.build_option_name, b.component_comment 
from apex_application_build_options b

    ,apex_application_pages  p
where b.application_id = :APP_ID
and   p.page_id        = :APP_PAGE_ID
and   b.build_option_name = p.build_option
and   b.application_id    = p.application_id

I applied the following condition will ensure the global page region is only displayed when the page has been assigned a build option.

select null
from apex_application_pages  p
where p.application_id = :APP_ID
and p.page_id = :APP_PAGE_ID
and p.build_option is not null

The region is also allocated to a relevant build option so it doesn't display outside development.

My build option in this case has a status of Include, but Exclude on export.

Build option setings

I'd like to see build option features expanded in future release, perhaps including extended release management options.

Use jQuery to dynamically modify Region title

In a recent post I showed how to determine the APEX region title on load of the page. This post will show how to change the region title dynamically - without submitting the page, and still in three steps.

1) Add a static ID to the region you want to change, this helps the jQuery selector identify the component to change. I set mine to edit_emp

2) Add JavaScript variable to your function and global variable declaration section in your page attributes.
var orig = "x";

I'm not completely happy with this part of the solution, but I find greater confidence than null comparisons in JavaScript.

3) Create a dynamic action on the event you wish to invoke change on the region heading. In my case I'll set it on change of the department. It needs to execute some JavaScript.

Dynamic Action definition

The JavaScript does two things. First, it records the original value of the region title - if this isn't done then the title will keep extending after each change. We use the region static ID to identify the region, then refer to H1 to identify the header text.

As I suggested in point 2, this comparison of orig is a little clunky, I welcome suggestions to make it more elegant (besides just defining your own text from scratch).

if (orig == "x")
   orig = $('#edit_emp h1').text();

$('#edit_emp h1').text(orig + ' (' + $('#P6_DEPTNO option:selected').text() + ')');

The second part extents the original region text with whatever you like - in my case I take the label of the value chosen in the select list.

This is demonstrated in my sample application.

Scott

Spice up your APEX application with third party plug-ins

At this year's Australian Oracle User Group Insync13 conference series I conducted a half-day workshop in Melbourne, Brisbane and Perth called "Spice up your APEX application with third party plug-ins"

I selected some commonly used plug-ins that I thought would be interesting and showed attendees how to download, configure and use them. I hoped this would build further awareness of the usefulness and expandability of APEX 4.x

I spent the first 10-15 minutes introducing what plug-ins are all about, and you can find link to the slides here.

I have a tutorial application that demonstrates the eleven GPL/MIT licenced (available for commerical use) plug-ins I chose do illustrate:
http://apex.oracle.com/pls/apex/f?p=PLUGIN

Future readers may find this useful as I've also listed instructions on how to use each of them.

Plug-in instructions

You can now ignore the 'claim workspace' section. On a side note, when running workshops at conferences I certainly learned a few ways to make things run more smoothly!

You can also download setup scripts to run the application in your own environment from here
sagecomputing.com.au/workshops

Many thanks to the author's of the APEX plug-ins that make our lives easier!
Find more at apex-plugin.com

Scott

Perth Insync13 Conference Review

From my employer's perspective we had a very successful year at the Perth Insync13 conference. Sage had 7 papers from 4 of our team, and as usual our exhibition booth was very popular.

We were pushing the mobile theme this year to help stand out to enter our giveaway you needed to use an APEX application, instead of the usual business card in a bowl.  It seemed we were going against the grain of some old habits, but this is 21st century IT so we encouraged all visitors to give it a go.

SAGE exhibition booth

Day 1

I was a busy boy with a half-day workshop and two presentations so I didn't get to see many other papers, but I had already seen quite a few at the Melbourne and Brisbane legs.

So I ended up being rather selective, and I was pretty happy with the ones I attended. First off the rank was an international guest Bjoern Rost Tackling Time Troubles. He was entertaining, engaging and a nice fellow - so we felt me must teach him some Australian vernacular.

I had already seen Penny's 12c optimizer [pdf] presentation but listened to the first few lego star wars jokes and she was well received - so I snuck in to see most of Chris Muir talk about UX design patterns.

I then had to prepare for my half-day workshop on consuming APEX plug-ins. My laptop started to get clammy and freeze which gave me a little scare, but I ended up kicking off in time. Attendees would claim a workspace from my laptop, but in hindsight I now know a much better way to arrange this - next time...

Interaction was good, and my main mission was accomplished - more awareness of the capability and availability of APEX plug-ins. I'll write a separate post on this with all the relevant links.

I finished the day with Phil Robinson talking about testing - I would have liked to see more developers at this very informative session.

Unfortunately I had to leave early on the best session of all - networking drinks, so I didn't attend any dinners. I did bump into Tariq Farooq, great to put a face to an online entity!

Day 2

Andrew Rosson from Lansen started the day with a comparison of GlassFish & WebLogic, which also included some very interesting news about GlassFish.

I took a break before the feature from Connor McDonald on CloneDB. It delivered much more than I was expecting, including 7 very impressive demonstrations.

Once again I did some presentation hopping, listening to Chris' perspective on the future of Oracle Forms before listening to Phil Robinson talk about mind maps and rich pictures - all conferences should have more presentations like this.

It was my turn again with my talk regarding an APEX tablet application. I had the pleasure of including an introduction on the issues at hand from our client Craig Purser from Ventura Home Group.

My prezi is available here: APEX in your hands - lessons learned from an APEX table project
Again, I'll write a separate post later summarising things we've learnt.

Jeff Kemp followed up with more APEX content using row level security, a paper inspired by information received from Connor earlier in the year.

Penny & I finished the day with a light hearted session on Being Productive in IT [pdf]. This was originally presented in Brisbane by Tim Daniell from Fresh Computer Systems, and he kindly gave us permission to offer our perspective on left-right brain issues. Penny was the left brain and I acted as the right - it was entertaining and well received.

While Sage wound down with a beer at the pub next door, I came up with an idea for a similar presentation next year - focussing on one of the issues we covered, stay tuned ;-)

It was great to see everyone I managed to catch up with, and hope it's not another year until I see you all again.

Thank you to all presenters, organisers, volunteers and delegates for making this another enjoyable conference.

Scott
2013 Perth Program Chair

Why I think GlassFish isn't dead

At this week's Perth Insync conference I attended a great session comparing Oracle Glassfish Server (OGS) with Web Logic Server (WLS), only to find out that Oracle will discontinue future commercial support of the OGS product as of 4th November 2013.

This instigated a great flurry of discussion as one of our current clients is on the way to deploying a server dedicated to APEX using one of the Glassfish products.

Background

Oracle recommends WLS as the web tier of choice for JDeveloper ADF, and the only choice for legacy Forms applications currently running on Apache that want to keep up with upgrading OS platforms.
OGS is typically a cheaper, lightweight alternative that can also support ADF - but is a more obvious choice for APEX deployments, in conjunction with the APEX Listener.

My head is rarely in the application server space, and I tend to not get too caught up with licencing discussions - no doubt others I refer to in this post know more about the topic than I, but I'm more than happy to provide an opinion from my perspective.

I try to catch up on my RSS feeds before the conference to keep an eye out for major announcements, this one obviously got through.
My first impression is that GlassFish has returned to what it was prior to the Oracle-Sun acquisition, so I found this made reading people's passionate opinions on the topic even more interesting.

The announcements

After finding this news story from ZDNet, this first post from familiar sources I encountered on the topic was from Kris Rice regarding APEX Listener support for application servers.

He confirmed the APEX Listener was still supported for WLS and Glassfish (3+), and in future support will be available for Apache Tomcat - thanks to public demand.

He also linked to the Aquarium's official annoucement regarding the Java EE and GlassFish Server Roadmap Update.

The discussion

I felt I needed more input so I started poking around to find a very vivid discussion on the topic, from Markus Eisele suggesting OGS is essentially dead and "thanks for all the fish".

I continued to read articles and they did seem to lean to one direction (anti-Oracle). Some looked back at Oracle's history with with open source projects, considering MySQL's fork into MariaDB.
The general concern is that since MySQL wouldn't be a revenue earner for Oracle, it would go neglected - stating that Oracle is even removing features.

To me this sounded like a slanted discussion without asking the other side. I'm not familiar with how MySQL works, but I'm sure Oracle limits the amount of hard parsing of SQL statements similar to this feature, just in a different component of the architecture.

I remember listening to a podcast describing what's new in MySQL 5.6 thinking that Oracle is vastly improving the MySQL architecture with solutions to match how the Oracle DB solves typical database issues. I didn't get the impression of neglect after reading these new features.

A number of people seem cynical of Larry Ellison's commitment to open source projects, but the information out there doesn't seem to reflect that.

Oracle's opinion

Then I started finding other contributions from Oracle's direction that started to provide some balance - and facts over fancy. Bruno Borges offered 6 Facts about GlassFish Announcement.

Someone else (I can't find the reference) noted the number of free/open source projects Oracle still delivers  - some of them rather high profile, so I think GlassFish shares some great company. To note just a few:

• VirtualBox
• SQL Developer
• Oracle Express Edition
• Hudson CI
• Various Linux projects

Many of them supporting arms for Oracle's revenue engines - as per what the Aquarium originally stated:

The primary role of GlassFish Server Open Source Edition has been, and continues to be, driving adoption of the latest release of the Java Platform, Enterprise Edition

Oracle can't resource every project, so others such as OpenOffice have been handed over to the Apache Software Foundation.

Conclusion

At the end of the day we can always refer to Oracle support document. Premier support ends for OGS 3.1.x in March 2016, extended support until 2019 - but sustaining support is indefinite.

GlassFish continues to feed & support important components of Oracle's infrastructure and I would have confidence it will continue to thrive in the forseeable future.

The following points help me come to this conclusion

1. APEX Listener continues to support OGS, in addition to WLS (and maybe Tomcat) - Oracle is pandering to customers needs, they don't need to 
2. OGS and WLS share code and are closely coupled with the Oracle JDK - oracle is committed to Java EE
3. OGS fills a niche and already shares a decent portion of the market - it has critical mass

I welcome further discussion on this and welcome any corrections to my statements.

Happy Friday - something from Isaac Asimov

"which I repeat here for everyone that find's this page"

Well Morgan, I found the page.

The backstory - I've been tidying my bookmarks, and I read through ACE Director Daniel Morgan's thoughts for 2013.
(For those Oracle technologists who haven't seen his Library, bookmark it now.)

Anyway, he referenced a book dedication from Isaac Asimov which I thought was a little terrific.

To Mankind

And the hope that the war against folly may someday be won, after all

After reading about the book, it's now on my list.

Happy Friday, Isaac

APEX 101 - Set region title on page load

I saw a forum post recently asking how to dynamically change the region title.

This is a little ambiguous - is this dynamic to the value set in APEX meta-data, or dynamic as a result of user events on the page?

Here is the instructions for the former:

1) Create new hidden item called P6_REGION_TITLE. I placed it on the same region as my form items.

2) Create a computation for this item Before Header, with perhaps a SQL expression like

Region title computation

This extends the concept of Pn_MODE demonstrated in my sample application, discussed in this tutorial. You could also interrogate Pn_ID to determine if reading an existing record or creating a new one.

CASE :P6_MODE 
WHEN 'R' THEN 'View employee'
WHEN 'E' THEN 'Edit employee'
END

3) Modify the region title to
&P6_REGION_TITLE.

When the page renders, the region title will be set relevant to the edit mode.

Here is a post how to modify the title after the page has rendered - often useful for modal dialogs or master/detail pages.

Scott

Customise Totals row in APEX classic report

This post covers one way of customising the declarative sum totals on classic APEX reports.

Classic Report sum totals

The default output isn't that flash looking, and no doubt many would prefer at least a different label. Unfortunately I haven't found a method within APEX, so I applied some jQuery.

If you're not aware, these are turned on using the checkbox in the 'Sum' column on the relevant region's 'Report Attributes'.

Classic Report Attributes

So to customise the label, first we should add a Static ID on the region attributes. This will be used to help identify the report.

Static Region ID

Then we can use Chrome's 'Inspect Element' option upon right-clicking the label. Using this we can identify more of the jQuery selector we'll need to identify this cell.

Using the browser to identify DOM components

Now I'd still consider myself beginner/intermediate when it comes to jQuery. I know how to adopt code well, but I'm not yet familiar with the full suite. So if any of you have suggestions on how to improve this bit of code, especially from a performance perspective - I'd be happy to hear them. I've resigned myself to never being an elegant OO programmer, but fire away if I've also made this ugly.

jQuery code running as part of page render

$('#emp td.data').each(function(){
     if ($(this).text() == 'report total:') {
        $(this).text('Total Salary');
        $(this).css({'font-weight':'bold', 'color':'red'});
     }
});

This code identifies the cell using the selector specified within the $(''). This is the #emp ID we added to the region, and any cell within that report. For each cell it will check the contents to match the default label, then modify that cell as desired.

The final product

So add CSS to your heart's content. I'd be happy to hear people's ideas on how to add snaz.

Simple page showing it in action.

Extra challenge : find a way to extend the selection to locate the cells with numeric totals.

Suggested references

jQuery selectors
Related OTN forum post

APEX 101 - Server-side Dynamic Actions

Dynamic actions are awesome.

Let's so you have a scenario where after you change the value of one item, you would like to validate it or perhaps calculate the value of a separate item - or both!

Given an entry of Radius, I would like to calcular Sphere area.

Let's add an Oracle Forms style when-validate-item

This requires a relatively simple Dynamic Action that fires on 'Change' of the field P16_RADIUS.
In this case no condition is required - any change will fire the action. You could ensure the value is above zero, for example.

Dynamic Action properties

The action itself contains the PL/SQL formula.
:P16_SPHERE_AREA := POWER(:P16_RADIUS,2) * 4 * 3.141592;
This can be any anonymous block, or call to a PL/SQL package.

'True' action properties

The most important part of this action are the 'Page items to Submit' and 'Page items to return' fields.
This is what passes information from the browser to the database (session state) and back again.

In this case, the P16_RADIUS value is sent from the browser to the database so it can be used in the PL/SQL expression calculation, then the resulting P16_SURFACE_AREA is set in the browser.

Note some of the other options facilitated by the checkboxes.

The flexibility that dynamic actions provide give me just cause to vote them the best feature of APEX 4.x, just above plug-ins.

Other links

Inspired by this forum post, amongst many others like it.
For a more in depth tutorial, check out this Oracle-by-example.
This example is among others on session state in a page of my sample application.

Friday Fun : Tim Minchin

I like Tim Minchin.

One year I took my wife to see him with West Australian Symphony Orchestra (WASO) at Kings Park in Perth - now she likes Tim Minchin.
It's a shame he didn't perform his 10 minute beat poem, but that's best watched animated, thanks to a project led by Tracy King.

Just recently he received an Honorary Degree of Doctor of Letters for his contribution to the arts - a field in which he's a master at melding with science.
I think if you watch his acceptance speech, I think you'll also like Tim Minchin. 

Note - the recording automatically shows Tim's portion, keep watching to see his introduction.

Advert: SAGE APEX 4.2 course

SAGE Computing

Attention Perth residents - and perhaps those interstate...

SAGE Computing are holding a 4 day Oracle Application 4.2 course from the 28th October, 2013.

Please contact me or Penny if you're interested in attending.

Scott.

Formatting 101 - Nested single row functions

Sometimes to get the data displayed exactly how you want it, you need to employ some nested functions.

with t as (
  select null n from dual union all 
  select 0  from dual union all 
  select 0.5 from dual union all 
  select 4 from dual union all 
  select 4.5 from dual)
select to_number(n) verbatim
  ,TO_CHAR(n,'fm90.9') no_trail
  ,TO_CHAR(n,'fm90.0') trail_zero
  ,RTRIM(TO_CHAR(n,'fm90.9'),'.') trail_decimal
  ,RTRIM(TO_CHAR(NVL(n,0),'fm90.9'),'.') and_null
from t
/

  VERBATIM NO_TRAIL TRAIL_ZERO TRAIL_DECIMAL AND_NULL
---------- -------- ---------- ------------- --------
                                             0        
         0 0.       0.0        0             0        
       0.5 0.5      0.5        0.5           0.5      
         4 4.       4.0        4             4        
       4.5 4.5      4.5        4.5           4.5    

I'm curious - does anyone have formatting techniques the prefer/hate to help follow the brackets & commas?

For example, I might indent similar to JavaScript, and line up brackets/commas.

<< my_loop >>
FOR r_rec IN (
  SELECT 
    DECODE(this_value
          ,'Y', 'Yes'
          ,'N', 'No'
          ,'Maybe')
  FROM   that_table
) LOOP
  null;
END LOOP my_loop;

Scott

Using LDAP to authenticate your APEX users

It can be fairly simple to configure your APEX application to authenticate against your Active Directory server - this means your users can use the same username/password as the use to log onto their desktop machine - a big plus.

Declarative definition can be as simple as heading to Shared Components -> Authentication scheme and selecting 'LDAP Directory' from the scheme gallery.

Settings might be as simple as:
Host : myserver
Port : 389
DN String : MYDOMAIN\%LDAP_USER%

Further examples of the distinguished name string are shown in the item help.

LDAP Authentication definition

I've also seen an interesting use case in a popular APEX book. Here they also enabled authorisation definition (roles based on LDAP groups) - and they did it efficiently with some PL/SQL & materialized views.

There are a number of other examples in the #orclapex blogosphere on this:
http://ruepprich.wordpress.com/2012/11/02/ldap-authentication-with-apex/
http://doloracle.blogspot.com.au/2011/10/how-to-apex-ldap-authentication-for.html
and plenty of support in the OTN forum.

If you're keen to get your hands a little dirtier, there is also a supporting package APEX_LDAP.

Scott

Regular Expressions 101 - REGEXP_COUNT

Not all regular expressions are scary.

As the documentation states, REGEXP_COUNT returns the number of times a pattern occurs in a string.

We can do this to simply count how many times the letter S appears in a string

select job, regexp_count(job,'S') 
from scott.emp;
JOB       REGEXP_COUNT(JOB,'S')  
--------- ---------------------- 
CLERK     0                      
SALESMAN  2                      
SALESMAN  2                      
MANAGER   0                      
SALESMAN  2                      
MANAGER   0                      
MANAGER   0                      
ANALYST   1                      
PRESIDENT 1                      
SALESMAN  2                      
CLERK     0                      
CLERK     0                      
ANALYST   1                      
CLERK     0                      

 14 rows selected 

Today I used this function to detect/count how many carriage returns in a string using CHR(13) instead of 'S', but no doubt you could use the power of regular expressions to do all sorts of things. One such example is validating email address format.

I've also used it in the past to identify dirty data - those with numeric digits when it should be all alphabetical characters (names).

Perth 2013 Insync conference program now available

While many in the Oracle universe are enjoying OOW, here in Perth we are preparing for our leg of the Insync Conference Series.

This year it's on 12-13 November at Pan Pacific Perth Hotel  - a healthy walk or short free bus ride from the Perth CBD.

Long story short - the program is now available. 

Download it now and see why you should come along. It you can only make it for one day, good luck in deciding which day would be best for you!

Consider attending the workshops, they're just like being in training. If you do want to attend any, please let us know - you'll also get the most out of them if you come prepared, so we can help you out with what to download (if relevant).

Scott
2013 Program Chair
Perth Insync

The trick with triggers

Creating triggers, prior to 11g, would default them to an enabled state.

From 11g, we have this in the documentation:

By default, a trigger is created in enabled state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER statement.

So when I write my DDL scripts, given this behaviour, I know what I'd prefer:

SQL> create table test_table(a number);
table TEST_TABLE created.
create or replace trigger test_trigger_good before insert on test_table for each row DISABLE
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_good compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

1 rows inserted.

or

SQL> create or replace trigger test_trigger_bad before insert on test_table for each row 
begin
  null -- missing semicolon
end;
/
TRIGGER test_trigger_bad compiled
Warning: execution completed with warning

SQL> insert into test_table (a) values (1);

SQL Error: ORA-04098: trigger 'DEVMGR.TEST_TRIGGER_BAD' is invalid and failed re-validation
04098. 00000 -  "trigger '%s.%s' is invalid and failed re-validation"
*Cause:    A trigger was attempted to be retrieved for execution and was
           found to be invalid.  This also means that compilation/authorization
           failed for the trigger.
*Action:   Options are to resolve the compilation/authorization errors,
           disable the trigger, or drop the trigger.

Only run this when you know things are cool, ie - the triggers does not have compilation errors.

alter trigger test_trigger_good enable;

Otherwise you get the same problem, since you can enable an invalid trigger.

Tip of the hat to Connor McDonald for suggesting this many moons ago.

APEX information from Montreal

If, like me, you are nowhere near APEXposed in Montreal, but you are interested in what might be coming in APEX5, then you must read this post from Scott Spendolini (USA Scott)
http://spendolini.blogspot.com.au/2013/09/apex-50-impressions.html

Other commentary on APEX5 here.

Scott (Australia Scott)

SQL 101 - Group by ROLLUP

One of the reasons I called renamed this blog grassroots-oracle was to occasionally illustrate some simple examples of functionality not everyone is aware of - or are maybe too scared to give it a go (or research).
Still today I occasionally work with people who aren't familiar with some features I almost take for granted.

Today's little observation was with ROLLUP - a function used in group by queries to provide sub-totals.

I was looking at log frequency by month, and I also needed to consider grand total, so consider:

Rollup folks!

It's one of those features in the data warehousing guide that should possibly be in an area more accessible. While I don't necessarily use it for production code - it's syntax that I often find handy with ad hoc queries while interrogating the database.

select count(*), trunc(view_date,'mm') 
from activity_log 
group by rollup(trunc(view_date,'mm')) 
order by 2 desc nulls last

You'll also want to check out single row functions such as GROUPING.


Go nuts.

Help APEX find it's way

In case you missed it, there was an OTN forum post about asking about the APEX5 release date. As Hilary mentions, it's Oracle's privilege to release it whenever they like.

That being said, there is an APEX Feature Request application.

While new feature will always be cool, I'd also like to see an introspection regarding some facets of the Development Builder.

I've requested a few myself, but for example (to get creative juices flowing)

• conditional on dynamic action true/false actions
• improvements in dynamic action copy
• extent cache infrastructure, including APIs (another similar entry)
• more multi-row editing facilities, similar edit all on page components, and grid edit for pages - but not so buried.
• features aiding release management / version control
  (possibly via extending build options?)
• littler things like adding static ID attribute in component wizards
• compile attribute inline documentation online
• all encompassing comments dictionary view
• small win - delete multiple templates at a time

I think it's also fairly safe to expect a third User Interface for tablets.
All of this on top of what is already mentioned in the APEX Statement of Direction.

For more APEX 5 thoughts, check out

• some recent commentary in Europe from Christian Rokitta 
• desires from Peter Raganitsch
• browser support info from Joel Kallman 
• session reviews from Bart Peeters
• and a post I forgot I did.

Let me know if you've seen any others.

I look forward to the next release, whenever it may come ;-)

Scott

ps - the post also noted 4.2.3 would be 'soon'

Post eastern Insync 2013

It's been over a month since my last confession - mainly due to a lot of interstate travel.

The reason for most of the travel was the AUSOUG Insync Conference Series - on the eastern coast anyway. So in the past month I've been to Sydney (and surrounds), Melbourne (twice) & Brisbane (extra 90 mins to fly home).

The conference series was due to begin in Sydney on the 15th, but unfortunately due to a number of factors (which I won't get into here) it had to be cancelled, along with the Canberra and Adelaide legs. Perth preparations are still going strong, and we look forward to bucking the trend.

This turned out expensive for me because I had promised my wife would come over on frequent flyer points and we'd spent time driving through the blue mountains and other touristy type things. And extra two days of that when the company can't sport for accommodation before I head to the Melbourne gig after the weekend - bummer!

I'll just say Sydney was beautiful and treated us to lovely weather - but we had no success whale watching day.

Melbourne was cold, and the conference numbers were lower than previous. Penny (my boss) was still happy with the turnouts to our talks and networking engagement was reasonably successful.

My presentations went well, and for those coming to Perth and want to join any of the half-day workshops (eg: DB 12c, JDeveloper & APEX) I recommend you bring your laptops and become aware of software needs before hand to make the most of the time. See further comments below.

Some presentations I'm keepto keep an eye out for in Perth in November:

1. Penny Cookson - Redesigning core on-line systems in ADF JDeveloper - if you ever want a case study on a ADF migration project - this is the perfect chance
2. Scott Wesley (me) - APEX in your Hands - another case study on an APEX application preparing for deployment on a tablet. We hope to get our client involved with the intro to this in Perth
3. Chris Muir (et al) - The Future of Oracle Forms: Upgrade. Modernize, or Migrate - if you still use Oracle Forms, come even just to keep yourself informed on where things are at and what's truly available.
4. Mark Lancaster - APEX 4.2 Building Responsive application using Twitter Bootstrap - a good overview of a framework worth incoroporating into your applications - it's not hard.
5. Penny Cookson - New Optimizer Features in Oracle 12c - A technical look at tuning features in 12c, with a funny twist. Uber nerd alert.
6. Kylie Payne - ADF for Newbies - I know there are plenty of you out there. Fresh perspective from an Oracle professional.
7. Connor McDonald - CloneDB - a new one from Connor, let's place bets on the slide count.
8. Jeff Kemp - APEX and row level security - presenting the works of an excellent blog post
9. Phil Robinson - Testing the limits of testing - everybody should be incorporating more testing.

We have a few more to be confirmed from Oracle ACEs.

Workshop tips

1. DB 12c - Martin Power - bring a 64 bit laptop with plenty of gigs free. Download his recommended files first and do your best to get VirtualBox booting beforehand. He brings a thumbdrive with a pre-built image, but it will take a while to copy, initiate, boot.
2. Forms -> ADF - Penny Cookson - Read her setup instructions and try your best to come prepared
3. APEX Plugins - Scott Wesley - the easiest to prepare for - just bring a laptop! You can also do it easily in your own 4.2 environment, just grab a few files beforehand so you can play along.

For those in the east - also good was

1. Guy Harrison - Avoiding death by Big Data - a lighthearted but effective 1000 ft view of how big data is shaping our world. I enjoy his talks, and try to incorporate some of his style in mine. Imitation is the sincerest form flattery, they say...
2. Tim Daniell - Being Productive in IT - a different, thought provoking comparison of the human brain and an Oracle database.
3. Connor McDonald - SQL Tuning 101 - if you missed it last year, too bad - a fast paced, humorous look at SQL basics that everyone will learn from.

SAGE will be exhibiting in Perth and will continue our fine form of booth design, thanks to Branka.

Scott

ps - to all Australian readers - happy footy finals month.

Get SQL Developer 4 going in 4 steps

After a little hiccup on my part, I've started using SQL Developer 4 Early Adopter 1 on two computers I work on. It's pretty easy:

1. Download SQL Developer 4 and it’s required JDK from:
   http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/sqldev-download-v4-1925679.html
    
2. Unpack this to a folder on your system
   sqldeveloper-4.0.0.12.27-no-jre.zip
   I used
   c:\sqldeveloper4EA\ 
3. Install the latest JDK,  probably not accepting the new toolbar for your browser...
   jdk-7u25-windows-i586.exe
   
4. Open by running - I tend to copy a shortcut on my quick-launch bar
   sqldeveloper.exeWhen prompted for location of java.exe the first time you open, I used
   C:\Program Files\Java\jdk1.7.0_25
   I also allow it to copy my existing preferences across.

Job done, let's see what interesting features I encounter. 

The slightly flashier icon

If you want to actively learn more, you must visit www.thatjeffsmith.com

Scott

12c for Windows

Did you know that Oracle 12c has been available for Windows for a week already?

https://blogs.oracle.com/UPGRADE/entry/oracle_database_12c_is_available

I've downloaded it, but let's see how long it takes to find a chance to install it, let alone play!

SAGE seminars at Insync13

It's officially less than a month before the 2013 Insync national conference series kicks off in Sydney.

The SAGE Computing Services team has a heavy presence this year. You can find Penny & myself scattered amongst all Australian states, with Kylie & Ray boosting the Perth schedule. Check out the Insync website for schedule updates.

Penny
Case Study: Redesigning core on-line systems in ADF JDeveloper
New Optimizer Features in Oracle 12c
Redeveloping a legacy application in JDeveloper ADF - a half day workshop

Scott
APEX in your hands: Lessons learnt from a tablet project
Reasons why you should upgrade your APEX environment

Spice up your APEX application with third party plug-ins - a half day workshop

Ray
"Elementary my Dear Weblogic" - the Case of Spying on ADF

Kylie
Oracle ADF for Newbies: Surviving your first project

We'll be posting further information about the workshops on the Sage website, but remember - you'll need to at least bring your laptop.

Keep an eye out for Eddie, too - he's working on something regaring ADF Essentials.

Scott

APEX rendering issues in IE10

I'm sure anyone who's tried to write an APEX application that works across the "big 3" browsers - Chrome, Firefox & IE will be familiar with the pain of rendering differences.

Especially when it comes to version differences across Internet Explorer.

Thanks to Trent in the OTN forum, I have this nifty tip if you find some rendering issues in IE10 using APEX that aren't present in 'normal' pages.

Ensure your page template has the meta http-equiv illustrated here

<!--[if (gt IE 9)|!(IE)]>  <!--> 
  <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">  
<!--<![endif]-->

Instead of

<!--[if (gt IE 9)|!(IE)]><!-->  <!--<![endif]-->

I found this improved rendering of (amongst now doubt other issues)

• sliders rendered properly;
• login form fields had expected width;
• and the list template "Featured List with Subtext" rendered consistently.

Scott

12 new 12c features for developers

I haven't installed 12c yet because
a) I haven't had the time
b) my Linux skills aren't the best

What I have done though is read through some of the documentation in the New Features manual.

The APEX features are just regurgitating what's in 4.2, since that's what's shipped out of the box with 12c - but I always like to look through what's improved with SQL and PL/SQL.

No doubt many of you may have seen or read presentations on what's coming up as early as last year, but for those who haven't - here are some I've spotted that made the cut for 12c and I look foward to tinkering with

1. PL/SQL function in the WITH clause. I think this may sometimes come in handy, and other times it will be abused and lead to some real ugly code. Performance will be interesting.
2. Increased size limit for VARCHAR2 etc - no doubt people have been asking for this since at least 8i...
3. dbms_utility.expand_sql_text - Recursively replace any view references with relevant tables. This would have been brilliant in a project I often visit where there are views upon views upon views.
4. utl_call_stack - returns structured information about an exception stack. Tom Kyte has already blogged about this one, I'd suggest he's been pushing for something like this for a while.
5. Sequences can be used as column DEFAULT. Yes. So very yes. Though again I will be interested in performance, since 11g's :new.id := my_seq.nextval was the same as selecting from dual.
6. Default value on explicit null insertion - for those times where you really must have a default value in the column!
   
7. Identity columns - full examples are scant, but looks interesting.
8. row_limiting_clause - for top-N reporting, apparently an ANSI standard. This definitely needs exploring.
9. Row pattern matching. Um, wow. Big data influences?
10. Cross apply; Lateral clauses. From what I think is going on here, this could be mighty handy.
11. SYS_CONTEXT sys_session_roles - role interrogation for current session - cool, and will be very handy. A little late for many Forms applications, though.
12. TRUNCATE TABLE CASCADE - where is that sledgehammer?

I listened to an Oracle Magazine podcast recently talking about MySQL - I wonder if the collaboration going on here has influenced 12c features, because it sounds like the quality of MySQL is enhanced with Oracle IP.

Of course, if you're in Australia or would like to visit, the Insync13 national conference series already has a bunch of 12c presentations that span JDeveloper, Optimizer, Coherence, Weblogic and general features.

Oracle 12c now up for grabs

For many geeks out there, throw todays productivity out the window - Oracle 12c is now available for Linux/Solaris.

I'm not sure the official press release is out, the documentation sure isn't - but Twitter sure is abuzz.

While searching, I also spotted an article saying the pluggable database component will be a costed option.

Let's see how long it takes Tim Hall to release a string of articles on new features.

Here I was betting on a July release... at least all our Insync13 onference submissions for 12c can be ratified!

Sage Computing Services now on Google+

I've recently created a Google+ page for Sage Computing Services, the wonderful company I work for that has just celebrated 20 years of consulting & training in Perth.

We aim to use this social media presence to announce public training events, community involvement, and anything else we find relevant. No doubt it's usefulness will expand over time.

Check us out!

Thursday thought: How can you read that?

Every website that has some form of feed needs this option:

Thanks Google+

This sort of responsive web layout isn't always cool, IMHO - it's like watching a tennis match.

Google Web Designer - a HTML5 development tool

So, in the coming months Google will be launching a HTML5 web development tool targeted at creative agencies and designers.

My geeky mind thought about that, remembered geocities and thought - boy we've come a long way.

My APEX mind read that and thought - I wonder how many similarities this may have with a tool like Oracle Application Express, and I wonder what we might be able to learn from it?

I wonder what sort of databases could be attached to it, or maybe a good API into further enhanced HTML5 storage capabilities will make other databases superfluous in this instance?

There are a number of tools out there today that probably do whatever Google is going to do, but with Google's brand and inertia, we might just give the crazies an even more effective tool for duping the masses.

I'm also interested what it might mean for bloggers...?

Review: 2013 OTN Forums upgrade

While there is a post discussing the ins/outs of the ugraded forum site, I thought I'd add a summary of what I've found here.

First impressions

Too much dead space (a bugbear of mine), but some key improvements should outweigh what can be fixed or adjusted.
I'm sure I'll get used to the new navigation & layout, but there does seem like some ambiguous double ups exist, eg: Content tab

Note: forums are now 'spaces', and threads are 'discussions'.

With my APEX hat on, it took me a while to find the 'Application Express' space from the home page - it's under 'Database', not 'Development tools' - which I probably would have known if I didn't always bookmark my forums - thankfully those still work, even though you can't open old URLs that include the forumID.
Turns out there is a search region hidden at the bottom of the page.

As for existing notification e-mails, Thread links from emails will not work - Message links are ok, even though the new id doesn't seem to match up to old one:
https://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=2546249
https://forums.oracle.com/message/11060390

Basically you can open up old URLs without the forumID - which thankfully means google search results are re-routed

The biggest gripe

This seems to be the list of forum posts is what I might call the IR Detail style, meaning you only see a handful of discussions per page scroll, rather than dozens. I wish there was an option to toggle between views styles.
I thought by maybe 'Following' the Application Express space might make these posts appear in my Content->Following option, but alas - I'm yet to discover what this does or if it works.

As I tidy this post from some initial notes, it seems that the Overview tab for the space is too verbose, while the Content tab shows a view more to my liking - except the first half of the screen is wasted on chaff.

The search appears to be more effective - but I always used to search using Google anyway.

New discussion

Start of a new discussion shows how great the new editor is, various ways to make your postings pretty.

New discussion

The SQL syntax highlight is not fixed width font, which for me negates a true advantage of syntax highlighting.
Will be annoying to use that menu to set syntax, I hope there will be a shortcut like {code}. I saw /pre mentioned somewhere at one point, some people have been asking for a brief manual which I'm sure will be forthcoming with updated FAQ that is easy for newcombers to locate.
Ability to add tables to posts wil probably be nifty, too.

Marking responses

I don't have any responses to give helpful/correct answers, but that functionality appears to have been consolidated and should be a lot easier to interpret, more intuitive and should encourage newer users of forum to indicate what's going on.

At the moment thought it seems you can reward yourself with correct answers. This may help some complaints of missing/extra points, which will be looked at. At the moment it seems missing points due to old threads that were always marked for deletion. My theory for extra points are for unrewarded single responses to answered questions. I think I'm about 150-200 points up, and this seems like a fair guess.

Layout of discussions are nicer, softened, contemporary. The syntax highlighting is great!

Reply a lot better, just some fanciness that opens a text area on the bottom of the page, making it a lot easier to reference older information

Old layout

New layout

I haven't been active enough yet to see how it really handles, or what's going on with the new notification e-mails, but there is chatter about it on the upgrade discussion.

Account settings

Existing bio information is mostly gone, just your name and status level, with a nearby legend. You are supposed to be able to add it back in Edit Profile (drop down near your name), but I haven't yet been able to save this.

There is a message on the home page regarding known issues, and you can find your old posts under Browse->Content->Participated

Posts related to you

Activity isn't too dissimilar from the IR detail style report for the Space overview, but I found the Content tab a lot more useful, listing 'Authored','Participated','Following' as different options

I think this shows the value of simple, row by row reports - though it has an option to change to a view that merges 'iconic' with discussion text and quicklinks.

Avatar can be changed in the Activity tab for your account, but only to list of icons (for now). Other Preferences changed here also.

Friends is obviously reminding everyone of Facebook, but you might also read it as 'Interesting people' that you might tag to keep an eye on their comments.
Having Friends also allows you to use the @ sign to make them aware of posts.

The Tasks tab option is currently disabled, although you will get emails regarding action items (when you get friends) that take you to the main 'Task List'. More ambiguity.

Speaking of ambiguity - the content is different depending on if you come from main Browse tab; account detail or the Space you're in - for instance I can see 'Jive Genius' option via the Browse tab..

Conclusion

I think it has been worth the wait, and we always expected some people to be happy/unhappy - so give Sonya & the team some kudos for some very hard work and be patient while the wrinkles are ironed out.

Scott