Oracle APEX Techniques - Video Course

I'm happy to finally announce a project this little green pig was assisting me with last year - a video series on Oracle Application Express techniques, published thanks to Packt Publishing.
http://www.packtpub.com/content/oracle-apex-techniques/video

The course targets new to intermediate APEX developers who have a general understanding of SQL and PL/SQL, and is based on APEX 4.2.

The course is 8 sections of 5 videos, each about 3-4 minutes each. The videos are screencasts from my laptop, with my voice describing what's happening on screen - very similar to a webinar.

Check out this sample clip looking at Interactive Reports detail view.

After having read a number of technical books and heard from other authors regarding the work involved, I can certainly say it was an interesting project to be involved with. I would love to include many other techniques I've acquired over the past six months in addition to the wealth of information that's out there - but we only had a limited number of minutes available - they sure add up quickly!

So if you're starting to learn Oracle Application Express; or you've been using it for a while and you're wondering if you've missed anything useful - you might like to consider this video series. I hope you find it valuable.

Oracle APEX Techniques

Cheers,

Scott

Performance of APEX conditions

I often think about performance of conditional expressions. I'm not just talking about the expense of whatever the test is, but the difference between the condition types used.

I’ve heard about it from a few sources, and Tony Andrews blogged about it recently, but never really been able to/had a chance quantify it. Roel Hartman has also blogged about this after reading my tweet. He shows some figures, but difficult to rinse, repeat & measure like a PL/SQL process.

For example, while I’ve always encouraged this type of expression for consistency (which runs dynamically), particularly with new developers learning the differences between item substitution syntax.

This performs better (declaratively):

I don't see this second debug statement when using the declarative method.

The benefit is minor, but if multiplied by 50 separate events, that’s 1.5 seconds extra in rendering time.

Use the low-code options when you can, your database will appreciate it. You could even nest regions to get around multiple conditions.

INSYNC13 National Conference series

In 2013 the Insync Conference Series has expanded to include six capital cities within Australia, thereby bringing Insync to multiple east coast locations and incoporating the Oracle with 20:20 Foresight Perth leg.

The website has been updated, and the call for papers is coming to a close this Friday 24th May.

2013 Conferences

• Sydney 15-16 Aug
• Melbourne 19-20 Aug
• Canberra 27-28 Aug
• Brisbane 2-3 Sept
• Adelaide 5-6 Sept
• Perth 12-13 Nov

We've already received a range of abstracts, but I would love to see more submitted before the end of the week - including mine!

Keeping with tradition, the Perth conference is a couple months after the rest (after Oracle Open World), so we might be a little more lenient with late submissions.

We encourage abstracts for any of these fields, and this year we hope to bring even more workshops to the programme.

• DBA: Application | Development | Production
• Dev: PL/SQL | Forms & Reporting | Apex | ADF
• E-Business Suite/Fusion Apps: Financials | OBIEE | Hyperion | Primavera
• Middleware: Content | WL Server | BPM | BPEL | SOA
• Other Oracle Products that you believe are a current 'HOT TOPIC'.

If you've never visited Australia before, I recommend you take the opportunity to attend and/or speak at least one of these events. I would personally hope to see some first time international guests from the APEX space (hint hint!)

Use the #INSYNC13 or #AUSOUG tags to keep up with details on Twitter.

Scott Wesley
2013 Program Chair (Perth)

Analytic functions within Pivot statements

Today I found that SQL analytics go hand in hand with PIVOT statements.

I had percentage data pivoted by a certain column, but I needed to sort it by
the total across each row.

For the purpose of this demo I create a table with some random monthly data

create table my_data as 
select /*+ no_merge */ username, round(dbms_random.value(1,12)) mth, round(dbms_random.value(1,100),0) val 
from my_users connect by level < 4;

Now with the following query I transpose the monthly totals across as columns using the PIVOT.

select * from (
  select  sum(val) av, username, mth
     ,sum(sum(val)) over (partition by username order by null) tot
  from my_data
  group by username, mth
) 
pivot 
(avg(av) -- avg() because I didn't need to modify the value any further
         -- I'm open to other suggestions
  for mth in 
  (1  as jan
  ,2  as feb
  ,3  as mar
  ,4  as apr
  ,5  as may
  ,6  as jun
  ,7  as jul
  ,8  as aug
  ,9  as sep
  ,10 as oct
  ,11 as nov
  ,12 as dec
  )
)
order by tot desc

Note the highlighted line - an analytical statement that sums yearly total for each username. This was the simplest way I could determine
a) a way to determine that total
b) order the report by the user with the highest totals

I haven't used PIVOT queries too often yet, but I can see how analytical functions will be vital to providing, er, pivotal information.

APEX Easter fun

This isn't really an easter egg, just more of a mild bug that makes you think - hopefully a little more about your own applications.

