Pro Oracle Application Express: My first thoughts

"So why is it called that?" a colleague asked me, looking at the copy of Pro Oracle Application Express lying on my desk. "Do they mean pro as in 'professional' or as in the opposite of anti? Either way it's not a great title, is it?"

I shrugged. I'd been waiting for this book for a week and a year (ordered: September 11, 2007 - received: September 18, 2008) so I wasn't about to quibble about its title. What I was curious about was if, during its year-long gestation period, the authors had kept the book up to date with the rapidly-changing world of Apex - after all, version 3.1 had come out in that time. I searched the index for 3.1's most impressive feature, interactive reports. No luck; not one mention. Not a good sign; had they delayed the book for so long that it was born outdated?

Flicking through the book didn't fill me with confidence either. I spotted a reference to the htmldb_application package. Nothing wrong with that, but I did wonder if it was the authors' preference or maybe a sign of how long ago the book was written that they hadn't used apex_application instead.

Not exactly filled with confidence I settled down to work my way through the first few chapters. I noted that the authors hadn't resorted to that chatty, overly-informal tone that many textbook authors use these days. Good for them; when I'm studying Application Express I don't need to feel like I'm having a beer in the pub with my mates. (When I'm in the pub, if you want to know, I start out talking about football and end up, when I'm thoroughly drunk, swearing I saw a UFO hovering over my local KFC in 2003. Not one mention of Apex.)

I also liked the way the authors approached the subject of Apex, the sequence in which they dealt with its various topics. Too many Oracle textbooks read like freeze-dried reference books; this book, I noted, seemed to have found the right middle point between reference book and those annoying "for dummies" books.

Some topics in the book struck me as a little strange. For instance, there's a bit about VPD, a useful subject for an Oracle developer to be knowledgeable about but not exactly Apex. I didn't mind this though; I'd been planning to read up about application contexts for a while now, so I was grateful for their concise explanations on the subject. On the other hand when I came across 2 pages demonstrating to me that putting double quotes around table names made them case sensitive I must admit that I felt as if I was being talked down to.

Two paragraphs ago I said that I liked the sequence in which the authors lined up their various topics, and that's the truth. However, I wonder if I would have found this book so welcoming if I was a complete Apex newbie. I dunno; maybe not.

So maybe the pro in the title does stand for 'professional', because for someone like me who has dipped their toes in Apex (and therefore is partway between a newbie and an expert) this book is perfectly positioned. Reading back over what I've written so far I suspect that you might think that this is aiming to be a negative review: the complete opposite is the truth. I am very glad that I bought this book - and in some respects I am even pleased I had to wait a year for it. Because already I am using things I am learning from it to fine-tune and improve my application.

But I'm beginning to ramble; I should summarise. What you probably want to know is should you buy the book? Well, if you're a newbie the answer is yes. Be warned though that this book won't replace the resources of the web; you'll still need to work through the Oracle By Example docs - maybe before you dig into this book. If you're not exactly a newbie but still no expert, then I think this book is most definitely for you. (I've just been reading the chapter on the Apex dictionary, for example, and that alone is - almost - worth the cover price.) And if you're an expert? Well, what do I know? - I've only been working with Apex for 6 months. Why do you want to take advice from me? Huh?

APEX_UTIL.PREPARE_URL (or Better than sitting in a glass box)

You don't need me to tell you about Session State Protection (SSP). You probably know a lot more about it than I do, you snivelling show-off. But if, like me, you've only just enabled and configured SSP for your application and it's screwed up all your hard work then maybe you'll want to read on.

First things first, let me confirm what you already suspect: SSP didn't screw up your application - you did. The problem I have is that I'd built a number of links by constructing the URLs from the results of sql queries. (Hard-coding: tut tut)

Select empno, ename, 'f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno employee_link
from emp;

