Friday, September 20, 2013

New APEX 4.2.3 Packaged Applications: Sample Reporting & Data Reporter

APEX 4.2.3 seems to be largely a maintenance release, with few new features added.  (Full details of what is included can be read here:

Despite this fact, there are a couple of new "features" that were added by way of a new packaged applications called Sample Reporting and Data Reporter.  Let's take a look at Sample Reporting first.  Upon initial inspection, this application seems quite unremarkable and basic, as it simply contains a few IRs and standard reports.  However, after a closer look, it is obvious that there is more to this than what is on the surface.

Upon running the Sample Reporting application, you'll see the following main five options:

The first two - Interactive Report & Standard Report are nothing more than their titles imply, and I won't spend any more time discussing them in this post.  The last three, however, do merit some additional attention.

Filter Reports
Filter Reports will seem very familiar to you, as this type of report has been implemented by a number of web sites.  Basically, there is a list of filters on either the left side of in a drop down.  Selecting a filter will, well, filter the report based on that criteria.  Adding additional filters will OR each condition.

The point behind Filter Reports is to enhance usability by reducing the number of options available.  While IRs are great, they may be overwhelming and not intuitive enough for some users.  Filter Reports solves this problem by presenting all of the available options right there on the page.  Users need only click on what they want to see.

If this is something that you think your users will benefit from, then you need to check out the second new packaged application, Data Reporter.  Data Reporter allows you to create a Filter Report on any table in your schema.  Rather than go into more detail on how to create a new report with the Data Reporter application, I'll reference Mike Hichwa's blog post here:

It would not surprise me at all if Filter Reports were integrated into APEX 5.0, as I think that a lot of users will benefit from their more streamlined approach to mining data.

Use Cases
The Use Cases section offers a number of different scenarios that involve APEX reports, as illustrated below:

While some of these examples are simple, many of them provide clear and concise examples on how to take your APEX reports to the next level.  For example, the Custom Reports Template &  Custom Buttons one illustrate how to easily change the look & feel of your reports to make them easier to use.  Developers of all skill levels will be able to utilize these Use Cases as points of reference for their own projects.

SQL Examples
As APEX developers, we often forget about the pure power available to us in the database itself.  From advanced search techniques to analytic functions, the Oracle Database can provide a layer of functionally that is unsurpassed.  The SQL Examples section outlines some of these features.

If nothing else, every APEX developer should have a look at these example and their corresponding SQL statements.  At a minimum, they will serve as a refresher.  But in most cases, most developers will learn a thing or two from these examples.

APEX 4.2.3 is available now as a patch or as a fresh installation.  If you don't want to upgrade your own instances, then head on over to, as it is running 4.2.3.

Thursday, September 19, 2013

Working with the APEX Tree

I found a great blog post by Tom Petrus that summarizes the power of the APEX tree here:

The post details a number of different attributes of the tree and how to interact with it.  It starts simple and shows how to get a tree reference and gradually gets more and more detailed, including how to search the tree and how to handle when a node is selected or even double clicked.

There's a working demonstration that goes with the post here:

Many of the techniques and tips here have been invaluable to me in recent days as I work on a new project that heavily involves using trees.  Thanks, Tom!

Tuesday, September 17, 2013

Oracle APEX @ OOW

Looks like the Oracle APEX team's annual OOW site is live here:  This site provides a list of APEX-related sessions at OOW in both a calendar and list view. It is also mobile friendly, which will make it nice to have while at the conference itself.

But the big news is spelled out right on the home page:

The Oracle Technology Network (OTN) Application Express Developer Challenge Oracle Open World 2013 is designed to highlight how quick and easy it is to build a "mobile" solution using Oracle Application Express.
Prizes include Amazon Gift Cards for first, second, and third. 
Participants will be required to build mobile pages (an application) utilizing Oracle Application Express in a free hosted development environment,
When entering the challenge participants must provide a workspace on At the completion of the challenge period, the participants will be locked out of the workspace specified.
Registration and details for the challenge won't be available until Monday, September 23rd at 3PM PDT, so be sure to check back then!

Monday, September 16, 2013

New Book: Expert Oracle Application Express Security

I remember vividly meeting with Jonathan Gennick at RMOUG 2012 at the Apress booth.  As always, he asked if I was up for writing something APEX-related.  And as always, I politely declined, as I just had too much going on at the time.  However, before he let me leave the booth, he pledged that I didn't have to write something that was 800+ pages, and that a niche topic book that was "only" a couple hundred pages would work.  Time to reconsider.

Fast forward a year and change later, and finally, I'm happy to announce that Expert Oracle Application Express Security is now available for purchase (well, it has been for a while, and I'm just now getting around to posting this).  The book really did not take an entire year to write, but there were a couple of challenges that were thrown in along the way.  First of all, that night, we sat down with Enkitec and began discussion the acquisition plans.  So that was a bit of a distraction.  Also, I knew that at the time, APEX 4.2 was near release, and I wanted to ensure that I covered that release, so I had to actually write some of the later chapters first, and then circle back and complete the first ones last, since they contained more APEX 4.2-specific elements.  Throw in the daily trials and tribulations of two kids and their hectic schedules and eventually a new job with new responsibilities, and all that added up to why it took longer than many of us wanted.

