Oracle9i Application Server Using the PL/SQL Gateway Release 1.0.2.1 Part Number A87562-01 |
Library | Service | Contents | Index

# 1
PL/SQL Gateway Overview

Oracle9i Application Server consolidates Oracle's middle-tier products into a single solution for the deployment of Web applications. The PL/SQL Gateway provides support for building PL/SQL-based applications on the Web. PL/SQL stored procedures can retrieve data from a database and generate HTTP responses containing data and code to display in a Web browser. The PL/SQL Gateway also supports other Oracle products such as Oracle Portal 3.0.

## 1.1 PL/SQL Gateway Configurations

Oracle9i Application Server provides two configurations for deploying PL/SQL-based Web applications:

- Oracle9i Application Server and mod_plsql support running in stateless mode only. This is the recommended configuration for users developing stateless PL/SQL-based Web applications. In the stateless mode, mod_plsql has a connection pooling mechanism that can keep database sessions open between HTTP requests.
  In a stateless environment, each HTTP request from a client maps to a new database session. Application state is typically maintained in HTTP cookies or database tables. Transaction state cannot span across requests. If a PL/SQL procedure executes successfully, an implicit commit is performed. If it executes with an error, an implicit rollback is performed.

- Oracle9i Application Server and mod_ose support running in both stateless and stateful mode. This is the recommended configuration for users developing stateful PL/SQL- and Java-based Web applications. When using mod_ose, the stateful mode is preferable because a new database session does not have to be created and destroyed for every HTTP request. For more information, see the Oracle8i Oracle Servlet Engine User's Guide in the Oracle 9i Application Server Documentation Library.
  In a stateful environment, each HTTP request from a client maps to the same database session. Application state is preserved in PL/SQL package variables. A transaction can span across requests because no implicit commits or rollbacks are performed.

## 1.2 Database Access Descriptors

Each PL/SQL Gateway request is associated with a Database Access Descriptor (DAD), a set of configuration values used for database access. A DAD specifies information such as:

- the database alias (Net8 service name).

- a connect string if the database is remote.

- a procedure for uploading and downloading documents.

You can also specify a username and password information in a DAD. If they are not specified, the user is prompted to enter a username and password when the URL is invoked. For more information, see "Authenticating Users".

## 1.3 Processing Client Requests

The following occurs when a server receives a request:

1. The Oracle HTTP Server receives a PL/SQL Server Page request, through Oracle Web Cache, from a client browser.

2. The Oracle HTTP Server routes the request to mod_plsql.

3. The request is forwarded by mod_plsql to Oracle8i PLSQL. By using the configuration information stored in your DAD, mod_plsql connects to the database, prepares the call parameters, and invokes the PL/SQL procedure in the database. See "Configuring the PL/SQL Gateway" for more information.

4. The PL/SQL procedure generates an HTML page using data and stored procedures accessed from the database.

5. The response is returned to mod_plsql.

6. The Oracle HTTP Server sends the response, through Oracle Web Cache, to the client browser.

The procedure that the PL/SQL Gateway invokes returns the HTTP response to the client. To simplify this task, the PL/SQL Gateway includes the PL/SQL Web Toolkit, a set of packages that you can use in your stored procedure to get information about the request, construct HTML tags, and return header information to the client. Install the toolkit in a common schema so that all users can access it. See "Using the PL/SQL Web Toolkit" for more information.

## 1.4 Invoking the PL/SQL Gateway

To invoke the PL/SQL Gateway in a Web browser, input the URL in the following format:

    protocol://hostname[:port]/prefix/DAD/[[!][schema.][package.]proc_
    name[?query_string]]

where:

##### Table 1-1 Invoking the PL/SQL Gateway Parameters

Parameter | Description
--- | ---
protocol | Either `http` or`https`. For SSL, use https.
hostname | The machine where the Web server is running.
port (optional) | The port at which the application server is listening. If omitted, port 80 is assumed.
prefix | A virtual path to handle PL/SQL requests that you have configured in the Web server. `pls` is the default setting for this parameter. For example, you can configure the Web server to set `pls` as the prefix so that all requests containing the`pls` prefix are routed to the PL/SQL Gateway.
DAD | The DAD entry to be used for this URL.
! character (optional) | Indicates to use the flexible parameter passing scheme. See"Flexible Parameter Passing" for more information.
schema (optional) | The database schema name. If omitted, name resolution for package.proc_name occurs based on the database user that the URL request is processed as.
package (optional) | The package that contains the PL/SQL stored procedure. If omitted, the procedure is stand-alone.
proc_name | The PL/SQL stored procedure to run. This must be a procedure and not a function. It can accept only IN arguments.
?query_string (optional) | The parameters for the stored procedure. The string follows the format of the GET method. For example: Multiple parameters are separated with the & character. Space characters in the values to be passed in are replaced with the + character. If you use HTML forms to generate the string (as opposed to generating the string yourself), the formatting is done automatically. The HTTP request may also choose the HTTP POST method to post data to the PL/SQL Gateway. See "POST and GET Methods" for more information.

