It’s becoming a little too easy to blame everything on the bankers these days: I almost feel like I’m siding with the bullies in beating up the frail kid with Coke bottle glasses. But it is true that with the ramshackle state of today’s economy, small companies embarking on their first Oracle Application Express (Apex) project cannot afford the luxury of making too many expensive mistakes, or trying to pick it up as they go along. It’s better – and a lot cheaper – to learn from someone else’s mistakes, to benefit from their experience. And that is where I come in.

I am going to assume that you’re building your Apex application on a pre-existing Oracle database, rather than one you’re creating from scratch. If this is the case, I would expect that your database, over time, has strayed from some of the normalization commandments that E F Codd brought down from Mount Sinai engraved on stone tablets: every real-life database does this. However, this means that you may benefit from taking some time out, before even creating your first Apex page, to ready your database for Application Express.

Tables with composite primary keys:

Perhaps the greatest draw that Apex has is its  integration with Oracle databases, the way it enables developers to practically magic up reasonably complicated web pages in mere minutes. To add a fully editable tabular report, for instance, all the developer need do is pick the table, inform Apex which columns make up its primary key, choose which columns to display, press the ‘run’ button and voilà, it’s done.

When creating a tabular report, Apex is able to cope with tables with more than a single column as their primary key. However, it draws the line at two. I imagine this is sufficient in the overwhelming majority of cases, but I would be surprised if every reasonably-sized database didn’t have a few tables that fall foul of this restriction. A quick query would tell us:

Therefore, in readying your database for Apex you are faced with your first dilemma: do you take the metaphorical bull by its proverbial horns and rebuild the offending tables? That decision would depend on the number of tables, and how deeply they are ingrained into the DNA of your database packages, procedures, and applications. There is nothing intrinsically wrong with composite primary keys, and I don’t think you should incur the expense of changing them just because you want to build Apex applications.

The alternative is to build views based on your offending tables, giving them a single primary key column (my personal preference is to concatenate all the table’s actual primary key columns to create a string that I then alias as view_id). You will need to arm these views with instead-of triggers to kick in when the user inserts, updates or deletes a record in the base table. Once you have done this, you can build your Apex pages using these views, creating tabular forms that utilize your views as if they were mere unremarkable tables.

Here’s what I mean: in one of the databases that I use at work we have a table (school_history) in which we record children’s education history – which schools they attended, between which dates, and that sort of thing. The primary key for this table is an id for the child, an id for the school, and the date on which they start at the school. The table looks something like this:

School_History:

            Child_id           number(8)

            School_id        number(8)

            Start_date       date

            Leave_date     date

            Full_time         varchar2(1)

            created_user   varchar2(30)

            created_date   date;

Because this table violates Apex’s two-column primary key rule, we had to overlay it with a view before we could turn it into a tabular form. Easily done.

And once we had our view and the instead-of trigger that we created to accompany it, we could continue creating our Apex page with no trouble.

The good news is Apex 4.1, which is in that exciting early adopter stage at the moment, turns this whole problem to dust by allowing you use tables’ ROWIDs instead of their primary keys. But until it becomes an official release we’re still going to need this work-around.

However, if your database contains a large number of tables with multi-column primary keys, creating views and their instead-of triggers will probably fade from being a simple, smart solution and quickly degenerate into drudgery itself. The answer, of course, is to generate these views and triggers automatically using a script that will trawl your database, and identify the affected tables and their primary key columns. I have written such a script (script1.sql) and attached it here. Please note that it names the views it creates using the format I adopted in the example above: [table_name]_V.

Once you’ve run the script or created your views and triggers manually, if there aren’t that many affected tables in your database, you’re one step closer to being ready to create your first Apex application.

The USER function

Oracle’s in-built USER function is a pretty useful gadget. Among other things, you probably use it when you tag database triggers onto tables as a measure of basic auditing, storing the username of the person who created or last modified a record.

With Apex, however, interaction with the database is carried out via a middleman with a default username of APEX_PUBLIC_USER. This enables Apex to insert a layer of security between itself and your database. It is also the user to whom execute privileges are granted on Apex’s many packages and procedures.

The downside to all this ingenuity is that it emasculates the USER function whenever it is triggered by an action from within an Apex application. Instead of returning the triggering user, the function returns APEX_PUBLIC_USER.

Apex comes with a handy replacement function, the less snappily named apex_application.g_user. This function, when triggered by an action from within an Apex application, returns the actual username of the user and not just APEX_PUBLIC_USER; when called from outside of an Apex application it returns null. As a consequence, when used in database objects it is usually paired with the USER function to handle both possibilities, as follows:

However, it is the easiest thing in the world to get carried away by the excitement of building that first Apex application and forget to make this change. It’s an insidious error too – the triggers and packages in your database that employ the USER function will continue to work, collecting and depending on useless data. It’s only when, some time later (and it may even be weeks or months), you need to use this data, that you will find your records littered with APEX_PUBLIC_USERs.

The solution, as above, is to track down all the instances where you use the function and replace them with apex_application.g_user. Fortunately, the data dictionary has armed us with an easy way to search through our code and track down the places where we have used the USER function. A quick query on the user_source view or, if you are only interested in the times the function is called from within a trigger, the user_triggers view, should give us the answers we need:

Okay, so the query is unforgivably inefficient, since I’ve kept it generic, but after a slight pause it should fulfil its task. If the number of objects involved is small, the wisest and safest thing to do is update them manually. Writing a script to carry out a blanket update of database objects is an action that I will only ever recommend as a last step. We database developers are a cavalier lot, and often we choose what’s interesting or ‘cool’ over what’s necessarily sensible. That said, if you find that you have many dozens of triggers or packages to update you may have no choice. At work, the database I was building my Apex application on top of contained a few hundred tables, each one towing along its own auditing trigger that called on the USER function. To update them manually would require the patience of a saint. The only viable solution was to write a script that would seek out the USER function in my triggers and substitute them with nvl(apex_application.g_user,USER).

I decided against writing a script to automatically make the same change in my other database objects – packages, procedures and functions. Too risky, I felt: the triggers I was blindly updating were relatively homogeneous and so the chance of me unwittingly causing irreparable damage was small, or at the very least contained. But if I carried out such an automatic update on my other database objects, I could conceivably be mopping up the debris for weeks to come.

The attached script (script2.sql) fingers through the database, tracks down triggers that employ the USER function, replaces it with nvl(apex_application.g_user,USER) and then rebuilds the trigger. It ends by reporting on any disabled triggers. These will need to be investigated individually; hopefully, there won’t be too many of them.

Conclusion

As a teenager, I used to enjoy hanging around our municipal swimming pool. It was mostly for the girls, but we also derived a certain cruel pleasure from watching their dads jump straight into the cold water without first dipping a toe in it to assess what they were getting into.

The stakes are a little higher when you’re diving into building your first Apex application. There will be surprises – hopefully, most of them will be pleasant. There will be moments when it’s all you can do to bite down on a swearword. I’m sure you expect that and, in some ways, don’t mind it. However, you probably also want to cut down on those problems that have the potential to grind your development to a halt for hours while you Google desperately for a solution or, worse still, poison your database while you work unaware. Hopefully, this article will help you some way towards that goal.