Skip Headers
Oracle® Database Advanced Application Developer's Guide
11g Release 1 (11.1)

Part Number B28424-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

10 Developing PL/SQL Web Applications

This chapter explains how to develop PL/SQL Web applications, which let you make your database available on the intranet.

Topics:

Overview of PL/SQL Web Applications

Typically, a Web application written in PL/SQL is a set of stored subprograms that interact with Web browsers through HTTP. A set of interlinked, dynamically generated HTML pages forms the user interface of a web application.

The program flow of a PL/SQL Web application is similar to that in a CGI Perl script. Developers often use CGI scripts to produce Web pages dynamically, but such scripts are often not optimal for accessing Oracle Database. Delivering Web content with PL/SQL stored subprograms provides the power and flexibility of database processing. For example, you can use DML, dynamic SQL, and cursors. You also eliminate the process overhead of forking a new CGI process to handle each HTTP request.

Figure 10-1 illustrates the generic process for a PL/SQL Web application.

Figure 10-1 PL/SQL Web Application

Description of Figure 10-1 follows
Description of "Figure 10-1 PL/SQL Web Application"

The process includes the following steps:

  1. A user visits a Web page, follows a hypertext link, or submits data in a form, which causes the browser to send a HTTP request for a URL to an HTTP server.

  2. The HTTP server calls a stored subprogram on an Oracle database according to the data encoded in the URL. The data in the URL takes the form of parameters to be passed to the stored subprogram.

  3. The stored subprogram invokes subprograms in the PL/SQL Web Toolkit. Typically, subprograms such as HTP.Print generate Web pages dynamically. A generated Web page varies depending on the database contents and the input parameters.

  4. The subprograms pass the dynamically generated page to the Web server.

  5. The Web server delivers the page to the client.

Implementing PL/SQL Web Applications

You can implement a Web browser-based application entirely in PL/SQL with the following Oracle Database components:

PL/SQL Gateway

The PL/SQL gateway enables a Web browser to invoke a PL/SQL stored subprogram through an HTTP listener. The gateway is a platform on which PL/SQL users develop and deploy PL/SQL Web applications.

mod_plsql

mod_plsql is one implementation of the PL/SQL gateway. The module is a plug-in of Oracle HTTP Server and enables Web browsers to invoke PL/SQL stored subprograms. Oracle HTTP Server is a component of both Oracle Application Server and Oracle Database.

The mod_plsql plug-in enables you to use PL/SQL stored subprograms to process HTTP requests and generate responses. In this context, an HTTP request is a URL that includes parameter values to be passed to a stored subprogram. PL/SQL gateway translates the URL, invokes the stored subprogram with the parameters, and returns output (typically HTML) to the client.

Some of the advantages of using mod_plsql over the embedded form of the PL/SQL gateway are as follows:

  • You can run it in a firewall environment in which the Oracle HTTP Server runs on a firewall-facing host while the database is hosted behind a firewall. You cannot use this configuration with the embedded gateway.

  • The embedded gateway does not support mod_plsql features such as dynamic HTML caching, system monitoring, and logging in the Common Log Format.

Embedded PL/SQL Gateway

You can use an embedded version of the PL/SQL gateway that runs in the XML DB HTTP Listener in the Oracle database. It provides the core features of mod_plsql in the database but does not require the Oracle HTTP Server. You configure the embedded PL/SQL gateway with the DBMS_EPG package in the PL/SQL Web Toolkit.

Some of the advantages of using the embedded gateway over mod_plsql are as follows:

  • You can invoke PL/SQL Web applications such as Application Express without the need to install Oracle HTTP Server, thereby simplifying installation, configuration, and administration of PL/SQL based Web applications.

  • You use the same configuration approach that is currently used to deliver content from Oracle XML DB in response to FTP and HTTP requests.

PL/SQL Web Toolkit

This set of PL/SQL packages is a generic interface that enables you to use stored subprograms invoked by mod_plsql at run time.

In response to a browser request, a PL/SQL subprogram updates or retrieves data from Oracle Database according to the user input. It then generates an HTTP response to the browser, typically in the form of a file download or HTML to be displayed. The Web Toolkit API enables stored subprograms to perform actions such as the following:

  • Obtain information about an HTTP request

  • Generate HTTP headers such as content-type and mime-type

  • Set browser cookies

  • Generate HTML pages

Table 10-1 describes commonly used PL/SQL Web Toolkit packages.

Table 10-1 Commonly Used Packages in the PL/SQL Web Toolkit

Package Description of Contents

HTF

Function versions of the subprograms in the htp package. The function versions do not directly generate output in a Web page. Instead, they pass their output as return values to the statements that invoke them. Use these functions when you need to nest function calls.

HTP

Subprograms that generate HTML tags. For example, the procedure htp.anchor generates the HTML anchor tag, <A>.

OWA_CACHE

Subprograms that enable the PL/SQL gateway cache feature to improve performance of your PL/SQL Web application.

You can use this package to enable expires-based and validation-based caching with the PL/SQL gateway file system.

OWA_COOKIE

Subprograms that send and retrieve HTTP cookies to and from a client Web browser. Cookies are strings a browser uses to maintain state between HTTP calls. State can be maintained throughout a client session or longer if a cookie expiration date is included.

OWA_CUSTOM

The authorize function used by cookies.

OWA_IMAGE

Subprograms that obtain the coordinates where a user clicked an image. Use this package when you have an image map whose destination links invoke a PL/SQL gateway.

OWA_OPT_LOCK

Subprograms that impose database optimistic locking strategies to prevent lost updates. Lost updates can otherwise occur if a user selects, and then attempts to update, a row whose values were changed in the meantime by another user.

OWA_PATTERN

Subprograms that perform string matching and string manipulation with regular expressions.

OWA_SEC

Subprograms used by the PL/SQL gateway for authenticating requests.

OWA_TEXT

Subprograms used by package OWA_PATTERN for manipulating strings. You can also use them directly.

OWA_UTIL

The following types of utility subprograms:

  • Dynamic SQL utilities to produce pages with dynamically generated SQL code.

  • HTML utilities to retrieve the values of CGI environment variables and perform URL redirects.

  • Date utilities for correct date-handling. Date values are simple strings in HTML, but must be properly treated as an Oracle Database datatype.

WPG_DOCLOAD

Subprograms that download documents from a document repository that you define using the DAD configuration.


See Also:

Oracle Database PL/SQL Packages and Types Reference for syntax, descriptions, and examples for the PL/SQL Web Toolkit packages

Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application

As explained in detail in the Oracle HTTP Server mod_plsql User's Guide, mod_plsql maps Web client requests to PL/SQL stored subprograms over HTTP. See this documentation for instructions.

See Also:

Using Embedded PL/SQL Gateway

The embedded gateway functions very similar to the mod_plsql gateway. Before using the embedded version of the gateway, familiarize yourself with the Oracle HTTP Server mod_plsql User's Guide. Much of the information is the same or similar.

Topics:

How Embedded PL/SQL Gateway Processes Client Requests

Figure 10-2 illustrates the process by which the embedded gateway handles client HTTP requests.

Figure 10-2 Processing Client Requests with Embedded PL/SQL Gateway