Example 1: A Web server is configured with`pls` as a prefix and the browser sends the following URL:

    http://www.acme.com:9000/pls/mydad/mypackage.myproc

The Web server running on `www.acme.com` and listening at port `9000` handles the request. When the Web server receives the request, it passes the request to the PL/SQL Gateway. This is because the `pls` prefix indicates that the Web server is configured to invoke the PL/SQL Gateway. The PL/SQL Gateway then uses the DAD associated with `mydad` and runs the `myproc` procedure stored in `mypackage`.

Example 2: Specify a URL without a DAD, schema, or stored procedure name.

     http://www.acme.com:9000/pls/mydad

Then the default home page for the `mydad` DAD (as specified on the Gateway Configuration pages) displays.

Example 3: Specify a URL to invoke the default DAD's default home page:

    http://www.acme.com:9000/pls

Generally, it does not matter what order the PL/SQL parameters are entered in the URL or the HTTP header since the parameters are passed by name. However, there are some exceptions to this rule. Refer to "Parameter passing" for more information.

### 1.4.1 POST and GET Methods

The POST and GET methods in the HTTP protocol instruct browsers on how to pass parameter data (usually in the form of name-value pairs) to applications. The parameter data is generated by HTML forms.

PL/SQL Gateway applications can use either method. Each method is as secure as the underlying transport protocol (http or https).

- When you use the POST method, parameters are passed in the request body. Generally, if you are passing large amounts of parameter data to the server, use the POST method.

- When you use the GET method, parameters are passed using a query string. The limitation of this method is that the length of the value in a name-value pair cannot exceed the maximum length for the value of an environment variable, as imposed by the underlying operating system. In addition, operating systems have a limit on how many environment variables you can define.

## 1.5 Transaction Mode

After processing a URL request for a procedure invocation, the PL/SQL Gateway performs a rollback if there were any errors. Otherwise, the Gateway performs a commit. This mechanism does not allow a transaction to span across multiple HTTP requests. In this stateless model, applications typically maintain state using HTTP cookies or database tables. For more information about stateful and stateless modes, see "PL/SQL Gateway Configurations".

## 1.6 Parameter passing

The PL/SQL Gateway supports:

- Parameter passing by name

  Each parameter in a URL that invokes procedure or functions identified by a unique name. Overloaded parameters are supported. See "Parameter Passing by Name (Overloaded parameters)" for more information.

- Flexible parameter passing

  Procedures are prefixed by a ! character. See "Flexible Parameter Passing" for more information.

- Large (up to 32K) parameters passing

  See "Large Parameter Passing" for more information.

### 1.6.1 Parameter Passing by Name (Overloaded parameters)

Overloading allows multiple subprograms (procedures or functions) to have the same name, but differ in the number, order, or the datatype family of the parameters. When you call an overloaded subprogram, the PL/SQL compiler determines which subprogram to call based on the data types passed.

PL/SQL allows you to overload local or packaged subprograms. Stand-alone subprograms cannot be overloaded. See the PL/SQL User's Guide in the Oracle Server documentation for more information on PL/SQL overloading.

You must give parameters different names for overloaded subprograms that have the same number of parameters. Because HTML data is not associated with datatypes, the PL/SQL Gateway does not know which version of the subprogram to call.

For example, although PL/SQL allows you to define two procedures using the same parameter names for the procedures, an error occurs if you use this with the PL/SQL Gateway.

    -- legal PL/SQL, but not for the PL/SQL Gateway

    CREATE PACKAGE my_pkg AS

      PROCEDURE my_proc (val IN VARCHAR2);

      PROCEDURE my_proc (val IN NUMBER);

    END my_pkg;

To avoid the error, name the parameters differently. For example:

    -- legal PL/SQL and also works for the PL/SQL Gateway

    CREATE PACKAGE my_pkg AS

      PROCEDURE my_proc (valvc2 IN VARCHAR2);

      PROCEDURE my_proc (valnum IN NUMBER);

    END my_pkg;

The URL to invoke the first version of the procedure looks similar to:

    http://www.acme.com/pls/myDAD/my_pkg.my_proc?valvc2=input

The URL to invoke the second version of the procedure looks similar to:

    http://www.acme.com/pls/myDAD/my_pkg.my_proc?valnum=34

#### 1.6.1.1 Overloading and PL/SQL Arrays

If you have overloaded PL/SQL procedures where the parameter names are identical, but the data type is owa_util.ident_arr (a table of varchar2) for one procedure and a scalar type for another procedure, the PL/SQL Gateway can still distinguish between the two procedures. For example, if you have the following procedures:

    CREATE PACKAGE my_pkg AS

      PROCEDURE my_proc (val IN VARCHAR2); -- scalar data type

      PROCEDURE my_proc (val IN owa_util.ident_arr); -- array data type

    END my_pkg;

Each of these procedures has a single parameter of the same name,`val`.

When the PL/SQL Gateway gets a request that has only one value for the val parameter, it invokes the procedure with the scalar data type.

