Sunday, April 09, 2006

XE: Calling stored procedures

Using the Oracle web toolkit and the PL/SQL gateway (either via mod_plsql or DBMS_EPG) you can call stored procedures via http(s) to generate output for the browser (HTML, XML, basically any content for any mime-type).

The syntax for this is
http://<server>:<port>/<dad>/<schema>.<procedure>

For example, if you wanted to call the procedure called HELLO_WORLD in the schema HR, you would use the following url (in a default Oracle XE installation on port 8080): http://127.0.0.1:8080/apex/hr.hello_world .

This doesn't work with Oracle XE (production) any longer but will result in an 403 Forbidden error:


In Oracle XE (production) you will have to "allow" a stored procedure to
be called through the embedded plsql gateway (DBMS_EPG) due to a stricter security
control.


This is documented in the Oracle XE documentation here.

The behaviour is controlled via the DAD-attribute request-validation-function ,
which maps to the stored function wwv_flow_epg_include_modules.authorize in
the schema FLOWS_020100. You can modify the existing
stored function called wwv_flow_epg_include_mod_local to
add more procedures to the list of modules that are allowed to be called.

Below you will see a full example of allowing the procedure hello_world to
be called:

1) create procedure hello_world
conn HR/<pwd>@xe

create or replace procedure hello_world as
begin
htp.p('hello world');
end;
/

grant execute on hello_world to anonymous
/

2) connect as SYSTEM and modify the function in the schema FLOWS_020100
conn SYSTEM/<pwd>@xe

alter session set current_schema=FLOWS_020100;

CREATE OR REPLACE function wwv_flow_epg_include_mod_local(
procedure_name in varchar2)
return boolean
is
begin
--
-- Administrator note: the procedure_name input parameter may be in the format:
--
-- procedure
-- schema.procedure
-- package.procedure
-- schema.package.procedure
--
-- If the expected input parameter is a procedure name only, the IN list code shown below
-- can be modified to itemize the expected procedure names. Otherwise you must parse the
-- procedure_name parameter and replace the simple code below with code that will evaluate
-- all of the cases listed above.
--
if upper(procedure_name) in (
'HR.HELLO_WORLD') then
return TRUE;
else
return FALSE;
end if;
end wwv_flow_epg_include_mod_local;
/

3) Now you can call it via http://127.0.0.1:8080/apex/hr.hello_world

// Make sure you compare the procedure name in upper case, i.e HR.HELLO_WORLD and not hr.hello_world!!!

Update: 22.05.2006

How to modify the stored function directly using SQL developer

You can also use the free Oracle tool SQL Developer to modify the stored function directly.

The stored function is located in the schema FLOWS_020100. This account is locked by default, thus you would have to unlock the account first in order to use the user FLOWS_020100 to modify the stored function.

Another option is to use a DBA account like SYSTEM to modify the stored function. The following step by step instructions explain how to do it.

1) After starting SQL Developer, right-click with the mouse on the connection and select "New Database Connection" .

2) Fill in the following details to establish a connection to your local XE instance, then click on "connect"


3) Next, click on XE, then Other Users.


4) Click on FLOWS_020100, then Functions, after that on WWV_FLOW_EPG_INCLUDE_MOD_LOCAL:


5) Click on "Edit" in the right panel, where you can see the function you want to edit

6) Edit the function

7) Compile the function

That's it. You don't have to restart the database, it works right away.

~Dietmar.

16 comments:

Anonymous said...

Thanks! It solved my problem. Again thanks u very much.

Anonymous said...

For me the same. Without this post i would not be able to go on with my programming. thank you very much

Anonymous said...

Thanks! Saved my day too

Anonymous said...

Hallo Dietmar,

interessanter bug

grüsse Wolfgang

Anonymous said...

Many thanks. We ported an app from an earlier XE version and had the same problem. Your instructions were very clear and fixed it for us too.

Anonymous said...

Thanks a lot, expecially for tips on SQL developer!