Description of Figure 10-2 follows
Description of "Figure 10-2 Processing Client Requests with Embedded PL/SQL Gateway"

The explanation of the steps in Figure 10-2 is as follows:

  1. The Oracle XML DB HTTP Listener receives a request from a client browser to request to invoke a PL/SQL subprogram. The subprogram can either be written directly in PL/SQL or indirectly generated when a PL/SQL Server Page is uploaded to the database and compiled.

  2. The XML DB HTTP Listener routes the request to the embedded PL/SQL gateway as specified in its virtual-path mapping configuration.

  3. The embedded gateway uses the HTTP request information and the gateway configuration to determine which database account to use for authentication.

  4. The embedded gateway prepares the call parameters and invokes the PL/SQL subprogram in the application.

  5. The PL/SQL subprogram generates an HTML page out of relational data and the PL/SQL Web Toolkit accessed from the database.

  6. The application sends the page to the embedded gateway.

  7. The embedded gateway sends the page to the XML DB HTTP Listener.

  8. The XML DB HTTP Listener sends the page to the client browser.

Unlike mod_plsql, the embedded gateway processes HTTP requests with the Oracle XML DB Listener. This listener is the same server-side process as the Oracle Net Listener and supports Oracle Net Services, HTTP, and FTP.

Configure general HTTP listener settings through the XML DB interface, which is described in Oracle XML DB Developer's Guide. Configure the HTTP listener either by using Oracle Enterprise Manager or by editing the xdbconfig.xml file. Use the DBMS_EPG package for all embedded PL/SQL gateway configuration, for example, creating or setting attributes for a DAD.

Installing Embedded PL/SQL Gateway

The embedded gateway requires the following components:

  • XML DB HTTP Listener

  • PL/SQL Web Toolkit

The embedded PL/SQL gateway is installed as part of Oracle XML DB. If you are using a preconfigured database created during an installation or by the Database Configuration Assistant (DBCA), then Oracle XML DB is already installed and configured. For information about manually adding Oracle XML DB to an existing database, see Oracle XML DB Developer's Guide.

The PL/SQL Web Toolkit is part of the standard installation of Oracle Database, so no supplementary installation is necessary.

Configuring Embedded PL/SQL Gateway

You configure mod_plsql by editing the Oracle HTTP Server configuration files. Because the embedded gateway is installed as part of the Oracle XML DB HTTP Listener, you manage the embedded gateway as a servlet through the Oracle XML DB servlet management interface.

The configuration interface to the embedded gateway is the PL/SQL package DBMS_EPG. This package modifies the underlying xdbconfig.xml configuration file that XML DB uses. The default values of the embedded gateway configuration parameters are sufficient for most users.

This section contains the following topics:

Configuring Embedded PL/SQL Gateway: Overview

As in mod_plsql, each request for a PL/SQL stored subprogram is associated with a Database Access Descriptor (DAD). A DAD is a set of configuration values used for database access. A DAD specifies information such as:

  • The database account to use for authentication

  • The subprogram to use for uploading and downloading documents

In the embedded PL/SQL gateway, a DAD is represented as a servlet in the XML DB HTTP Listener configuration. Each DAD attribute maps to an XML element in the configuration file xdbconfig.xml. The value of the DAD attribute corresponds to the element content. For example, the database-username DAD attribute corresponds to the <database-username> XML element; if the value of the DAD attribute is HR it corresponds to <database-username>HR<database-username>. Note that DAD attribute names are case-sensitive.

Use the DBMS_EPG package to perform the following embedded PL/SQL gateway configurations:

  1. Create a new DAD with the DBMS_EPG.CREATE_DAD procedure.

  2. Set DAD attributes with the DBMS_EPG.SET_DAD_ATTRIBUTE procedure. Note that all DAD attributes are optional. If you do not specify an attribute, then the default value is used.

Table 10-2 lists the embedded PL/SQL gateway attributes and the corresponding mod_plsql DAD parameters. Note that all enumeration values in the "Legal Values" column are case-sensitive.

Table 10-2 Mapping Between mod_plsql and Embedded PL/SQL Gateway DAD Attributes

mod_plsql DAD Attribute Embedded PL/SQL Gateway DAD Attribute Multiple Occurr. Legal Values

PlsqlAfterProcedure

after-procedure

No

String

PlsqlAlwaysDescribeProcedure

always-describe-procedure

No

Enumeration of On, Off

PlsqlAuthenticationMode

authentication-mode

No

Enumeration of Basic, SingleSignOn, GlobalOwa, CustomOwa, PerPackageOwa

PlsqlBeforeProcedure

before-procedure

No

String

PlsqlBindBucketLengths

bind-bucket-lengths

Yes

Unsigned integer

PlsqlBindBucketWidths

bind-bucket-widths

Yes

Unsigned integer

PlsqlCGIEnvironmentList

cgi-environment-list

Yes

String

PlsqlCompatibilityMode

compatibility-mode

No

Unsigned integer

PlsqlDatabaseUsername

database-username

No

String

PlsqlDefaultPage

default-page

No

String

PlsqlDocumentPath

document-path

No

String

PlsqlDocumentProcedure

document-procedure

No

String

PlsqlDocumentTablename

document-table-name

No

String

PlsqlErrorStyle

error-style

No

Enumeration of ApacheStyle, ModplsqlStyle, DebugStyle

PlsqlExclusionList

exclusion-list

Yes

String

PlsqlFetchBufferSize

fetch-buffer-size

No

Unsigned integer

PlsqlInfoLogging

info-logging

No

Enumeration of InfoDebug

PlsqlInputFilterEnable

input-filter-enable

No

String

PlsqlMaxRequestsPerSession

max-requests-per-session

No

Unsigned integer

PlsqlNLSLanguage

nls-language

No

String

PlsqlOWADebugEnable

owa-debug-enable

No

Enumeration of On, Off

PlsqlPathAlias

path-alias

No

String

PlsqlPathAliasProcedure

path-alias-procedure

No

String

PlsqlRequestValidationFunction

request-validation-function

No

String

PlsqlSessionCookieName

session-cookie-name

No

String

PlsqlSessionStateManagement

session-state-management

No

Enumeration of StatelessWithResetPackageState, StatelessWithFastRestPackageState, StatelessWithPreservePackageState

PlsqlTransferMode

transfer-mode

No

Enumeration of Char, Raw

PlsqlUploadAsLongRaw

upload-as-long-raw

No

String


The embedded gateway assumes default values when the attributes are not set. The default values of the DAD attributes are sufficient for most users of the embedded gateway. mod_plsql users do not need the following attributes:

  • PlsqlDatabasePassword

  • PlsqlDatabaseConnectString (because the embedded gateway does not support logon to external databases)

Like the DAD attributes, the global configuration parameters are optional. Table 10-3 describes the DBMS_EPG global attributes and the corresponding mod_plsql global parameters.

Table 10-3 Mapping Between mod_plsql and Embedded PL/SQL Gateway Global Attributes

mod_plsql DAD Attribute Embedded PL/SQL Gateway DAD Attribute Multiple Occurr. Legal Values

PlsqlLogLevel

