Skip to main content

APEX 4.2 Reporting - Book Review


Recently I was asked by someone from Packt Publishing to review the new book "Oracle APEX 4.2 Reporting" - in exchange for a free e-copy.

When I saw the table of contents, I already thought: "This is covering a lot...". And that turns out to be true - and one of the main points of criticism I have. It starts off with a whole chapter about Installation and Configuration. The title of the chapter doesn't say that, because it's called "Know your horse before you ride it". Thats one of the other points of criticism: The author is now and then to be trying to be funny and entertaining (in his own way). Another example: after a paragraph about installing the APEX Listener he writes: "APEX now has ears". And there are much more examples - to me it's annoying and distracting. But the major part of that chapter is very well covered by the installation documents that come with the different products. Another remarkable note is that the author suggest to use Internet Explorer 9 - something most developers try to keep away from as far as possible (please use Firefox and/or Chrome to develop ... just use IE when you have to do some end-user-like testing).

The second chapter, Conventional Reporting in APEX, is mostly about the Classic Reports. But before he covers that, there are some paragraphs about Authentication, the USERENV namespace and the CGI environment variables. Those paragraphs shouldn't be there (and they only cover those subjects very shallow). Then it immediately jumps into deep - so no easy start and building it up from simple to more complex per paragraph. Searching, Sorting and Grouping of classic reports are next. The author uses some very interesting and powerful SQL features, like "grouping sets", but they could be introduced more gently. Formatting the results, matrix reporting and pivoting are next. Again very nice stuff, but not very well introduced. Templating, hierarchical queries (with and without the recursive with clause), Trees, everything is in there. And apart from that, some JavaScript wizardry to change or style the outcome of the reports. Uploading (not very report-related imho) and different ways of downloading files are discussed. Also deleting records (from within a report) is covered - although the way the author does it, is overly complex (using a Dynamic Action with a JavaScript and PL/SQL part should do the trick as well). For the Ajax calls the author uses the old school, and undocumented, htmldb_get function, instead of the newer and better apex.server.process
Of course Tabular Forms are covered as well. But instead on how to handle checkboxes (or radio buttons) in a Tabular Form (always a pain) and the use of the APEX_ITEM API, he shows some (nifty) JavaScript function to change the item type (which could be solved more elegantly by using APEX_ITEM in the query itself). Also Master-detail reporting is covered - but too briefly.
Next are time series, using partitioning and some analytic functions. As these functions are so powerful, they deserve way more attention than just the two pages. And one page about the MODEL clause is also very very short....
VPD is also covered - but only the, real, EE-only variant. No mention of "poor man's VPD": sys_context.
All in all, a chapter about Classic Reports that covers more than just that (some parts belong in a separate SQL and/or Analytics chapter) - and doesn't cover all the real important stuff. 

Chapter 3, In the APEX Mansion - Interactive Reports, starts out with explaining the features under the Action menu. After explaining all this (and some side steps), the Icon View and Detail View is briefly discussed - I would love to see more about that here! Changing the Action menu with JavaScript and - especially - changing the looks of an IT using CSS are worthwhile to read. Also a good piece on how to get multiple IR's on a page is a good read. Then it goes over to pipelined-table functions (not very IR-related) and into what's seems to be a hobby of the author: the ODCITable interface. Such a very specialised a hardly-ever-used functionality, it should be better of in an appendix.
After that Collections are covered (these are way more important than the obscure ODCI stuff). But also too shallow.
So this chapter does cover some neat IR features, but some of the paragraphs are better of in a different SQL chapter or appendix. And also Collections deserve their own chapter.

Then it goes off with The Fairy Tale Begins - Advanced Reporting (although we already did some fairly advanced stuff). It starts with LDAP Authentication .... what does that have to do with this chapter is totally unclear. The next paragraph is about sparkline reports - so that's quite a change from the LDAP paragraph, but very cool and well written. The same applies to the slider column in a report. HTML charts is next - but what is missing is CSS charts....
Next is how to use XMLTYPE for reporting - not the most obvious choice - and why you would do this is not explained (because you can?). Just a small paragraph on Google Visualisations is next - describing not more than "it's there, you can use it". Some of the Anycharts Flash charts (not the HTML variants) are discussed briefly as well. I would suspect a whole chapter on charting in this kind of book...
A drag and drop calendar gets just a few lines - no examples, no code (apart from in the sample app), no explanation. 
How to show images in your report is discussed and how to show a detail report in a modal dialog box (there are more elegant ways doing that, i.e. using a plugin...). A short paragraph about wizards and hierarchical queries and regular expressions concludes this chapter - explaining next to nothing.
Conclusion: The sparkline and slider stuff was relevant to this chapter; Charts deserve more; the rest is so shallow covered it's next to useless.

Chapter 5, with the "exciting" title "Flight to Space Station: Advanced APEX" grabs a lot of (mostly unrelated) stuff into one chapter - all very briefly touched: PSP, Java in the DB, FusionCharts (why here, why not with Google Visualisations and Anycharts e.a. in a separate chapter?) , tag cloud, creating plugins (there is a whole book on that...), websheets (could be covered in depth in a separate chapter), mail configuration, APEXExport utility, OLAP Cubes (interesting, but no explanation how to actually use them in APEX Reporting), Advanced Queueing, "Other APEX Features" (APEX Views, Page locking , Advisor , etc), the APEX API (but you get more info from the documentation), some database API's (also here: you're better off reading the docs). 
So this chapter covers a lot of totally unrelated stuff, and so shallow, the book would be better without it.

