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.

Making the move from Forms to Apex: What a developer needs to know

It's been close to 5 months since I first dipped my toes in the waters of Oracle Application Express and whilst I am not foolish enough to yet claim the status of expert, I believe I've travelled far enough down the road to be able to advise those coming behind me.

This entry is aimed mostly at Oracle Forms developers who are thinking of taking up Apex. I know you: I was you.

The most comforting thing about Oracle Forms is its insularity - it's a small, closed world which one can easily conquer. All you need do is master PL/SQL and a few Forms trigger/validation tricks and you're basically there. But you and I both know that despite Oracle's half-hearted protestations the road Forms is travelling down is a dead-end. It's time to get out.

So what do you need to know or do if you want to jump ship to Application Express?

1. You already have the tools: The good news is that your PL/SQL skills are not redundant. Validations, page processes, LOVs and lots more in Apex are - or can be - coded in PL/SQL. Which means that you're practically ready to get started. However...
2. You do not have all the tools: I don't know about you, but as a Forms developer after I'd mastered PL/SQL I never really did that much studying. If you're thinking of transferring to Apex be prepared to go back to hitting the books. PL/SQL is useful, but it's not all you'll need. Because Apex is a web technology and is reasonably open you'll need to be familiar with other web technologies. You will definitely need to learn HTML and JavaScript; you'll probably want to acquaint yourself with Ajax and CSS. You may want to look into DHTML; I have needed to dabble with a bit of Flash. The list is endless so be prepared to study. However...
3. Don't expect to find any books: Cos there aren't any at the moment. I've had this book on pre-order for close to a year and it's been pushed back more times than Bruce Willis's hairline. However...
4. The internet is full of resources: You've somehow found your way to this blog. Good. You're obviously intelligent and discerning; probably drop-dead gorgeous too. No need to be modest about it. However you'll find the Apex forum even more useful. Also take a look at Oracle's documentation (the 2 Day developer's guide is a very good start, and if you work your way through the User's guide you won't need to spend thousands of pounds/euros/dollars/naira on an Apex course). You'll also want to keep an eye on the growing army of Apex blogs: the Apex Blog Aggregator is the best way to do so. With all of this you'll be ready to get started with Application Express. However...
5. You will want to install the ApexLib framework: the ApexLib framework is an Application Express add-on developed by a guy named Patrick Wolf. It's a pl/sql-javascript brew that adds an extra layer of user-friendliness and developer-friendliness to Apex. If you're a newbie you might not, as yet, appreciate it's full impact but - trust me - it's brilliant. (If you're a little worried that Apex should need an add-on, don't be. Instead appreciate the fact that you're moving to an IDE that allows the development of add-ons. We didn't get that in the closed shop of Forms! I don't currently know that many other Apex add-ons - tho you can buy some extra themes from ApexSkins - but I'm sure others will appear with time.) Finally...
6. You will want to bear in mind the differences between Forms and Apex: Perhaps the most frustrating thing when you start developing in Apex is actually a little thing. You have reduced control over the actual look of your application. You know how you can draw and size and align and position text items in Forms' WYSIWYG editor, right? Well, because Apex is a web technology you have no such luxury. It's a small thing but it may irritate you. But the fact that you even notice that tells you how short the learning curve is. I guarantee you that you'll be up and running in no time.

So should you make the jump from Forms to Apex? In my opinion you should. In my experience Apex has been welcoming, robust and reliable. In Apex you can develop forms as rapidly as you would in Oracle Forms: debugging them is not necessarily a breeze, but then debugging is more of a bitch in Forms.

Of course I cannot guarantee you that Oracle Application Express will enjoy the longevity of Oracle Forms - as a matter of fact, I doubt that in today's fast-changing world any tool will last that long. However, for the Oracle Forms developer, still hobbled by Forms' closed shop, it is - again this is just my opinion - the best introduction to the wider world of web technologies. So if/when Apex does disappear you and I will be armed and ready to attack the next big thing.

Building a report-builder (or my career as an inbred redneck)

New day, new requirement. Welcome to my life.

What my boss would like is a sort of halfway house between a set of pre-written, inflexible standard reports and a completely flexible (and thus complicated) report-building tool, such as Crystal Reports or Hummingbird BI. An additional requirement is that the solution I come up with should cost my company nothing but my time - and so going out and buying some third-party product is out of the question.

Time to get my MacGyver hat on.

My intention is to kinda reproduce (and improve upon) a form I previously created in Oracle Forms, in which I applied a thin layer of interpretation between the end user and the database, and then presented the user with a list of report columns, a list of report filters, and a number of report templates. The user can then select any combination of columns to appear in their report, and any combination of parameters to filter it by. And my job is to take this permutation (whatever it might be) and build the sql code needed to produce a report from it.

The way I achieved this in the earlier incarnation of my Oracle Forms flexible report builder was to back each report column and filter with a huge block of sql code and, depending on the columns and filters selected by the user, I outer-joined these blocks of code together to produce the report's sql code.

This system worked (and works) reasonably well, but has the disadvantage of producing humongously long sql scripts to create even the simplest of reports. Goodbye code efficiency and often, goodbye runtime speed.

And so I'm wondering if I can do things a little differently this time; perhaps by dynamically determining the relationship between tables on the basis of their primary and foreign key constraints. Sounds horribly complicated though (imagine having to write code that will automatically determine the connection between any set of 2 or 3 or 20 tables in a database of 1000 tables!) and I'm not as clever as I used to be - I've got too many pints of Foster's lager and too many hours watching American Idol with my girlfriend to thank for that!

And so I'm wondering if anyone else has had to create their own reasonably-flexible report builder who can give me any pointers? (All the developers in my office have been working there for far too many years and so our knowledge is now a little inbred - kinda like the Royal family or some redneck hicks from Kentucky - and it's sometimes difficult to generate new ideas in-house.) How do you generate the sql that you need? How do you ensure that your tables are correctly joined?

I know that none of the foregoing is exactly Apex-related (although I will be implementing my solution in Apex and probably be blogging about it here) but I do think the pl/sql involved might be quite interesting, so please do pitch in with your two cents' worth if you've got any ideas or suggestions.

APEX_APPLICATION.G_PRINT_SUCCESS_MESSAGE (or Donny From The Block)

There are known knowns
These are things that we know that we know
There are known unknowns
That is, there are things we now know we don't know
But there are also unknown unknowns
These are things we do not know we do not know.

Am I the only one that can see that there's a sort of rough meter to Donald Rumsfeld's famous words? Why hasn't anyone sampled it and laid it over a slammin' dance or hiphop beat yet? There's a chart number one in there somewhere - and I'm giving it to you for free.

Every day I bump into the unknown unknowns of Application Express, discovering - often by happy serendipity - cool things that it is capable of. It's almost like Apex is a character in Heroes, and I'm not yet aware of its superpowers. (Don't believe me? Set the boolean apex_application.g_read_users_bloody_minds to TRUE and watch your app fly!)

Last week I needed to dynamically output a success message to the screen. Yes, I know that whilst creating my processes, validations etc, I can specify the messages that I would like to send to the user - but how can I dynamically control that #SUCCESS_MESSAGE# region?

After a lot of searching I chanced upon this variable: apex_application.g_print_success_message. This is the variable that holds the success message that is output to the screen and it can be set programmatically.

APEX_APPLICAITON (which is the synonym for the FLOWS_03000.WWV_FLOW package) is a veritable chocolate box of useful stuff - chances are that you've used some of it in the past, but perhaps, like me, you are unaware of exactly how much it contains. When I get a spare afternoon (like that'll ever happen!) I intend to sit down and study it.

If only - and I know I sound like a broken record here - Oracle would deign to give us more than the for dummies documentation that is currently available! (I'm currently considering whether to go on this Advanced Apex course but I'm hesitant because I don't know how advanced it'll actually be: has anyone out there been on it?)

Talking about records I'm already working on my second single with Donald Rumsfeld:

Don't be fooled by the rockets that I've got
I'm still, I'm still Donny from the block.
Used to have a little, now I've got Iraq
I'm still, I'm still Donny from the block.

See you at number 1!