|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 ...
... 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 ...
... 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:
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
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 (
... 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
See also Example 2 which builds upon this example.