log-level

No

Unsigned integer

PlsqlMaxParameters

max-parameters

No

Unsigned integer


See Also:

Configuring User Authentication for Embedded PL/SQL Gateway

Because it uses the XML DB authentication schemes, the embedded gateway handles database authentication differently from mod_plsql. In particular, it does not store database passwords in a DAD.

Note:

If you want to serve a PL/SQL Web application on the Internet but maintain Oracle Database behind a firewall, then you cannot use the embedded PL/SQL gateway to run the application. You must use mod_plsql instead.

Use the DBMS_EPG package to configure database authentication. This section contains the following topics:

Configuring Static Authentication with DBMS_EPG

Static authentication is for the mod_plsql user who stores database usernames and passwords in the DAD so that the browser user is not required to enter database authentication information.

To set up static authentication, follow these steps:

  1. Log on to the database as an XML DB administrator, that is, a user with the XDBADMIN role assigned. For example:

    CONNECT SYSTEM/password
    
  2. Create the DAD. For example, the following procedure creates a DAD invoked HR_DAD and maps the virtual path to /hrweb/:

    EXEC DBMS_EPG.CREATE_DAD('HR_DAD', '/hrweb/*');
    
  3. For this step, you need the ALTER ANY USER system privilege. Set the DAD attribute database-username to the database account whose privileges must be used by the DAD. For example, the following procedure specifies that the DAD named HR_DAD has the privileges of the HR account:

    EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('HR_DAD', 'database-username', 'HR');
    

    Note that the DAD attribute database-username is case sensitive.

  4. Assign the DAD the privileges of the database user specified in the previous step. This authorization enables end users to invoke procedures and access document tables through the embedded PL/SQL gateway with the privileges of the authorized account. For example:

    EXEC DBMS_EPG.AUTHORIZE_DAD('HR_DAD', 'HR');
    

    Alternatively, you can log off as the user with XDBADMIN privileges, log on as the database user whose privileges must be used by the DAD, and then assign these privileges to the DAD. For example:

    CONNECT HR/password
    EXEC DBMS_EPG.AUTHORIZE_DAD('HR_DAD');
    

    Note that multiple users can authorize the same DAD. The database-username attribute setting of the DAD determines which user's privileges to use.

Unlike mod_plsql, the embedded gateway connects to the database as the special user ANONYMOUS, but accesses database objects with the user privileges assigned to the DAD. The database rejects access if the browser user attempts to connect explicitly with the HTTP Authorization header.

Note:

The account ANONYMOUS is locked after XML DB installation. If you want to use static authentication with the embedded PL/SQL gateway, then you must first unlock this account.
Configuring Dynamic Authentication with DBMS_EPG

Dynamic authentication is for the mod_plsql user who does not store database usernames and passwords in the DAD.

In dynamic authentication, a database user does not have to authorize the embedded gateway to use its privileges to access database objects. Instead, browser users must supply the database authentication information through the HTTP Basic Authentication scheme.

The action of the embedded gateway depends on whether the database-username attribute is set for the DAD. If the attribute is not set, then the embedded gateway connects to the database as the user supplied by the browser client. If the attribute is set, then the database restricts access to the user specified in the database-username attribute.

To set up dynamic authentication, follow these steps:

  1. Log on to the database as a an XML DB administrator, that is, a user with the XDBADMIN role. For example:

    CONNECT SYSTEM/password
    
  2. Create the DAD. For example, the following procedure creates a DAD invoked DYNAMIC_DAD and maps the virtual path to /hrweb/:

    EXEC DBMS_EPG.CREATE_DAD('DYNAMIC_DAD', '/hrweb/*');
    
  3. Optionally, set the DAD attribute database-username to the database account whose privileges must be used by the DAD. The browser user will be prompted to enter the username and password for this account when accessing the DAD. For example, the following procedure specifies that the DAD named DYNAMIC_DAD has the privileges of the HR account:

    EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('DYNAMIC_DAD', 'database-username', 'HR');
    

    Note that the attribute database-username is case sensitive.

WARNING:

Passwords sent through the HTTP Basic Authentication scheme are not encrypted. Configure the embedded gateway to use the HTTPS protocol to protect the passwords sent by the browser clients.

Configuring Anonymous Authentication with DBMS_EPG

Anonymous authentication is for the mod_plsql user who creates a special DAD database user for database logon, but stores the application procedures and document tables in a different schema and grants access to the procedures and document tables to PUBLIC.

To set up anonymous authentication, follow these steps:

  1. Log on to the database as an XML DB administrator, that is, a user with the XDBADMIN role assigned. For example:

    CONNECT SYSTEM/password
    
  2. Create the DAD. For example, the following procedure creates a DAD invoked HR_DAD and maps the virtual path to /hrweb/:

    EXEC DBMS_EPG.CREATE_DAD('HR_DAD', '/hrweb/*');
    
  3. Set the DAD attribute database-username to ANONYMOUS. For example:

    EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('HR_DAD', 'database-username', 'ANONYMOUS');
    

    Note that both database-username and ANONYMOUS are case sensitive.

    There is no need to authorize the embedded gateway to use ANONYMOUS privileges to access database objects, because ANONYMOUS has no system privileges and owns no database objects.

Determining the Authentication Mode of a DAD

If you know the name of a DAD, then the authentication mode for this DAD depends on the following factors:

  • Does the DAD exist?

  • Is the database-username attribute for the DAD set?

  • Is the DAD authorized to use the privilege of the database-username user?

  • Is the database-username attribute the same as one of the user authorized to use the DAD?

Authentication for the DAD is static only if each of the preceding questions is answered in the affirmative. Table 10-4 shows how the answers to the preceding questions determine the authentication mode.

Table 10-4 Authentication Possibilities for a DAD

DAD Exists? database-username set? User authorized? Mode

Yes

Yes

Yes

Static

Yes

Yes

No

Dynamic restricted

Yes

No

Does not matter

Dynamic

Yes

Yes (to ANONYMOUS)

Does not matter

Anonymous

No



N/A


For example, assume that you create a DAD named MY_DAD. If the database-username attribute for MY_DAD is set to HR, but the HR user does not authorize MY_DAD, then the authentication mode for MY_DAD is dynamic and restricted. A browser user who attempts to execute a PL/SQL subprogram through MY_DAD is prompted to enter the HR database username and password.

The DBA_EPG_DAD_AUTHORIZATION view shows which users have authorized use of a DAD. The DAD_NAME column displays the name of the DAD; the USERNAME column displays the user whose privileges are assigned to the DAD. Note that the DAD authorized may or may not exist.

Creating and Configuring DADs: Example

You can use the sample SQL script in Example 10-1 to create and configure different DADs for testing purposes. The script does the following:

  • Creates a DAD invoked Static_Auth_DAD for database user HR and assigns it the privileges of the HR account.

  • Creates a DAD invoked Static_Auth_DAD_2 for database user HR but accidentally (for illustration) assigns Static_Auth_DAD_Typo the privileges of the HR account. Note that the Static_Auth_DAD_Typo DAD does not exist.

  • Creates a DAD invoked Dynamic_Auth_DAD that is not restricted to any user.

  • Creates a DAD invoked Dynamic_Auth_DAD_Restricted that is restricted to the HR account.

  • Accidentally (for illustration) assigns Static_Auth_DAD the privileges of the database user OE, even though the database-user attribute for this DAD is set to HR.

