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 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!


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


Anonymous said...

From Matthew Hejazi (I hope this help others too.)

The following is how to setup procedure call from URL DB Procedure though APEX URL at runtime, if using Weblog.

1) Before you begin make sure all the apex_ schemas are unlocked and without expiration.
2) Make sure all the passwords are the same and you can sqlplus to it.
3) Make sure is a strong password. In this example, I am using mytestpassword.
4) APEX uses ORDS built into apex.war file.
5) Figure out where the configuration most default.xml locate.
6) Cd to that dritory.
7) In my case /u02/software/listener
8) Ls -l
9) You should see apex.war
10) Create a file modify.properties
11) Put this inside – this will turn debug on too. So you can change it to ‘N’..
12) Save the file.
13) Cd ./apex the directory is under listener.
14) Backup the default.xml to default.xml.backup
15) Execute this at command line, java -jar apex.war set-properties modify.properties
16) Above execute will add encrypted password to default.xml.
17) You will see something like this if cat default.xml
18) apexdbprod.sandiego.edu
19) Cd ./conf “/u02/software/listener/apex/conf”
20) Ls –l
21) Make sure the password in apex.xml is same as the one in default.xml.
22) In the DB find the function called APEX_050100.wwv_flow_epg_include_mod_local.
23) The above function is under apex_05xxxx.
24) In the function comment out “Return False;” and add your procedure or function.
25) If you are, confused at this point, google for the above function.
26) Every should work. So, URL will execute the procedure you added.
27) URL in APEX Application will look like this with LINK type.
28) 'TEXXX.XXXX_My_happy_procedure'?p_filename=#FILE_NAME#&p_full_path=#FULL_PATH#&p_to_path=#TO_LOC#&location=&P3_SYSTEM
29) If the above is not working also see this url
30) https://ora-00001.blogspot.com/2015/11/how-to-change-apex-public-user-password.html

Anonymous said...

You can email me with questions at mhejazi_76@yahoo.com

Anonymous said...

Matthew Hejazi.
Also please make sure to allow execute of your procedure to
Grant execute your procedure to APEX_PUBLIC and APEX_05XXX.