But enough about the excuses, and more about the book!  The book contains 14 chapters, which range in topic from assessing a threat to preventing SQL injection to securing data at the database level.  I've summarized each chapter below:

Chapter 1 begins with a discussion of how to identify and assess threats to your applications.  It uses home security as an analogy when discussing this, since everyone already understands how to secure their home and has likely already taken steps to do so.  It then categorizes all threats into two categories: preventable and unpreventable, and briefly discusses examples of each 
Chapter 2 covers what a security plan is and how to implement one for your organization.  The main objective when creating such a plan is to first properly assess what the threats are, as specified in the previous chapter.  The security plan is an ever-changing document that has to adjust as threats do, and should be reviewed often. 
Chapter 3 provides an overview of the APEX architecture from a security perspective.  It starts by reviewing the Administration Console and how to configure Workspaces.  It then covers a bit of APEX architecture, as well as touches on the different options for the web listener tier. 
Chapter 4 outlines all of the Instance Settings that pertain to security, and what the implications of setting them improperly are. 
Chapter 5 does the same as the previous chapter, but does so at the Workspace level. 
Chapter 6 covers setting within an application that pertain to security.  It discusses them at the application, page and component level, as well as provides some advice when building mobile applications. 
Chapter 7 outlines the three main threats to an APEX application: SQL Injection, Cross Site Scripting and URL Tampering.  It illustrates example of each, as well as shows how to protect against them. 
Chapter 8 covers how User Authentication schemes work and how they can be better secured.  It also discusses the pros and cons of each type of scheme, as well as some commonly used APIs. 
Chapter 9 talks about Authorization Schemes and how they can be used throughout an application for access control purposes.  It also briefly covers the Access Control feature of APEX. 
Chapter 10 provides an alternate, more secure way to download CSV files from an APEX report.  It provides step-by-step instructions to implement this solution in your applications. 
Chapter 11 outlines a technique called Secure Views.  Secure Views can be used in conjunction with a database context to provide a more secure way to display your data at no additional cost, if you are not using the Enterprise Edition of the database. 
Chapter 12 is similar to the previous one, but it uses Virtual Private Database, which is a feature of the Enterprise Edition of the database. 
Chapter 13 illustrates a concept called Shadow Schema.  By using a limited privilege schema as your parse-as schema in an APEX application, you greatly increase the security of that application using this technique. 
Chapter 14 concludes with some examples of how using encryption in your application can increase the security of your data.
You can get the book online from Amazon here:  Alternatively, if you'll be at OOW this year, we will be giving away copies at our booth in the exhibit hall.

Thursday, September 12, 2013

APEX 5.0 Impressions

