Oracle Internet Application Server Using mod_plsql Release 1.0.1 A83590-02 |
|  | Contents | Index

# 1
mod_plsql Overview

Oracle Internet Application Server (iAS) consolidates Oracle's middle-tier products into a single solution for development and deployment of Web applications. The standard version of iAS version 1.0 includes:

- Oracle HTTP Server (powered by Apache) and Servlet Engine

- Oracle Java Server Pages (JSP) Engine

- mod_plsql

- Oracle 8i Cache

- Oracle Tools (included in Enterprise Edition)

mod_plsql provides support for building and deploying PL/SQL-based applications on the Web. PL/SQL stored procedures can retrieve data from database tables and generate HTTP responses containing data and code to display in a Web browser. mod_plsql supports other Oracle products such as WebDB 2.2 and includes a number of new features.

## 1.1 Stateless and Stateful modes

The database session state includes the state of PL/SQL package variables, application state, and transaction state.

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.

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

iAS provides two configurations for deploying PL/SQL-based Web applications:

- iAS plus mod_plsql supports running in stateless mode. This is the recommended configuration for users who want to develop stateless PL/SQL-based Web applications.

- iAS plus mod_OSE supports running in both stateless and stateful mode. This is the recommended configuration for users who want to develop 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 destoyed for every HTTP request. For more information, see the mod_OSE documentation.

## 1.2 Database Access Descriptors

Each mod_plsql request is associated with a database access descriptor (DAD), a named set of configuration values used for database access. A DAD specifies information such as:

- the database alias (SQL*Net V2 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 will be prompted to enter a username and password when the URL is invoked.

## 1.3 Processing client requests

The following occurs when a server receives a request:

1. The Web server receives a mod_plsql request from a client and forwards the request to mod_plsql.

2. mod_plsql uses the DAD's configuration values (see "Configuring mod_plsql" for more information) to determine how to connect to the database.

3. mod_plsql connects to the database, prepares the call parameters, and invokes the PL/SQL procedure in the database.

4. The PL/SQL procedure generates an HTML page, which can include dynamic data accessed from tables in the database as well as static data.

5. The output from the procedure is returned via the response buffer back to mod_plsql and the client.

The procedure that mod_plsql invokes should return HTML data back to the client. To simplify this task, mod_plsql comes with 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. You 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 mod_plsql

To invoke mod_plsql in a Web browser, the URL must typically be in the following format:

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

where:

protocol can be either `http` or`https`. For SSL, use https.

hostname is the machine where the Web server is running.

port is the port at which the application server is listening. If omitted, port 80 is assumed.

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

DAD is the DAD entry to be used for this URL.

! character, if present, indicates that flexible parameter passing scheme must be used. See"Flexible Parameter Passing" for more information.

schema is 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 is the package that contains the PL/SQL stored procedure. If omitted, the procedure is stand-alone.

proc_name specifies the PL/SQL stored procedure to run. This must be a procedure and not a function. It can accept only IN arguments.

?query_string specifies parameters (if any) for the stored procedure. The string follows the format of the GET method. For example:

- Multiple parameters are separated with the & character, and 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 will be done automatically for you.

- The HTTP request may also choose the HTTP POST method to post data to mod_plsql. See "POST and GET Methods" for more information.

For example, if 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` would handle the request. When the Web server receives the request, it will pass the request to mod_plsql. This is because the pls prefix indicates that the Web server is configured to invoke mod_plsql. mod_plsql then uses the DAD associated with mydad and runs the myproc procedure stored in mypackage.

You can specify a URL without a DAD, schema or stored procedure name. For example, if you specify

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

then the default home page for the `mydad` DAD (as specified on the mod_plsql configuration page) displays.

If you specify

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

the default DAD's default home page is invoked.

Generally, you do not need to be concerned with the order in which PL/SQL parameters are given in the URL or the HTTP header, because the parameters are passed by name. However, there are some exceptions to this rule. Please refer to Parameter passing for more information.

### 1.4.1 POST and GET Methods

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

mod_plsql applications can use either method. The method that you use is as secure as the underlying transport protocol (http or https).

When you use the POST method, parameters are passed in the request body. When you use the GET method, parameters are passed using a query string. These methods are described in the HTTP 1.1 specification, which is available at the W3C web site at:

    http://www.w3.org/Protocols/HTTP/1.1/draft-ietf-http-v11-spec-rev-01.txt

The limitation of the GET 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.

Generally, if you are passing large amounts of parameter data to the server, you should use the POST method instead.

## 1.5 Overview of mod_plsql Features

### 1.5.1 Authentication

mod_plsql provides levels of authentication in addition to those provided by the Web Server itself. Whereas the Web server protects documents, virtual paths, etc., mod_plsql protects users logging into the database or running a PL/SQL Web application.

#### 1.5.1.1 Database Controlled Authentication

mod_plsql supports authentication at the database level. It uses HTTP Basic Authentication but authenticates credentials by using them to attempt to log on to the database. Authentication is verified against a user database account, using user names and passwords that are either:

- stored in the DAD. The end user is not required to log in. This method is useful for Web pages that provide public information

- provided by the users via a browser-based basic HTTP authentication dialog box. The end user must provide a username and password in the dialog box.

#### 1.5.1.2 Deauthentication

mod_plsql allows users to log off (clear HTTP authentication information) programatically through a PL/SQL procedure without having to exit all instances of the browser. Because of the use of cookies, this feature is supported on Netscape 3.0 or higher and Internet Explorer. On other browsers, the user may have to exit the browser to deauthenticate.

Another method of deauthentication is to add `/logmeoff` after the DAD in the URL, for example

    http://myhost:2000/pls/myDAD/logmeoff

#### 1.5.1.3 Custom Authentication

Custom authentication enables applications to authenticate users within the application itself, not at the database level.

You can enable custom authentication using the Custom Authentication parameter on mod_plsql configuration page, or using the custom_auth parameter. This parameter can be set to one of the following values:

- Basic - authentication is performed using basic HTTP authentication. Most applications will use Basic authentication.

- Global Owa - authorization id performed in the OWA package schema.

- Custom Owa - authorization is performed using packages and procedures in the user's schema, or if not found, in the OWA package schema

- PerPackage - authentication is performed by packages and procedures in the user's schema

- Single Sign-On - authentication is performed using the Oracle Single Sign-On feature of the Login Server. You can use this mode only if your application is set up to work with the Login Server

##### 1.5.1.3.1 Implementing the authorize function

Custom authentication needs a static username/password to be stored in a configuration file, and cannot be combined with the dynamic username/password authentication.

The syntax of the authorize function is:

    function authorize return boolean;

To enable custom authentication, you must

1. Set the level of authentication by editing the privcust.sql file

2. Reload it

3. Implement the authentication function.

mod_plsql uses the username/password provided in the DAD to log into the database. Once the login is complete, authentication control is passed to the application. Application-level PL/SQL hooks (callback functions) are then called. The implementations for these callback functions are left to the application developers. The return value of the callback function determines if the authentication succeeded or failed: if the function returns TRUE, authentication succeeded. If it returns FALSE, authentication failed and code in the application is not executed.

You can place the authentication function in different locations, depending on when it is to be invoked:

If you want the same authentication function to be invoked for all users and for all procedures, change the line in the privcust.sql file to:

    owa_sec.set_authorization(OWA_SEC.GLOBAL)

and implement the owa_custom.authorize function in the OWA Package schema, which contains the PL/SQL Web Toolkit.

If you want a different authentication function to be invoked for each user and for all procedures, change the line in the privcust.sql file to:

    owa_sec.set_authorization(OWA_SEC.CUSTOM)

and implement the owa_custom.authorize function in each user's schema. For users who do not have that function in their schema, the owa_custom.authorize function in the OWA package schema will be invoked instead.

For 3.0 users: if you implemented owa_init.authorize in each user's schema, you need to migrate the function to each user's owa_custom package.

If you want the authentication function to be invoked for all users but only for procedures in a specific package or for anonymous procedures, change the line in the privcust.sql file to:

     owa_sec.set_authorization(OWA_SEC.PER_PACKAGE)

and implement the authorize function in that package in each user's schema. If the procedure is not in a package, then the anonymous authorize function is called instead. The following table summarizes the parameter values:

OWA_SEC.NO_CHECK | N/A | N/A
OWA_SEC.GLOBAL | All packages | owa_custom.authorize in the OWA package schema
OWA_SEC.PER_PACKAGE | Specified package | packageName.authorize in the user's schema
OWA_SEC.PER_PACKAGE | Anonymous procedures | authorize in the user's schema
OWA_SEC.CUSTOM | All package | owa_custom.authorize in the user's schema, or, if not found, in the OWA package schema

When you use custom authentication, you can use the subprograms in the owa_sec package. You should not use owa_sec if you are not using custom authentication.

### 1.5.2 Transaction model

After processing a URL request for a procedure invocation, mod_plsql 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.

### 1.5.3 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 "Overloaded parameters" for more information.

- Flexible parameter passing

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

- Large (up to 32K) parameters.

  See "Large parameters" for more information.

#### 1.5.3.1 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, it is impossible for mod_plsql to know which version of the subprogram to call.

For example, PL/SQL allows you to define the two procedures in the example below. If parameter names for these procedures are the same, an error occurs when you try to use them 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 something like:

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

The URL to invoke the second version of the procedure looks something like:

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

#### 1.5.3.2 Overloading and PL/SQL Arrays

If you have overloaded PL/SQL procedures where the parameter names are identical, but where 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. When it gets a request with more than one value for the val parameter, it then invokes the procedure with the array data type.

Example 1: If you send the following URL:

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

the scalar version of the procedure executes.

Example 2: If you send the following URL:

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

the array version of the procedure executes.

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

#### 1.5.3.3 Flexible Parameter Passing

You can have HTML forms from which users can select any number of elements. If these elements have different names, you must create overloaded procedures to handle each possible combination, or you could insert hidden form elements to ensure that the names in the query string are consistent each time, regardless of which elements the user chooses.

mod_plsql makes this easier by supporting a flexible parameter passing scheme. In order to use flexible parameter passing for a URL-based procedure invocation, prefix the name of the procedure with a `!' character in the URL. The procedure must have the following signature:

    procedure [proc_name] is

    		  	     name_array  IN  [array_type]

    	       value_array IN  [array_type],

    where:

[proc_name] is the name of the PL/SQL procedure that you are invoking.

name_array specifies the names from the query string (indexed from 1) in the order submitted.

value_array specifies the values from the query string (indexed from 1) in the order submitted.

[array_type] is any PL/SQL index-by table of varchar2 type (e.g., owa.vc_arr).

Note The above is a two parameter interface, which is recommended for use with mod_plsql. A four parameter interface is supported for compatibility.

Example 1: If you send the following URL:

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

The `!' prefix tells mod_plsql that it must use flexible parameter passing. It will invoke procedure scott.myproc and pass it the following two arguments:

    num_entries ==] 3

    reserved ==] ()

Example 2: 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 `!' prefix tells mod_plsql that it must use flexible parameter passing. It will invoke procedure`scott.my_pkg.myproc` and pass it the following four arguments:

    num_entries ==] 3

    name_array ==] (`x', `y', `x');

    values_array ==] (`a', `b', `c')

    reserved ==] ()