Example 10-1 Configuring Authentication Modes

rem ---------------------------------------------------------------------
rem Create DAD with static auth
rem ---------------------------------------------------------------------
 
CONNECT SYSTEM/password
EXEC DBMS_EPG.CREATE_DAD('Static_Auth_DAD', '/static/*')
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('Static_Auth_DAD', 'database-username', 'HR')
 
CONNECT HR/password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD')
 
rem ---------------------------------------------------------------------
rem Create DAD with static auth typo
rem ---------------------------------------------------------------------
 
CONNECT SYSTEM/password
EXEC DBMS_EPG.CREATE_DAD('Static_Auth_DAD_2', '/static2/*')
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('Static_Auth_DAD_2', 'database-username', 'HR')
 
CONNECT HR/password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD_Typo')
 
rem ---------------------------------------------------------------------
rem Create DAD with dynamic auth
rem ---------------------------------------------------------------------
 
CONNECT SYSTEM/password
EXEC DBMS_EPG.CREATE_DAD('Dynamic_Auth_DAD', '/dynamic/*')
 
rem ---------------------------------------------------------------------
rem Create DAD with dynamic auth restricted
rem ---------------------------------------------------------------------
 
CONNECT SYSTEM/password
EXEC DBMS_EPG.CREATE_DAD('Dynamic_Auth_DAD_Restricted', '/dynamic/*')
EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('Dynamic_Auth_DAD_Restricted',
                                'database-username', 'HR')
 
rem ---------------------------------------------------------------------
rem Authorize DAD by a non-DAD user (DAD database-username is 'HR')
rem ---------------------------------------------------------------------
 
CONNECT OE/password
EXEC DBMS_EPG.AUTHORIZE_DAD('Static_Auth_DAD')
EXIT
Determining the Authentication Mode for a DAD: Example

Example 10-2 creates a PL/SQL procedure named show_dad_auth_status that shows the privileges of a specified DAD. You execute the procedure by passing it the name of a DAD. Note that the procedure exits with an error if the specified DAD does not exist.

The procedure queries DBA_EPG_DAD_AUTHORIZATION to determine whether the specified DAD is set up for static authentication. If no row is found for the DAD, then authentication is dynamic. The procedure also executes the DBMS_EPG.GET_DAD_ATTRIBUTE procedure to determine whether the database-username attribute is set for the specified DAD. If it is set, and if the DAD uses dynamic authentication, then this DAD is restricted to the specified user. Otherwise, the DAD is set up for dynamic authentication for any user.

Example 10-2 Determining the Authentication Mode for a DAD

-----------------------------------------------------------------------
-- This procedure shows the DAD authorization status for the given DAD
-----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE show_dad_auth_status(p_dadname VARCHAR2) 
IS
  v_daduser VARCHAR2(32);
  v_cnt     PLS_INTEGER;