I had the opportunity to sit through two APEX 5.0 presentations yesterday at APEXposed in Montreal - one by Joel Kallman, and the other by David Peake.

By far, the most anticipated feature is the new page layout UI.  This interface is designed to do two things: make arranging regions and items easier, and allowing for batch updating of common item attributes.  While the demonstrations were a bit rough and the UI is clearly not finished, this direction represents a lot of promise for APEX developers, as it is the one area that APEX has been lacking since day one.

Speaking of interfaces, there will be a new User Interface - Tablet - added to all applications.  This will help bridge the gap between Desktop and Phone-based browsers, should a developer feel the need to do so.

One of the smaller yet more impressive features was the ability to add CSS/JS files as a ZIP files.  The APEX listener will automatically unzip those files and even cooler - preserve the paths.  This way, you can easily upload a set of related JS files and ensure that they will still function as intended.  It also has a cool way of versioning these files.  The URL that is uses for them will have a path like this: /v212/  As these files are modified and/or re-uploaded, the version will change, ensuring that the files do not become cached on user's devices.

While we didn't see it, the new tabular forms replacement - called Multi-Row Edit Region - seems promising.  Also, the ability to add multiple IRs to a single page will also become reality. IRs are getting a little bit of a makeover, with modal dialogs replacing the traditional menu and the ability to freeze columns are added.

Another feature that has been long anticipated will be the ability to authenticate to the Application Builder et. all with a custom authentication scheme.  This feature probably has more political impact than technical, since it allows APEX developers to be a part of an organization's identity management infrastructure.  It will also make it a heck of a lot easier to remember your credentials for any workspace, since they can all be the same.

Speaking of security, it looks like there is some planned integration with Oracle Real Application Security, or RAS.  RAS is a no-cost feature of the enterprise edition of the database, and is very similar to VPD.  With it, you can define roles, users and privileges that can be used with any technology.  Not too many more details were available about this, but it also seems promising.

Autocomplete in PL/SQL regions (or at least Application Processes! :) ) is also a part of APEX 5.  When typing in code, you can hit control+space and autocomplete things like item names, APEX APIs, and database objects.  To compliment this, a new code editor - very similar to that included with ApexLib - is baked into APEX 5.

Overall, it looks like there's a lot of interesting features to come in APEX 5.  While there's definitely a lot of work to be done, each time I see it, more and more of the features are working better and better.  It will be interesting to see how much progress can be made between now and OpenWorld.

ODTUG Sunday Symposium @ OpenWorld

Wow, it's been almost a year since I've blogged.  Guess that's what happens when you get busy!

In any case, if you're going to be at OpenWorld and use APEX, be sure to stop by and see the ODTUG APEX Symposium.  This year, we're going to focus on printing with APEX.  We have four different solutions lined up: PL/PDF, BI Publisher, Jasper Reports and the APEX Listener w/FOP.  Each presenter will be discussing the benefits of each solution from a cost, ease of use, integration and security perspective.  This will give the attendees an objective review of these printing solutions.  We'll conclude with a panel discussion summarizing all of the solutions together.

Here's the session details so that you can add it to your OpenWorld agendas:

  • Printing Options for Oracle Application Express: Oracle Business Intelligence Publisher [UGF10238] Sunday, Sep 22, 9:15 AM - 10:15 AM - Moscone West - 2005

  • Printing Options for Oracle Application Express: Jasper Reports [UGF10240] Sunday, Sep 22, 10:30 AM - 11:30 AM - Moscone West - 2005

  • Printing Options for Oracle Application Express: FOP and Oracle Application Express Listeners [UGF10239] Sunday, Sep 22, 11:45 AM - 12:45 PM - Moscone West - 2005

  • Printing Options for Oracle Application Express: PL/PDF [UGF10237] Sunday, Sep 22, 2:15 PM - 3:15 PM - Moscone West - 2005

  • Printing Options for Oracle Application Express: Q&A Panel [UGF10241] Sunday, Sep 22, 3:30 PM - 4:30 PM - Moscone West - 2005
Hope to see some of you there!