René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Oracle's PL/SQL gateway: example 1

This is an example to demonstrate the PL/SQL gateway. In this example, a table is created, filled with some values and its content made available to a browser via HTTP.
I was able to create this simple web application on an Oracle express edition 10g (Oracle XE).
I connect as system ...
connect system
... to create a new user (named web_app_user).
create user            web_app_user 
  identified by        web_app_user
  default   tablespace users
  temporary tablespace temp
  quota unlimited on   users;
This user will create the table and the procedure for this example. Therefore, I need to grant him the according system privileges.
grant create table,
      create session,
      create procedure
to web_app_user;
Now, this user connects ...
connect web_app_user/web_app_user
... and creates the table:
create table web_app_table (
  id number   primary key,
  txt varchar2(20)
);
The table is filled with a few values:
insert into web_app_table values (1, 'one'  );
insert into web_app_table values (2, 'two'  );
insert into web_app_table values (3, 'three');
insert into web_app_table values (4, 'four' );

commit;
web_app also creates the procedure that reads the records in the table and prints them as a HTML table:
create procedure show_table as 
begin

  htp.p(q'#
        <html>
        <h1>The tables content</h1> 
        <table>
        #');

  for r in (select id, txt from web_app_table) loop

    htp.p('<tr><td>' ||
             r.id    || '</td><td>' || 
             r.txt   || '</td></tr>'
    );

  end loop;

  htp.p(q'#
         </table>
         </html>
         #');

end;
/
The PL/SQL gateway runs (as it seems, I couldn't verify that in the documentation) under the anonymous schema. So, anonymous needs the execution object privilege on the procedure.
grant execute on show_table to anonymous;
As a last step, I need to configure which procedures can be accessed via the PL/SQL gateway. This can be made in the wwv_flow_epg_include_mod_local procedure, which, on my Oracle installation (currently: Oracle express edition 10g [Oracle XE]) is located in the flows_020100 schema.
This procedure is given a procedure name (including its schema) and returns true if the procedure is allowed to be executed and false otherwise.
flows_020100's account is locked (select account_status from dba_users where username = 'FLOWS_020100'; returns EXPIRED & LOCKED). So, I connect as system again ...
connect system
... and alter the procedure by prepending its name with the schema name it lives in:
create or replace function flows_020100.wwv_flow_epg_include_mod_local (
    procedure_name in varchar2
) return boolean
is begin  
  
   if procedure_name = 'web_app_user.show_table' then
      -- Allow access to procedure web_app_user.show_table
      return true;
   end if;

   -- Disallow 'calling' any other procedure:
   return false;

end wwv_flow_epg_include_mod_local;
/
I believe, the next step is not necessary, but when I wrote this example, I inadvertantly dropped the procedure and with it the execution grant. So, I grant execution to this procedure to anonymous as well.
grant execute on flows_020100.wwv_flow_epg_include_mod_local to anonymous;
The procedure and its output can now be called and displayed by pointing a browser to
http://localhost:8080/apex/web_app_user.show_table

Links

See also Example 2 which builds upon this example.