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.