(Yes yes, I know that the above query is silly but I'm just trying to make a point.)

The thing is, the moment you configure and enable session state protection links like this stop working. Suddenly you start getting session state protection violation errors. This is because links that you construct yourself don't contain the checksum portion of the URL.

To resolve this we need to turn to a rather useful function: APEX_UTIL.PREPARE_URL. What you do is feed this bad boy your self-constructed link and it'll spit out a link including the checksum. Sweet.

Select empno, ename, APEX_UTIL.PREPARE_URL ('f?p=:APP_ID:1::APP_SESSION::::P1_EMPNO:' ||empno) employee_link
from emp;

And hey, all of a sudden your links start working again. Abracadabra! Eat your heart out, David Blaine: sitting in a glass box is NOT real magic.

PS: Yesterday I received my copy of Pro Oracle Application Express. Only 10 months after I ordered it. Hopefully I'll be able to write a quick review in the next few days.

Version control (or How the world really ended)

I'm at the stage in the life of my app where I am beginning to think seriously about version control. Being a true software developer, I am as gung-ho and devil-may-care as the best of them and I have, thus far, regarded my nightly database backups as application backups too (and, because Oracle Apex lives completely within the database you can do this, but perhaps it is unwise). But now that we've delivered a beta version to the customer I have to take version control a lot more seriously.

The Wikipedia page for Oracle Application Express pre-warns me: version control is ground upon which Apex stands unsteadily. The reason for this is, paradoxically, Apex's main strength: Apex is a joy because it lives wholly in the database and every change you make merely updates the FLOWS_03000 tables - however this means that it is so much harder to obtain source files for your version control vault.

What I have decided to do is this: For every major release of my application I will export my whole application (along with my theme and images) and save the files to my source control programme. However, for the many minor changes and bug fixes that will come between these major releases I will simply export and save the relevant pages. This way whenever I need to upgrade a client with a bug fix all I'll have to do is send them the page files and some sql updating the FLOW_VERSION column of the WWV_FLOWS table (which is where Apex stores the version number of your application).

All of this, I admit, sounds more than a little fiddly. It means I'll need to keep an eye on every page I update for every bug fix, but I guess this is no different from keeping an eye on every form I update when working in Oracle Forms.

Having said that I'd very much like to hear what you do to version control your Apex application.

On a completely different note, am I the only one who has noticed that the week the Large Particle Hadron Collider was switched on is the exact same week the financial markets went into complete meltdown? Coincidence? Hmm, they did warn us that it'd bring about the end of the world.

I'm just saying.

A few issues around deployment (or How the world ends)

Apparently, whenever people believe that the world is about to end they tend to react in one of two ways. They either get very prayerful and (re)discover God, or they let go of all their inhibitions and go wild with orgies and alcohol and such. I live in a block of flats, right above a pair of good-looking women, so with all this talk in the news of recent of the possibility of the Large Hadron Collider swallowing the earth in a black hole, I guess it's obvious which way I was hoping they'd go.

Large Hadron Collider or not, I have had a sense of ending of recent because this week I have finally deployed the Apex application that I have been working on (and bitching about in this blog) for the past few months.

I have learned a few things this past week. For instance, I spotted that you can enter a version number against your application (under Shared Components). Ah cool; I was installing my application for user acceptance testing so I entered a version number of 0.9. I applied my changes and ran my application and -

... nothing. Eventually I realised that I would have to edit my page templates to show the version. To do this I added #APP_VERSION# to the footer and voila! (I know I make this complaint all the time, but it's crazy that I had to search for close to an hour to find out about #APP_VERSION#. Go on, stick "#APP_VERSION# Apex" into Google - it's practically a googlewhack!)

Next thing I found out (although I concede that this might be due to some mistake that I am making) is that if you export an application as Run Only (as opposed to Run and Build) you can still import it as Run and Build - thus exposing all the code behind the app. So how am I supposed to protect my intellectual property from the prying eyes of my client - or am I missing something here? Also I found that if you import an application as run only you can't import images into it.

Other than all this - and to be fair all of this is rather insignificant - the deployment of my application went really smoothly. Not a thing to worry about.

Except that pesky Hadron Collider and the end of the world. Forgive me, I've gotta go now: I've got me a bottle of champagne; I'm gonna go knock on the flat downstairs.

I don't know for sure how the earth started, but let's see if I can end it with a big bang.