BEGIN
  -- Get DAD's database-username attribute
  v_daduser := DBMS_EPG.GET_DAD_ATTRIBUTE(p_dadname, 'database-username');
 
  -- Determine whether DAD authorization exists for the DAD user
  SELECT COUNT(*)
    INTO v_cnt
  FROM   DBA_EPG_DAD_AUTHORIZATION da
  WHERE  da.DAD_NAME = p_dadname 
  AND    da.USERNAME = v_daduser;

  -- If DAD authorization exists for the DAD user it is static authentication
  IF (v_cnt > 0) THEN
    DBMS_OUTPUT.PUT_LINE('''' || p_dadname ||
                         ''' is set up for static auth for user ''' ||
                         v_daduser || '''.');
    RETURN;
  END IF;
 
  -- Is dynamic authentication restricted to a particular user?
  IF (v_daduser IS NOT NULL) THEN
    DBMS_OUTPUT.PUT_LINE('''' || p_dadname ||
                         ''' is set up for dynamic auth for user ''' ||
                         v_daduser || ''' only.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('''' || p_dadname ||
                         ''' is set up for dynamic auth for any user.');
  END IF; 
END;
/

Assume that you have run the script in Example 10-1 to create and configure various DADs. The following example queries the authorization status of Static_Auth_DAD and shows sample output:

SET SERVEROUTPUT ON
EXEC show_dad_auth_status('Static_Auth_DAD')

'Static_Auth_DAD' is set up for static auth for user 'HR'.
Determining the Authentication Mode for All DADs: Example

Example 10-3 shows an anonymous PL/SQL block that determines the authentication mode for all registered DADs, that is, all DADs returned by executing the DBMS_EPG.GET_DAD_LIST procedure. The block executes the show_dad_auth_status procedure to find the authentication mode for each DAD.

Example 10-3 Showing the Authentication Mode for All DADs

DECLARE
  v_dad_names DBMS_EPG.VARCHAR2_TABLE; 
BEGIN 
  -- Show the DAD authorization status for all the DADs
  DBMS_OUTPUT.PUT_LINE('---------- authorization status for all DADs ----------'); 
  DBMS_EPG.GET_DAD_LIST(v_dad_names);

  -- loop through DAD names and display authorization status for each DAD 
  FOR i IN 1..v_dad_names.count LOOP
    show_dad_auth_status(v_dad_names(i));
  END LOOP; 
END;
/

If you ran the script in Example 10-1 to create and configure various DADs, the output is:

---------- authorization status for all DADs ----------
'Static_Auth_DAD' is set up for static auth for user 'HR'.
'Static_Auth_DAD_2' is set up for dynamic auth for user 'HR' only.
'Dynamic_Auth_DAD' is set up for dynamic auth for any user.
'Dynamic_Auth_DAD_Restricted' is set up for dynamic auth for user 'HR' only.
Showing DAD Authorizations that Are Not in Effect: Example

Example 10-4 shows an anonymous PL/SQL block that shows DAD authorizations that are not in effect. This situation can occur in either of the following situations:

  • A database user who authorizes a DAD is not the same user specified by the database-username attribute of the DAD

  • A database user authorizes a DAD that does not exist

Example 10-4 Showing DAD Authorizations that Are Not in Effect

DECLARE
  v_dad_names  DBMS_EPG.VARCHAR2_TABLE; 
  v_dad_user   VARCHAR2(32);
  v_dad_found  BOOLEAN;
BEGIN 
  -- Show the DAD authorizations that are not in effect
  DBMS_OUTPUT.PUT_LINE('---------- DAD authorizations not in effect ----------');
 
  -- Get the DAD list for later use
  DBMS_EPG.GET_DAD_LIST(v_dad_names);
 
  -- Go through each DAD authorization and look for the DAD database-name
  -- attribute setting
  FOR r IN (SELECT * FROM DBA_EPG_DAD_AUTHORIZATION) LOOP
    v_dad_found := FALSE;

    FOR i IN 1..v_dad_names.count LOOP
      IF (r.DAD_NAME = v_dad_names(i)) THEN
        v_dad_user := DBMS_EPG.GET_DAD_ATTRIBUTE(r.DAD_NAME, 'database-username');

        -- Does the DAD database-username attribute match the user the
        -- the DAD is authorized for?
        IF (r.USERNAME <> v_dad_user) THEN
          DBMS_OUTPUT.PUT_LINE('DAD authorization of ''' || r.dad_name ||
                               ''' by user ''' || r.username || '''' ||
                               ' is not in effect because the DAD user is ' ||
                               '''' || v_dad_user || '''.');
        END IF;
        v_dad_found := TRUE;
        EXIT;
      END IF;
    END LOOP;
 
    -- Does the DAD exist?
    IF (NOT v_dad_found) THEN
      DBMS_OUTPUT.PUT_LINE('DAD authorization of ''' || r.dad_name ||
                           ''' by user ''' || r.username ||
                           ''' is not in effect because the DAD does not exist.');
    END IF;
  END LOOP;
END;
/

If you run the script in Example 10-1 to create and configure DADs, output (reformatted to fit on the page) is:

---------- DAD authorizations not in effect ----------
DAD authorization of 'Static_Auth_DAD' by user 'OE' is not in effect because the
  DAD user is 'HR'.
DAD authorization of 'Static_Auth_DAD_Typo' by user 'HR' is not in effect
  because the DAD does not exist.
Examining Embedded PL/SQL Gateway Configuration

The following script helps you examine the configuration of the embedded PL/SQL gateway:

$ORACLE_HOME/rdbms/admin/epgstat.sql

Example 10-5 shows the output of the epgstat.sql script for Example 10-1.

Example 10-5 epgstat.sql Script Output for Example 10-1

SQL> @epgstat.sql
+--------------------------------------+
| XDB protocol ports:                  |
|  XDB is listening for the protocol   |
|  when the protocol port is nonzero. |
+--------------------------------------+

HTTP Port FTP Port
--------- --------
     8080        0

1 row selected.

+---------------------------+
| DAD virtual-path mappings |
+---------------------------+

Virtual Path                     DAD Name
-------------------------------- --------------------------------
/dynamic/*                       Dynamic_Auth_DAD_Restricted
/static/*                        Static_Auth_DAD
/static2/*                       Static_Auth_DAD_2

3 rows selected.

+----------------+
| DAD attributes |
+----------------+

DAD Name        DAD Param                DAD Value
------------    --------------------- ----------------------------------------
Dynamic_Auth    database-username        HR
_DAD_Restric
ted

Static_Auth_    database-username        HR
DAD

Static_Auth_    database-username        HR
DAD2

3 rows selected.

+---------------------------------------------------+
| DAD authorization:                                |
|  To use static authentication of a user in a DAD, |
|  the DAD must be authorized for the user.         |
+---------------------------------------------------+

DAD Name                         User Name
-------------------------------- --------------------------------
Static_Auth_DAD                  HR
                                 OE
Static_Auth_DAD_Typo             HR

3 rows selected.

+----------------------------+
| DAD authentication schemes |
+----------------------------+

DAD Name             User Name                        Auth Scheme
-------------------- -------------------------------- ------------------
Dynamic_Auth_DAD                                      Dynamic
Dynamic_Auth_DAD_Res HR                               Dynamic Restricted
tricted

Static_Auth_DAD      HR                               Static
Static_Auth_DAD_2    HR                               Dynamic Restricted

4 rows selected.

+--------------------------------------------------------+
| ANONYMOUS user status:                                 |
|  To use static or anonymous authentication in any DAD, |
|  the ANONYMOUS account must be unlocked.               |
+--------------------------------------------------------+

Database User   Status
--------------- --------------------
ANONYMOUS       EXPIRED

1 row selected.

+-------------------------------------------------------------------+
| ANONYMOUS access to XDB repository:                               |
|  To allow public access to XDB repository without authentication, |
|  ANONYMOUS access to the repository must be allowed.              |
+-------------------------------------------------------------------+

Allow repository anonymous access?
----------------------------------
true

1 row selected.

Invoking PL/SQL Stored Subprograms Through Embedded PL/SQL Gateway

The basic steps for invoking PL/SQL subprograms through the embedded PL/SQL gateway are the same as for the mod_plsql gateway. See Oracle HTTP Server mod_plsql User's Guide for instructions. You need to adapt the mod_plsql instructions slightly for use with the embedded gateway. For example, invoke the embedded gateway in a browser by entering the URL in the following format:

protocol://hostname[:port]/virt-path/[[!][schema.][package.]proc_name[?query_str]]

The placeholder virt-path stands for the virtual path that you configured in DBMS_EPG.CREATE_DAD. The mod_plsql documentation uses DAD_location instead of virt-path.

The following topics documented in the first chapter of Oracle HTTP Server mod_plsql User's Guide apply equally to the embedded gateway:

  • Transaction mode

  • Supported data types

  • Parameter-passing scheme

  • File upload and download support

  • Path-aliasing

  • Common Gateway Interface (CGI) environment variables

Securing Application Access with Embedded PL/SQL Gateway

The embedded gateway shares the same protection mechanism with mod_plsql. See Oracle HTTP Server mod_plsql User's Guide for instructions.

Restrictions in Embedded PL/SQL Gateway

The mod_plsql restrictions documented in the first chapter of Oracle HTTP Server mod_plsql User's Guide apply equally to the embedded gateway. In addition, the embedded version of the gateway does not support the following features:

  • Dynamic HTML caching

  • System monitoring

  • Single sign-on (SSO)

Using Embedded PL/SQL Gateway: Scenario

This section illustrates how to write a simple application that queries the hr.employees table and delivers HTML output to a Web browser through the PL/SQL gateway. It assumes that you have both XML DB and the sample schemas installed.

To write and execute the program follows these steps:

  1. Log on to the database as a user with ALTER USER privileges and make sure that the database account ANONYMOUS is unlocked. The ANONYMOUS account, which is locked by default, is required for static authentication. If the account is locked, then use the following SQL statement to unlock it:

    ALTER USER anonymous ACCOUNT UNLOCK;
    
  2. Log on to the database as an XML DB administrator, that is, a user with the XDBADMIN role. For example:

    CONNECT SYSTEM/password
    

    Note that you can determine which users and roles were granted the XDADMIN role by querying the data dictionary as follows:

    SELECT *
    FROM   DBA_ROLE_PRIVS
    WHERE  GRANTED_ROLE = 'XDBADMIN';
    
  3. Create the DAD. For example, the following procedure creates a DAD invoked HR_DAD and maps the virtual path to /hrweb/:

    EXEC DBMS_EPG.CREATE_DAD('HR_DAD', '/plsql/*');
    
  4. Set the DAD attribute database-username to the database user whose privileges must be used by the DAD. For example, the following procedure specifies that the DAD HR_DAD accesses database objects with the privileges of user HR:

    EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('HR_DAD', 'database-username', 'HR');
    

    Note that the attribute database-username is case sensitive.

  5. Log off as the XML DB administrator and log on to the database as the database user whose privileges must be used by the DAD. For example:

    CONNECT HR/password
    
  6. Authorize the embedded PL/SQL gateway to invoke procedures and access document tables through the DAD. For example:

    EXEC DBMS_EPG.AUTHORIZE_DAD('HR_DAD');
    
  7. Create a sample PL/SQL stored procedure invoked print_employees. The following program creates an HTML page that includes the result set of a query of hr.employees:

    CREATE OR REPLACE PROCEDURE print_employees
    IS
      CURSOR emp_cursor IS
      SELECT last_name, first_name
      FROM hr.employees
      ORDER BY last_name;
    BEGIN
      HTP.PRINT('<html>');
      HTP.PRINT('<head>');
      HTP.PRINT('<meta http-equiv="Content-Type" content="text/html">');
      HTP.PRINT('<title>List of Employees</title>');
      HTP.PRINT('</head>'); 
      HTP.PRINT('<body TEXT="#000000" BGCOLOR="#FFFFFF">');
      HTP.PRINT('<h1>List of Employees</h1>');
      HTP.PRINT('<table width="40%" border="1">');
      HTP.PRINT('<tr>');
      HTP.PRINT('<th align="left">Last Name</th>');
      HTP.PRINT('<th align="left">First Name</th>');
      HTP.PRINT('</tr>');
      FOR emp_record IN emp_cursor LOOP
        HTP.PRINT('<tr>');
        HTP.PRINT('<td>' || emp_record.last_name  || '</td>');
        HTP.PRINT('<td>' || emp_record.first_name || '</td>');
      END LOOP;
      HTP.PRINT('</table>');
      HTP.PRINT('</body>');
      HTP.PRINT('</html>');
    END;
    /
    
  8. Ensure that the Oracle Net listener can accept HTTP requests. You can determine the status of the listener on Linux and UNIX by running the following command at the system prompt:

    lsnrctl status | grep HTTP
    

    Output (reformatted from a single line to multiple lines due to page size constraints):

    (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=test.com)(PORT=8080))
      (Presentation=HTTP)
      (Session=RAW)
    )
    

    If you do not see the HTTP service started, then you can set following in you initialization parameter file (replacing listener_name with the name of your Oracle Net local listener), then restart the database and the listener:

    dispatchers="(PROTOCOL=TCP)"
    local_listener=listener_name
    
  9. Run the print_employees program from your Web browser. For example, you can use the following URL, replacing host with the name of your host computer and port with the value of the PORT parameter in the previous step:

    http://host:port/plsql/print_employees
    

    For example, if your host is test.com and your HTTP port is 8080, then enter:

    http://test.com:8080/plsql/print_employees
    

    The Web browser returns an HTML page with a table that includes the first and last name of every employee in the hr.employees table.

Generating HTML Output with PL/SQL

Traditionally, PL/SQL Web applications use function calls to generate each HTML tag for output. These functions are part of the PL/SQL Web Toolkit packages that come with Oracle Database. Example 10-6 illustrates how to generate a simple HTML page by calling the HTP functions that correspond to each HTML tag.

Example 10-6 Displaying HTML Tags with HTP Functions

CREATE OR REPLACE PROCEDURE html_page
IS
BEGIN
  HTP.HTMLOPEN;                            -- generates <HTML>
  HTP.HEADOPEN;                            -- generates <HEAD>
  HTP.TITLE('Title');                      -- generates <TITLE>Hello</TITLE>
  HTP.HEADCLOSE;                           -- generates </HTML> 

  -- generates <BODY TEXT="#000000" BGCOLOR="#FFFFFF">
  HTP.BODYOPEN( cattributes => 'TEXT="#000000" BGCOLOR="#FFFFFF"');

  -- generates <H1>Heading in the HTML File</H1>
  HTP.HEADER(1, 'Heading in the HTML File');

  HTP.PARA;                                 -- generates <P>        
  HTP.PRINT('Some text in the HTML file.'); 
  HTP.BODYCLOSE;                            -- generates </BODY>
  HTP.HTMLCLOSE;                            -- generates </HTML>
END;

An alternative to making function calls that correspond to each tag is to use the HTP.PRINT function to print the text and tags together. Example 10-7 illustrates this technique.

Example 10-7 Displaying HTML Tags with HTP.PRINT

CREATE OR REPLACE PROCEDURE html_page2
IS
BEGIN
  HTP.PRINT('<html>');
  HTP.PRINT('<head>');
  HTP.PRINT('<meta http-equiv="Content-Type" content="text/html">');
  HTP.PRINT('<title>Title of the HTML File</title>');
  HTP.PRINT('</head>');

  HTP.PRINT('<body TEXT="#000000" BGCOLOR="#FFFFFF">');
  HTP.PRINT('<h1>Heading in the HTML File</h1>');
  HTP.PRINT('<p>Some text in the HTML file.');
  HTP.PRINT('</body>');

  HTP.PRINT('</html>');
END;

Chapter 11, "Developing PL/SQL Server Pages" describes an additional method for delivering using PL/SQL to generate HTML content. PL/SQL server pages enables you to build on your knowledge of HTML tags and avoid learning a new set of function calls. In an application written as a set of PL/SQL server pages, you can still use functions from the PL/SQL Web toolkit to do the following:

Passing Parameters to PL/SQL Web Applications

To be useful in a wide variety of situations, a Web application must be interactive enough to allow user choices. To keep the attention of impatient Web surfers, streamline the interaction so that users can specify these choices very simply, without excessive decision-making or data entry.

The main methods of passing parameters to PL/SQL Web applications are:

Topics:

Passing List and Dropdown-List Parameters from an HTML Form

List boxes and drop-down lists are implemented with the HTML tag <SELECT>.

Use a list box for a large number of choices or to allow multiple selections. List boxes are good for showing items in alphabetical order so that users can find an item quickly without reading all the choices.

Use a drop-down list in the following situations:

  • There are a small number of choices

  • Screen space is limited.

  • Choices are in an unusual order.

The drop-down captures the attention of first-time users and makes them read the items. If you keep the choices and order consistent, then users can memorize the motion of selecting an item from the drop-down list, allowing them to make selections quickly as they gain experience. Example 10-8 shows a simple drop-down list.

Example 10-8 HTML Drop-Down List

<form>
<select name="seasons">
<option value="winter">Winter
<option value="spring">Spring
<option value="summer">Summer
<option value="fall">Fall
</select>

Passing Radio Button and Checkbox Parameters from an HTML Form

Radio buttons pass either a null value (if none of the radio buttons in a group is checked), or the value specified on the radio button that is checked.

To specify a default value for a set of radio buttons, you can include the CHECKED attribute in one of the INPUT tags, or include a DEFAULT clause on the parameter within the stored subprogram. When setting up a group of radio buttons, be sure to include a choice that indicates "no preference", because once the user selects a radio button, they can still select a different one, but they cannot clear the selection completely. For example, include a "Don't Care" or "Don't Know" selection along with "Yes" and "No" choices, in case someone makes a selection and then realizes it was wrong.

Checkboxes need special handling, because your stored subprogram might receive a null value, a single value, or multiple values:

All the checkboxes with the same NAME attribute make up a checkbox group. If none of the checkboxes in a group is checked, the stored subprogram receives a null value for the corresponding parameter.

If one checkbox in a group is checked, the stored subprogram receives a single VARCHAR2 parameter.

If more than one checkbox in a group is checked, the stored subprogram receives a parameter with the PL/SQL type TABLE OF VARCHAR2. You must declare a type like this, or use a predefined one like OWA_UTIL.IDENT_ARR. To retrieve the values, use a loop:

CREATE OR REPLACE PROCEDURE handle_checkboxes ( checkboxes owa_util.ident_arr )
AS
BEGIN
  ...
  FOR i IN 1..checkboxes.count
  LOOP
    htp.print('<p>Checkbox value: ' || checkboxes(i));
  END LOOP;
  ...
END;
/
SHOW ERRORS;

Passing Entry-Field Parameters from an HTML Form

Entry fields require the most validation, because a user might enter data in the wrong format, out of range, and so on. If possible, validate the data on the client side using client-side Javascript, and format it correctly for the user or prompt them to enter it again.

For example:

  • You might prevent the user from entering alphabetic characters in a numeric entry field, or from entering characters once a length limit is reached.

  • You might silently remove spaces and dashes from a credit card number if the stored subprogram expects the value in that format.

  • You might inform the user immediately when they type a number that is too large, so that they can retype it.

Because you cannot always rely on such validation to succeed, code the stored subprograms to deal with these cases anyway. Rather than forcing the user to use the Back button when they enter wrong data, display a single page with an error message and the original form with all the other values filled in.

For sensitive information such as passwords, a special form of the entry field, <INPUT TYPE=PASSWORD>, hides the text as it is typed in.

For example, the following procedure accepts two strings as input. The first time it is invoked, the user sees a simple form prompting for the input values. When the user submits the information, the same procedure is invoked again to check if the input is correct. If the input is OK, the procedure processes it. If not, the procedure prompts for new input, filling in the original values for the user.

-- Store a name and associated zip code in the database.
CREATE OR REPLACE PROCEDURE associate_name_with_zipcode
(
  name VARCHAR2 DEFAULT NULL,
  zip VARCHAR2 DEFAULT NULL
)
AS
  booktitle VARCHAR2(256);
BEGIN
-- Both entry fields must contain a value. The zip code must be 6 characters.
-- (In a real program you perform more extensive checking.)
  IF name IS NOT NULL AND zip IS NOT NULL AND length(zip) = 6 THEN
    store_name_and_zipcode(name, zip);
    htp.print('<p>The person ' || name || ' has the zip code ' || zip || '.');
-- If the input was OK, we stop here and the user does not see the form again.
    RETURN;
  END IF;

-- If some data was entered, but it is not correct, show the error message.
  IF (name IS NULL AND zip IS NOT NULL)
    OR (name IS NOT NULL AND zip IS NULL)
    OR (zip IS NOT NULL AND length(zip) != 6)
  THEN
    htp.print('<p><b>Please re-enter the data. Fill in all fields, and use a
              6-digit zip code.</b>');
  END IF;

-- If the user has not entered any data, or entered bad data, prompt for
-- input values.

-- Make the form invoke the same procedure to check the input values.
  htp.formOpen( 'scott.associate_name_with_zipcode', 'GET');
  htp.print('<p>Enter your name:</td>');
  htp.print('<td valign=center><input type=text name=name value="' || name ||
 '">');  htp.print('<p>Enter your zip code:</td>');
  htp.print('<td valign=center><input type=text name=zip value="' || zip || '">');
  htp.formSubmit(NULL, 'Submit');
  htp.formClose;
END;
/
SHOW ERRORS;

Passing Hidden Parameters from an HTML Form

One technique for passing information through a sequence of stored subprograms, without requiring the user to specify the same choices each time, is to include hidden parameters in the form that invokes a stored subprogram. The first stored subprogram places information, such as a user name, into the HTML form that it generates. The value of the hidden parameter is passed to the next stored subprogram, as if the user had entered it through a radio button or entry field.

Other techniques for passing information from one stored subprogram to another include:

  • Sending a "cookie" containing the persistent information to the browser. The browser then sends this same information back to the server when accessing other Web pages from the same site. Cookies are set and retrieved through the HTTP headers that are transferred between the browser and the Web server before the HTML text of each Web page.

  • Storing the information in the database itself, where later stored subprograms can retrieve it. This technique involves some extra overhead on the database server, and you must still find a way to keep track of each user as multiple users access the server at the same time.

Uploading a File from an HTML Form

You can use an HTML form to choose a file on a client system, and transfer it to the server. A stored subprogram can insert the file into the database as a CLOB, BLOB, or other type that can hold large amounts of data.

The PL/SQL Web toolkit and the PL/SQL gateway have the notion of a "document table" that holds uploaded files.

See Also:

mod_plsql User's Guide

Submitting a Completed HTML Form

By default, an HTML form must have a Submit button, which transmits the data from the form to a stored subprogram or CGI program. You can label this button with text of your choice, such as "Search", "Register", and so on.

You can have multiple forms on the same page, each with its own form elements and Submit button. You can even have forms consisting entirely of hidden parameters, where the user makes no choice other than clicking the button.

Using JavaScript or other scripting languages, you can do away with the Submit button and have the form submitted in response to some other action, such as selecting from a drop-down list. This technique is best when the user only makes a single selection, and the confirmation step of the Submit button is not essential.

Handling Missing Input from an HTML Form

When an HTML form is submitted, your stored subprogram receives null parameters for any form elements that are not filled in. For example, null parameters can result from an empty entry field, a set of checkboxes, radio buttons, or list items with none checked, or a VALUE parameter of "" (empty quotation marks).

Regardless of any validation you do on the client side, always code stored subprograms to handle the possibility that some parameters are null:

  • Use a DEFAULT clause in all parameter declarations, to prevent an exception when the stored subprogram is invoked with a missing form parameter. You can set the default to zero for numeric values (when that makes sense), and use DEFAULT NULL when you want to check whether or not the user actually specifies a value.

  • Before using an input parameter value that has a DEFAULT NULL declaration, check if it is null.

  • Make the subprogram generate sensible results even when not all input parameters are specified. You might leave some sections out of a report, or display a text string or image in a report to indicate where parameters were not specified.

  • Provide a way to fill in the missing values and run the stored subprogram again, directly from the results page. For example, include a link that invokes the same stored subprogram with an additional parameter, or display the original form with its values filled in as part of the output.

Maintaining State Information Between Web Pages

Web applications are particularly concerned with the idea of state, the set of data that is current at a particular moment in time. It is easy to lose state information when switching from one Web page to another, which might result in asking the user to make the same choices over and over.

You can pass state information between dynamic Web pages using HTML forms. The information is passed as a set of name-value pairs, which are turned into stored subprogram parameters for you.

If the user has to make multiple selections, or one selection from many choices, or it is important to avoid an accidental selection, use an HTML form. After the user makes and reviews all the choices, they confirm the choices with the Submit button. Subsequent pages can use forms with hidden parameters (<INPUT TYPE=HIDDEN> tags) to pass these choices from one page to the next.

If the user is only considering one or two choices, or the decision points are scattered throughout the Web page, you can save the user from hunting around for the Submit button by representing actions as hyperlinks and including any necessary name-value pairs in the query string (the part following the ? within a URL).

An alternative way to main state information is to use Oracle Application Server and its mod_ose module. This approach lets you store state information in package variables that remain available as a user moves around a Web site.

See Also:

The Oracle Application Server documentation set at http://www.oracle.com/technology/documentation

Performing Network Operations in PL/SQL Stored Subprograms

While built-in PL/SQL features are focused on traditional database operations and programming logic, Oracle Database provides packages that open up Internet computing to PL/SQL programmers.

This section contains the following topics:

Sending E-Mail from PL/SQL

You can send e-mail from a PL/SQL program or stored subprogram with the UTL_SMTP package. You can read about this package in the Oracle Database PL/SQL Packages and Types Reference.

The following code example illustrates how the SMTP package might be used by an application to send e-mail. The application connects to an SMTP server at port 25 and sends a simple text message.

PROCEDURE send_test_message
IS
    mailhost    VARCHAR2(64) := 'mailhost.fictional-domain.com';
    sender      VARCHAR2(64) := 'me@fictional-domain.com';
    recipient   VARCHAR2(64) := 'you@fictional-domain.com';
    mail_conn  utl_smtp.connection;
BEGIN
    mail_conn := utl_smtp.open_connection(mailhost, 25);
    utl_smtp.helo(mail_conn, mailhost);
    utl_smtp.mail(mail_conn, sender);
    utl_smtp.rcpt(mail_conn, recipient);
-- If message were in single string, open_data(), write_data(), and close_data()
--  could be in a single call to data().
    utl_smtp.open_data(mail_conn);
    utl_smtp.write_data(mail_conn, 'This is a test message.' || chr(13));
    utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13));
    utl_smtp.close_data(mail_conn);
    utl_smtp.quit(mail_conn);
    EXCEPTION
        WHEN OTHERS THEN
           -- Insert error-handling code here
           NULL;
END;

Getting a Host Name or Address from PL/SQL

You can determine the host name of the local system, or the IP address of a given host name from a PL/SQL program or stored subprogram using the UTL_INADDR package. You can find details about this package in the Oracle Database PL/SQL Packages and Types Reference. You use the results in calls to the UTL_TCP package.

Using TCP/IP Connections from PL/SQL

You can open TCP/IP connections to systems on the network, and read or write to the corresponding sockets, using the UTL_TCP package. You can find details about this package in the Oracle Database PL/SQL Packages and Types Reference.

Retrieving HTTP URL Contents from PL/SQL

You can retrieve the contents of an HTTP URL using the UTL_HTTP package. The contents are typically in the form of HTML-tagged text, but might be plain text, a JPEG image, or any sort of file that is downloadable from a Web server. You can find details about this package in the Oracle Database PL/SQL Packages and Types Reference.

The UTL_HTTP package lets you:

  • Control the details of the HTTP session, including header lines, cookies, redirects, proxy servers, IDs and passwords for protected sites, and CGI parameters through the GET or POST methods.

  • Speed up multiple accesses to the same Web site using HTTP 1.1 persistent connections.

  • Construct and interpret URLs for use with UTL_HTTP through the ESCAPE and UNESCAPE functions in the UTL_URL package.

Typically, developers have used Java or Perl to perform these operations; this package lets you do them with PL/SQL.

CREATE OR REPLACE PROCEDURE show_url
(
    url      IN VARCHAR2,
    username IN VARCHAR2 DEFAULT NULL,
    password IN VARCHAR2 DEFAULT NULL
) AS
    req       utl_http.req;
    resp      utl_http.resp;
    name      VARCHAR2(256);
    value     VARCHAR2(1024);
    data      VARCHAR2(255);
    my_scheme VARCHAR2(256);
    my_realm  VARCHAR2(256);
    my_proxy  BOOLEAN;
BEGIN
-- When going through a firewall, pass requests through this host.
-- Specify sites inside the firewall that don't need the proxy host.
  utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com');

-- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes,
-- rather than just returning the text of the error page.
  utl_http.set_response_error_check(FALSE);

-- Begin retrieving this Web page.
  req := utl_http.begin_request(url);

-- Identify ourselves. Some sites serve special pages for particular browsers.
  utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');

-- Specify a user ID and password for pages that require them.
  IF (username IS NOT NULL) THEN
    utl_http.set_authentication(req, username, password);
  END IF;

  BEGIN
-- Start receiving the HTML text.
    resp := utl_http.get_response(req);

-- Show the status codes and reason phrase of the response.
    dbms_output.put_line('HTTP response status code: ' || resp.status_code);
    dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase);

-- Look for client-side error and report it.
    IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN

-- Detect whether the page is password protected, and we didn't supply
-- the right authorization.
      IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN
        utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy);
        IF (my_proxy) THEN
          dbms_output.put_line('Web proxy server is protected.');
          dbms_output.put('Please supply the required ' || my_scheme ||
            ' authentication username/password for realm ' || my_realm ||
            ' for the proxy server.');
        ELSE
          dbms_output.put_line('Web page ' || url || ' is protected.');
          dbms_output.put('Please supplied the required ' || my_scheme ||
            ' authentication username/password for realm ' || my_realm ||
            ' for the Web page.');
        END IF;
      ELSE
        dbms_output.put_line('Check the URL.');
      END IF;

      utl_http.end_response(resp);
      RETURN;

-- Look for server-side error and report it.
    ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN

      dbms_output.put_line('Check if the Web site is up.');
      utl_http.end_response(resp);
      RETURN;

    END IF;
    
-- The HTTP header lines contain information about cookies, character sets,
-- and other data that client and server can use to customize each session.
    FOR i IN 1..utl_http.get_header_count(resp) LOOP
      utl_http.get_header(resp, i, name, value);
      dbms_output.put_line(name || ': ' || value);
    END LOOP;

-- Keep reading lines until no more are left and an exception is raised.
    LOOP
      utl_http.read_line(resp, value);
      dbms_output.put_line(value);
    END LOOP;
  EXCEPTION
    WHEN utl_http.end_of_body THEN
    utl_http.end_response(resp);
  END;

END;
/
SET serveroutput ON 
-- The following URLs illustrate the use of this procedure,
-- but these pages do not actually exist. To test, substitute
-- URLs from your own Web server.
exec show_url('http://www.oracle.com/no-such-page.html')
exec show_url('http://www.oracle.com/protected-page.html')
exec show_url('http://www.oracle.com/protected-page.html', 'scott', 'tiger')

Using Tables, Image Maps, Cookies, and CGI Variables from PL/SQL

Packages for all of these functions are supplied with Oracle8i and higher. You use these packages in combination with the mod_plsql plug-in of Oracle HTTP Server (OHS). You can format the results of a query in an HTML table, produce an image map, set and get HTTP cookies, check the values of CGI variables, and combine other typical Web operations with a PL/SQL program.

Documentation for these packages is not part of the database documentation library. The location of the documentation depends on the particular application server you are running. To get started with these packages, look at their subprogram names and parameters using the SQL*Plus DESCRIBE statement:

DESCRIBE HTP;
DESCRIBE HTF;
DESCRIBE OWA_UTIL;