Next, Using PL/SQL Reporting Packages, Jasper and Eclipse BIRT,  makes more sense. PL_PDF is mentioned, FOP and Cocoon as well, but most attention goes to Jasper and BIRT.
So this chapter isn't too bad, although as it's all about printing that might have been in the title - and the most obvious one, BI Publisher, is missing here (but magically returns in a later(!) chapter).

Integrating APEX with OBIEE shows the authors background: he's obviously an OBIEE expert. A lot of information about how to use OBIEE (might be more detailed books out there, but you'll get the hang of it) and how to integrate it with APEX. And then there's the (long awaited) BI Publisher paragraph. Described is how to create a report just using BIP and how to integrate it with APEX using web services - and very briefly using a direct link to the BIP report. Although that last part is useful, the "standard" way of integration (using Report Queries and Templates in APEX) isn't even mentioned....
If you know nothing about OBIEE and want to know more, this is your chapter. IMHO, the BI Publisher stuff should be in the same chapter as the other printing options....

Chapter 8, All About Web Services and Integrations, starts with how to extract data in a readable format from an XML document returned by a web service. Demoing the standard APEX features on using web services might be a better start...
The APEX Listener's Resource Templates is old stuff nowadays (not on the time of writing though), you'd better check out the built-in RESTful web services feature.
A long paragraph bout BPEL (also author's expertise) follows, but without any real connection with APEX - apart from the fact you can call a BPEL web service from within APEX. Next paragraph is about SAP Crystal Reports - very briefly - and this should be in the same chapter as the other printing solutions - if at all...
A few other strange paragraphs, Migrating from MS Access and Migrating from Forms/Reports, are thrown in as well (the relation with the chapter title is missing I think). Integrating with the Google API is an interesting piece and definitely worth a read. Integrating with Oracle R Enterprise concludes this chapter. Also somewhat obscure, but maybe it's interesting to you.
Although the title is promising, I am missing a lot of (web services related) content and I am getting a lot of stuff I didn't expect here - or in this book.

The final chapter, Performance Analysis, does point out some possible "performance bottlenecks", but not deeply. The parts about different indexes is way to shallow to be of any value at all. Performance enhancements on the web server side are also discussed - but not in depth either. The available database tools for performance monitoring are mentioned, but just enough to get into a conversation at coffee-machine-level. 
So alas nothing really worthwhile in this chapter. 

The appendices contain some info on SQL Injection Cross Site Scripting - which deserve a real chapter in their own - listener configuration and auditing. Nothing mind blowing....

So, it has been quite a long review. And what is my overall conclusion? First of all, the author is very knowledgable: he knows a lot detail stuff about a lot of different subjects. Second, the order of the chapters and paragraphs could be way better. "Printing Reports" should have it's own chapter with all the options in it - and the same holds for Charting. I think that's especially the publisher / editor to blame. And I am missing report-related stuff and getting a lot of stuff I wouldn't expect in a book called "APEX 4.2 Reporting".

All in all, there are some valuable pieces of information in this book, but it's not the best book ever written... In (a combination of) other books you'll find better and more well structured information.


Post a Comment

Popular posts from this blog

Refresh selected row(s) in an Interactive Grid

In my previous post I blogged about pushing changed rows from the dabatase into an Interactive Grid. The use case I'll cover right here is probably more common - and therefore more useful!

Until we had the IG, we showed the data in a report (Interactive or Classic). Changes to the data where made by popping up a form page, making changes, saving and refreshing the report upon closing the dialog. Or by clicking an icon / button / link in your report that makes some changes to the data (like changing a status) and ... refresh the report.  That all works fine, but the downsides are: The whole dataset is returned from the server to the client - again and again. And if your pagination size is large, that does lead to more and more network traffic, more interpretation by the browser and more waiting time for the end user.The "current record" might be out of focus after the refresh, especially by larger pagination sizes, as the first rows will be shown. Or (even worse) while you…

Dockerize your APEX development environment

Nowadays Docker is everywhere. It is one of the main components of Continuous Integration / Continuous Development environments. That alone indicates Docker has to be seen more as a Software Delivery Platform than as a replacement of a virtual machine.

However ...

If you are running an Oracle database using Docker on your local machine to develop some APEX application, you will probably not move that container is a whole to test and production environments. Because in that case you would not only deliver a new APEX application to the production environment - which is a good thing - but also overwrite the data in production with the data from your development environment. And that won't make your users very excited.
So in this set up you will be using Docker as a replacement of a Virtual Machine and not as a Delivery Platform.
And that's exactly the way Martin is using it as he described in this recent blog post. It is an ideal way to get up and running with an Oracle database …

apex_application.g_f0x array processing in Oracle 12

If you created your own "updatable reports" or your custom version of tabular forms in Oracle Application Express, you'll end up with a query that looks similar to this one:
then you disable the "Escape special characters" property and the result is an updatable multirecord form.
That was easy, right? But now we need to process the changes in the Ename column when the form is submitted, but only if the checkbox is checked. All the columns are submitted as separated arrays, named apex_application.g_f0x - where the "x" is the value of the "p_idx" parameter you specified in the apex_item calls. So we have apex_application.g_f01, g_f02 and g_f03. But then you discover APEX has the oddity that the "checkbox" array only contains values for the checked rows. Thus if you just check "Jones", the length of g_f02 is 1 and it contains only the empno of Jones - while the other two arrays will contain all (14) rows. So for processing y…