Example 1: Send the following URL to execute the scalar version of the procedure:

    http://www.acme.com/pls/myDAD/my_proc?val=john

When the PL/SQL Gateway gets a request with more than one value for the val parameter, it then invokes the procedure with the array data type.

Example 2: Send the following URL to execute the array version of the procedure:

    http://www.acme.com/pls/myDAD/my_proc?val=john&val=sally

To ensure that the array version executes, use hidden form elements on your HTML page to send dummy values that are checked and discarded in your procedure.

### 1.6.2 Flexible Parameter Passing

The PL/SQL Gateway supports flexible parameter passing to handle HTML forms where users can select any number of elements. To use flexible parameter passing for a URL-based procedure invocation, prefix the procedure with an exclamation mark (!) in the URL. You can use two or four parameters. The two parameter interface provides improved performance with the PL/SQL Gateway. The four parameter interface is supported for compatibility.

Note: For questions about backwards compatibility with OAS, refer to "Using Flexible Parameters and the Exclamation Mark".

#### 1.6.2.1 Two parameter interface

    procedure [proc_name] is

    		  	     name_array  IN  [array_type],

    	       value_array IN  [array_type],

where:

##### Table 1-2 Two Parameter Interface Parameters

Parameter | Description
--- | ---
proc_name (optional) | The name of the PL/SQL procedure that you are invoking.
name_array | The names from the query string (indexed from 1) in the order submitted.
value_array | The values from the query string (indexed from 1) in the order submitted.
array_type (optional) | The values from the query string (indexed from 1) in the order submitted.

Example: If you send the following URL:

    http://www.acme.com/pls/myDAD/!scott.my_proc?x=john&y=10&z=doe

The exclamation mark prefix (!) instructs the PL/SQL Gateway to use flexible parameter passing. It invokes procedure scott.myproc and passes it the following two arguments:

    name_array ==> (`x', `y', `z')
    values_array ==> ('john', '10', 'doe')

#### 1.6.2.2 Four parameter interface

    procedure [proc_name] is
             (num_entires IN NUMBER,
              name_array  IN  [array_type],
              value_array IN  [array_type],
              reserved in [array_type]);

where:

##### Table 1-3 Four Parameter Interface Parameters

Parameter | Description
--- | ---
proc_name (optional) | The name of the PL/SQL procedure that you are invoking.
num_entries | The number of name_value pairs in the query string
name_array | The names from the query string (indexed from 1) in the order submitted.
value_array | The values from the query string (indexed from 1) in the order submitted.
reserved | Not used. It is reserved for future use.
array_type (optional) | Any PL/SQL index-by table of varchar2 type (e.g., owa.vc_arr).

Example: If you send the following URL, where the query_string has duplicate occurrences of the name "x":

    http://www.acme.com/pls/myDAD/!scott.my_pkg.my_proc?x=a&y=b&x=c

The exclamation mark prefix (!) instructs the PL/SQL Gateway to use flexible parameter passing. It invokes procedure`scott.my_pkg.myproc` and passes it the following four arguments:

    num_entries ==> 3
    name_array ==> (`x', `y', `x');
    values_array ==> (`a', `b', `c')
    reserved ==> ()

### 1.6.3 Large Parameter Passing

The values passed as scalar arguments and the values passed as elements to the index-by table of varchar2 arguments can be up to 32K in size.

For example, when using flexible parameter passing (described in "Flexible Parameter Passing"), each name or value in the query_string portion of the URL gets passed as an element of the `name_array` or`value_array` argument to the procedure being invoked. These names or values can be up to 32KB in size.

## 1.7 File Upload and Download

The PL/SQL Gateway allows you to:

- Upload and download files as raw byte streams without any character set conversions. The files are uploaded into the document table. A primary key is passed to the PL/SQL upload handler routine so that it can retrieve the appropriate table row.

- Specify one or more tables per application for uploaded files so that files from different applications are not mixed together.

- Provide access to files in these tables via a URL format that doesn't use query strings, for example:

       http://www.acme.com:9000/mysite/pls/docs/cs250/lecture1.htm

  This is required to support uploading a set of files that have relative URL references to each other.

- Upload multiple files per form submission.

- Upload files into LONG RAW and BLOB types of columns in the document table.

### 1.7.1 Document Table Definition

You can specify the document storage table on a per DAD basis. The document storage table must have the following definition:

    CREATE TABLE [table_name] (

    NAME           VARCHAR2(256) UNIQUE NOT NULL,
    MIME_TYPE      VARCHAR2(128),
    DOC_SIZE       NUMBER,
    DAD_CHARSET    VARCHAR2(128),
    LAST_UPDATED   DATE,
    content_type   VARCHAR2(128),
    [content_column_name] [content_column_type]
    [ , [content_column_name] [content_column_type]]

    );

Users can choose the`table_name.` The`content_column_type` type must be either LONG RAW or BLOB.

The `content_column_name` depends on the corresponding `content_column_type`:

- If the content_column_type is LONG RAW, the content_column_name must be CONTENT.