Anonymous said...

hallo dietmar,

ich habe dies analog mit

sys.htp.print

versucht.

das tuts aber nicht!

gruss wolfgang

Unknown said...

Hallo Wolfgang,

I don't think this is possible, nor should it be.

It seems like these system packages are excluded by default:
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14190/confmods.htm#CIHIAHHG

The parameter PlsqlExclusionList in the mod_plsql gateway is mapped to exclusion-list in DBMS_EPG:
http://dba-services.berkeley.edu/docs/oracle/manual-10gR2/appdev.102/b14258/d_epg.htm#BABFFDEH


Regards,
~Dietmar.

Unknown said...

Wolfgang,

if you really need to do this, you can create another package myhtp which calls the functions of sys.htp as a wrapper and allow that package to be called.

Regards,
~Dietmar.

Anonymous said...

Hi Dietmar,
thanks for the useful instruction and the refernce to the XE docs.
Wha I'm desperately looking for would be a hint on how to pass an XML document as a parameter. I'm aware of the 32K "feature" but this wouldn't be a problem for me. Yet, I'm quite sure that the XML files will extend the allowed URL size, so GET wouldn't be an option for me.
May I just POST an XML file as a parameter and will this then be mapped as a parameter?

Thanks, Jürgen

Unknown said...

Hallo Jürgen,

as always, it depends a bit on what you want to do.

Yes, you would post a XML string like any other string.

See this example:
<form id="form1" name="form1" method="post" action="http://127.0.0.1:8080/apex/hr.process_xml">
XML
<textarea name="p_xml" cols="50" rows="10"></textarea>
<input type="submit" value="Senden" />
</form>

and the stored function would be:
create or replace procedure process_xml(p_xml varchar2) is
begin
htp.p(htf.escape_sc(p_xml));
end;
/

grant execute on hr.process_xml to anonymous
/

And finally you would have to allow the procedure HR.PROCESS_XML to be called like described above in the blog entry.

As far as the 32K limitation goes, Carl Backstrom from the Apex team has built a workaround for it (if you are using it in an Apex application):
http://www.oracle.com/technology/products/database/application_express/packaged_apps/sample_code.html#LARGE

I have created a modified version of it that runs on XE:
http://forums.oracle.com/forums/thread.jspa?messageID=1518418#1518418

Regards,
~Dietmar.

Anonymous said...

Thanks very much for your example. I guess I'm able to solve this now. Although: strange, but in my version of the database the wwv_flow_epg_include_modules.authorize is wrapped...
Am I right when saying that it should be possible to include an authorization method of my own by setting the request-validation-function-property to a value of my own choice?
At least I'm going to check that.

Best regards, Jürgen

Unknown said...

Jürgen,

yes, wwv_flow_epg_include_modules.authorize is wrapped and for security reasons, I believe. The do some other checks and *also* call your local function wwv_flow_epg_include_mod_local to include *your* procedures.

Yes, you can set another authorization function using DBMS_EPG, but I wouldn't see any reason to do so.

Do you have any problems with this?

What do you want to accomplish?

Regards,
~Dietmar.

Anonymous said...

Hi Dietmar,
My goal is to implement an application specific REST-based WebService with mod_plsql. I'm using an OracleXE database to hold my business logic an data and expose it to the clients via this interface.
Before I tried to utilize the SOAP based .NET WebService functionality, but having a SOAP envelope around any request, having to have two WebServers, forcing my clients to install IIS etc. brught me to the point to look for this solution.
As it stands now, I'm slowly getting control over this environment and it seems to solve my requirements. I'm not using this in the environment of APEX, so interfacing with the APEX based procedures doesn't look too clean to me. This is why I'm looking for a separated approach.

Best regards, Jürgen

Unknown said...

Thanks!! That worked a treat!!

Raphael Nunes said...

Thanks a lot for this tutorial. It helps me so much.
although the grant to anonymous didn't work, instead I made a grantee to APEX_PUBLIC_USER