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