- If the content_column_type is BLOB, the content_column_ name must be BLOB_CONTENT.

An example of legal document table definition is:

      NAME               VARCHAR(128)   UNIQUE NOT NULL,
      MIME_TYPE          VARCHAR(128),
      DOC_SIZE           NUMBER,
      DAD_CHARSET        VARCHAR(128),
      LAST_UPDATED       DATE,
      CONTENT_TYPE       VARCHAR(128),
      CONTENT            LONG RAW,
      BLOB_CONTENT       BLOB ;

#### 1.7.1.1 Semantics of the CONTENT column

The contents of the table are stored in a content column. There can be more than one content column in a document table. However, for each row in the document table, only one of the content columns is used. The other content columns are set to NULL.

#### 1.7.1.2 Semantics of the CONTENT_TYPE column

The content_type column tracks in which content column the document is stored. When a document is uploaded, the PL/SQL Gateway sets the value of this column to the type name.

For example, if a document was uploaded into the BLOB_CONTENT column, then the CONTENT_TYPE column for the document is set to the string `BLOB'.

#### 1.7.1.3 Semantics of the LAST_UPDATED column

The LAST_UPDATED column reflects a document's creation or last modified time. When a document is uploaded, the PL/SQL Gateway sets the
LAST_UPDATED column for the document to the database server time.

If an application then modifies the contents or attributes of the document, it must also update the LAST_UPDATED time.

The PL/SQL Gateway uses the LAST_UPDATED column to check and indicate to the HTTP client (browser) if the browser can use a previously cached version of the document. This reduces network traffic and improves server performance.

#### 1.7.1.4 Semantics of the DAD_CHARSET column

The DAD_CHARSET column keeps track of the character set setting at the time of the file upload. This column is reserved for future use.

### 1.7.2 Old Style Document Table Definition

For backward capability with the document model used by older releases of WebDB 2.x, the PL/SQL Gateway also supports the following old definition of the document storage table where the CONTENT_TYPE, DAD_CHARSET and LAST_UPDATED columns are not present.

    /* older style document table definition (DEPRECATED) */
    CREATE TABLE [table_name]
    (

    NAME         VARCHAR2(128),
    MIME_TYPE    VARCHAR2(128),
    DOC_SIZE     NUMBER,
    CONTENT      LONG RAW

    );

### 1.7.3 Relevant Parameters

For each DAD, the following configuration parameters are relevant for file upload or download.

    document_table (document_table_name)

The document_table parameter specifies the table to be used for storing documents when file uploads are performed via this DAD.

Syntax:

    document_table = [document_table_name]

Examples:

    document_table = my_documents

or,

    document_table = scott.my_document_table

### 1.7.4 document_path (Document Access Path)

The document_path parameter specifies the path element to access a document. The document_path parameter follows the DAD name in the URL. For example, if the document access path is `docs`, then the URL would look similar to:

    http://neon/pls/myDAD/docs/myfile.htm

The `myDAD` is the DAD name and `myfile.htm` is the file name.

Syntax:

    document_path = [document_access_path_name]

#### 1.7.4.1 document_proc (Document Access Procedure):

The document_pro procedure is an application-specified procedure. It has no parameters and processes a URL request with the document access path. The document access procedure calls `wpg_docload.download_file(filename)` to download of a file. It knows the filename based on the URL specification. For example, this can be used by an application to implement file-level access controls and versioning. An example of such an application is shown in "File Download" .

Syntax:

    document_proc = [document_access_procedure_name]

Examples:

    document_proc = my_access_procedure

or,

    document_proc = scott.my_pkg.my_access_procedure

#### 1.7.4.2 upload_as_long_raw

The DAD parameter,`upload_as_long_raw`, configures file uploads based on their file extensions. The value of an`upload_as_long_raw` DAD parameter is a comma separated (,) list of file extensions. Files with these extensions are uploaded by the PL/SQL Gateway into the content column of `long_raw` type in the document table. Files with other extensions are uploaded into the BLOB content column.

The file extensions can be text literals (jpeg, gif, etc.). In addition, an asterisk (*) can be used as a special file extension and matches any file whose extension has not been listed in an `upload_as_long_raw` setting.

Syntax:

    upload_as_long_raw = [file_extension][,[file_extension]]*

`[file_extension]` is an extension for a file (with or without the `.' character, e.g., `txt' or `.txt') or the wildcard character *.

Examples:

    upload_as_long_raw = html, txt
    upload_as_long_raw = *

### 1.7.5 File Upload

To send files from a client machine to a database, create an HTML page that contains:

- A FORM tag whose enctype attribute is set to`multipart/form-data` and whose action attribute is associated with a PL/SQL Gateway procedure call, referred to as the "action procedure."

- An INPUT element whose type and name attributes are set to file. The `INPUT type="file"` element enables a user to browse and select files from the file system.

When a user clicks Submit, the following events occur:

1. The browser uploads the file specified by the user as well as other form data to the server.

2. The PL/SQL Gateway stores the file contents in the database in the document storage table. The table name is derived from the document_table DAD setting.

3. The action procedure specified in the action attribute of the FORM is run similar to invoking a PL/SQL Gateway procedure without file upload.

The following example shows an HTML form that lets a user select a file from the file system to upload. The form contains other fields that allow the user to provide information about the file.

    <html>
    <head>
    <title>test upload</title>
    </head>
    <body>
     <FORM 	enctype="multipart/form-data"
    action="pls/myDAD/write_info"
    method="POST">
    <p>Author's Name:<INPUT type="text" name="who">
    <p>Description:<INPUT type="text" name="description"><br>
    <p>File to upload:<INPUT type="file" name="file"><br>
    <p><INPUT type="submit">
    </FORM>
    </body>
    </html>

When a user clicks Submit on the form, the browser uploads the file listed in the`INPUT type="file"` element.

The`write_info` procedure then runs. The procedure writes information from the form fields to a table in the database and returns a page to the user. The action procedure does not have to return anything to the user, but it is a good idea to let the user know whether the Submit succeeded or failed.

A sample write_info procedure:

    procedure write_info (
    who         in varchar2,
    description in varchar2,
    file        in varchar2) as
    begin
    insert into myTable values (who, description, file);
    htp.htmlopen;
    htp.headopen;
    htp.title('File Uploaded');
    htp.headclose;
    htp.bodyopen;
    htp.header(1, 'Upload Status');
    htp.print('Uploaded ' || file || ' successfully');
    htp.bodyclose;
    htp.htmlclose;
    end;

The filename obtained from the browser is prefixed with a generated directory name to reduce the possibility of name conflicts. The "action procedure" specified in the form renames this name. So, for example, when `/private/minutes.txt` is uploaded, the name stored in the table by the gateway is `F9080/private/minutes.txt.` The application can rename this in the called stored procedure. For example, the application can rename it to`scott/minutes.txt`.

### 1.7.6 Specifying Attributes (Mime Types) of Uploaded Files

In addition to renaming the uploaded file, the stored procedure can alter other file attributes. For example, the form in the example from "File Upload" could display a field for allowing the user to input the uploaded document's Multipurpose Internet Mail Extension (MIME) type.

The MIME type can be received as a parameter in write_info. The document table would then store the mime type for the document instead of the default mime type that is parsed from the multipart form by the PL/SQL Gateway when uploading the file.

### 1.7.7 Uploading Multiple Files

To send multiple files in a single submit, the upload form must include multiple <INPUT type="file" name="file"> elements. If more than one file INPUT element defines`name` to be of the same name, then the action procedure must declare that parameter name to be of type owa.vc_arr. The names defined in the file INPUT elements could also be unique, in which case, the action procedure must declare each of them to be of varchar2. For example, if a form contained the following elements:

    <INPUT type="file" name="textfiles">
    <INPUT type="file" name="textfiles">
    <INPUT type="file" name="binaryfile">

As a result, the action procedure must contain the following parameters:

    procedure handle_text_and_binary_files(textfiles IN owa.vc_arr,

    binaryfile IN varchar2).

### 1.7.8 File Download

After you have sent files to the database, you can download them, delete them from the database, and read and write their attributes.

To download a file, create a stored procedure without parameters that calls
wpg_docload.download_file (file_name) to initiate the download. The document download packages are in the PL/SQL Web Toolkit. See "Installing Required Packages" for more information.

The HTML page presented to the user simply has a link to a URL which includes the Document Access Path and specifies the file to be downloaded.

For example, if the DAD specifies that the Document Access Path is docs and the Document Access Procedure is webview.process_download, then the webview.process_download procedure is called when the user clicks on the URL:

    http://www.acme:9000/pls/webview/docs/myfile.htm

An example implementation of process_download is:

    procedure process_download is
    	v_filename varchar2(255);
    begin
      -- getfilepath() uses the SCRIPT_NAME and PATH_INFO cgi
      -- environment variables to construct the full pathname of

      -- the file URL, and then returns the part of the pathname
      -- following `/docs/'
      v_filename := getfilepath;
      select name into v_filename from plsql_gateway_doc
      where UPPER(name) = UPPER(v_filename);
      -- now we call docload.download_file to initiate
      -- the download.
      wpg_docload.download_file(v_filename);
    exception
      when others then
    	v_filename := null;
    end process_download;

