Skip Headers

Oracle® Application Server mod_plsql User's Guide 10g Release 3 (10.1.3) B25599-01 |
| Home | Solution Area | Contents | Index

Previous | Next |

# 3 Understanding mod_plsql

mod_plsql provides support for deploying PL/SQL-based database applications on the World Wide Web. It is part of Oracle HTTP Server, which ships with Oracle Application Server and Oracle Database.As part of the Oracle HTTP Server, it is the job of mod_plsql to interpret a URL sent by a Web browser to a Web server, call the appropriate PL/SQL subprograms to treat the browser request, then return the generated response to the browser. Typically, mod_plsql responds to a Web browser HTTP request by constructing an HTML page to display. There are additional uses for mod_plsql, of which two are listed subsequently:

- Transfer files from a client machine to or from Oracle Database. You can upload and download text files or binary files.

- Perform custom user authentication in Web applications.

As a plug-in to Oracle HTTP Server, mod_plsql causes stored procedures to be executed in response to HTTP requests. For each URL that is processed, mod_plsql either uses a database session from its connection pool, or creates a new session on the fly and pools it. For mod_plsql to invoke the appropriate database PL/SQL procedure in a URL-processing session, you must first configure a virtual path and associate that path with a Database Access Descriptor (DAD).

A DAD is a named set of configuration values that specify the information necessary to create a session for a specific database and a specific database user/password. This includes the database service name and the Globalization Support setting (for example, language) for the session. Refer to Section 3.2, "Database Access Descriptors (DADs)" for more information.

To develop the stored procedures that are executed by mod_plsql at runtime, you use the PL/SQL Web Toolkit: a set of PL/SQL packages that can be used to obtain information about an HTTP request; specify HTTP response headers, such as cookies, content-type, and mime-type, for HTTP headers; set cookies; and generate standard HTML tags for creating HTML pages. Refer to Oracle Application Server PL/SQL Web Toolkit Reference for more information.

This chapter discusses the following topics:

- Processing Client Requests

- Database Access Descriptors (DADs)

- Invoking mod_plsql

- Transaction Mode

- Supported Data Types

- Parameter Passing

- File Upload and Download

- Path Aliasing (Direct Access URLs)

- Common Gateway Interface (CGI) Environment Variables

- Using Caching with PL/SQL Based Web Applications

- Performance Tuning Areas for mod_plsql

- Restrictions in mod_plsql

## 3.1 Processing Client Requests

mod_plsql is an Oracle HTTP Server plug-in that communicates with the database. It maps browser requests into database stored procedure calls over a SQL*Net connection. It is often indicated by a `/pls` virtual path.

The following scenario provides an overview of what steps occur when a server receives a client request:

Figure 3-1 Overview of the Process When a Server Receives a Client Request

Description of "Figure 3-1 Overview of the Process When a Server Receives a Client Request"

1. The Oracle HTTP Server receives a request containing a virtual path, which is configured to be serviced by mod_plsql.

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

3. By using the configuration information stored in your DAD, mod_plsql connects to the database. The request is forwarded by mod_plsql to the Oracle Database.

4. mod_plsql prepares the call parameters, and invokes the PL/SQL procedure in the application.

5. The PL/SQL procedure generates an HTML page using data and the PL/SQL Web Toolkit accessed from the database.

6. The response is returned to mod_plsql.

7. The Oracle HTTP Server sends the response to the client browser.

The procedure that mod_plsql invokes returns the HTTP response to the client. To simplify this task, mod_plsql includes the PL/SQL Web Toolkit, which contains a set of packages called the owa packages. Use these packages 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.

## 3.2 Database Access Descriptors (DADs)

Each mod_plsql 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 (Oracle Net service name).

- a connect string, if the database is remote.

- a procedure for uploading and downloading documents.

You can also specify 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.

See Also: Oracle HTTP Server Administrator's Guide for descriptions of the DAD parameters and an overview of the mod_plsql configuration files.

## 3.3 Invoking mod_plsql

To invoke mod_plsql in a Web browser, input the URL in the following format:

    protocol://hostname[:port]/DAD_location/[[!][schema.][package.]proc_name[?query_string]]

Table 3-1 Invoking mod_plsql 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 Web server is listening. If omitted, port 80 is assumed.
DAD location | A virtual path to handle PL/SQL requests that you have configured in the Web server. The DAD location can contain only ASCII characters.
! character (optional) | Indicates to use the flexible parameter passing scheme. See Section 3.6.2, "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 standalone.
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 mod_plsql. See "POST, GET, and HEAD Methods" for more information.

Example 3-1 Invoking A Procedure That Does Not Take Arguments

    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 mod_plsql. This is because the `/pls/mydad` indicates that the Web server is configured to invoke mod_plsql. It then uses the DAD associated with `/pls/mydad` and runs the `myproc` procedure stored in `mypackage`.

Example 3-2 Invoking A Procedure That Takes Arguments

    http://www.acme.com:9000/pls/mydad/mypackage.myproc?a=v&b=1

The Web server running on `www.acme.com` and listening at port `9000` handles the request. When the Web server receives the request, it uses the DAD associated with `/pls/mydad` and runs the `myproc` procedure stored in `mypackage`, and passes two arguments, `a` and `b`, with the values `v`, and `1` to the procedure.

Example 3-3 Invoking the Default Procedure Stored in the DAD Configuration

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

The Web server running on `www.acme.com` and listening at port `9000` handles the request. When the Web server receives the request, it uses the DAD associated with `/pls/mydad` and invokes the default procedure configured in the DAD. For example, if the configuration parameter `PlsqlDefaultPage` in the DAD `/pls/mydad` is set to `myschema.mypackage.myproc`, then the procedure `myschema.mypackage.myproc` is invoked for the request.

In this example, the default home page for the `mydad` DAD (as specified in the DAD Configuration) is displayed.

POST, GET, and HEAD Methods

The POST, GET, and HEAD 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.

mod_plsql applications can use any of the methods. Each method is as secure as the underlying transport protocol (HTTP or HTTPS).

- When using 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 using 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.

- When using the HEAD method, it has the same functionality as the GET method. The difference is that only the HTTP status line and the HTTP headers are passed back. No content data is streamed back to the browser. This is useful for monitoring tools in which you are only interested if the request is processed correctly.

- Mixed Mode - In mod_plsql you can pass some of the parameters in a query string and the remaining ones as POST data. For example, if you have a procedure `foo` (a varchar2, b number), and want to pass values "v" and "1" to 'a' and 'b' respectively, you could do so in three ways to create URLs:

  - All values are specified as part of the query string.

    `http://host:port/pls/DAD/foo?a=v&b=1`

  - All values are specified as part of the POST data.

    `http://host:port/pls/DAD/foo, POST data="a=v&b=1"`

  - Some of the parameters are specified in the URL and the rest in the POST data.

    `http://host:port/pls/DAD/foo?a=v, POST data="b=1"`

Note: POST data is generated as part of the input fields on a HTML form. You should not create the POST string manually in the PL/SQL procedure, or in the URL. The Submit operation of the HTML form will generate a POST request and pass the value to your procedure.

## 3.4 Transaction Mode

After processing a URL request for a procedure invocation, mod_plsql performs a rollback if there were any errors. Otherwise, it 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.

## 3.5 Supported Data Types

Because HTTP supports character streams only, mod_plsql supports the following subset of PL/SQL data types:

- NUMBER

- VARCHAR2

- TABLE OF NUMBER

- TABLE OF VARCHAR2

Records are not supported.

## 3.6 Parameter Passing

mod_plsql 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 Section 3.6.1, "Parameter Passing by Name (Overloaded Parameters)" for more information.

- Flexible parameter passing

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

- Large (up to 32K) parameters passing

  See Section 3.6.3, "Large Parameter Passing" for more information.

  Note: mod_plsql handles multi-value variables by storing the values in a PL/SQL table. This enables you to be flexible about how many values the user can pick, and it makes it easy for you to process the user's selections as a unit. Each value is stored in a row in the PL/SQL table, starting at index 1. The first value (in the order that it appears in the query string) of a variable that has multiple values is placed at index 1, the second value of the same variable is placed at index 2, and so on. The PL/SQL application should not rely on the ordering of the arguments passed by mod_plsql, as it can change without notice. If the order of the values in the PL/SQL table is significant to your procedure, you need to modify your PL/SQL application to do the ordering internally. If you do not have variables with multiple values, the order in which the variables appear does not matter, because their values are passed to the procedure's parameters by name, and not by position. The PL/SQL tables used as parameters in the mod_plsql environment must have a base type of `VARCHAR2`. Oracle can convert `VARCHAR2` to other data types such as `NUMBER`, `DATE`, or `LONG`. The maximum length of a `VARCHAR2` variable is 32K. If you cannot guarantee that at least one value will be submitted to the PL/SQL table (for example, the user can select no options), use a hidden form element to provide the first value. Not providing a value for the PL/SQL table produces an error, and you cannot provide a default value for a PL/SQL table.

### 3.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 enables you to overload local or packaged subprograms. Standalone subprograms cannot be overloaded.

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

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

    -- legal PL/SQL, but not for mod_plsql
    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 mod_plsql
    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:9000/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:9000/pls/mydad/my_pkg.my_proc?valnum=34

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, mod_plsql 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 mod_plsql gets a request that has only one value for the `val` parameter, it invokes the procedure with the scalar data type.

Example 3-4 Sending a URL to Execute the Scalar Version of a Procedure

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

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

When mod_plsql gets a request with more than one value for the `val` parameter, it then invokes the procedure with the array data type.

Example 3-5 Sending a URL to Execute the Array Version of a Procedure

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

    http://www.acme.com:9000/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.

### 3.6.2 Flexible Parameter Passing

You can have HTML forms from which users can select any number of elements. If these elements have different names, you would have to create overloaded procedures to handle each possible combination. Alternatively, you could insert hidden form elements to ensure that the names in the query string are consistent each time, regardless of what elements the user chooses. mod_plsql makes this operation easier by supporting 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 mod_plsql. The four parameter interface is supported for compatibility.

#### 3.6.2.1 Two Parameter Interface

    procedure [proc_name]
         (name_array IN [array_type],
         value_array IN  [array_type]);

Table 3-2 Two Parameter Interface Parameters

Parameter | Description
--- | ---
`proc_name` (required) | 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` (required) | Any PL/SQL index-by table of varchar2 type (Example, owa.vc_arr).

Example 3-6 Two Parameter Interface

If you send the following URL:

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

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

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

Note: When using this style of Flexible Parameter Passing, the procedure must be defined with the parameters `name_array` and `value_array`. The datatypes of these arguments should match the datatypes shown in the example.

#### 3.6.2.2 Four Parameter Interface

The four parameter interface is supported for compatibility.

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

Table 3-3 Four Parameter Interface Parameters

Parameter | Description
--- | ---
`proc_name` (required) | 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` (required) | Any PL/SQL index-by table of varchar2 type (Example, owa.vc_arr).

Example 3-7 Four Parameter Interface

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

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

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

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

Note: When using this style of Flexible Parameter Passing, the procedure must be defined with the parameters `num_entries`, `name_array`, `value_array`, and `reserved`. The datatypes of these arguments should match the datatypes shown in the example.

### 3.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 Section 3.6.2, "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.

## 3.7 File Upload and Download

mod_plsql enables 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 document table for each DAD so that uploaded files from different applications are not mixed. Also, the Direct BLOB (Binary Large Object) Download feature enables you to download content from any database table.

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

      http://www.acme.com:9000/pls/mydad/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 for each form submission.

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

This section discusses the following:

- Document Table Definition

- Old Style Document Table Definition

- Configuration Parameters for Document Upload/Downloading

- File Upload

- Specifying Attributes (Mime Types) of Uploaded Files

- Uploading Multiple Files

- File Download

### 3.7.1 Document Table Definition

You can specify the document storage table for each DAD. 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:

    CREATE TABLE MYDOCTABLE (
      NAME               VARCHAR(256)   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 ;
    );

#### 3.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.

#### 3.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, mod_plsql 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'.

#### 3.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, mod_plsql 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.

mod_plsql 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.

#### 3.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.

### 3.7.2 Old Style Document Table Definition

For backward capability with the document model used by older releases of WebDB 2.x, mod_plsql 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

    );

### 3.7.3 Configuration Parameters for Document Upload/Downloading

The following configuration parameters in the DAD affect a document upload/download operation:

- "PlsqlDocumentTablename"

- "PlsqlDocumentPath (Document Access Path)"

- "PlsqlDocumentProcedure (Document Access Procedure)"

- "PlsqlUploadAsLongRaw"

Example 3-8 Parameters for Document Upload/Download

If the configuration for these parameters in a DAD is as follows:

    PlsqlDocumentTablename   scott.my_document_table
    PlsqlUploadAsLongRaw   html
    PlsqlDocumentPath   docs
    PlsqlDocumentProcedure   scott.my_doc_download_procedure

then:

- mod_plsql will retrieve data from, or store to a database table called my_document_table in the scott schema.

- All file extensions except `.html` will be uploaded to the document table as BLOBs. All files with `.html` extension will be uploaded as Long Raw.

- All URLs which have the keyword docs immediately following the DAD location will result in invocation of the procedure scott.my_doc_download_procedure.

  Typically, this procedure will call wpg_docload.download_file to initiate a file download for a file whose name is based on the URL specification.

A simple example with the preceding configuration is:

    http://www.acme.com:9000/pls/dad/docs/index.html

This results in downloading of the file `index.html` from the Long Raw column of the database table scott.my_document_table. Note that the application procedure has full control on the file download to initiate, and has the flexibility to define a more complex `PlsqlDocumentProcedure` that implements file-level access controls and versioning.

Note: The application defined procedure scott.my_doc_download_procedure has to be defined without arguments, and should rely on the CGI environment variables to process the request.

#### 3.7.3.1 PlsqlDocumentTablename

The `PlsqlDocumentTablename` parameter specifies the table for storing documents when file uploads are performed through this DAD.

Syntax:

    PlsqlDocumentTablename  [document_table_name]

    PlsqlDocumentTablename  my_documents

or,

    PlsqlDocumentTablename  scott.my_document_table

#### 3.7.3.2 PlsqlDocumentPath (Document Access Path)

The `PlsqlDocumentPath` parameter specifies the path element to access a document. The `PlsqlDocumentPath` 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://www.acme.com:9000/pls/mydad/docs/myfile.htm

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

Syntax:

    PlsqlDocumentPath  [document_access_path_name]

#### 3.7.3.3 PlsqlDocumentProcedure (Document Access Procedure)

The `PlsqlDocumentProcedure` 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 a file. It knows the filename based on the URL specification. For example, an application can use this to implement file-level access controls and versioning. An example of this is in Section 3.7.7, "File Download".

Syntax:

    PlsqlDocumentProcedure  [document_access_procedure_name]

Example 3-9 PlsqlDocumentProcedure

    PlsqlDocumentProcedure  my_access_procedure

or,

    PlsqlDocumentProcedure  scott.my_pkg.my_access_procedure

#### 3.7.3.4 PlsqlUploadAsLongRaw

The DAD parameter, `PlsqlUploadAsLongRaw`, configures file uploads based on their file extensions. The value of a `PlsqlUploadAsLongRaw` DAD parameter is a one-entry-for-each-line list of file extensions. Files with these extensions are uploaded by mod_plsql 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, and so on) or an asterisk (*) matches any file whose extension has not been listed in the `PlsqlUploadAsLongRaw` setting.

Syntax:

    PlsqlUploadAsLongRaw  [file_extension]
    PlsqlUploadAsLongRaw *

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

Example 3-10 PlsqlUploadAsLongRaw

    PlsqlUploadAsLongRaw  html
    PlsqlUploadAsLongRaw  txt
    PlsqlUploadAsLongRaw  *

### 3.7.4 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 mod_plsql 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. mod_plsql stores the file contents in the database in the document storage table. The table name is derived from the `PlsqlDocumentTablename` DAD setting.

3. The action procedure specified in the `action` attribute of the FORM is run (similar to invoking a mod_plsql procedure without file upload).

   Note: The parsing of HTML documents is deprecated in mod_plsql. mod_plsql used to parse the content of an HTML file when it was uploaded, and identified other files that the HTML document was referring to. This information was then stored into a table. The table name was constructed by appending the name of the document table with "part". This functionality was found to be not of use to customers and has been deprecated, starting in version 9.0.4 of mod_plsql.

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

1. The browser uploads the file listed in the`INPUT type="file"` element.

2. The`write_info` procedure then runs.

3. The procedure writes information from the form fields to a table in the database and returns a page to the user.

   Note: 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, as shown subsequently.

       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 mod_plsql 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`.

See Also: RFC 1867, "Form-Based File Upload in HTML" (IETF)

### 3.7.5 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 Section 3.7.4, "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 mod_plsql when uploading the file.

### 3.7.6 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).

### 3.7.7 File Download

After you have uploaded files to the database, you can download them from the database in three different ways, as described here:

- Define a PL/SQL procedure that calls `wpg_docload.download_file(file_name)` to download file `file_name`.

- Define a virtual path (`PlsqlDocumentPath`) for document downloads in the DAD configuration, and associate a user-defined procedure with that path (`PlsqlDocumentProcedure`). When mod_plsql detects, immediately after the DAD name, the virtual path specified by `PlsqlDocumentPath`, it automatically invokes the user-defined procedure (`PlsqlDocumentProcedure`), which in turn must call `wpg_docload.download_file(file_name)` to initiate download of file `file_name`. This user-defined procedure should have a prototype such that it be invoked without being passed any additional arguments.

  For example, if the DAD "mydad" specifies that `PlsqlDocumentPath` as "`docs`" and `PlsqlDocumentProcedure` is configured `as "myschema.pkg.process_download`", then the procedure "`myschema.pkg.process_download`" is invoked by mod_plsql whenever the URL is of the format `http://www.acme.com:9000/pls/mydad/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 path name of
           -- the file URL, and then returns the part of the path name
           -- 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;

- Use the Direct Binary Large Object (BLOB) Download mechanism to download a BLOB from any database table. You do this by calling a PL/SQL procedure that streams the standard HTTP headers, such as mime-type and content-length, and then invokes `wpg_docload.download_file(blob_name)` to download BLOB `b,ob_name`, as shown here:

  1. Create a stored procedure that calls `wpg_docload.download_file(blob)` where `blob` is of data type BLOB. Since mod_plsql has no information about the contents in the BLOB, you must supply them.

  2. Setup the Content-Type and other headers.

     In the following example, the procedure uses the name from the argument to select a BLOB from a table and initiates the Direct BLOB download:

         create or replace procedure download_blob(name in varchar2) is
         myblob blob;
         begin

     - Select the BLOB out of mytable using the name argument.

           select blob_data into myblob from mytable where blob_name = name;

     - Setup headers which describes the content.

           owa_util.mime_header('text/html', FALSE);
           htp.p('Content-Length: ' || dbms_lob.getlength(myblob));
           owa_util.http_header_close;

     - Initiate Direct BLOB download.

           wpg_docload.download_file(myblob);
           end;

       The structure of the `mytable` table is as follows:

           create table mytable
           (
           blob_name varchar2(128),
           blob_data blob
           );

     When document downloads are initiated but the "Direct BLOB download" is not used, then the argument passed to `wpg_docload.download_file` should be able to uniquely identify the filename to download from the document table. While streaming back the content for such documents, mod_plsql will generate the HTTP response headers based on the information stored in other columns of the document table entry for the filename. The MIME_TYPE, DOC_SIZE, and LAST_UPDATED columns will be used to add response headers of "Content-Type", "Content-Length", and "If-Modified-Since" headers respectively.

Note: Every time you call the `wpg_docload.download_file` API from a procedure, a file download operation is initiated by mod_plsql. During such operations, no other HTML content generated by the procedure is passed back to the browser.

Downloading Documents with Multibyte Characters

If you are using mod_plsql on a Windows platform, and are running against a multibyte database that contains the special character 0x5c as part of a multibyte character set (Japanese or Korean), you must edit the DAD used to access the application, by modifying the file `dads.conf`. To do this:

1. Open the file `ORACLE_HOME``/Apache/modplsql/conf/dads.conf`.

2. Locate the DAD used to access the application.

3. Add the line "`WindowsFileConversion Off`" to this DAD entry.

4. Save the file.

5. Restart Oracle HTTP Server.

If you do not update the DAD configuration, you will experience failures while downloading documents which contain 0x5c in the filename. For example:

- In OracleAS Portal, you will see the download error:

      Error: Document not found (WWC-46000)

- When using mod_plsql against your own PL/SQL application, file downloads will result in the error:

      HTTP-404 Not Found

## 3.8 Path Aliasing (Direct Access URLs)

Path Aliasing enables applications using mod_plsql to provide direct reference to its objects using simple URLs. The Path Aliasing functionality is a generalization of how the document download functionality is provided. The following configuration parameters in the DAD are used for Path Aliasing:

- PlsqlPathAlias

- PlsqlPathAliasProcedure

For Example, if the configuration for these parameters in a DAD is as follows:

    PlsqlPathAlias   myalias
    PlsqlPathAliasProcedure   scott.my_path_alias_procedure

then, all URLs that have the keyword myalias immediately following the DAD location will invoke the procedure `scott.my_path_alias_procedure`. Based on the URL specification, this procedure can initiate an appropriate response.

Note: The application defined procedure `scott.my_path_alias_procedure` has to be defined to take one argument of type `varchar2` called p_path. This argument will receive everything following the keyword used in `PlsqlPathAlias`. For example, in the preceding configuration, the URL: `http://www.acme.com:9000/pls/dad/myalias/MyFolder/MyItem` will result in the procedure scott.my_path_alias_procedure receiving the argument MyFolder/MyItem.

## 3.9 Common Gateway Interface (CGI) Environment Variables

The OWA_UTIL package provides an API to get the values of CGI environment variables. The variables provide context to the procedure being executed through mod_plsql. Although mod_plsql is not operated through CGI, the PL/SQL application invoked from mod_plsql can access these CGI environment variables.

The list of CGI environment variables is as follows:

- HTTP_AUTHORIZATION

- DAD_NAME

- DOC_ACCESS_PATH

- HTTP_ACCEPT

- HTTP_ACCEPT_CHARSET

- HTTP_ACCEPT_LANGUAGE

- HTTP_COOKIE

- HTTP_HOST

- HTTP_PRAGMA

- HTTP_REFERER

- HTTP_USER_AGENT

- PATH_ALIAS

- PATH_INFO

- HTTP_ORACLE_ECID

- DOCUMENT_TABLE

- REMOTE_ADDR

- REMOTE_HOST

- REMOTE_USER

- REQUEST_CHARSET (refer to Section 3.9.2.1, "REQUEST_CHARSET CGI Environment Variable")

- REQUEST_IANA_CHARSET (refer to Section 3.9.2.2, "REQUEST_IANA_CHARSET CGI Environment Variable")

- 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;

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

### 3.9.1 Adding and Overriding CGI Environment Variables

The `PlsqlCGIEnvironmentList` DAD parameter is a one-entry-for-each-line list of name and value pairs that can override any environment variables or add new ones. If the name is one of the original environment variables (as listed in Section 3.9, "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.

Note: Refer to the Oracle HTTP Server Administrator's Guide for information about the mod_plsql Configuration Files.

If no value is specified for the parameter, then the value is obtained from the Oracle HTTP Server. With Oracle HTTP Server, you can pass the DOCUMENT_ROOT CGI Environment variable by specifying:

    PlsqlCGIEnvironmentList DOCUMENT_ROOT

New environment variables passed in through this configuration parameter are available to the PL/SQL application through the owa_util.get_cgi_env interface.

Example 3-11 PlsqlCGIEnvironmentList with Environment Variable Overrides

    PlsqlCGIEnvironmentList SERVER_NAME=myhost.mycompany.com
    PlsqlCGIEnvironmentList 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 3-12 PlsqlCGIEnvironmentList with New Environment Variable

    PlsqlCGIEnvironmentList MYENV_VAR=testing
    PlsqlCGIEnvironmentList SERVER_NAME=
    PlsqlCGIEnvironmentList 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".

### 3.9.2 PlsqlNLSLanguage

For mod_plsql, the Globalization Support setting is controlled by the DAD level setting of `PlsqlNLSLanguage`. If PlsqlNLSLanguage is not configured at the DAD level, the Globalization Support configuration is picked up from the environment setting of the Oracle NLS_LANG parameter. For details on this parameter, refer to the "mod_plsql" section in the Oracle HTTP Server Administrator's Guide.

#### 3.9.2.1 REQUEST_CHARSET CGI Environment Variable

The CGI environment variable REQUEST_CHARSET is set based on the setting of `PlsqlNLSLanguage`. If `PlsqlNLSLanguage` is not configured at the DAD level, the Globalization Support setting is picked up from the environment setting of the Oracle NLS_LANG parameter.

The PL/SQL application can access this information by a function call. For example:

    owa_util.get_cgi_env('REQUEST_CHARSET');

#### 3.9.2.2 REQUEST_IANA_CHARSET 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 on the Internet.

The PL/SQL application can access this information by a function call. For example:

    owa_util.get_cgi_env('REQUEST_IANA_CHARSET');

## 3.10 Using Caching with PL/SQL Based Web Applications

Caching can improve the performance of PL/SQL based Web applications. To improve performance, you can cache Web content generated by PL/SQL procedures in the middle tier and decrease the database workload.

This section covers the techniques used in caching, including the following:

- Using the Validation Technique - An application asks the server if the page has been modified since it was last presented.

- Using the Expires Technique - Based upon a specific time period, the PL/SQL based Web application determines the page will be cached, or should be generated again.

- System- and User-level Caching with PL/SQL Based Web Applications - This is valid whether you are using the Validation Technique or the Expires Technique. The level of caching is determined by whether a page is cached for a particular user or for every user in the system.

These techniques and levels are implemented using `owa_cache` packages located inside the PL/SQL Web Toolkit.

See Also: Oracle Application Server PL/SQL Web Toolkit Reference

### 3.10.1 Using the Validation Technique

In general, the validation technique basically asks the server if the page has been modified since it was last presented. If it has not been modified, the cached page will be presented to the user. If the page has been modified, a new copy will be retrieved, presented to the user and then cached.

There are two methods which use the Validation Technique: Last-Modified method, and the Entity Tag method. The next two sections show how these techniques are used in the HTTP protocol. Although the PL/SQL Gateway does not use the HTTP protocol, many of the same principles are used.

#### 3.10.1.1 Last-Modified

When a Web page is generated using the HTTP protocol, it contains a Last-Modified Response Header. This header indicates the date, relative to the server, of the content that was requested. Browsers save this date information along with the content. When subsequent requests are made for the URL of the Web page, the browser then:

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 server.

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

If the two do not 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 browser must replace its cache entry with the new content and new date information.

#### 3.10.1.2 Entity Tag Method

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 browser. Servers 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 Last Modified method. Servers generate the ETag as part of the Response Header. The browser stores this opaque header value along with the content that is steamed back. When the next request for this content arrives, the browser passes the If-Match header with the opaque value that it stored to the server. Because the server generated this opaque value, it is able to determine what to send back to the browser. The rest is exactly like the Last-Modified validation method as described earlier.

#### 3.10.1.3 Using the Validation Technique for mod_plsql

Using HTTP validation caching as a framework, the following is the Validation Model for mod_plsql.

PL/SQL based Web applications that want to control the content being served should use this type of caching. This technique offers some moderate performance gains. One example of this would be a Web application that serves dynamic content that could change at any given time. In this case, the Web application needs full control over what is being served. Validation caching always asks the Web application whether the cached content is stale or not before serving it back to the browser.

Figure 3-2 shows the use of the validation technique for mod_plsql.

1. The Oracle HTTP Server receives a PL/SQL procedure request from a client server. The Oracle HTTP Server routes the request to mod_plsql.

2. mod_plsql prepares the request.

3. mod_plsql invokes the PL/SQL procedure in the Web application. mod_plsql passes the usual Common Gateway Interface (CGI) environment variables to the Web application.

4. The PL/SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the `owa_cache` procedure from the PL/SQL Web Toolkit to set the tag and cache level:

       owa_cache.set_cache(p_etag, p_level);

Table 3-4 Validation Model Parameters

Parameter | Description
--- | ---
`set_cache` procedure | Sets up the headers to notify mod_plsql that the content being streamed back can be cached. Then, the mod_plsql 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.

1. The HTML is returned to mod_plsql.

2. mod_plsql stores the cacheable content in its file system for the next request.

3. The Oracle HTTP Server sends the response to the client browser.

Figure 3-2 Validation Technique

Description of "Figure 3-2 Validation Technique"

#### 3.10.1.4 Second Request Using the Validation Technique

Using the Validation Technique for mod_plsql, a second request is made by the client browser for the same PL/SQL procedure.

Figure 3-3 shows the second request using the Validation Technique.

1. mod_plsql detects that it has a cached content for the request.

2. mod_plsql forwards the same tag and caching level information (from the first request) to the PL/SQL procedure as part of the CGI environment variables.

3. The PL/SQL procedure uses these caching CGI environment variables to check if the content has changed. It does so by calling the following `owa_cache` functions from the PL/SQL Web Toolkit:

       owa_cache.get_etag;
       owa_cache.get_level;

   These owa functions get the tag and caching level.

4. The Web application sends the caching information to mod_plsql.

5. Based on that information determines whether the content needs to be regenerated or can be served from the cache.

   1. If the content is still the same, the procedure calls the `owa_cache.set_not_modified` procedure and generates no content. This causes mod_plsql to use its cached content. The cached content is directly streamed back to the browser.

   2. If the content has changed, it generates the new content along with a new tag and caching level. mod_plsql replaces its stale cached copy with a new one and updates the tag and caching level information. The newly generated content is streamed back to the browser.

Figure 3-3 Validation Technique-Second Request

Description of "Figure 3-3 Validation Technique-Second Request"

### 3.10.2 Using the Expires Technique

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

This caching technique offers the best performance. Use if your PL/SQL based Web application is not sensitive to serving stale content. One example of this is an application that generates news daily. The news can be set to be valid for 24 hours. Within the 24 hours, the cached content is served back without contacting the application. This is essentially the same as serving a file. After 24 hours, mod_plsql will again fetch new content from the application.

Assume the same scenario described for the Validation model, except the procedure uses the Expires model for caching.

Figure 3-4 shows the use of the expires technique for mod_plsql.

1. The Oracle HTTP Server receives a PL/SQL Server Page request from a client server. The Oracle HTTP Server routes the request to mod_plsql.

2. The request is forwarded by mod_plsql to the Oracle Database.

3. mod_plsql invokes the PL/SQL procedure in the application and passes the usual Common Gateway Interface (CGI) environment variables to the application.

4. The PL/SQL procedure generates content to pass back. If the PL/SQL procedure decides that the generated content is cacheable, it calls the `owa_cache` procedure from the PL/SQL Web Toolkit to set the validity period and cache level:

       owa_cache.set_expires(p_expires, p_level);

Table 3-5 Expires Model Parameters

Parameter | Description
--- | ---
`set_expires` procedure | Sets up the headers to notify mod_plsql that Expires caching is being used. mod_plsql 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.

1. The HTML is returned to mod_plsql.

2. mod_plsql stores the cacheable content in its file system for the next request.

3. The Oracle HTTP Server sends the response to the client browser.

Figure 3-4 The Expires Technique

Description of "Figure 3-4 The Expires Technique"

Second Request Using the Expires Technique

Using the same expires model explained earlier, a second request is made by the client browser for the same PL/SQL procedure.

Figure 3-5 shows the second request using the Expires Technique.

1. mod_plsql detects that it has a cached copy of the content that is expires-based.

2. mod_plsql checks the content's validity by taking the difference between the current time and the time this cache file was created.

   1. If this difference is within the validity period, the cached copy is still fresh and will be used without any database interaction. The cached content is directly streamed back to the browser.

   2. If the difference is not within the validity period, the cached copy is stale. mod_plsql invokes the PL/SQL procedure and generates new content. The procedure then decides whether to use expires-based caching again. If so, it also determines the validating period for this new content. The newly generated content is streamed back to the browser.

Figure 3-5 The Expires Technique-Second Request

Description of "Figure 3-5 The Expires Technique-Second Request"

### 3.10.3 System- and User-level Caching with PL/SQL Based Web Applications

A 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). This is for every user that shares the cache.

  By using system-level caching, you can save both space in your file system and time for all users in the system. One example of this would be a Web application that generates content that is intended for everybody using the Web application. By caching the content with the system-level setting, only one copy of the content is cached in the file system. Furthermore, every user on that system benefits since the content is served directory from the cache.

- For user-level content, it passes the string `USER` as the parameter for the caching level. This is for a specific user that is logged in. The stored cache is unique for that user. Only that user can use the cache. The type of user is determined by the authentication mode. Refer to the following table for the different types of users.

Table 3-6 Type of User Determined by Authentication Mode

Authentication Mode | Type of User
--- | ---
Single Sign On (SSO) | Lightweight user
Basic | Database user
Custom | Remote user

For example, if no user customizes a PL/SQL based Web application, then the output can be stored in a system-level cache. There will be only one cache copy for every user on the system. User information is not used since the cache can be used by multiple users.

However, if a user customizes the application, a user-level cache is stored for that user only. All other users still use the system level cache. For a user-level cache hit, the user information is a criteria. A user-level cache always overrides a system-level cache.

See Also: Section 2.2, "Authenticating Users Through mod_plsql" for more information on authentication modes.

PL/SQL Web Toolkit Functions (owa_cache package)

Your decision whether to use the Validation technique or the Expires technique determines which `owa_cache` functions to call.

The `owa_cache` package contains 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.

Table 3-7 lists the primary functions to call.

Table 3-7 Primary owa_cache Functions

owa Functions | Purpose
--- | ---
`owa_cache.set_cache (p_etag IN varchar2, p_level IN varchar2)` | Validation Model - Sets up the headers. `p_etag` parameter tags the generated content. `p_level` parameter is the caching level to use.
`owa_cache.set_not_modified` | Validation Model - Sets up the headers to notify mod_plsql to use the cached content. Only used when a validation -based cache hit occurs.
`owa_cache.get_level` | Validation Model - Gets the caching level, `USER` or `SYSTEM`. Returns null if the cache is not hit.
`owa_cache.get_etag` | Validation Model - Gets the tag associated with the cached content. Returns null if the cache is not hit.
`owa_cache.set_expires(p_expires IN number, p_level IN varchar2)` | Expires Model - Sets up the headers. `p_expires` parameter is the number of minutes the content is valid. `p_level` parameter is the caching level to use.

## 3.11 Performance Tuning Areas for mod_plsql

When tuning mod_plsql to improve the performance of PL/SQL based Web applications, it is important to be familiar with the internal working of mod_plsql. This section presents a basic overview of some mod_plsql functionality.

This section covers the following topics:

- Connection Pooling with mod_plsql

- Closing Pooled Database Sessions

- Detecting Dead Database Connections in a Connection Pool

### 3.11.1 Connection Pooling with mod_plsql

On UNIX platforms, the Database Server connection pooling logic supplied with mod_plsql can be best explained with an example. Consider the following typical scenario:

1. The Oracle HTTP Server listener is started. There are no database connections in the connection pool maintained by mod_plsql.

2. A browser makes a mod_plsql request (R1) for Database Access Descriptor (DAD) D1.

3. One of the Oracle HTTP Server processes (`httpd` process P1) starts servicing the request R1.

4. mod_plsql in process P1 checks its connection pool and finds that there are no database connections in its pool for that user request.

5. Based on the information in DAD D1, mod_plsql in process P1 opens a new database connection, services the PL/SQL request, and adds the database connection to its pool.

6. From this point on, all subsequent requests to process P1 for DAD D1 can now make use of the database connection pooled by mod_plsql.

7. If a request for DAD D1 gets picked up by another process (process P2), then mod_plsql in process P2 opens its own database connection, services the request, and adds the database connection to its pool.

8. From this point on, all subsequent requests to process P2 for DAD D1 can now make use of the database connection pooled by mod_plsql.

9. Now, assume that a request R2 is made for DAD D2 and this request gets routed to process P1.

10. mod_plsql in process P1 does not have any database connections pooled for DAD D2, and a new database session is created for DAD D2 and pooled after servicing the request. Process P1 now has two database connections pooled, one for DAD D1 and another for DAD D2.

    The important details in the example shown in steps 1-10 are:

    1. Each Oracle HTTP Server process serves all types of requests, such as static files requests, servlet requests, and mod_plsql requests. There is no control on which Oracle HTTP Server process services the next request.

    2. One Oracle HTTP Server process cannot use or share the connection pool created by another process.

    3. Each Oracle HTTP Server process pools at most one database connection for each DAD.

    4. User sessions are switched within a pooled database connection for a DAD. For DADs based on Oracle Application Server Single Sign-On (SSO), proxy authentication is used to switch the user session. For non-SSO users, using HTTP basic authentication with the username and password not in the DAD, users are re-authenticated on the same connection.

    5. Multiple DADs may point to the same database instance, but database connections are not shared across DADs even within the same process.

    6. Unused DADs do not result in any database connections.

In the worst-case scenario, the total number of database connections pooled by mod_plsql is a factor of the total number of active DADs multiplied by the number of Oracle HTTP Server (`httpd`) processes running at any given time for a single Oracle HTTP Server instance. If you have configured the Oracle HTTP Server processes to a high number, you need to configure the back-end database to handle a corresponding number of database sessions, and remember that this configuration value needs to be multiplied times the number of Oracle HTTP Server instances that use the back-end database.

For example, if there are three Oracle HTTP Server instances configured to spawn a maximum of 50 `httpd` processes each, plus two active DADs, you need to set up the database to allow 300 (`3*50*2`) sessions. This number does not include any sessions that are needed to allow other Web applications to connect.

On Windows platforms, the Oracle HTTP Server runs as a single process. On such systems, the mod_plsql connection pool is shared across threads, and the total number of database connections is a factor of the number of concurrent requests for each DAD. Due to the sharing of database connections across threads, Section 4.3.4, "Two-Listener Strategy" does not apply to Windows systems.

### 3.11.2 Closing Pooled Database Sessions

Pooled database sessions are closed under the following circumstances:

1. When a pooled connection has been used to serve a configured number of requests.

   By default, each connection pooled by mod_plsql is used to service a maximum of 1000 requests, and then the database connection is shut down and reestablished on the next mod_plsql request. This is done to make sure that any resource leaks in the PL/SQL based Web application, or in the Oracle client server side, do not adversely affect the system. Change the default value of `1000` by tuning the DAD configuration parameter `PlsqlMaxRequestsPerSession`.

2. When a pooled connection has been idle for an extended period of time.

   By default, the cleanup thread in mod_plsql cleans up each pooled connection after 15 minutes of idle time. The value for idle session timeout is controlled by the configuration setting `PlsqlIdleSessionCleanupInterval`. If your site is accessing mod_plsql content less frequently, the idle session cleanup happens more frequently, and users will establish database connections that are not being used too often. In such situations, consider increasing the default setting of `PlsqlIdleSessionTimeoutInterval` for better performance. Note that keeping pooled database connections open for a longer time means additional load on the database to allow for more sessions.

3. On UNIX systems, when the Oracle HTTP Server process goes down.

   On UNIX systems, the Oracle HTTP Server configuration parameter `MaxRequestsPerChild` governs when an Oracle HTTP Server process will be shut down. For example, if this parameter is set to `5000`, each Oracle HTTP Server process would serve exactly 5000 requests before it is shut down. Oracle HTTP Server processes could also start up and shut down as part of Oracle HTTP Server maintenance based on the configuration parameters `MinSpareServers`, `MaxSpareServers`, and `MaxClients`.

   An incorrect configuration of Oracle HTTP Server could result in a setup where Oracle HTTP Server processes are started up and shut down heavily, resulting in ineffective mod_plsql connection pooling. For best performance, configure Oracle HTTP Server such that each Oracle HTTP Server process remains active for a certain period of time, thus preventing the processes from shutting down.

   See Also: The section "mod_plsql" in the Oracle HTTP Server Administrator's Guide.

4. When mod_plsql detects dead connections in its connection pool.

   See Section 3.11.3, "Detecting Dead Database Connections in a Connection Pool" for more information.

### 3.11.3 Detecting Dead Database Connections in a Connection Pool

mod_plsql maintains a pool of connections to the database, and reuses established database connections for subsequent requests. If there is no response from a database connection in a connection pool, mod_plsql detects this, discards the dead connection, and creates a fresh database connection for subsequent requests.

The dead database connection detection feature of mod_plsql eliminates the occurrence of random errors when a database node or instance goes down. This feature is also extremely useful in high availability configurations like Real Application Cluster (RAC). If a node in an RAC cluster has gone down, mod_plsql detects this and immediately starts servicing requests using the other RAC nodes.

By default, when a RAC node or database instance goes down and mod_plsql had previously pooled connections to the node, the first mod_plsql request which uses a dead connection in its pool will result in a failure response of HTTP-503 being sent back to the end-user. This failure is then used by mod_plsql to trigger the detection and removal of all dead connections in its pool. mod_plsql pings all connection pools that were created before the node failure, and this ping operation is performed at the time of processing the next request that uses a pooled connection. If the ping operation fails, the database connection is discarded, and a new connection is created and processed.

Note: If after node failure, multiple mod_plsql requests come in concurrently, and mod_plsql has not yet detected the first dead connection, there could be multiple failures at that instant.

See Also: Section 3.11.2, "Closing Pooled Database Sessions" for information on other scenarios where mod_plsql closes dead database connections even when no mod_plsql request is made to the server.

mod_plsql provides two configuration options for tuning the dead database connection detection feature:

- Specifying the Option to Detect Dead Database Connections

- Specifying the Timeout Period

#### 3.11.3.1 Specifying the Option to Detect Dead Database Connections

mod_plsql corrects connections after it detects a failure that could be caused by a database node going down. This is controlled by the `PlsqlConnectionValidation` parameter. For details on the PlsqlConnectionValidation parameter, refer to the "mod_plsql" section in the Oracle HTTP Server Administrator's Guide.

See Also: Table 4-1, "Database Access Descriptor (DAD) Parameters Recommended Setting Summary"

#### 3.11.3.2 Specifying the Timeout Period

When the `PlsqlConnectionValidation` parameter is set to `Automatic` or `AlwaysValidate`, mod_plsql attempts to test pooled database connections.

You can specify the timeout period for mod_plsql to test a bad database connection in a connection pool. This is controlled by the `PlsqlConnectionTimeout` parameter, which specifies the maximum time mod_plsql should wait for the test request to complete before it assumes that a connection is not usable.

For details on the PlsqlConnectionTimeout parameter, refer to the "mod_plsql" section in the Oracle HTTP Server Administrator's Guide.

See Also: Table 4-1, "Database Access Descriptor (DAD) Parameters Recommended Setting Summary"

## 3.12 Restrictions in mod_plsql

The following restrictio ns exist in mod_plsql:

- The maximum length of the HTTP cookie header is 32000 bytes. Values higher than this generate an error.

- The maximum length of any single cookie within the HTTP cookie is 3990. Values higher than this generate an error. This limit is due to the OCI array bind limit of strings in arrays.

- There is a hard maximum cookie limit in mod_plsql that limits the number of cookies being set at any given time. That limit is set to 20. Anything over 20 will be dropped.

- The PL/SQL Gateway does not support calling procedures with OUT parameters to be called from a Web interface. Doing this may result in ORA-6502 errors. The recommended approach is not to call any procedure that has OUT variables in it. However, the current architecture will let you modify a value as long as the modified value does not exceed the length that was passed in. Existing applications that encounter this problem need to be modified in one of the following ways:

  - Implement wrappers for procedures with OUT parameters so that such procedures are not invoked directly through a browser URL.

  - Create a local variable that gets assigned the value of the parameter being passed in, and is then used for all internal changes.

- The total number of name value pairs that can be passed to a PL/SQL procedure is 2000.

- mod_plsql limits the size of a single parameter that can be passed to a procedure to 32512 bytes.

- It is not possible to use identical DAD locations in different virtual hosts.

- The maximum value allowed for the `PlsqlCacheMaxSize` and `PlsqlCacheTotalSize` parameters is 4294967296 bytes (4 Gigabytes). If you specify a value higher than this, mod_plsql issues a warning and sets the value to 4 GB internally.