If you type in "0" in the page navigation bar, it takes you to the typical global page / page zero for desktop.
In the component view, instead of displaying page processing and shared components, it displays application level components - items, process, computations; and an "About" region in the third column, above recently edited pages. Useful stuff...

If you type "-0", it renders page zero detail as if it were a normal page.

If you type "-1", or any other negative number,
- in 4.2.1 it takes you into a horrible "no data found" that's annoying to recover from, similar to if you type a positive page number that doesn't exist
- in 4.2.2 it handles it a little more elegantly, a little better than it did pre 4.2 - displaying no page detail.

Harmless "fun".

Review: Oracle APEX for Mobile Web Applications

Oracle Application Express for Mobile Web Applications

If you haven't heard about this book yet, you're not reading enough media on-line.

APEX regulars Roel Hartman, Christian Rokitta and APEX product manager David Peake are the published authors, but I think it’s safe to say we can also thank Dan McGhan for starting the project.
(I mention Roel explicitly a few times where I'm pretty sure he was involved, sorry if I apply undue credit anywhere!)

I've been meaning to write this review for quite some time - I had the pleasure of technical reviewing this book & from the first chapter I knew this was going to be a winner - something very popular for anyone with half a brain that can bust out an APEX application.

Introduction to APEX for Mobile Development

Let’s be honest, the title alone sold the book for me. Mobile development is apparently kinda popular right now, as is APEX - so it seemed the perfect pair.

I think it was a great introduction to what’s expected out of mobile development, particularly through the eyes of an APEX developer. It details issues such as web vs native & responsive design. It should whet your appetite, if not - you’re in the wrong game ;-)

Creating Mobile Pages

This chapter starts of by de-constructing a basic jQuery Mobile page, which sets the scene well for the type of HTML to expect from these pages.
You are then walked through the creating of your ‘first’ mobile page using a list view and a basic form. The author then uses these to illustrate the many ways you can define navigation between pages, including a mobile dialog.
He then finishes with an intro to transitions, loading widgets and templates.

Highlights - realising the programmatic flexibility APEX can provide.

Presenting Data through List Views

As I’ve learned in a current project developing an application for a tablet - list views are a fundamental cultural shift from the desktop, providing a standard workflow construct.
This chapter details the various options APEX provides when defining these lists views.
The author also includes some sample CSS to help show how to identify then manipulate components on the page - it’s not too hard once you are shown some examples.

Highlights - advanced formatting with CSS examples; swipe to delete

Accepting Input via Forms

This chapter starts with the simplest, most common form ever - the login page. The author notes how bland the stock form is, and uses the power of CSS to tart it up.

The next most important aspect of mobile development is discussed - grid layout.
Screen real estate is also precious, so there are some examples of collapsible content.

Also included are discussions regarding HTML5 input options such as the slider & toggle switch, and ways to enhance existing selectors such as radio groups and select lists.

I keep saying things are important, but mobile is a whole new world and notifications are another example of a fundamental product feature that needs re-addressing in the mobile world.
If you’re a fan of iOS, Roel continues the chapter by showing how to customise the delete dialog box with a native looking popup; then follows it up with a much nicer success confirmation.

If that’s not enough, the chapter puts finishing touches on your ‘first’ form to make it look ultra professional - and we’re only four chapters in!

Highlights - more CSS examples, grid layout, notifications

Displaying Calendars and Charts

As if your application isn't looking sexy enough already, we can also add various charts and calendars.

Out of the box calendar’s are not too bad - but again Roel takes things a few steps further with some inspiration from iOS.

He doesn't go into too much detail with charts - they’re already pretty nifty and the APEX team are constantly working with AnyChart to ensure greater capability and conformity with various devices. Later in the chapter though he demonstrates some of the other options out there such as Highcharts & Flotcharts - which includes an example of a RESTful web-service.

The surface is also skimmed with mapping options, but I think again it’s because of the reliance on external interfaces.

Highlights - calendar enhancement

Implementing a Navigation Springboard

This chapter starts with an obvious lean towards Apple devices, but springs back to setting up a very nifty iconic menu (see what I did there?).

Roel then shows how to add tab navigation across the footer of your page - useful for certain applications but it could be considered dead real estate.
Shortly after he does detail how to move away from the typical HTML style buttons to iconic buttons in your header that you’d expect from a mobile application.

The coolest part of this chapter is a slide menu just like the one you see in Facebook mobile. He really puts list templates to work - one of my favourite APEX constructs.

Highlights - iconic menu with notification counters; sliding menu

Theming Your Mobile Application

This is a small but useful chapter describing how to turn your application from a royal blue to something more suitable to your client’s needs.

It’s also a chapter where I’ll claim a little credit for improvement. Changing swatches within templates can be tedious, so I suggested the author provide instructions to parameterise the templates with some form of substitution string - thereby making it easier to switch between swatches.

