Learning Oracle Application Express (or teaching Angelina Jolie the MC Hammer dance)

I'm a Londoner so, unsurprisingly, I think London is the greatest city in the world (feel free to disagree if you enjoy being wrong). However I do accept that London isn't always that friendly to foreigners. Other major European cities have street signs and directions in the local language and English, but London seems to say to you, "Speak English - or get lost. Literally."

The Apex blogosphere can be a little like that; we often blog about advanced minutiae, perhaps to the exclusion of absolute beginners. This week 2 of my colleagues started learning Apex and so I've decided to put together a little Apex 101 study guide for their benefit and for that of other Apex padawans. (And if you say that this is only tangentially different from a post I wrote 2 years ago my answer is that all blog posts are kinda related; it's like that 6 degrees of separation theory that says, as far as I understand it, that I and Angelina Jolie are practically best friends.)

Where to start: There is only one place to start, and that's at the Oracle Apex website. Sign up for a workspace - it's free and easy. Have a look round, press buttons to see what they do: it's like developing with a condom on - safe, without commitment, and, hey, you can even do it drunk. However, the very best way to really dip your toes into the water is to work through Oracle's 2 Day Developers Guide [PDF link here]. When you're done with that you may wish to return here to flirt with the other tutorials.

Internet resources: Did you know that you're only ever 6 clicks away from an Apex blogger? Fact. We're like LOLCATS, adverts for blue pills for men, and YouTube videos of Rick Astley. Fortunately, the Apex Blog Aggregator has found the best blogs about Apex and, erm, aggregated them. Alternatively, you can go here or even here.

Another internet resource that you'll probably want to bookmark is OTN (which either stands for a. Oracle Technology Network or b. Orangutans Tangoing in the Netherlands. You decide.) The forum's search functionality is more Altavista than Google, but chances are that any question you've got has probably been asked and answered before.

Tutorials: The same way that with each regeneration since the first series in 1963, Dr Who has come back as someone slightly younger and better looking, each regeneration of Apex has resulted in a slicker, more intuitive IDE. However, I attended the Oracle University's 5 day Apex course and found it very useful. You may also want to have a look at SkillBuilders. I've attended some of their webinars and they've been excellent. Hey, why don't you put on a pair of geeky glasses, download some of their past webinars and pretend you're me? Whatever pedals your bike, pal.

Books: If you are the type who prefers to learn from books there is a growing catalogue of Apex books out there. Here's a list. And here are two reviews by a blogger who I respect immensely - and who Angelina Jolie is practically best friends with.

