6
PL/SQL Gateway Tutorial

This section provides a step-by-step guide on creating a simple application that displays the contents of a database table as an HTML table. The application 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 so that you can add new settings to the server configuration. The database to which you are connecting already has the PL/SQL Web Toolkit installed. Refer to the PL/SQL Web Toolkit Reference Guide 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 using 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 into the Database

The stored procedure that the application invokes is current_users (defined below). The procedure retrieves the contents of the all_users table 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 the PL/SQL applications that you want to run with the PL/SQL Gateway are stored. Use the parameters shown in the following table:

Table 6-1 Parameters

Parameter	Value
Database Access Descriptor Name	Scott
Schema	Scott
Oracle User Name	Scott
Oracle Password	Tiger
Oracle Connect String	htmlperf-tcp
Authentication Mode	Basic
Session Cookie Name
Create a Stateful Session?	No
Enable Connection Pooling	Yes
Maximum Number of Worker Threads	10
Default (Home) Page	Scott.home
Document Table	Scott.wwdoc_document
Document Access Path	docs
Document Access Procedure	Scott.wpg_testdoc.process_download
Extensions to be Uploaded as LONGRAW	*
Path Alias
Path Alias Procedure

Note: To 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

Or you can invoke the procedure from an HTML page. 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.

6 PL/SQL Gateway Tutorial

6.1 Creating and Loading the Stored Procedure into the Database

Table 6-1 Parameters

6.2 Creating an HTML Page to Invoke the Application

6
PL/SQL Gateway Tutorial