Any time you call wpg_docload.download_file(filename) from a procedure running in the Gateway, a download of the file `filename``` is initiated. However, when a file downloaded is initiated, no other HTML (produced via HTP interfaces) generated by the procedure, is passed back to the browser.

The PL/SQL Gateway looks for the file filename in the document table. There must be a unique row in the document table whose NAME column matches the filename. The PL/SQL Gateway generates HTTP response headers based on the information in the MIME_TYPE column of the document table. The `content_type` column's value determines which content columns the document's content comes from. The contents of the document are sent as the body of the HTTP response.

## 1.8 Path Aliasing (Direct Access URLs)

Path Aliasing enables applications using the PL/SQL Gateway to provide direct reference to its objects using simple URLs. The PL/SQL Gateway allows you to directly access documents within an application using the document access path and a document access procedure. For example, the `docs` keyword in the URL below tells the PL/SQL Gateway that this request is for document access.

`http://<HostName>[:Port]/<DADName>/docs/<FolderName/Document>`

The above assumes that the Document Access Path is `docs`.

Path Aliasing provides the equivalent function by allowing means of direct access to application objects other than documents. Two fields in Database Access Descriptor's configuration information support path aliasing:

- Path Alias

- Path Alias Procedure

If the PL/SQL Gateway encounters in an incoming URL the keyword entered in the Path Alias field, it invokes the procedure entered in the Path Alias Procedure field.

