Oracle Forms to Apex Conversion (or Obama might not actually be Jesus)

Hey, I just thought up a definition for politics: soap operas for boring people. (Or maybe I should change that from boring people to grown ups; I'm not sure.) Think about it: a small cast of main characters, huge dramas, power play, and the occasional sex scandal - how is that any different from Desperate Housewives or Eastenders?

Not being American I watched the recent elections with semi-detached interest. Every few days I'd log onto YouTube and watch John McCain's latest attack ad calling Obama a terrorist, a Muslim, a communist, an Arab, an ... elitist?

But here's the weird thing; ever since Obama won the election he and his men have practically been releasing attack ads against Obama themselves. "You know how we talked about hope and change? Well, is there any chance you could, erm, hope for a little less and expect change a little slower?"

The excitement and anticipation in the Apex community (or at least that subset of it that intersects with the Oracle Forms community) that followed the announcement of version 3.2, and the subsequent need to manage our expectations kinda reminds me of Obama trying to dampen his supporters' frenzy.

The revelation that version 3.2 will include a Forms to Apex converter is, undoubtedly, big news. If, like me, you work for a company that has been churning out .fmxes for well over a decade and has now decided that the best path to the future is Oracle Apex, a Forms2Apex engine could save you thousands of man-hours, hundreds of thousands of pounds, and an incalculable amount of earache from your customers. How can you not get excited about that?

I have the privilege of being one of a small number of outsiders invited by Oracle's David Peake (he's the PM for Application Express) to participate in their Early Adopter programme for version 3.2. He kicked it off with a web conference where, while he was understandably proud of the hard work he and his team have put into this release, I sensed he was doing an Obama and managing our expectations.

So here's what you need to know: Apex version 3.2 does not come with a witchcraft plugin pre-installed. You will still need to do some work. You will need to set up a project and dedicate time to it. Some things just cannot be done automatically.

So are your expectations sufficiently dampened yet? Well, here's the good news. Apex version 3.2 will do a lot more than you now currently expect. We were treated to a demo of its capabilities (and later this week I'll get my grubby mitts on it myself) and I'm very impressed by what it can do and the way that it does it.

I hope to be blogging a bit more in the next few days, giving you details of how it works and what exactly it can do. If you've got any specific questions that you'd like me to investigate for you, feel free to leave them as comments to this entry. Do be aware that I am not an employee of Oracle (as a developer that uses Oracle's technology I have an intense love-hate - or rather like-hate - relationship with the company) and so the answers I give you will not necessarily represent the views of Oracle. There's even a possibility that my answers may be wrong. But hey -

Typos in my dads.conf (or Car chases, sex scenes and a dashing blogger)

Apparently Jerry Bruckheimer refuses to make any movie that cannot be summarised in six words (although lots of people would probably argue that most Jerry Bruckheimer movies can easily be summarised in a single four-letter word that starts with a 'c', ends with a 'p' and manages to squeeze an 'r' and an 'a' between them). In this respect, I guess, this blog is very much like a Bruckheimer movie - car chases, sex scenes, gunfight and a swashbucklingly handsome blogger - in that it can be summarised in 6 words: Apex is great because it's in the database. (Okay, make that 8 words.)

This is brilliant because it means that when I need to send my client an upgrade (version 0.9.0.1 baby!) all I have to do is send them a file which they can run in SQL Plus. Except my last upgrade didn't work... Instead I got an error message saying that I needed to set something called a security_group_id. Huh?

I tried to import the application via Apex and my browser exploded (not literally, of course; I'm not really Jerry Bruckheimer).

The error I got was:

Mon, 06 Oct 2008 13:59:00 GMT

ORA-00942: table or view does not exist

DAD name: apex
PROCEDURE : wwv_flow.accept
URL : http://xxxxxx.net:80/pls/apex/wwv_flow.accept
PARAMETERS :
===========

ENVIRONMENT:
============
PLSQL_GATEWAY=WebDb
GATEWAY_IVERSION=3

It went on in a similar vein for a hundred more lines but what the hell did it all mean? ORA -00942 I understood (I've been an Oracle developer for so long that I see ORA numbers in my nightmares) and I could see that the procedure it was trying to run was wwv_flow.accept (which definitely existed). A second glance led me suspect that the problem might be in my dads.conf file (the Database Access Descriptor config file). I had a look at it; it looked fine to me. I went over it line by line and still couldn't see anything wrong with it.

Turns out I was looking to the wrong Jerry Bruckheimer vehicle; instead of Con Air I should have been looking to the subtlety of CSI. Here's what I was doing wrong (and if you're getting this same error when you try to upload a file in Apex you've probably made this same mistake); I had a typo in my dads.conf.

Instead of:

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_objects$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apex

I had:

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8
PlsqlAuthenticationMode Basic
SetHandler pls_handler
PlsqlDocumentTablename wwv_flow_file_object$
PlsqlDatabaseUsername APEX_PUBLIC_USER
PlsqlDefaultPage apex
PlsqlDatabasePassword apex

Spotted it yet? I didn't either.

In typing my dads.conf I had typed 'object$' instead of 'objects$' in the line:

PlsqlDocumentTablename wwv_flow_file_objects$

Once I corrected this typo and restarted my web service it all worked. Easy-peasy - when you know how. So I've got to say thank you to this site which pointed me in the right direction.

Google Motion Charts in Apex (or Me, February, March & Mummy)

I've got an old Iraqi 100 dinars note in my wallet (don't ask me why - long story) and it's got this huge picture of Saddam Hussein on it. Just how vain do you have to be as a president to put your own face on your country's money? Or what about President Saparmurat Niyazov of Turkmenistan who was so egocentric that he renamed the month of January after himself and April after his mother?

Crazy, huh? Except we've all got a little narcissistic dictator in us, which is why I sometimes obsessively keep an eye on this blog's hit-counter. Which is why I know that most people who chance upon this blog do so from googling "AnyCharts" or "FusionCharts" (see my post about integrating FusionCharts with Apex here). Flash charts are obviously a big thing: users find them informative and we developers find them fun to play with.

Which is why I nearly wet myself with excitement when I heard that Gapminder's really cool flash chart (go on, click the link and press play. We'll wait for you) had recently been rebranded as 'motion charts', released as a Google gadget and can be accessed using the Google visualization API (no, I hadn't heard of it either). How cool would it be to have one of them in your apex app? (The Google visualization gallery actually has quite a few interesting charts; they're probably worth a second look too.)

The Google motion chart page, fortunately, contains some sample code (although you might want to read the Google Visualization overview to fully understand how the whole thing holds together). To use it in your apex application all you have to do is create a Dynamic PL/SQL region and output the javascript code using htp.prn, populating the Google DataTable in a pl/sql loop.

And that's that. Easy-peasy when you know how.

The only small issues I've had with motion charts so far is that while they seem happy to display an annual timeline they do appear a little buggy when you try to portray smaller date increases, and no matter what the user guide says I am unable programmatically control the size or colour of the bubbles.

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.