And that's it, my friend, all you need to get started. I'm not going to claim that this blog post has changed your life or that you came to me as a boy/girl and are leaving as a man/woman (unless it's taken you 20 years to read these 600 words), but hopefully you now got all the resources you need to hack your way through the Apex jungle. There's a lot more I can show you (have you seen my MC Hammer dance? No?) but my doorbell is ringing. It's probably Angelina.

Oracle Application Express 3.2: The Essentials and More: A review (or Things I've learned from TV)

I have a nephew who is 10. Just the other day he was asking me about ancient Rome and I was halfway through a long description of the lives of gladiators before I realised that all the 'facts' I was giving him had come straight from the TV show, Spartacus: Blood and Sand and not from a history book at all.

I realised, with shame, that almost everything I think I know comes from television. Ask me about psychology and I've got Lie to Me; ask me about forensic science and I've got CSI; ask me about the inner thoughts of women and I've got Sex and the City; ask me about Oracle Apex...

A couple weeks ago the good people at Packt Publishers kindly sent me a copy of the newest Apex book on the market, Oracle Application Express 3.2: The Essentials and More by Arie Geller and Matthew Lyon to review.

Here's what I think:

Don't write an expiry date into your name (or what I learned from Space 1999):

There's an elephant in the room; let's ignore it no longer. It is the most unfortunate coincidence of timing that this book on Apex 3.2 has come out the same week we've all been going crazy about Apex 4. Definitely embarrassing. But does this mean that the book, like Benjamin Button, is born already old and out-of-date? In some ways the answer, unfortunately, is yes; but until the market is flooded with Apex 4 books you should not let that put you off this book. The core of Apex remains unchanged, and this book covers that admirably.

Sequence does not matter (or what I learned from Quantum Leap)

I must admit that I had a serious issue with the sequence in which the authors chose to order their topics. I completely understand their thinking, but when I buy an Apex book I do not want to spend the first 40 pages reading about DOM objects, javascript, CSS and html before the first real mention of Apex, and another 40 pages before I get my first real look at the IDE!

However, I understand that technical books are not necessarily meant to be read sequentially. If you buy this book feel free to skip straight to page 79; you can return to the earlier pages later. And you should, because a lot of it is actually informative (I, personally, needed to read the section on shortcuts on page 63).

People talk about what they know (or what I learned from House)

"If you go to an oncologist with a headache," Dr House says in one episode, "he'll diagnose cancer; but take that same headache to an optician and he'll recommend new glasses."

This book is full of references to right-to-left languages and Apex's globalisation abilities. I'm guessing this is something one or both of the authors are interested in. If this is functionality that you require then you really must buy this book.

The story is in the details (or what I learned from The Wire)

Reading this book I made a list of subjects I felt the authors covered very well, and of those capabilities of Apex that I - not a newbie in Apex but far from an expert - was discovering for the first time. And I'm pleased to report that this list was much longer than I expected it to be. From whole chapters like the very useful section on Best Practices (Chapter 24: a must-read for any newbie) and the chapter on Deployment (Chapter 20), to little things using $v in Apex Ajax instead of $x(itemName).value.

There's more. Their work on the APEX_ITEM api was exhaustive (I did not know about APEX_ITEM.MD5_CHECKSUM). And, as an Oracle Forms developer, I was very interested in Chapter 23, which is about migrating applications from Forms. I got the impression that this was not necessarily something that interested the authors as much as it interested me, but it is good to find a book that covers the subject.

Nothing's perfect (or what I learned from Lost)

The chapter on migration wasn't the only time I felt that perhaps the authors were bored with a particular aspect of their subject. The whole section on the Apex IDE was often merely descriptive rather than explanatory.

In addition, I understand that they needed to include a section on the Apex SQL Workshop for completeness, but if you're an Oracle developer and you don't use TOAD, PL/SQL Developer, SQL Developer or even SQL*Plus then you really shouldn't be over here playing with the big kids. Apologise to the rest of the class and then leave the room immediately.

Conclusion

As I said earlier we cannot ignore the fact that Apex 4 casts its wide shadow over this book. Only you can decide how important a factor this is to you. Outside of that fact, this is quite a good book: if you are a newbie it will not get you started on its own - for that you will need the Internet's resources (start here: apex.oracle.com) - however, the authors' approach to Apex is often quite theoritical (and not just brutishly practical as many technical books can be) and this will furnish you with the background information you will need if you wish to do more than merely dabble with this technology.

If you are an experienced Apex developer the answer to the question "should I buy this book?" depends largely on your attitude to technical books. Personally, I like them. Google has pretty much killed off technical reference books, but I think there is still room for books like this that give you more information than a cursory web search can.

I'd love to tell you more, but I've got to go watch Jerry Springer. My nephew might have some questions about the life of the average, normal American.

Should you upgrade to Apex 4.0? (or Apex - with extra Katherine Heigl!)

There comes a time in the life of every man (and be warned that I spend all my time outside of work watching cheesy Hollywood romcoms and this might have warped my sense of reality) when his wife comes to him and says these words:

"Do you think I should get breast implants?"

In that instant the man is faced with the most crucial of dilemmas: it's not that he doesn't love his wife (who, in a Hollywood romcom, will inevitably be played with cutesy kookiness by Katherine Heigl), but surely an upgrade is always a good thing? After the implants she won't just be his darling Katherine Heigl, she'll be be darling Katherine Heigl - with extra Katherine Heigl!

Developers all across Apexdom are waking up to the same dilemma this morning, because of the following bit of news:


That's right, Oracle Application Express 4.0 has abandoned the shy coyness of beta mode and is finally fluttering its flirty eyes at us as a full release. Come and get me, boys (and girls), it seems to be saying. We've all always loved Apex - but Application Express 4.0 is Apex, in a manner of speaking, with extra Katherine Heigl!

Over the past 2 years my company has strengthened our commitment to Apex. It is now the first solution we look to for every project we get and, despite the fact that Oracle still seem intent on marketing it as an lightweight means of pimping your Excel spreadsheets, it has been able to cope with everything we have thrown at it.

Apex 4.0 wears its bling with ease: its UI has been tarted up impressively; its Dynamic Actions is a new, simple declarative interface for the creation of javascript/jQuery; its Plug-ins is unquestionably the first step in bringing an iTunes-style 'app store' that will enable developers share clever bits of code to Apex; and its Websheets gives developers the ability to easily add Google Docs-style collaboration to their applications. Oh, and interactive reports, the crown jewel of Apex's previous incarnation has been spruced up too. All in all, Application Express 4.0 is definitely a huge step forward from version 3.2.

So should we upgrade immediately? Should you?

In our Hollywood romcom Katherine Heigl will probably not get those breast implants. Instead she'll learn, through a hilarious sequence of events triggered by an encounter with a wise, old Chinaman, to love who she is inside. But, gorgeous as she is, you should be ashamed of yourself - why are you letting Ms Heigl take your technical decisions for you?

Here are the questions we are asking ourselves to help us decide whether to upgrade:

1. Can we upgrade? 4.0 requires, at the very least, a 10.2.0.3 database. Our development environment is a 9.2 database (we choose to develop using a database version that matches that of our most backward customer). Before we upgrade we'll need to sort this out.
2. What are the risks of upgrading? It is rarely wise to be the first guy to scream Leeroy Jenkins! and go rushing in to upgrade. It's usually wiser to let others make mistakes and then gingerly step over their bodies.
3. What are the risks of not upgrading? Minimal, in the short run. In the long run, of course, you don't want to fall too far behind.
4. Do you need to upgrade? Of course not. Apex 3.2 was a fantastic product and I assume that you are happy with the applications you have built with it. But 'need' is one thing, 'want' is another. The wheel was a great invention - but hey, wasn't it a sweet thing that Mr Dunlop improved it by wrapping it in rubber? Technology creates its own need: I bet that in a year you won't be able to imagine Apex development without plug-ins.
5. How easy is it to upgrade? Having not upgraded myself I can only pass on hearsay. I have heard that it's as easy as pie. However, do remember that you will need to upgrade the environments of all your clients too if yours is an application that is deployed in multiple locations.

So here's what I would advise: wait a little - not too long - and then update your development environment to 4.0. Play around with it (or, if you prefer, play around with it at http://apex.oracle.com) and discover how it can make your applications better.

So I guess what I'm saying is this: Yes, Katherine Heigl, do get your implants - but perhaps, one breast at a time?

The coming of Apex 4 (or 12 kilometres of features)

Take me to Bloggers Square and have all the other Apex Bloggers hurl rotting vegetables at me, because I have barely had a moment to glance at Apex EA4. Work gets in the way, unfortunately. However, what I have managed to see of it so far is impressive. Actually, it's a lot more than merely impressive, it's ... exciting. And how often do we get to say that about technology that doesn't have a name that begins with a lowercase i?

Anyway, I thought I should do my own little thing towards building up anticipation of this new version of Apex. And so, without further ado, as the vicar said to the actress: Here's my little thing...

Calling stored procedures from apex pages (or The Da Vinci PL/SQL Code)

Hollywood director Ron Howard had a problem. He'd been given the bestselling book in the world to turn into a movie. But while The Da Vinci Code was undoubtedly a page-turner, it did not readily lend itself to celluloid. After all, the story was about a professor of religion - not about a wisecracking, shoot-first-ask-questions-later action hero who likes to blow stuff up and make love to beautiful women. How do you make that exciting?

What Ron Howard did was this: he played loud, suspenseful mood music even in scenes where Tom Hanks is merely racing through cathedrals or reading books in the library. How else could he make a film about Roman catholic history seem exciting?

Last week, a colleague of mine was heading to a client's site for an important demo of one of our Apex applications. Sitting in the back of a taxi, 10 minutes away from the meeting, he tested the application by pressing a button and then ...

[insert loud suspenseful music here]

- an error!

HTTP 403

Forbidden

The requested operation is not allowed.

He was now 7 minutes away from a demo which could make or lose our company half a million pounds... 6 minutes away ... 5 minutes...

[more suspenseful music ... drums that sound like heartbeats ... ]

Tom Hanks quickly consults Google; it spits back a million unhelpful results ("want to buy cheap meds from Canada?") ... 4 minutes ... 3 minutes...

And then he read about the wwv_flow_epg_include_mod_local function.

Here's the deal with this function. It is in your Flows_xxxxx schema and if you wish to execute a stored procedure directly from your URL (http://.../apex/schemaName.procedureName) you need to edit this function, explicitly naming the stored procedures that you wish to run. Specific details of how to do this are available here.

... 2 minutes to deadline ... 1 minute ... 30 seconds...

Tom Hanks quickly edits the function. He comments out the apposite sections and adds his procedure name to the list. He executes the function. [... 15 seconds ... 10 seconds ...]

It works.

... 0 seconds ...

The End.

Epilogue: My colleague is happy to report that the demo went well and we are in with a good chance of winning the contract. His name is not really Tom Hanks. (It is Steven Seagal.)

At fault for this problem, of course, is Oracle. Apex is undoubtedly their most developer-friendly tool, but it is mind-boggling that there isn't a declarative way of updating the wwv_flow_epg_include_mod_local function. Also since it resides in the Flows schema it is the easiest thing in the world to update it in the production environment but forget to make the same changes when deploying at a client site (after all, the function is not exported with your application).