6
mod_PL/SQLTutorial

This section provides a step-by-step guide on creating and invoking a simple application that displays th contents of a database table as an HTML table. The application consists of one PL/SQL cartridge. The cartridge invokes a stored procedure that calls functions and procedures defined in the PL/SQL Web Toolkit.

This tutorial assumes the following:

You have completed the section, "Installing required packages".
You can log in as the admin user on the server. This is required because you will be adding new settings to the server configuration. The database to which you will be connecting already has the PL/SQL Web Toolkit installed. See "PL/SQL Web Toolkit Installation" for more information
You have the SCOTT schema in your Oracle database. The PL/SQL cartridge logs into the database using scott/tiger as the username and password. If you do not have the SCOTTschema, you can use an existing schema on your database, or you can create SCOTT usng the CREATE SCHEMA command.

A schema is a user account containing as a collection of database objects such as tables, views, procedures, and functions. Each object in the schema can access other objects in the same schema.

6.1 Creating and Loading the Stored Procedure onto the Database

The stored procedure that the application invokes is current_users (defined below). The procedure retrieves the contents of the all_userstable and formats it as an HTML table.

To create the stored procedure, save the text of the procedure in a file called
current_users.sql, and then run Oracle Server Manager to read and execute the statements in the file.

Type the following lines and save it in a file called current_users.sql. The current_users procedure retrieves the contents of the all_users table and formats it as an HTML table.
```
 create or replace procedure current_users
     AS
         ignore boolean;
     BEGIN
         htp.htmlopen;
         htp.headopen;
         htp.title('Current Users');
         htp.headclose;
         htp.bodyopen;
         htp.header(1, 'Current Users');
         ignore := owa_util.tablePrint('all_users');
         htp.bodyclose;
         htp.htmlclose;
     END;
     /
     show errors
```
This procedure uses functions and procedures from the htp and owa_util packages to generate the HTML page. For example, the htp.htmlopen procedure generates the string
<html>, and htp.title('Current Users') generates <title>Current Users</title>.

The owa_util.tablePrint function queries the specified database table, and formats the contents as an HTML table.
Start up Server Manager in line mode. ORACLE_HOME is the directory that contains the Oracle database files.
```
     prompt> $ORACLE_HOME/bin/svrmgrl
```

Connect to the database as "scott". The password is "tiger".
```
SVRMGR> connect scott/tiger
```

Load the current_users stored procedure from the current_users.sql file. You need to provide the full path to the file if you started up Server Manager from a directory different than the one containing the current_users.sql file.
```
SVRMGR> @  Name of script file: current_users.sql
```
Exit Server Manager.
```
SVRMGR> exit
```
Configure a DAD to point to the schema where PL/SQL applications that you want to run with mod_PL/SQL are stored, for example:

Notes

You need to configure only one DAD per schema.
If you want require a user to log on to the database containing the application, leave the Oracle User Name and Oracle Password fields blank.

6.2 Creating an HTML Page to Invoke the Application

To run the current_users procedure, enter the following URL in your browser:

http://<host>:<port>//pls/mydad/scott.current_users

It is more common, however, to invoke the procedure from an HTML page. For example, the following HTML page has a link that calls the URL.

<HTML>
<HEAD>
<title>Current Users</title>
</HEAD>

<BODY>
<H1>Current Users</H1>
<p><a href="http://hal.us.oracle.com:9999/simpleApp1/cart1/current_
users">Run 
current_users</a>
</BODY>
</HTML>

The figure below shows the source page (the page containing the link that invokes the stored procedure), and the page that is generated by the current_users stored procedure.

6mod_PL/SQLTutorial

6.1 Creating and Loading the Stored Procedure onto the Database

6.2 Creating an HTML Page to Invoke the Application

6
mod_PL/SQLTutorial