Wednesday, April 09, 2014

Oracle Application Express 4.2.5 now available

Oracle Application Express 4.2.5 is now released and available for download.  If you wish to download the full release of Oracle Application Express 4.2.5, you can get it from the Downloads page on OTN.  If you have Oracle Application Express 4.2, 4.2.1, 4.2.2, 4.2.3 or 4.2.4 already installed, then you need to download the APEX 4.2.5 patch set from My Oracle Support.  Look up patch number 17966818.

As is stated in the patch set note that accompanies the Oracle Application Express 4.2.5 patch set:
  • If you have Oracle Application Express release 4.2, 4.2.1, 4.2.2, 4.2.3 or 4.2.4 installed, download the Oracle Application Express 4.2.5 patch set from My Oracle Support and apply it.  Remember - patch number 17966818.
  • If you have Oracle Application Express release 4.1.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.2.5 release from the Oracle Technology Network (OTN).
  • If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.2.5 release from the Oracle Technology Network (OTN).
As usual, there are a large number of issues corrected in the Application Express 4.2.5 patch set.  You can see the full list in the patch set note.

Some changes in the the Oracle Application Express 4.2.5 patch set:
  1. A number of bug fixes and functionality additions to many of the Packaged Applications.
  2. One new packaged application - Live Poll.  This was the creation of Mike Hichwa.  Live Poll is intended for real-time, very brief polling (in contrast to a formal survey, which can be created and administered via Survey Builder).
  3. One new sample application - the Sample Geolocation Showcase, created by Oracle's Carsten Czarski, who did a masterful job in demonstrating how Oracle's spatial capabilities (via Oracle Locator) can be easily exploited in an Oracle Application Express application.  Try it for yourself today on!
  4. A handful of bug fixes in the underlying Application Express engine and APIs.

APEX 4.2.5 should be the end of the line for Oracle Application Express 4.2.x.

Thursday, March 20, 2014

Yet Another Post How to Link to Download a File or Display an Image from a BLOB column

On an internal mailing list, an employee (Richard, a long-time user of Oracle Application Express) asked:

"...we are attempting to move to storing (the images) in a BLOB column in our own application tables.  Is there no way to display an image outside of page items and reports? "

Basically, he has a bunch of images stored in the BLOB column of the common upload table, APEX_APPLICATION_FILES (or WWV_FLOW_FILES).  He wishes to move them to a table in his workspace schema, but it's unclear to him how they can be displayed.  While there is declarative support for BLOBs in Application Express, there are times where you simply wish to get a link which would return the image - and without having to add a form and report against the table containing the images.

I fully realize that this question has been answered numerous times in various books and blog posts, but I wish to reiterate it here again.

Firstly, a way not to do this is via a PL/SQL procedure that is called directly from a URL.  I see this "solution" commonly documented on the Internet, and in general, it should not be followed.  The default configuration of Oracle Application Express has a white list of entry points, callable from a URL.  For security reasons, you absolutely want to leave this restriction in place and not relax it.  This is specified as the PlsqlRequestValidationFunction for mod_plsql and security.disableDefaultExclusionList for Oracle REST Data Services (nee APEX Listener).  With this default security measure in place, you will not be able to invoke a procedure in your schema from a URL.  Good!

The easiest way to return an image from a URL in an APEX application is either via a RESTful Service or via an On-Demand process.  This blog post will cover the On-Demand process.  It's definitely easier to implement via a RESTful Service, and if you can do it via a RESTful call, that will always be much faster - Kris has a great example how to do this. However, one benefit of doing this via an On Demand process is that it will also be constrained by any conditions or authorization schemes that are in place for your APEX application (that is, if your application requires authentication and authorization, someone won't be able to access the URL unless they are likewise authenticated to your APEX application and fully authorized).

  1. Navigate to Application Builder -> Shared Components -> Application Items
  2. Click Create
    • Name:  FILE_ID
    • Scope:  Application
    • Session State Protection:  Unrestricted
  3. Navigate to Application Builder -> Shared Components -> Application Processes
  4. Click Create
    • Name: GETIMAGE
    • Point:  On Demand: Run this application process when requested by a page process.
  5. Click Next
  6. For Process Text, enter the following code:

    for c1 in (select *
                 from my_image_table
                where id = :FILE_ID) loop
        sys.owa_util.mime_header( c1.mime_type, FALSE );
        sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.blob_content));
        sys.htp.p('Content-Disposition: attachment; filename="' || c1.filename || '"' );
        sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary
        sys.wpg_docload.download_file( c1.blob_content );
    end loop;

Then, all you need to do is construct a URL in your application which calls this application process, as described in the Application Express Application Builder Users' Guide.  You could manually construct a URL using APEX_UTIL.PREPARE_URL, or specify a link in the declarative attributes of a Report Column.  Just be sure to specify a Request of 'APPLICATION_PROCESS=GETIMAGE' (or whatever your application process name is).  The URL will look something like:


That's all there is to it.

A few closing comments:
  1. Be mindful of the authorization scheme specified for the application process.  By default, the Authorization Scheme will be "Must Not Be Public User", which is normally acceptable for applications requiring authentication.  But also remember that you could restrict these links based upon other authorization schemes too.
  2. If you want to display the image inline instead of being downloaded by a browser, just change the Content-Disposition from 'attachment' to 'inline'.
  3. A reasonable extension and optimization to this code would be to add a version number to your underlying table, increment it every time the file changes, and then reference this file version number in the URL.  Doing this, in combination with a Cache-Control directive in the MIME header would let the client browser cache it for a long time without ever running your On Demand Process again (and thus, saving your valuable database cycles).
  4. Application Processes can also be defined on the page-level, so if you wished to have the download link be constrained by the authorization scheme on a specific page, you could do this too.
  5. Be careful how this is used. If you don't implement some form of browser caching, then a report which displays 500 images inline on a page will result in 500 requests to the APEX engine and database, per user per page view! Ouch! And then it's a matter of time before a DBA starts hunting for the person slamming their database and reports that "APEX is killing our database". There is an excellent explanation of cache headers here.

Thursday, March 06, 2014

Finally...the official sizing guide for Oracle Application Express

The following question was recently posted on an internal mailing list:
"Is there a sizing/capacity/scalability guide available for APEX?"
I'm always fascinated by this question.  I appreciate the fact that this is a standard, acceptable practice in the industry, and people come to expect it.  How else could architects and planners appropriately allocate resources without some form of estimate?  This impacts capital expenditures and budgets and rack space and energy costs and support costs and human capital.  People seem to be looking for some simple formula like:
(X number of pages in an APEX application) * (Y number of concurrent users) = (W number of processors) + (Z number of GB of RAM)
Voila!  Plug that formula into your favorite spreadsheet and away you go.  Well....if I lured you in with the title of this blog post, I have to be honest - it's all fiction.  There is no such thing.  But why not?  There are a number of reasons.

  1. There is no such thing as a representative, typical application.  As I've often bloviated in the past, Oracle Application Express is as fast or as slow as you, the developer, make it.  The overhead associated with the APEX engine itself is fairly static (measured in hundredths of a second). If you have a query that takes 30 seconds to execute and you put this query in a report in an APEX application, you can expect the execution of that page to take just over 30 seconds per page view.

  2. What does "concurrent" mean?  Is that the total number of users in an hour?  Total number of users in a 5-minute interval?  Or is that the high-water mark of number of users all clicking the mouse or hitting the Enter key, all at the same time?

  3. What is the typical "think time" of an end user?  Effectively, resources are only being consumed when there is a request actively being processed by the APEX engine.  So while the end user is interpreting the results of a report or keying in data in a form, they aren't (typically) making any requests to the APEX engine.

  4. How much memory will be consumed by the typical page view?  Does your application allocate GB's of in-memory LOBs, per user per page view?  This would have a definite impact on scalability.
The total number of pages in an application has close to zero correlation to scalability and throughput.  You can have a 1,000-page application, each page with sub-second performance, which will be far more scalable than a 1-page application that consumes 15 seconds per page view.

As the Oracle Database Performance and Tuning Guide states, there are many variables involved in workload estimation, and it's typically done via either benchmarking or extrapolation from a similar system.  But what is "a similar system" for an APEX application?  Does a call-center application at one enterprise approximate the back-office order processing system at another company?

I can understand how a formula can be prepared for a COTS application.  If you're deploying Fusion Applications or the eBusiness Suite or JD Edwards or SAP, those applications are created, the business logic is written, the queries and transactions are crafted, and concurrency has been measured on representative systems for a given workload.  But I don't understand how someone can produce a sizing guide for any application development framework - Application Express, ADF, .NET, Java.  It's like asking "how scalable is C?"

An application that our team wrote and runs for Oracle is quite scalable (the oft-mentioned Aria People employee directory).  Yesterday (05-MAR), there were 2.1M page views on this system with a median page rendering time of 0.03 seconds from 45,314 distinct users.  The busiest hour saw 129,284 page views through the APEX engine (35.9 page views/second).  If another team within Oracle wrote this same system but didn't tune the SQL like we did, is that a reflection on the scalability of APEX?  And if the answer to that question is "no", then is the hardware configuration all that relevant?

Back in 2007, my manager Mike Hichwa took a draft note that I wrote and published an article for  Oracle Magazine entitled "Sizing up Performance".  There is a very simple formula which can be used to estimate the throughput of an APEX application.  This isn't going to help you determine how much hardware to buy or how to estimate the size of your VM, but it will help estimate (in back-of-the-napkin form) how scalable an existing APEX application will be on an existing system.

With all this said, we, on the Oracle Application Express team, have been deficient.  At a minimum, we should have a list of systems developed by our customers, with specific information about the hardware configuration, purpose of the system, and number of end-users served.  Maybe we should also obtain the level of expertise of the developers.  We will gather this information and publish it online (without specific customer names).  If nothing else, this can serve as the foundation for extrapolation by architects and designers.

Friday, January 31, 2014

Oracle Application Express 5.0 Early Adopter 1 now available

We are quite happy to announce the beginning of the Oracle Application Express 5.0 Early Adopter program, at  This is our open-to-the-public beta program where we encourage our customers (new and old), and also those just interested in Oracle Application Express, to kick the tires of our forthcoming release.  Click the big blue "Request a Workspace" button to get started.

You'll notice right away that the authentication for Oracle Application Express requires an Oracle account.  This is the same account you would use for many Oracle sites, including the OTN Community discussion forums.  If you don't have an account, then simply follow the instructions on the login page to "Sign up for a free Oracle Web account".  However, ensure that you specify the same email address as your Oracle Web account when requesting a new workspace.

The list of new features in Oracle Application Express 5.0 Early Adopter 1 can be reviewed here.  Not everything is ready for prime time, so these are the features we are specifically looking for feedback.

We plan on having an Oracle Application Express 5.0 Early Adopter 2 program.  When that happens, the entire instance will be rebuilt, so don't get too married to any of the data or applications - they will be removed.  Also, there is no guarantee that the applications you create can be imported into any future release of APEX.

The Known Issues will be populated soon, as well the application to review your submitted feedback.  However, we encourage you to use this Early Adopter instance and provide your unvarnished comments.  We still have some miles to travel for Oracle Application Express, but we believe that this will eventually become one of the watershed releases for APEX and the community.

Thank you for all of your support.

Thursday, January 23, 2014

Happy Birthday, Oracle Application Express!

Happy birthday, Oracle Application Express!

In January 2004, Oracle Database 10g was released.  And bundled with the Oracle 10g Database distribution was an additional disc called the Companion CD, which included both Oracle HTTP Server, mod_plsql and this new tool called Oracle HTML DB 1.5.  This was the date of the first officially distributed and supported software from Oracle which has grown into today's Oracle Application Express.

To recap the years:

  • 2004  HTML DB 1.5 - Initial release
  • 2004  HTML DB 1.6 - User interface Themes
  • 2005  HTML DB 2.0 - SQL Workshop
  • 2006  Application Express 2.1 - Embedded with Oracle 10gR2 Express Edition (XE)
  • 2006  Application Express 2.2 - Packaged Applications
  • 2007  Application Express 3.0 - Flash Charts, PDF printing, Microsoft Access migration
  • 2008  Application Express 3.1 - Interactive Reports, runtime-only installation
  • 2009  Application Express 3.2 - Oracle Forms to Oracle Application Express conversion
  • 2010  Application Express 4.0 - Plug-ins, Dynamic Actions, Team Development
  • 2011  Application Express 4.1 - Data Upload, improved Tabular Forms, Error Handling
  • 2012  Application Express 4.2 - Mobile support, mobile and responsive themes, RESTful Web Services

Oracle Application Express has been delivered with every version of the Oracle Database since 2004.  Beginning with Oracle Database 11gR1, Oracle Application Express moved to the database distribution and was treated as a "standard" database component.  Beginning with Oracle Database 12c, Oracle Application Express is installed by default in every Oracle database.  Oracle Application Express is the development framework for both Oracle Audit Vault and Database Firewall and the 12c Multitenant Self-Service Provisioning applications., the customer evaluation instance of Oracle Application Express, keeps chugging along - with an average of 1,000 new workspace requests per week.

In 2004 / 2005, customers would "dip their toe in the water" with HTML DB.  Today, Oracle Application Express is an approved development framework in countless large enterprises, managing literally hundreds of applications on a single Oracle Database instance.

To say that Oracle Application Express has matured over the past 10 years can be a bit misleading.  Some perceive maturing as "getting old".  I would much rather characterize this as adapting and evolving...and growing.  The story with Oracle Application Express is far from over.  Not only are there vast improvements which need to be made in the framework itself (especially with respect to developer productivity and enterprise deployment), but we need to improve in many other ways - documentation, examples, videos, communication, usability, and a plethora of excellent customer-provided enhancement requests.  The industry is constantly changing and evolving as well, and Oracle Application Express must adapt and evolve and, in some respects, lead.

For now, though, let me just offer a simple "Happy birthday!"

Friday, December 13, 2013

Oracle Application Express 4.2.4 now available

As an early Christmas present / late Hanukkah present, Oracle Application Express 4.2.4 is now released and available for download.  If you wish to download the full release of Oracle Application Express 4.2.4, you can get it from the Downloads page on OTN.  If you have Oracle Application Express 4.2, 4.2.1, 4.2.2, or 4.2.3 already installed, then you need to download the APEX 4.2.4 patch set from My Oracle Support.  Look up patch number 17607802.

As is stated in the patch set note that accompanies the Oracle Application Express 4.2.4 patch set:
  • If you have Oracle Application Express release 4.2, 4.2.1, 4.2.2 or 4.2.3 installed, download the Oracle Application Express 4.2.4 patch set from My Oracle Support and apply it.  Remember - patch number 17607802.
  • If you have Oracle Application Express release 4.1.1 or earlier installed (including Oracle HTML DB release 1.5), download and install the entire Oracle Application Express 4.2.4 release from the Oracle Technology Network (OTN).
  • If you do not have Oracle Application Express installed, download and install the entire Oracle Application Express 4.2.4 release from the Oracle Technology Network (OTN).
As usual, there are a large number of issues corrected in the Application Express 4.2.4 patch set.  You can see the full list in the patch set note.

Some changes in the the Oracle Application Express 4.2.4 patch set:
  1. Large number of bug fixes and administrative UI enhancements to almost all of the Packaged Applications.
  2. A new packaged application - Standards Tracker.  This was the idea and creation of David Gale, who used this application to help manage the standards across all of the other packaged applications delivered with Oracle Application Express.
  3. Numerous bug fixes in the underlying Application Express engine.

Friday, November 08, 2013

You don't lack APEX lack Oracle skills

For years, I've been fighting the perception by some customers that "APEX is slow".  Back in 2010, I opined about the corporate architect who didn't like Oracle Application Express because it was slow and couldn't scale.  What helped him form his opinion?  They had a business unit that developed a large number of applications, and the applications were slow.  Granted, the applications were developed and deployed in an uncontrolled and unconstrained environment, but that didn't matter to him.  All he (and their CIO) knew was that these were Oracle APEX applications, and they were slow.  Perception is everything.

I had an email exchange with a contact at this same customer this week.  He did mention the lack of "APEX skills" in the organization, but as I candidly told him, the vast majority of their issues were a lack of Oracle skills, not APEX skills.  They had many people, not necessarily proficient in data modeling or SQL performance tuning or Oracle, building applications intended to service thousands of concurrent end users.  As I told him:

I wouldn't focus too much on "APEX skills".  The problems you've had at your company are "Oracle skills".  How to design a data model.  How to write and tune efficient SQL access.  How to be aware of data security aspects.  How to author and maintain secure, efficient PL/SQL.  APEX is simply the veneer over a database application.
Anytime I start a new APEX application, I actually don't touch Oracle Application Express.  I usually start in SQL Developer Data Modeler and start designing the foundation of my application in a logical and efficient data model.  Once I have that perfected, it's usually quite straightforward to build the Web interface in APEX on top of this.

Just today, I received another shining example of this philosophy of "Oracle skills".  One of my favorite customers at a large enterprise in Europe, where they have hundreds of APEX applications deployed across the enterprise, sent me the following:

....we talked a couple of months ago about the weird visibility of APEX performance in companies. Just to give you another example, we also fight against the perception that APEX is slow, because people know that applications were built with APEX, and if the response time is bad, then APEX is slow.  It's that simple.  We had a complaint about a couple of APEX pages.  One page took around 15 seconds to render with a complicated chart, the other page took around 3 minutes to show up.  
The result of the analysis was - guess - bad DB design paired with bad SQL.  They had a history table with 1.5 billion records, bad partitioning and they populated all LOV filters from this table, distinct, order by.  At the end, the fact table was just 7 million records to filter from the LOV filter elements.  We just built up a new table with all distinct values of the dimensions (the data is loaded only once every night) as base table to serve all filter elements.  We dropped the unnecessary indexes and built a reasonable one and.....voila we were down to 2 seconds for the chart and 3 seconds for the page that took 180 seconds before.  
It's very hard to explain that APEX is neither fast nor slow, because it is not doing anything relevant for itself but just query the database with the good or bad SQL that the developers provide against a good or poorly designed Oracle database, which is more or less busy with 50 other projects and data loads.

If you look at the remedy to improve the performance of their application, it was little to zero APEX and almost all Oracle knowledge and skill.  By the way, creating a select list against 1.5 billion rows is going to take an equally long amount of time using .NET, PHP, Java, Jython, Perl, or whatever.

What's the intended takeaway from this blog post?  Understand the architecture of APEX and how it interacts with the database, but also invest in understanding the Oracle database, SQL and database design.