PL/SQL packages through the APEX Webserver

The integrated web server of Oracle Application Express allows you to access PL/SQL packages through a simple HTTP call, like that: http://my-server/apex/package.procedure . So it is possible to produce output within your package through the Oracle build-in HTP package.

First it is necessary to grant execute rights to the APEX_PUBLIC_USER to let him access the package:

GRANT execute ON mypackage TO apex_public_user;

Second there is a function in your apex schema (e.g. apex_040000 depends on the actual version) called wwv_flow_epg_include_mod_local. This function is responsible for the access management. Each time someone tries to call a procedure over http, the function will be asked if it is allowed to execute the requested procedure. If you want to implement any complex authorization handling, do everything necessary in this function. Here is a simple example how to restrict the access:

create function wwv_flow_epg_include_mod_local (procedure_name in varchar2)
return boolean
is
begin
    if upper(procedure_name) like (
          'schema.http_package%') then
        return TRUE;
    else
        return FALSE;
    end if;
end wwv_flow_epg_include_mod_local;

Posted from Paris, Île-de-France, France.