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

1. cd $ORACLE_HOME/Apache/modplsql/owa
2. Log into SQL*Plus as SYS.
3. 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.sql

   creates 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://machine.domain:port/pls/dad-name/package_name.procedure_name

e.g.

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: