René Nyffenegger's collection of things on the web | |
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
|
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:
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:
|