Introducing Web PL/SQL
Peter Goldthorp July 2002
Internet applications written in PL/SQL are stored procedures that interact with browsers or other HTTP clients. Users interact with the database by visiting a web page or pressing a
Submit button on an HTML form. Any choices that the user makes on an HTML form are passed as parameters to the stored procedure. Parameters can also be included in the URL used to invoke the stored procedure.
The results of the stored procedure are returned as tagged HTML or XML. They can be displayed in a browser or downloaded as a file. PL/SQL is especially good for producing dynamic content based on database processing. Its supports DML statements, cursors and dynamic SQL. Web PL/SQL programming is similar to CGI. It allows you to generate dynamic pages based on database content. Web PL/SQL offers a number of advantages over CGI. The main ones are database connections and memory management are handled by the database and configuration management is often simpler.
Hello World! in Web PL/SQL
1. Verify/Install Web PL/SQL Packages
Check to see if your database has the web pl/sql toolkit installed. Login as system and execute the following command
Select object_name, object_type, owner from dba_objects where object_name in ('HTP');
Look for packages and package bodies called 'HTP'
If they don't exist you will need to install them. Follow the instructions listed below:
Follow these steps to install the web toolkit with iAS 1.0.x
- cd $ORACLE_HOME/Apache/modplsql/owa
- Log into SQL*Plus as SYS.
run the owaload.sql script by using the following command:
@owaload.sql sys_password owa_user owa_password default_tablespace temporary_tablespace connect_string log_file;
where sys_password is the SYS database password. owa_user is the OWA user name. This is the schema in which the packages will be installed. OWA_PUBLIC is recommended. owa_password is the password for the OWA user. default_tablespace is the default tablespace in which the OWA packages will be installed. temporary_tablespace is the temporary tablespace for the packages to use for temporary operations. connect_string is the TNS names alias if you are installing in a remote database. log_file is the installation log file. For example:
owaload.sql change_on_install OWA_PUBLIC OWA_PASSWORD users temp mydb_alias owaload.log owaload.sqlcreates a schema for the owa_user and installs the OWA packages and PL/SQL Web Toolkit in the schema. It also creates public synonyms and makes the packages public so that all users in the database have access to them. Therefore, only one installation per database is needed.
2. Create a Web PL/SQL package
Use SQL*Plus to login to the database as any user with execute privileges on the htp package and create the following package:
create or replace package web_plsql as procedure hello; end web_plsql; / create or replace package body web_plsql as procedure hello is begin htp.p('Hello World!'); end hello; end web_plsql; /
3. Create a DAD (Database Access Descriptor)
A Database access descriptor (DAD) is the component that connects web requests to the database. Any Oracle webserver (e.g. iAS, HTTP Server, OAS or WebDB) can be used to create a DAD. Oracle HTTP server is installed as a default component for 8.1.7 (and later) databases. To configure a DAD using Oracle HTTP Server go to the default page and follow the link titled mod_plsql. Then select 'Gateway Database Access Descriptor Settings' on the Gateway Configuration Menu.
This will take you to the DAD administration page. Select the option 'Add Default (blank configuration)' and then enter a DAD name (e.g. WEBTEST) and the username, password and connect string for the user that you used to create the web_plsql package
Test your package
Use a web browser to test your configuration. Navigate to:
http://devtest1.goldthorp.com:7777/pls/web_plsql.hello or http://localhost/pls/web_plsql.hello
4. Dynamic Content
Modify your package to generate dynamic content. e.g:
create or replace package web_plsql as procedure hello; end web_plsql; / create or replace package body web_plsql as procedure hello is cursor find_user is select username from all_users order by username; begin htp.p('<pre>'); for u_rec in find_user loop htp.p('Hello '||u_rec.username); end loop; htp.p('</pre>'); end hello; end web_plsql; /
5. Parameter Passing
Modify your package again:
create or replace package web_plsql as procedure hello; procedure hello(c_owner IN all_users.username%type); end web_plsql; / create or replace package body web_plsql as procedure hello is cursor find_user is select username from all_users order by username; begin htp.p('<pre>'); for u_rec in find_user loop htp.p('Hello <a href="web_plsql.hello?c_owner='||u_rec.username||'">' ||u_rec.username||'</a>'); end loop; htp.p('</pre>'); end hello; procedure hello(c_owner IN all_users.username%type) is cursor find_object(c_owner IN all_users.username%type) is select object_name , object_type from all_objects where owner= c_owner order by object_name; begin htp.p('<pre>'); for u_rec in find_object(c_owner) loop htp.p(u_rec.object_name||' - '||u_rec.object_type); end loop; htp.p('</pre>'); end hello; end web_plsql; /
This time the procedure will produce a hypertext link for each of the users. Click on a link to see a list of objects for the user: