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

Oracle's PL/SQL gateway: example 2

This example builds upon example 1 which created a web_app_user and a web_app_table. Both are necessary to run this example.
While example 1 showed the entire content of the web_app_table, this example will extend it to display the txt value for a given id in that table.
I connect as web_app_user ...
connect web_app_user/web_app_user
... to create the procedure that will display the record. The parameter p_id will be supplied through the PL/SQL gateway:
create procedure show_record(p_id in number) as
  v_txt web_app_table.txt%type;
begin

  htp.p('<html>');

  select txt into v_txt from web_app_table
   where id = p_id;

  htp.p('txt: ' || v_txt);

  htp.p('</html>');

end;
/
anonymous must be able to execute the procedure:
grant execute on show_record to anonymous;
The PL/SQL gateway needs to be informed that web_app_user.show_record may be invoked. I do that like explained in example 1. I connect as system ...
connect system
... and modify wwv_flow_epg_include_mod_local.
create or replace function flows_020100.wwv_flow_epg_include_mod_local (
    procedure_name in varchar2
) return boolean
is begin  
  
   if procedure_name in ( 
        'web_app_user.show_table',
        'web_app_user.show_record')
   then
      return true;
   end if;

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

end wwv_flow_epg_include_mod_local;
/
The procedure can now be invoked with
http://localhost:8080/apex/web_app_user.show_record?p_id=2
Of course, in a real web application, you don't want the users to enter the p_id in a URL. Instead, the p_id parameter could be provided through an HTML form:
<form action='http://localhost:8080/apex/web_app_user.show_record'
      method='post'>

      <input name='p_id'><br>
      <input type='submit'>
</form>