For example, if the incoming URL is

`http://www.acme.com:9000/portal_DAD/URL/path_alias_URL`

and the Path Alias is`URL`, the PL/SQL Gateway invokes the Path Alias Procedure, passing everything after the keyword `URL` to the invoked procedure.

Applications that use path aliasing must implement the Path Alias Procedure. The procedure receives the rest of the URL (`path_alias_URL`) after the keyword, `URL`, as a single parameter, and is therefore responsible and also fully capable of dereferencing the object from the URL.

Although there is no restriction on the name and location for this procedure, it can accept only a single parameter, p_path, with the datatype varchar2.

## 1.9 Caching

Caching can improve performance of PL/SQL Web applications. You can cache PL/SQL procedures and Web content in the middle-tier. Subsequent requests for the content may be retrieved from the cache, with or without validation from the database, thereby decreasing the database workload. When enabled, caching increases the scalability of your Web application.

There are a number of cache mechanisms in the HTTP protocol suite. The HTTP protocol consists of Requests and Responses. A user agent, for example a Web browser, can supply metadata in the Request Headers.

Content providers such as PL/SQL procedures can supply the cache-controlling metadata using one or more HTTP Response Headers. In subsequent HTTP requests, this metadata is supplied by the user agent so that the content provider can determine the validity of the user agent's cache entry.

In cases such as the Expires Response Header, the metadata indicates that the content is valid for a certain period of time. Subsequent requests need not be made until that period of time elapses. The content provider has indicated that it need not be reaccessed for a period of time, although the user agent may still do so.

### 1.9.1 Validation technique

When a Web page is initially generated, it contains a Last-Modified Response Header. This header indicates the date, relative to the server, of the content that was requested. User agents with caching capabilities save this date information along with the content. When subsequent requests are made for the URL of the Web page, the user agent:

1. `Determines if it has a cached version.`

2. `Extracts the date information.`

3. `Generates the Request Header If-Modified-Since.`

4. `Sends the request the content provider.`

Cache-enabled content providers look for the If-Modified-Since header and compare it to their content's date. If the two match, an HTTP Response status header such as "HTTP/1.1 304 Not Modified" is generated, and no content is streamed. Upon receipt of this status code, the user agent can reuse its cache entry because it has been validated.

If the two don't match, an HTTP Response header such as "HTTP/1.1 200 OK" is generated and the new content is streamed, along with a new Last-Modified Response header. Upon receipt of this status code, the user agent must replace its cache entry with the new content and new date information.

Another validation method provided by the HTTP protocol is the ETag (Entity Tag) Response and Request header. The value of this header is a string that is opaque to the user agent. Content providers generate this string based on their type of application. This is a more generic validation method than the If-Modified-Since header, which can only contain a date value.

The ETag method works very similar to the date method. Content providers generate the ETag header value as part of the Response Header. The user agent stores this opaque header value along with the content that is steamed back. When the next request for this content arrives, the user agent passes the If-Match header with the opaque value that it stored to the content provider. Because the content provider generated this opaque value, it is able to determine what to send back to the user agent. The rest is exactly like the Last-Modified validation method as described above.

### 1.9.2 Expires technique

If a Web page contains an Expires Response Header, the user agent may use this date value, combined with the Date Response Header, to determine how long the response is valid. It does not need to contact the content provider during this time because the validity criteria have already been established. Therefore, the user agent can directly stream back the cached content for that request.

### 1.9.3 The PL/SQL Gateway Cache

Using the HTTP protocol as the design basis, the PL/SQL Gateway serves as a user agent and a PL/SQL procedure serves as the content provider. Similar to HTTP, headers and environment variables are the communication mechanism between the user agent and the content provider.

One of the assumptions is that the content being cached is varying and typically secured on a per user basis, although the physical URL being cached might be the same across users. Furthermore, content might be in different languages. These assumptions make this design different from the HTTP/1.1 protocol, which uses only the URL to create a cache key. The PL/SQL Gateway uses the URL in conjunction with the user and language to form the cache key.

There are two levels of caching for each request:

- User-level caching is for a specific user that is logged in. The stored cache is unique for that user. Only that user gets to use the cache.

- System-level caching is for a group of users that shares the cache.

For example, if no individual user chooses to customize a customizable PL/SQL Web application, then the application's output can be stored in a system-level cache. Therefore, there is only a single cache copy for every user on the system.

