Oracle8i
Oracle Servlet
Engine User's Guide Release 3 (8.1.7) A83720-01 |
|
This chapter describes new and changed features for release 8.1.7. The topics in this chapter include:
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.
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:
/pls
. You might specify different virtual paths to set up multiple instances of the servlet, each with different settings.
plsGateway
. This is the name that you use when forwarding requests from another servlet.
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¶m2=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.
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:
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.
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.
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);
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.
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.
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.
The procedures in this package use the following type for passing parameters:
TYPE varchar2_table IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
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;
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);
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);
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;
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);
Revokes gateway administrative privileges of a user.
PROCEDURE revoke_admin(port IN PLS_INTEGER, username IN VARCHAR2);
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);
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);
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;
Deletes a global attribute.
PROCEDURE delete_global_attribute(port IN PLS_INTEGER, attrname IN VARCHAR2);
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);
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);
Drops a DAD from the gateway configuration.
PROCEDURE drop_dad(port IN PLS_INTEGER, dadname IN VARCHAR2);
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);
set_dad_attribute(8080, 'myApp', 'default_page', 'myApp.home'); set_dad_attribute(8080, 'myApp', 'document_path', 'docs');
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;
Deletes a DAD attribute.
PROCEDURE delete_dad_attribute(port IN PLS_INTEGER, dadname IN VARCHAR2, attrname IN VARCHAR2);
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 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);
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:
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);
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)
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);
|
![]() Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|