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


Louis-Guillaume Carrier-B├ędard said...

again, another great story! :)

Peter Raganitsch said...

Yeah, you are right, it would be so much easier to just declare it in the instance settings.

But: you are not limited to use this one defined procedure to check for what is allowed.
mod_plsql has the parameter PlsqlRequestValidationFunction to declare a procedure which is called to verify if the called procedure is allowed or not.

So you can put a user-defined procedure in your own schema here and don't have to modify the original one.

see: http://download.oracle.com/docs/cd/B15904_01/web.1012/b14010/secure.htm

Keep up the funny stories!


David Peake said...


We have to wrestle with making APEX as developer-friendly as possible and pleasing the security czars. With certain security related activities we have to make you jump through additional hoops to ensure you are consciously modifying the default configuration.

If you are successful with your bid , please let me know - Always looking for APEX success stories.