It finishes with a short piece on managing icons between devices using CSS media queries.

Highlights - media query example

Working with Dynamic Actions

Let’s face it, dynamic actions are one of the top 3 features of APEX. This is why I'm a little torn with the approach of this chapter - the first half is a simple breakdown of how dynamic actions work and how to use them, something that seemed a little below the target audience and previously presumed skill level of the reader (my perspective). I do, however, feel they’re vital to good applications and I'm glad there is another chance to show the community how they work.

The second half shows how dynamic actions have been modified for APEX 4.2, and introduces the new declarative events such as orientation change, scrolling, tapping & touching & swipes.
The author provides a few mobile specific examples of dynamic actions, then details some advanced topics such as non-standard selection types, event scope & custom events - all of which are also useful in desktop environments.

Given the style of the book so far, I was a little disappointed with this chapter - I think it had more potential than was delivered, with respect to whoever wrote this chapter.

Highlights - advanced dynamic action techniques.

Deploying Applications Natively

If you want to interact with the hardware of your device using a toolkit such as PhoneGap, you should buy this book for this chapter alone.
HTML5 is still catching up with hardware capability, and the various browsers are even further behind - so these types of interfaces allow you to embed your APEX application in a lower layer within a mobile device.

The chapter starts with a good philosophical discussion on native vs web vs hybrid applications, then describes the PhoneGap architecture.
From there it’s a step-by-step guide on configuring an Android Development environment for Cordova. iPhone users - there is also a section on cross-platform support.
It then shows how to use the PhoneGap APIs in your APEX pages using the device’s camera as an example.

Unfortunately I didn't have the time to implement an application natively during the technical review, partially because I had some laptop issues myself when this chapter was ready. The chapter is very detailed, however, and it will be front row & centre very shortly for when I do.

Highlights - all of it - I believe you can thank Christian for this one

Securing your Mobile Application

I wouldn’t consider myself an expert in security, but I have a high level of interest - a presentation I wrote on the topic won me best paper in 2011. So I was looking forward to reading the implications for mobile.

It’s a fairly concise chapter, covering SSL, idle sessions, encryption & session state protection. I think the best pages discuss application & schema splitting, getting the reader to think about these implications, but doesn’t go into a large amount of detail.

Highlights - timeout messaging techniques

Conclusion

I think the book is excellent. It covers all the topics you think you’ll need for mobile development, and packs a whole bunch more.

I will say it started strong, but tapered off towards the end - but I think that may be due to the fact books are difficult to manage, author & produce while still maintaining some sort of life. Kudos to the authors involved.

It’s a little biased towards iPhones - evident because I own an Android, but there isn't much content that requires such a device. Of course, this is the nature of the real world, and considerations need to be made when developing applications for mobile devices - what is your target market?

You will also need patience. It’s bleeding edge material that sometimes requires supplementary files or Oracle database adjustments. If you’re still learning the power of jQuery & CSS (aren't we all?) then there may be times your code isn't quite right and it might take some time to work out, but it’s part of the learning process - it just demonstrates how valuable this book is.

Well done gang.

If you're interested in more of my reviews, check this out.

Freezing the Navbar Menu plugin to top of page

Recently I blogged about a nifty CSS only menu that is a great alternative to the horrible APEX tab-sets. Unfortunately I had to ditch it for a recent project because it didn't work well on touch screen devices, so I picked up Enkitec's Navbar plugin instead.

I wanted to freeze the menu at the top of the page, so when the user scrolled down, the menu would remain at the top of the screen.
I tried doing this some time ago with a side-bar menu but came across a bunch of problems - this time round it required very little CSS to make it happen. This stackoverflow post suggested 2 lines, but my APEX example extended a touch more:

.navbar {
  position:fixed;
  width:100%;
  z-index:10000;
}
.apex_span_12, .apex_span_10 {
  margin-top:50px;
}

The key is the position fixed, width 100%, but the fixed position had some minor side effects - some objects overlapped my menu, and all existing content shifted up underneath the menu.

To fix the overlap I used z-index, which is the third dimension beyond height/width and decides which components appear above another when the position is set. I added a large z-index value so the menu always overlapped other objects I had such as a google map, CSS checkbox switches and other maps that by default went over the menu instead of under like all the APEX components.

Then I had to make sure all my regular content started underneath the menu, so this is where the margin comes in. Since I was using the responsive Theme 25, I identified those classes as the lowest common denominator for moving my content.

Depending on the page template used, it was either of those classes - basically if I had a side-bar region the apex_span_n changed. I'm happy to receive feedback on a smarter solution, but this worked for me.

I used 50 pixels, but the font in my menu is at 130% to benefit bigger fingers on the touch screen.

Hope it helps!

Scott