Skip to main content


Showing posts from 2013

Oracle APEX Reporting Tips & Tricks - Book Review

Just after my not-so-positive previous book review, another author dared to ask me for my review. I asked him whether he was sure, and he - confidently - answered positive. This book, written by George Bara from Romania, is called "Oracle APEX Reporting Tips & Tricks", so - from the title -  it seems to be covering similar subjects as the previous one. 
Chapter 1, "Introducing APEX", covers the install of APEX and the creation of a first application. The good thing here is that the author doesn't rewrite the docs, but just links to them. Right after that he introduces the two main report types: the Classic Report and the Interactive Report.
Chapter 2, "Interactive Reports Basics", covers the cool options from the Action menu as well how a developer can dis- or enable these options. Also the Alternate and Icon view is explained, followed by a brief example on styling an IR using CSS. After that multiple download options are covered, including an ext…

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…

Creating RESTful API's with the APEX Listener

In REST you model the resources, not the actions. So you define a GET /apex/oow13/hr/employees instead of a GET /apex/oow13/GetAllEmployees. With REST you have six methods (with the most common associated database actions: GET (select), HEAD (select), OPTIONS (to get a list of methods supported by the resource), PUT (merge or update), DELETE (delete), POST (insert).
You can manage the REST resources from either within SQL Developer or APEX. Within SQL Developer the resources are grouped into Modules (like PL/SQL packages) with a URI prefix (like /hr) and within that Resource Templates. As an example you can define a GET request ( select * from emp where empno = :id ), then the URI Template of the resource will be something like employees/{id}. When you call the URL (like http://localhost:8888/apex/oow13/hr/employees/12345), this will result in a JSON string (or XML or CSV) containing the employee data of employee 12345. Within a query you can use "special" columns - startin…

Mobile Development with APEX 5.0

In APEX 5.0 a Tablet User Interface will be introduced. The idea is not that you will end up with three user interfaces (Desktop, Tablet and Mobile), but use te Tablet UI for desktop usage as well. You might even want to use the Tablet UI - as an common "average - for all devices. The plans are to include something like Themeroller into APEX 5.0. So you can 100% skin your application within APEX.  APEX 5 will include the jQuery Mobile features that are available on the moment that 5 ships. Because the current version of jQM (1.3) contains features as panels, reflow tables and column toggle (the current version of APEX ships version 1.2 - and "upgrading" to a higher version of jQM will not work for 100%). A new CSS Calender component will be available with more flexibility, so it supports drag and drop on mobile devices. jQM 1.4 will have some theme improvements: One theme (swatch A), a flat look, switching from icons to SVG - so the images will look better on high reso…

Oracle Database 12c Real Application Security and APEX

Oracle Real Application Security (RAS) applies security policies at the database layer. So those policies are applied to the data and is not relying on the security built in into an application (like VPD). Security is applied to direct connections. Policies are stored in an "Identity and Policy Store".
As an example an employee can see some public information of other employees and all his own data. A manager can see all data of all his employees. So you need both column and row level security. To accomplish the row level security RAS uses "Data Realms". And privileges - like viewSSN and viewSalary - will be assigned to columns. Each Data Realm ha an associated ACL with grants. So a policy is a collection of Data Realms and associated ACLs.  A user is granted a role. A role is a combination of policies. And this works for all applications that access this data, either APEX, SQL*Plus or whatever.
In APEX you have to create Authorization Schemes that query the RAS A…

OOW 2103 : Get your own Database Instance in the Cloud

Today the "Database as a Service" has been announced. Not to confuse with the "Database Platform as a Service" that was announced last year and is available since early this year. With the "platform" service, you get one schema within a database. With the "database" service, you get ... a database - either 11gR2 or 12cR1. And you have access to the operating system etc. So in fact you get your own VM with an Oracle database pre-installed.
And within this service, there are three levels defined: 1. Basic - The DB instance is managed by yourself. 2. Managed - The DB instance is managed by Oracle. Backup and recovery, patching and upgrade, all taken care of. 3. Premium Managed - Like Managed, but in a RAC environment, so more highly available.  I haven't seen any SLA's yet - as you would especially in a Premium Managed Service. Prices are also still unclear. But it is offered on a monthly subscription basis.  The good news is, all ACE DIrec…

OOW 2013 : The new Oracle In-Memory Database

Today the new In-Memory technique for the Oracle database has been introduced. This is not something like TimesTen(+) or so, but a new way of querying data. The concept is like this: All tables that are marked for keeping data in-memory, will be "stored" using both row and column format. The good old row format will be used for the regular OLTP systems, while the column format is used for queries. To speed up those queries, the column format will be held in-memory - and in-memory only. Only the row format goes to disk, just like nowadays.  To keep all data in sync, the in-memory data will be marked stale during a DML option that affects that data. Therefore the overhead added to DML statements is very small. Upon request the in-memory data will be updated.  This in-memory data, a.k.a. the "Column Store" is an optional component of the SGA. So you have to switch it on. And you can pick which tables, which partitions, which columns and whether you want it distribute…

My OOW 2013 Expectations

Redwood Shores, Thursday September 19. 5:30 AM. 
5:30 AM? So I must be in the "wrong" timezone.... Just like most European based Oracle ACE Directors in this hotel I woke up early. Not as early as some others - I noticed tweets from around 2:30 - but nevertheless early. After a decent flight with some sleep and a few movies we arrived at San Francisco airport yesterday around noon. We were already warned by the flight attendant about the long lines for immigration, but this wasn't quite what we expected: Usually the line starts somewhere in the big hall but this was about 200 metres before the hall! So expectations were set at a 3 hour waiting time ... After one hour we arrived in the hall and then it showed that half of the hall wasn't used for the queues - which is a good thing, because then there are less people in front of you as you night expected - but also half of the booths weren't used. And that was what caused the long delay in the first place. Probably…

Running an APEX app native on an iPhone - revisited

Almost two years ago I wrote two blogposts about how to run an APEX application as native as possible on an iPhone (see the demo and the details). But that was so long ago, using Phonegap 1.3.0 and APEX 3-something.
Nowadays we have APEX 4.2 with more mobile capabilities and Phonegap is rebranded to Cordova and already on version 3. So the question is: Is it still possible, and is it easier of harder? The answer is: it is still possible and way easier. Especially the command-line-interface (CLI) of Cordova helps a lot to get up and running within a minute. And for getting data in and out of the database we now can use the RESTful web services of the APEX Listener.
Details will be revealed later, but here's the "live demo".

Get your abstracts in for the best conference of 2014 !

We're just in the second half of the 2013 and now we're already talking about "The best conference of 2014"?  Yep! And this conference will be held on Jun 22-26 in Seattle. So I am talking about ODTUG Kscope 14. What else could it be .... And the best conference needs the best preparation.  And as content is one of the main things that makes this conference awesome, we need our content lined up as soon as possible. And that's why the call for papers is open now and will be open until October 15 (no extension!). So you still have two months to come up with a subject you're passionate about. If there is something you want to share with the Kscope audience, this is your chance. As we need content for all attendees, from beginner to advanced and for every subject, I am pretty sure you can think of a few things that you can talk about! And this is also an excellent opportunity to dive into a subject and learn yourself: Just submit an abstract of something you want…

APEXposed 2013, Sep 10 and 11 in Montreal

While KScope seems only a few weeks ago, ODTUG already announced a new great APEX event!
APEXPosed 2013 will be held on September 10 and 11 in Montreal. The agenda looks great, with a nice mix from local and international speakers. The APEX Development Team will be represented by both Joel Kallman and David Peake. And, so just a few weeks before Oracle Open World, we might get a sneak peak of APEX 5 ... you never know! Other international speakers are Steven Feuerstein, Scott Spendolini, Anton Nielsen ... and myself. The complete line up can be found here.
It'll be my first visit to Canada and I am really looking forward to it. I've planned to explore the city a few days before the conference, so if you've got tips, must sees etc., let me know!
So register now for this event (, I am sure it'll be awesome!

Two articles in one week...

Last week I presented at the OUG Scotland conference. To my surprise, I received the latest, 50th edition (!), of Oracle Scene - the publication of the UK OUG during registration. And, less to my surprise, there was an article in there written by yours truly: "Building Mobile Web Applications with Oracle Application Express". 

And when I got back home, I found the magazine of the Dutch Oracle User Group on the doorstep, with another article authored by me. Not really "another" as it is just a translated version (the English one was first...). 
Nevertheless, quite proud of publishing not one, but even two articles in well known magazines in the same week!

APEX Conditions and Performance

Thanks to a tweet of Scott Wesley (@swesley_perth), I stumbled upon this blog post by Tony Andrews. In that blog Tony lists a few examples of APEX Conditions and concludes that the declarative approach must be the best way of doing it. Although his assumptions and conclusions makes a lot of sense, I wondered whether it was actually true and what the difference in performance would be. So I created a very similar test myself and take a look at the debug output. And here are the results (in microseconds) ...

Type Statement Run 1Run 2Declarative...<nothing here>...653 667 PL/SQL Expression"...Execute Statement: begin wwv_flow.g_boolean := :P3_JOB_ID = 'AD_PRES'; end;” 1533 1823SQL Exists...Execute Statement: select count(*) from sys.dual where exists (SELECT NULL FROM DUAL WHERE :P3_JOB_ID = 'AD_PRES')16842023PL/SQL Function"""...Execute Statement: declare function x return boolean is begin begin" return :P3_JOB_ID = 'AD_PRES'; end; &quo…

Using LDAP for Authentication and Authorization within APEX

One of my current customers would like to use their LDAP (Microsoft Active Directory) server for authentication and authorization of APEX applications. Of course we tried to set up a standard LDAP Authenication that's available within APEX. But we couldn't get that to work. Maybe it has to do with the fact that the client stored their Users within Groups within Groups within .... . Or maybe it doesn't do a full tree walk in the directory. Or maybe it is just because it is Microsoft - and not Oracle Internet Directory (OID). So we moved to a custom Authentication using the DBMS_LDAP functions (and some examples from the Pro Oracle Application Express book and Tim Hall - a.k.a. Oracle Base).

One of the issues we encountered that we wanted to use the user's login name, like "jdoe" and not his full name ("John Doe"). And the login name is stored in the "sAMAccountName" attribute. But authenticating using just "jdoe" didn't work. …

How attending #Kscope13 can change your life ...

Wow. Doesn't that sound dramatic? It sure does. But in a weird way it also might come true - as I have experienced in the past.
The first Kscope conference I attended was five years ago. And purely by coincidence that was in New Orleans as well! That first year I met all the people I knew from the forums, blogs etc. I had never met one of them before in real life. But due to the relatively small size of the conference and the specific open "Kscope culture" (you can just step up to anyone and start talking!), getting in touch with whoever you like is quite easy. So contacting the "guru's"  - as I saw those people back then, I know better know; now they're all "just" friends - has never been easier!
And what did that bring me? After that Kscope I focussed more on sharing my knowledge by blogging, answering questions o the OTN Forum and presenting.After a few years Oracle rewarded my efforts with an ACE award (and I got an assignment as ACE Direct…

Mark your calendars: May 14 ODTUG's Expert Panel on APEX !

On May 14, the second online ODTUG's Expert Panel will take place.  This time it's all about Oracle Application Express!!

So tune in on May 14, at 12:00 EDT (that's 5PM in the UK, 6PM CET).
The panelists are: Dietmar Aust, Dan McGhan and myself.  Patrick Cimolini will act as the moderator.
During this session you can ask any APEX related question to one of the three (or all three) panelists. And that can be either via chat or by voice - like a talk-radio show...
You can register here.
Oh, and did i mention it was FREE ??
Virtually see you there! Roel

New book: Oracle Application Express for Mobile Web Applications

Very recently a new book came out: "Oracle Application Express for Mobile Web Applications". This book is aimed at developers with some knowledge of APEX and want to know more about building web applications with APEX (of course) especially aimed at mobile devices (iPhone, iPad, and other brands of smartphones and tablets). And although I had promised myself never to get myself involved in writing another book after "Expert Oracle Application Express" (because it is sooo much work), I did get lured into writing this one by Dan McGhan.  Alas, due to personel circumstances, Dan had to step out of this project, but this vacancy has been filled in more than adequate by Christian Rokitta and David Peake.  Although I didn't receive my own author copies yet, David was so kind to bring his ones over during last week's APEX World in The Netherlands. And here is the picture of the unofficial launch of the book.
Before I forget, you can order the book on Amazon... I …

Help! APEX is blowing up our memory!

That's the message I received from a DBA friend who ran into this problem at her customer site. What really happened was that a couple of times a week one of the APEX processes was killed by the operating system due to reaching the limit of 16Gb memory. 16Gb!
And this resulted in an ORA-04030 message in the Oracle Database. So I went to that client site and we dove into the database log files, the APEX logging, the access log files and the error log files of the web server. Every time we noticed calls of WWW_FLOW_FILE_MGR.GET_FILE that seemed to be the problem. But the application wasn't huge, just 25 pages. Nothing complex, all pretty straight forward. So we started to look for the origin of those calls.
Just like in a lot of applications, this application also had a few images on the top of the screen (like Home, Logout, etc.). Just four or five on every page. However, these images were stored inside the APEX Repository, using the #WORKSPACE_IMAGES# substitution string. And …