However, if one of the users customizes the application, then a new user-level cache is stored for that user only. All other users still use the system level cache. This is explained in more detail in "System- and User-level Caching".

#### 1.9.3.1 owa_cache package

The owa_cache package contains functions and procedures to set and get special caching headers and environment variables. These allow developers to use the PL/SQL Gateway cache more easily. This package should already be installed in your database (see "Installing Required Packages" for more information). See "owa_cache" for a complete specification of the owa_cache package.

These are the primary functions to call:

- `owa_cache.set_cache(p_etag IN varchar2, p_level IN varchar2)`

  This function sets up the headers for the validation model of caching. The p_etag parameter is the string that tags the generated content. The p_level parameter is the caching level to use.

- `owa_cache.set_expires(p_expires IN number, p_level IN varchar2)`

  This function sets up the headers for the expires model of caching. The p_expires parameter is the number of minutes the generated content is valid. The p_level parameter is the caching level to use.

- `owa_cache.set_not_modified`

  This function is only valid for the validation model. It sets up the headers to notify the gateway to use the cached content.

- `owa_cache.get_level`

  This function is only valid for the validation model. It gets the caching level, "USER" or "SYSTEM".

- `owa_cache.get_etag`

  This function is only valid for the validation model. It gets the tag associated with the cached content.

#### 1.9.3.2 Validation Model

This model is similar to the HTTP ETag caching technique. The PL/SQL Gateway always asks the PL/SQL procedure whether the content has changed or not.

Assume a PL/SQL procedure is called for the first time through the PL/SQL Gateway. The PL/SQL Gateway executes the procedure and passes the usual Common Gateway Interface (CGI) environment variables. The procedure generates content to pass back. If the procedure decides that the generated content is cacheable, it calls the owa_cache procedure to set the tag and the cache level:

`owa_cache.set_cache(p_etag, p_level);`

where:

##### Table 1-4 Validation Model Parameters

Parameter | Description
--- | ---
set_cache function | Sets up the headers to notify the PL/SQL Gateway that the content being streamed back can be cached. Then, the PL/SQL Gateway caches the content on the local file system along with the tag and caching level information as it is streamed back to the browser.
p_etag | The string that the procedure generates to tag the content.
p_level | The caching level ("SYSTEM" for system level or "USER" for user level).

Next, assume a second request for the same PL/SQL procedure. The PL/SQL Gateway detects that it has a cached content for the request. In this case, it does something special: it passes that same tag and caching level information, which it got last time from executing the same procedure, as part of the CGI environment variables. The procedure then uses these caching CGI environment variables to check if the content has changed. It does so by calling the following `owa_cache` functions:

`owa_cache.get_etag;`

`owa_cache.get_level;`

These functions get the tag and caching level respectively. Since the PL/SQL procedure generated these the last time, it can do any kind of processing on them to determine whether the content needs to be regenerated or not.

If the content is still the same, the procedure calls the following `owa_cache` procedure:

`owa_cache.set_not_modified;`

and generates no content. This tells the PL/SQL Gateway to use its cached content for this request. Therefore, the cached content is directly streamed back to the browser.

On the other hand, if the PL/SQL procedure determines that the content has changed, it generates the new content along with a new tag and caching level. It does not call `owa_cache.set_not_modified` because the PL/SQL Gateway has a stale copy of the content. Instead the PL/SQL Gateway replaces its stale cached copy with the newly generated one and updates the tag and caching level information associated with it.

#### 1.9.3.3 Expires Model

In the Validation model, the PL/SQL Gateway always asks the PL/SQL procedure if it can serve the content from the cache. In the expires model, the procedure preestablishes the content validity period. Therefore, the PL/SQL Gateway can serve the content from its cache without asking the procedure. This further improves performance because no interaction with the database is required.

Assume the same scenario described above for the Validation model, except the procedure uses the Expires model for caching. Once it has generated the content, the procedure calls the following owa_cache procedure:

`owa_cache.set_expires(p_expires, p_level);`

where:

##### Table 1-5 Expires Model Parameters

Parameter | Description
--- | ---
set_expires procedure | Sets up the headers to notify the PL/SQL Gateway that Expires model caching is being used. The PL/SQL Gateway then caches the content to the file system along with the validity period and caching level information.
p_expires | Number of minutes that the content is valid.
p_level | Caching level.

Next, assume the same procedure invoked a second time through the browser. The PL/SQL Gateway detects that it has a cached copy of the content that is expires-based, then checks for its validity by taking the difference between the current time and the time this cache file was created. If this difference is within the validity period, the cached copy is still fresh and served to the browser without any database interaction.

If the difference is not within the validity period, the cached copy is stale. In this case, the PL/SQL Gateway invokes the procedure. The procedure then decides whether to use expires-based caching again. Alternatively, it can use the validation model caching or no caching at all.

### 1.9.4 System- and User-level Caching

The PL/SQL procedure determines whether generated content is system-level content or user-level. This helps the PL/SQL Gateway cache to store less redundant files if more than one user is looking at the same content. It decides this by:

- For system-level content, the procedure passes the string "SYSTEM" as the caching level parameter to the owa_cache functions (set_cache for validation model or set_expires for expires model).

- For user-level content, it passes the string "USER" as the parameter for the caching level.

The difference in the PL/SQL Gateway between system- and user-level caching is how the user information is applied. For system-level caching, user information is not used since the cache can be used by multiple users.

For a user-level cache hit, the user information is a criteria. A user-level cache always overrides a system-level cache. If both a system-level cache and a user-level cache copy exist for a user, the user-level cache is applied.

## 1.10 Common Gateway Interface (CGI) Environment Variables

The OWA_UTIL package provides an API to get the values of CGI environment variables, which serve to provide context to the procedure being executed via the PL/SQL Gateway. Although the PL/SQL Gateway is not operated through CGI, the PL/SQL application invoked from the PL/SQL Gateway can access these CGI environment variables. The following is an alphabetical list of the available CGI Environment Variables:

- AUTHORIZATION

- DAD_NAME

- DOC_ACCESS_PATH

- DOCUMENT_TABLE (see "document_table (document_table_name)" for more information)

- HTTP_ACCEPT

- HTTP_ACCEPT_ENCODING

- HTTP_ACCEPT_CHARSET

- HTTP_ACCEPT_LANGUAGE

- HTTP_COOKIE

- HTTP_HOST

- HTTP_PRAGMA

- HTTP_REFERER

- HTTP_USER_AGENT

- PATH_ALIAS

- PATH_INFO

- REMOTE_ADDR

- REMOTE_HOST

- REMOTE_USER (see "REMOTE_USER CGI Environment Variable" for more information)

- REQUEST_CHARSET (see "REQUEST_CHARSET CGI environment variable" for more information)

- REQUEST_IANA_CHARSET

- REQUEST_METHOD

- REQUEST_PROTOCOL

- SCRIPT_NAME

- SCRIPT_PREFIX

- SERVER_NAME

- SERVER_PORT

- SERVER_PROTOCOL

A PL/SQL application can get the value of a CGI environment variable using the owa_util.get_cgi_env interface.

Syntax:

    owa_util.get_cgi_env(param_name in varchar2) return varchar2;

where:

param_name is the name of the CGI environment variable. param_name is case-insensitive.

### 1.10.1 Overriding CGI Environment Variables

The cgi_env_list DAD parameter is a comma separated list of name and value pairs which can override any environment variables or add new ones. If the name is one of the original environment variables (as listed in "Common Gateway Interface (CGI) Environment Variables"), that environment variable is overridden with the given value. If the name is not in the original list, a new environment variable is added into the list with that same name and value given in the parameter.

Access cgi_env_list through the PL/SQL Gateway configuration file (`wdbsvr.app`). This configuration file describes settings for the PL/SQL Gateway module. For UNIX it is located at:
<ORACLE_HOME>`/Apache/modplsql/cfg/wdbsvr.app`
For NT, it is located at:

<ORACLE_HOME>\Apache\modplsql\cfg\wdbsvr.app

where <ORACLE_HOME> is the location of your Oracle9i Application Server installation.

Example 1:

cgi_env_list=SERVER_NAME=myhost.mycompany.com, REMOTE_USER=testuser

This example overrides the SERVER_NAME and the REMOTE_USER CGI environment variables with the given values since they are part of the original list.

Example 2:

cgi_env_list=MYENV_VAR=testing, SERVER_NAME=,REMOTE_USER=user2

This example overrides the SERVER_NAME and the REMOTE_USER variables. The SERVER_NAME variable is deleted since there is no value given to it. A new environment variable called MYENV_VAR is added since it is not part of the original list. It is assigned the value of "testing".

### 1.10.2 NLS

For PL/SQL Gateway mod_plsql, the following restrictions apply:

- The NLS_LANG parameter of the database must match that of the Oracle HTTP Server powered by Apache, or

- The NLS_LANG parameter of the database and Oracle HTTP Server powered by Apache, must be of fixed character width and both must be the same size.

#### 1.10.2.1 REQUEST_CHARSET CGI environment variable

Every request to the PL/SQL Gateway is associated with a DAD. The CGI environment variable REQUEST_CHARSET is set as follows:

- The REQUEST_CHARSET is set to the default character set in use.

  - For the embedded gateway, this is the database's default character set.

  - For the gateway deployed in the middle-tier (as part of the Oracle HTTP Server), this is the character set information derived from the NLS_LANG environment variable.

The PL/SQL application can access this information via a function call of the form:

    	owa_util.get_cgi_env(`REQUEST_CHARSET');

#### 1.10.2.2 REQUEST_IANA_CHARSE CGI environment variable

This is the IANA (Internet Assigned Number Authority) equivalent of the
REQUEST_CHARSET CGI environment variable. IANA is an authority that globally coordinates the standards for charsets used on the Internet.

| Copyright © 2001 Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index