A
Writing PL/SQL Servlets

This chapter describes new and changed features for release 8.1.7. The topics in this chapter include:

Overview of PL/SQL Servlets

When you use the Internet Application Server (iAS), you typically access PL/SQL stored procedures over the Web by using the mod_plsql module. This module is recommended for stateless PL/SQL procedures, where the transaction state and values of package variables are not preserved once the original procedure call is finished.

You can also run PL/SQL stored procedures using the Oracle Servlet Engine through the mod_ose module of iAS. This module is recommended for stateful PL/SQL procedures, which behave similar to Java servlets. These procedures can preserve state (such as package variables and transaction state) across multiple HTTP requests.

See Also:

For detailed information about running PL/SQL procedures over the web, see Using mod_plsql in the Oracle HTTP Server documentation.

Configuring mod_ose to Run PL/SQL Servlets

To run PL/SQL stored procedures as servlets, you must first load and publish one servlet that serves as a gateway (known as the embedded PL/SQL gateway). This is a one-time operation. The PL/SQL procedures can then run over the Web without any code changes or loading/publishing steps for each procedure.

From SQL*Plus, connect as SYS and run the script rdbms/admin/initplgs.sql to load the embedded PL/SQL gateway servlet into the database server.

From the system command line, use the sess_sh command to publish the servlet so that it can be accessed through a URL. This operation registers a virtual path, and every request for a document using that virtual path is handled by the embedded PL/SQL gateway servlet, which runs the appropriate PL/SQL stored procedure. For example:

% $ORACLE_HOME/jis/bin/unix/sess_sh -s http://webserver:portnumber -u 
sys/change_on_install
--Session Shell--
--type "help" at the command line for help message
$ publishservlet -virtualpath pls/* /webdomains/contexts/default plsGateway 
SYS:oracle.plsql.web.PLSQLGatewayServlet

This publishes the gateway servlet under the name plsGateway with a default context. In this example:

PL/SQL stored procedures can be accessed using the virtual path /pls. You might specify different virtual paths to set up multiple instances of the servlet, each with different settings.
You can choose a different name in place of plsGateway. This is the name that you use when forwarding requests from another servlet.
You must specify the SYS: parameter as shown. It is the name of the actual Java class file.

A URL to access a stored procedure through the gateway might look like one of these:

http://webserver/pls/dadname/procedurename
http://webserver/pls/dadname/schemaname.procedurename
http://webserver:portnumber/pls/dadname/procedurename
http://webserver/pls/dadname/procedurename?param1=value1&param2=value2

The procedure names in these URLs specify PL/SQL procedures. They can use either a stateful or a stateless execution model depending on how you configure the DAD, as explained in the following section.

See Also:

Using mod_plsql in the documentation for the Oracle HTTP Server for information about the DAD configuration parameters.

Oracle8i Java Tools Reference for the syntax of the sess_sh command.

Writing Stateful PL/SQL Stored Procedures

Typically, when a PL/SQL stored procedure is run over the web, its state goes away when the procedure ends. This state information includes the values of any package variables it accesses, its transaction state, and any rows it inserted into temporary tables.

You might want to change this behavior when several procedures are called in sequence, for example during a registration procedure that uses several different HTML forms. Instead of passing the information from one procedure to another using CGI-style parameters, you can store it in package variables until the entire process is complete. You can do a single commit or rollback when the registration succeeds or fails.

You can preserve this state information across calls to PL/SQL stored procedures by following these steps:

Publish the embedded PL/SQL gateway servlet through the Oracle Servlet Engine, as previously described. This only needs to be done once.
Set the stateful attribute of the DAD to Yes. By default, its value is No. This only needs to be done once, and remains in effect for all packages and stored procedures called through this DAD. You can also set this attribute at the global level, so that all new DADs inherit the same setting.
Create a package containing some variables, if you need storage for data to be preserved across calls.
Write one or more PL/SQL stored procedures that access the package variables, perform different parts of a single transaction, and generally take advantage of stateful execution.
When all the data is ready, explicitly commit if the operation is successful, or rollback if the operation fails. There is no implicit commit when the procedure ends. When an exception is raised, there is an implicit rollback to the state at the beginning of the current procedure call, but the transaction remains open so a commit or rollback is still needed at the end.

To explicitly delete the package state information, you can call DBMS_SESSION.RESET_PACKAGE. This technique lets you get the performance benefits of stateful procedures while keeping the default behavior for state information.

Configuring Database Access Descriptors from an Application

When you configure a Web server to run Oracle stored procedures, you typically use a browser interface to set up the database access descriptor (DAD). To automate this operation, you can configure the DAD by calling procedures in the package DBMS_EPGC. The following example shows how to set or change the DAD configuration from an application. The next section describes each procedure in package DBMS_EPGC.

--
-- A sample procedure that configures an embedded gateway
-- instance for the given port number.
--
CREATE OR REPLACE PROCEDURE sample1_init_cfg(port IN PLS_INTEGER) IS
BEGIN

   --
   -- reset instance (port) configuration.
   --
   dbms_epgc.drop_instance(port);
   dbms_epgc.create_instance(port);

   --
   -- set global attributes for the embedded PL/SQL Gateway instance.
   --
   dbms_epgc.set_global_attribute(port, 'defaultdad', 'HR');
   dbms_epgc.set_global_attribute(port, 'adminPath', '/admin_/');
   dbms_epgc.set_global_attribute(port, 'stateful', 'Yes');


   --
   -- create a database access descriptor (DAD) called APPS
   -- 
   dbms_epgc.create_dad(port, 'APPS');
   dbms_epgc.set_dad_attribute(port, 'APPS', 'default_page', 'APPS.pkg.home');
   dbms_epgc.set_dad_attribute(port, 'APPS', 'document_table', 'APPS.doc_tab');
   dbms_epgc.set_dad_attribute(port, 'APPS', 'document_path', 'docs');
   dbms_epgc.set_dad_attribute(port, 'APPS', 'upload_as_blob', 'jpeg, gif, 
txt');
   dbms_epgc.set_dad_attribute(port, 'APPS', 'document_proc',
                               'APPS.doc_pkg.process_download');
   -- override global setting for stateful attribute
   dbms_epgc.set_dad_attribute(port, 'APPS', 'stateful', 'No');


   --
   -- create a database access descriptor (DAD) called HR.
   --
   dbms_epgc.create_dad(port, 'HR');
   --
   dbms_epgc.set_dad_attribute(port, 'HR', 'username', 'scott');
   dbms_epgc.set_dad_attribute(port, 'HR', 'password', 'tiger');
   dbms_epgc.set_dad_attribute(port, 'HR', 'default_page', 'HR.hello');
   dbms_epgc.set_dad_attribute(port, 'HR', 'document_table', 'wpg_new_doctab');
   dbms_epgc.set_dad_attribute(port, 'HR', 'document_path', 'docs');
   dbms_epgc.set_dad_attribute(port, 'HR', 'upload_as_blob', 'txt');
   dbms_epgc.set_dad_attribute(port, 'HR', 'upload_as_long_raw', 'sql');
   dbms_epgc.set_dad_attribute(port, 'HR', 'document_proc',
                               'HR.docpkg.process_download');

   --
   -- Commit the changes.
   -- 
   COMMIT;

END;
/
show errors;

--
-- Configure the embedded gateway for port 8080.
--
EXECUTE sample1_init_cfg(8080);

If you have worked with DADs before, you might be familiar with the syntax of the configuration files used by WebDB and OAS. You can import such information in a single operation, as demonstrated by the following program:

-- A sample procedure that configures an embedded gateway
-- using the import method.
--
CREATE OR REPLACE PROCEDURE sample2_init_cfg(port IN PLS_INTEGER) IS
   string VARCHAR2(2000);
BEGIN

   string := '
[PLSQL_GATEWAY]
adminpath = /admin_/
defaultdad = HR
stateful = yes
[DAD_APPS]
DEFAULT_PAGE=APPS.pkg.home
DOCUMENT_PATH=docs
DOCUMENT_PROC=APPS.doc_pkg.process_download
DOCUMENT_TABLE=APPS.doc_tab
STATEFUL=no
UPLOAD_AS_BLOB=jpeg, gif, txt
[DAD_HR]
DEFAULT_PAGE=HR.hello
DOCUMENT_PATH=docs
DOCUMENT_PROC=HR.docpkg.process_download
DOCUMENT_TABLE=wpg_new_doctab
USERNAME=scott
PASSWORD=tiger
UPLOAD_AS_BLOB=txt
UPLOAD_AS_LONG_RAW=sql
';

   dbms_epgc.drop_instance(port);
   dbms_epgc.create_instance(port);

   dbms_epgc.import(port, string);
   --
   -- Commit the changes.
   -- 
   COMMIT;

END;
/
show errors;

--
-- Configure the embedded gateway for port 8080.
--
EXECUTE sample2_init_cfg(8080);

See Also:
The Using mod_plsql book for the Oracle HTTP Server for descriptions of the configuration parameters. Some of the caching and connection pooling parameters do not apply when the stored procedures are accessed outside of mod_plsql.

Package DBMS_EPGC

This package lets you configure database access descriptors (DADs) for the Oracle Servlet Engine.

The embedded PL/SQL gateway runs as a plug-in in the Oracle Servlet Engine embedded in the Oracle database. There can be multiple instances of the Oracle Servlet Engine, each listening for HTTP requests on a unique port. With each port, you can associate an instance of the embedded PL/SQL gateway. The port number in the the HTTP request determines which instance of the embedded PL/SQL gateway (with its associated configuration) is used.

Each instance of the embedded PL/SQL gateway is independently configurable. You can use the DBMS_EPGC package to set and get this configuration information.

Because the configuration information is stored in the database rather than on a middle tier, it does not work with DADs from the Oracle HTTP Server. You can exchange configuration information with DADs on a middle tier using the IMPORT/EXPORT procedures in this package.

Security Model

Although all users have execute privileges on this package, the package performs its own security checking by maintaining a private list of administrative users; only these user can call the methods of this package. SYS and SYSTEM are always administrative users by default. The GRANT_ADMIN and REVOKE_ADMIN procedures control the embedded gateway administration privileges for other database users.

Transactional Behavior

All operations run in the caller's transactional context. The caller must explicitly commit after calling any update operations such as import, set, or drop.

To execute configuration operations in a separate transaction context, you can wrap the calls to this package in an autonomous PL/SQL block.

Types

The procedures in this package use the following type for passing parameters:

   TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;

Exceptions

The procedures in this package can raise the following exceptions:

config_error EXCEPTION;
PRAGMA EXCEPTION_INIT(config_error, -20000);
config_error_num CONSTANT PLS_INTEGER := -20000;

user_already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(user_already_exists, -20001);
user_already_exists_num CONSTANT PLS_INTEGER := -20001;

invalid_port EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_port, -20002);
invalid_port_num PLS_INTEGER := -20002;

invalid_username EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_username, -20003);
invalid_username_num PLS_INTEGER := -20003;

not_an_admin EXCEPTION;
PRAGMA EXCEPTION_INIT(not_an_admin, -20004);
not_an_admin_num PLS_INTEGER := -20004;

privilege_error EXCEPTION;
PRAGMA EXCEPTION_INIT(privilege_error, -20005);
privilege_error_num PLS_INTEGER := -20005;

dad_not_found EXCEPTION;
PRAGMA EXCEPTION_INIT(dad_not_found, -20006);
dad_not_found_num PLS_INTEGER := -20006;

invalid_dad_attribute EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_dad_attribute, -20007);
invalid_dad_attribute_num PLS_INTEGER := -20007;

invalid_global_attribute EXCEPTION;
PRAGMA EXCEPTION_INIT(invalid_global_attribute, -20008);
invalid_global_attribute_num PLS_INTEGER := -20008;

instance_already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(instance_already_exists, -20009);
instance_already_exists_num PLS_INTEGER := -20009;

Summary of Subprograms

CREATE_INSTANCE Procedure

Creates a gateway instance identified by a port number. This call must be done before configuring attributes and privileges for the instance.

If the instance (port) is already in use, this operation results in an error.

The bulk configuration procedures (IMPORT and EXPORT) can be used without explicitly creating the instance.

The calling user of this routine automatically gets administrative privileges on this gateway instance.

PROCEDURE create_instance(port IN PLS_INTEGER);

DROP_INSTANCE Procedure

Drops the configuration information for a gateway instance identified by a port number. In some cases it might be easier to drop and recreate the instance than to modify it.

PROCEDURE drop_instance(port IN PLS_INTEGER);

DROP_ALL_INSTANCES Procedure

Drops the configuration information for all gateway instances in the database.

The caller of this procedure must either be SYS or have administrative privileges on all gateway instances in the database.

PROCEDURE drop_all_instances;

GRANT_ADMIN Procedure

The following APIs grant and revoke gateway administration privileges to database users. The SYS and SYSTEM users are always administrative users by default.

Grants gateway administrative privileges to a user.

PROCEDURE grant_admin(port IN PLS_INTEGER, username IN VARCHAR2);

REVOKE_ADMIN Procedure

Revokes gateway administrative privileges of a user.

PROCEDURE revoke_admin(port IN PLS_INTEGER, username IN VARCHAR2);

GET_ADMIN_LIST Procedure

Gets the list of gateway administrative users, other than SYS and SYSTEM. If no such users exist, the result is an empty table, with zero elements.

PROCEDURE get_admin_list(port  IN          PLS_INTEGER,
                         users OUT NOCOPY  VARCHAR2_TABLE);

SET_GLOBAL_ATTRIBUTE Procedure

Sets the value of a global attribute, one that applies to all DADs. If an attribute is already set for a given port number, the old value is overwritten with the new one.

Attribute names are not case-sensitive. Attribute values are sometimes case-sensitive, for example when the values represent UNIX filenames, but values such as Yes and No are not case-sensitive.

PROCEDURE set_global_attribute(port      IN PLS_INTEGER,
                               attrname  IN VARCHAR2,
                               attrvalue IN VARCHAR2);

GET_GLOBAL_ATTRIBUTE Procedure

Gets the value of a global attribute. Returns NULL if the attribute has not been set. Raises an exception if the attribute is not a valid attribute.

FUNCTION get_global_attribute(port      IN PLS_INTEGER,
                              attrname  IN VARCHAR2)
RETURN VARCHAR2;

DELETE_GLOBAL_ATTRIBUTE Procedure

Deletes a global attribute.

PROCEDURE delete_global_attribute(port      IN PLS_INTEGER,
                                  attrname  IN VARCHAR2);

GET_ALL_GLOBAL_ATTRIBUTES Procedure

Get all global attributes/values for an embedded gateway instance. The output is two index-by tables, one with the attribute names, and the other with the corresponding attribute values. If the gateway instance has no global attributes set, the output arrays are empty.

PROCEDURE get_all_global_attributes(port           IN  PLS_INTEGER,
                                    attrnamearray  OUT NOCOPY VARCHAR2_TABLE,
                                    attrvaluearray OUT NOCOPY VARCHAR2_TABLE);

CREATE_DAD Procedure

Creates a new DAD, with no attributes set. The DAD name is not case-sensitive. If a DAD with this name already exists, the old information is deleted.

PROCEDURE create_dad(port IN PLS_INTEGER, dadname IN VARCHAR2);

DROP_DAD Procedure

Drops a DAD from the gateway configuration.

PROCEDURE drop_dad(port IN PLS_INTEGER, dadname IN VARCHAR2);

SET_DAD_ATTRIBUTE Procedure

Sets an attribute for a DAD (Database Access Descriptor). It creates the DAD if it does not already exist. Any old value of the attribute is overwritten.

DAD names and DAD attribute names are not case sensitive. DAD attribute values might be case-sensitive depending upon the attribute.

PROCEDURE set_dad_attribute(port      IN PLS_INTEGER,
                            dadname   IN VARCHAR2,
                            attrname  IN VARCHAR2,
                            attrvalue IN VARCHAR2);

Example

set_dad_attribute(8080, 'myApp', 'default_page', 'myApp.home');
set_dad_attribute(8080, 'myApp', 'document_path', 'docs');

GET_DAD_ATTRIBUTE Procedure

Gets the value of a DAD attribute. Raises an error if DAD does not exist, or if the attribute is not a valid attribute. Returns NULL if the attribute is not set.

function get_dad_attribute(port      IN PLS_INTEGER,
                           dadname   IN VARCHAR2,
                           attrname  IN VARCHAR2) return VARCHAR2;

DELETE_DAD_ATTRIBUTE Procedure

Deletes a DAD attribute.

PROCEDURE delete_dad_attribute(port      IN PLS_INTEGER,
                               dadname   IN VARCHAR2,
                               attrname  IN VARCHAR2);

GET_DAD_LIST Procedure

Gets the list of all DADs for an embedded gateway instance. If no DADs exist, the result is an empty table, with zero elements.

PROCEDURE get_dad_list(port IN PLS_INTEGER,
                       dadarray OUT NOCOPY VARCHAR2_TABLE);

GET_ALL_DAD_ATTRIBUTES Procedure

Get all attributes of a DAD. The output is two index-by tables, one with the attribute names, and the other with the corresponding attribute values. If the DAD has no attributes set, the output arrays are empty.

PROCEDURE get_all_dad_attributes(port           IN  PLS_INTEGER,
                                 dadname        IN  VARCHAR2,
                                 attrnamearray  OUT NOCOPY VARCHAR2_TABLE,
                                 attrvaluearray OUT NOCOPY VARCHAR2_TABLE);

IMPORT Procedure

The following procedures let you bulk load the configuration information for an embedded PL/SQL gateway. The input can be in any of the following forms:

A VARCHAR2 (with a maximum length of 32 KB)
An index-by table of VARCHAR2 variables
A CLOB

The syntax of the configuration information must be the same as that used by the Oracle HTTP Server in iAS. The easiest way to create it is to export it from an existing DAD.

PROCEDURE import(port IN PLS_INTEGER,
                 cfg  IN VARCHAR2);

PROCEDURE import(port IN PLS_INTEGER,
                 cfg  IN DBMS_EPGC.VARCHAR2_TABLE);

PROCEDURE import(port IN PLS_INTEGER,
                 cfg  IN CLOB);

EXPORT Procedure

The following procedures export the configuration information of an embedded PL/SQL gateway to a flattened form so that it can be used with the Oracle HTTP Server in iAS. The output can be any of the following:

VARCHAR2 (with a maximum length of 32 KB)
index-by table of VARCHAR2 variables

CLOB

PROCEDURE export(port   IN  PLS_INTEGER,
                 cfg    OUT NOCOPY VARCHAR2);

PROCEDURE export(port IN  PLS_INTEGER,
                 cfg  OUT NOCOPY dbms_epgc.VARCHAR2_TABLE);

PROCEDURE export(port   IN  PLS_INTEGER,
                 cfg    OUT NOCOPY CLOB);

A Writing PL/SQL Servlets