Tuesday 19 January 2010

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).