#### 1.5.3.4 Large parameters

Section 1.5.3.2 and Section 1.5.3.3 above indicate that you can use mod_plsql to invoke procedures with either scalar or index-by table of varchar2 arguments. The values passed as scalar arguments and values that are 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.5.4 File Upload and Download

mod_plsql 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 row of the table.

- 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://myhost/mysite/pls/docs/cs250/lecture1.htm

  This is required to support uploading a set of files that have relative 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.5.4.1 Document Table Definition

mod_plsql enables you to 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_typeVARCHAR2(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 content_column_type is LONG RAW, the content_column_name must be CONTENT.

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

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.5.4.1.1 Semantics of the CONTENT column

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

##### 1.5.4.1.2 Semantics of the CONTENT_TYPE column

The content_type column is used to track which content column the document is stored in. When a document is uploaded, mod_plsql will set the value of this column to be the type name (i.e. the [content_column_type] of the content column into which the document is uploaded.

For example, if a document was uploaded into the BLOB content column, then the content_type column for the document will be set to the string `BLOB'.

##### 1.5.4.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 will set the
LAST_UPDATED column for the document to be the database server time (as obtained from sysdate()) at the time of upload. If an application subsequently modifies or replaces the contents or attributes of the document, it must also update the LAST_UPDATED time.

The LAST_UPDATED column is used by mod_plsql to check and indicate to the HTTP client (e.g., a browser) if it is okay for the HTTP client to use a previously cached version of the document. This helps reduce network traffic and response times and improves server performance and scalability.

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

#### 1.5.4.2 Old Style Document Table Definition

For backward capability with the document model used by older releases of WebDB 2.X, mod_plsql will also support 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.5.4.3 Relevant Parameters

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

##### 1.5.4.3.1 document_table (Document Table Name)

The document_table parameter specifies the name of 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.5.4.4 document_path (Document Access Path)

This specifies the path element to immediately follow the DAD name in the URL to access a document. For example, if the document access path is `docs`, then the URL to access a document might look like:

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

where `myDAD` is the DAD name and `myfile.htm` is the file name. The document access path mechanism enables the standard-style document access URLs required for WebDB's features for building Web sites.

Syntax

    document_path = [document_access_path_name]

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

This is an application-specified procedure, with no parameters, that processes a URL request with the document access path. The document access procedure should call `wpg_docload.download_file(filename)` to initiate download of a file. It should figure out the filename based on the complete URL specification. This can be used by an application, for example, 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.5.4.4.2 upload_as_long_raw

The DAD parameter`upload_as_long_raw` is used to configure 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 will be uploaded by mod_plsql into the content column of `long_raw` type in the document table.

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 explicitly listed in an `upload_as_long_raw` setting.

Syntax

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

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

Examples

    upload_as_long_raw = html, txt
    upload_as_long_raw = *

#### 1.5.4.5 File Upload

To upload files from a client machine to a database, you 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 the submit button to trigger the form action, the following events occur:

1. The browser uploads the contents of 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 document_table 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.

The following example shows an HTML form that enables a user to 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="/sample/plsql/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 a Submit button 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 upload operation succeeded or failed.

A sample write_info procedure might look like:

    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 should rename this name to what it wants. So, for instance, when `/private/minutes.txt` is uploaded, the name stored in the table by the gateway would look like `F9080/private/minutes.txt.` The application can rename this to whatever it wants in the called stored procedure. For instance, the application can rename it to`scott/minutes.txt`.

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

In addition to renaming the uploaded file, the stored procedure that is the action target of the form can alter other attributes relating to the file. For example, the form in the example shown in section 1.5.4.5 on page 28 could display a field for allowing the user to input the uploaded document's mime type.

The mime type could be received as a parameter in write_info. The document table could 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.

#### 1.5.4.7 Uploading Multiple Files

To upload multiple files per submit action, 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">

then the action procedure must contain the following parameters:

    procedure handle_text_and_binary_files(textfiles IN owa.vc_arr,

    binaryfile IN varchar2).

#### 1.5.4.8 File Download

After you have uploaded 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 with no parameters that calls
wpg_docload.download_file(file_name) to initiate the download. The document download packages are in docload.sql. See "Installing required packages" for more information about docload.sql.

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

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

    http://acme/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``` will be initiated. The restriction, however, is that when a file downloaded is initiated, no other HTML (produced via HTP interfaces) generated by the procedure, will be passed back to the browser.

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

## 1.6 CGI Environment Variables

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

mod_plsql provides the following CGI environment variables:

- REMOTE_USER

- DAD_NAME

- DOC_ACCESS_PATH

- PATH_INFO

- SCRIPT_NAME

- SERVER_PORT

- SERVER_NAME

- REQUEST_METHOD

- REMOTE_HOST

- REMOTE_ADDR

- SERVER_PROTOCOL

- HTTP_USER_AGENT

- HTTP_PRAGMA

- HTTP_HOST

- HTTP_ACCEPT

- HTTP_ACCEPT_ENCODING

- HTTP_ACCEPT_LANGUAGE

- HTTP_ACCEPT_CHARSET

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

- REQUEST_IANA_CHARSET

- DOCUMENT_TABLE (See "document_table (Document Table Name)" for more information)

- AUTHORIZATION

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.6.1 NLS

The NLS extensions are part of the DAD or global settings in the Gateway configuration and they provide a flexible infrastructure to request and retrieve values to and from Oracle databases in different languages/formats. Even when the database is configured with other NLS settings, all the conversions are handled implicitly by the database and mod_plsql.

For example, if you have a database that is configured with US or NLS Currency but you want to present the values in Japanese Yen to the user, all you need to do is set NLS Currency to Japanese Yen. When the data is retrieved from the database, it will be presented as Japanese Yen.

#### 1.6.1.1 REQUEST_CHARSET CGI environment variable

Every request to mod_plsql is associated with a DAD. The CGI environment variable REQUEST_CHARSET will be set as per the following rules:

- Otherwise, if NLS_LANG is specified as part of the Gateway's global configuration information, then the REQUEST_CHARSET CGI environment variable will be set to the character set portion of the global NLS_LANG parameter.

- Otherwise, the REQUEST_CHARSET will be set to the default character set in use.

  - For the embedded gateway this will be the database's default character set.

  - For the gateway deployed in the middle-tier (as part of WebDB listener or Oracle HTTP Server) this will be the character set information derived from the NLS_LANG environment variable of the WebDB listener process.

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

    	owa_util.get_cgi_env(`REQUEST_CHARSET');

#### 1.6.1.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 used on the Internet.

| Copyright © 2000 Oracle Corporation. All Rights Reserved. |
|  | Contents | Index