PK
\Eoa, mimetypeapplication/epub+zipPK \E iTunesMetadata.plistZ
mod_plsql provides support for deploying PL/SQL-based database applications on the World Wide Web. It is part of Oracle HTTP Server, which ships with Oracle Fusion Middleware and Oracle Database.As part of the Oracle HTTP Server, it is the job of mod_plsql to interpret a URL sent by a Web browser to a Web server, call the appropriate PL/SQL subprograms to treat the browser request, then return the generated response to the browser. Typically, mod_plsql responds to a Web browser HTTP request by constructing an HTML page to display. There are additional uses for mod_plsql, of which two are listed subsequently:
Transfer files from a client machine to or from Oracle Database. You can upload and download text files or binary files.
Perform custom user authentication in Web applications.
As a plug-in to Oracle HTTP Server, mod_plsql causes stored procedures to be executed in response to HTTP requests. For each URL that is processed, mod_plsql either uses a database session from its connection pool, or creates a new session on the fly and pools it. For mod_plsql to invoke the appropriate database PL/SQL procedure in a URL-processing session, you must first configure a virtual path and associate that path with a Database Access Descriptor (DAD).
A DAD is a named set of configuration values that specify the information necessary to create a session for a specific database and a specific database user/password. This includes the database service name and the Globalization Support setting (for example, language) for the session. Refer to Section 3.2, "Database Access Descriptors (DADs)" for more information.
To develop the stored procedures that are executed by mod_plsql at runtime, you use the PL/SQL Web Toolkit: a set of PL/SQL packages that can be used to obtain information about an HTTP request; specify HTTP response headers, such as cookies, content-type, and mime-type, for HTTP headers; set cookies; and generate standard HTML tags for creating HTML pages. Refer to Oracle Fusion Middleware PL/SQL Web Toolkit Reference for more information.
This chapter discusses the following topics:
mod_plsql is an Oracle HTTP Server plug-in that communicates with the database. It maps browser requests into database stored procedure calls over a SQL*Net connection. It is often indicated by a /pls
virtual path.
The following scenario (Figure 3-1) provides an overview of what steps occur when a server receives a client request:
The Oracle HTTP Server receives a request containing a virtual path, which is configured to be serviced by mod_plsql.
The Oracle HTTP Server routes the request to mod_plsql.
By using the configuration information stored in your DAD, mod_plsql connects to the database. The request is forwarded by mod_plsql to the Oracle Database.
mod_plsql prepares the call parameters, and invokes the PL/SQL procedure in the application.
The PL/SQL procedure generates an HTML page using data and the PL/SQL Web Toolkit accessed from the database.
The response is returned to mod_plsql.
The Oracle HTTP Server sends the response to the client browser.
The procedure that mod_plsql invokes returns the HTTP response to the client. To simplify this task, mod_plsql includes the PL/SQL Web Toolkit, which contains a set of packages called the owa packages. Use these packages in your stored procedure to get information about the request, construct HTML tags, and return header information to the client. Install the toolkit in a common schema so that all users can access it.
Each mod_plsql request is associated with a Database Access Descriptor (DAD), a set of configuration values used for database access. A DAD specifies information such as:
the database alias (Oracle Net service name).
a connect string, if the database is remote.
a procedure for uploading and downloading documents.
You can also specify username and password information in a DAD. If they are not specified, the user is prompted to enter a username and password when the URL is invoked.
See Also: Oracle Fusion Middleware Administrator's Guide for Oracle HTTP Server for descriptions of the DAD parameters and an overview of the mod_plsql configuration files. |
To invoke mod_plsql in a Web browser, input the URL in the following format:
protocol://hostname[:port]/DAD_location/[[!][schema.][package.]proc_name[?query_string]]
Table 3-1 lists parameters for invoking mod_plsql.
Table 3-1 Invoking mod_plsql Parameters
Parameter | Description |
---|---|
protocol |
Either |
hostname |
The machine where the Web server is running. |
port (optional) |
The port at which the Web server is listening. If omitted, port 80 is assumed. |
DAD location |
A virtual path to handle PL/SQL requests that you have configured in the Web server. The DAD location can contain only ASCII characters. |
(optional) |
Indicates to use the flexible parameter passing scheme. See Section 3.6.2, "Flexible Parameter Passing" for more information. |
schema (optional) |
The database schema name. If omitted, name resolution for |
package (optional) |
The package that contains the PL/SQL stored procedure. If omitted, the procedure is standalone. |
proc_name |
The PL/SQL stored procedure to run. This must be a procedure and not a function. It can accept only IN arguments. |
?query_string (optional) |
The parameters for the stored procedure. The string follows the format of the GET method. For example:
|
Example 3-1, Example 3-2, and Example 3-3 discuss how different types of procedures are invoked.
Example 3-1 Invoking A Procedure That Does Not Take Arguments
http://www.acme.com:9000/pls/mydad/mypackage.myproc
The Web server running on www.acme.com
and listening at port 9000
handles the request. When the Web server receives the request, it passes the request to mod_plsql. This is because the /pls/mydad
indicates that the Web server is configured to invoke mod_plsql. It then uses the DAD associated with /pls/mydad
and runs the myproc
procedure stored in mypackage
.
Example 3-2 Invoking A Procedure That Takes Arguments
http://www.acme.com:9000/pls/mydad/mypackage.myproc?a=v&b=1
The Web server running on www.acme.com
and listening at port 9000
handles the request. When the Web server receives the request, it uses the DAD associated with /pls/mydad
and runs the myproc
procedure stored in mypackage
, and passes two arguments, a
and b
, with the values v
, and 1
to the procedure.
Example 3-3 Invoking the Default Procedure Stored in the DAD Configuration
http://www.acme.com:9000/pls/mydad
The Web server running on www.acme.com
and listening at port 9000
handles the request. When the Web server receives the request, it uses the DAD associated with /pls/mydad
and invokes the default procedure configured in the DAD. For example, if the configuration parameter PlsqlDefaultPage
in the DAD /pls/mydad
is set to myschema.mypackage.myproc
, then the procedure myschema.mypackage.myproc
is invoked for the request.
In this example, the default home page for the mydad
DAD (as specified in the DAD Configuration) is displayed.
The POST, GET, and HEAD methods in the HTTP protocol instruct browsers on how to pass parameter data (usually in the form of name-value pairs) to applications. The parameter data is generated by HTML forms.
mod_plsql applications can use any of the methods. Each method is as secure as the underlying transport protocol (HTTP or HTTPS).
When using the POST method, parameters are passed in the request body. Generally, if you are passing large amounts of parameter data to the server, use the POST method.
When using the GET method, parameters are passed using a query string. The limitation of this method is that the length of the value in a name-value pair cannot exceed the maximum length for the value of an environment variable, as imposed by the underlying operating system. In addition, operating systems have a limit on how many environment variables you can define.
When using the HEAD method, it has the same functionality as the GET method. The difference is that only the HTTP status line and the HTTP headers are passed back. No content data is streamed back to the browser. This is useful for monitoring tools in which you are only interested if the request is processed correctly.
Mixed Mode - In mod_plsql you can pass some of the parameters in a query string and the remaining ones as POST data. For example, if you have a procedure foo
(a varchar2, b number), and want to pass values "v" and "1" to 'a' and 'b' respectively, you could do so in three ways to create URLs:
All values are specified as part of the query string.
http://host:port/pls/DAD/foo?a=v&b=1
All values are specified as part of the POST data.
http://host:port/pls/DAD/foo, POST data="a=v&b=1"
Some of the parameters are specified in the URL and the rest in the POST data.
http://host:port/pls/DAD/foo?a=v, POST data="b=1"
Note: POST data is generated as part of the input fields on a HTML form. You should not create the POST string manually in the PL/SQL procedure, or in the URL. The Submit operation of the HTML form will generate a POST request and pass the value to your procedure. |
After processing a URL request for a procedure invocation, mod_plsql performs a rollback if there were any errors. Otherwise, it performs a commit. This mechanism does not allow a transaction to span across multiple HTTP requests. In this stateless model, applications typically maintain state using HTTP cookies or database tables.
Because HTTP supports character streams only, mod_plsql supports the following subset of PL/SQL data types:
NUMBER
VARCHAR2
TABLE OF NUMBER
TABLE OF VARCHAR2
Records are not supported.
mod_plsql supports:
Parameter passing by name
Each parameter in a URL that invokes procedure or functions identified by a unique name. Overloaded parameters are supported. See Section 3.6.1, "Parameter Passing by Name (Overloaded Parameters)" for more information.
Flexible parameter passing
Procedures are prefixed by a!
character. See Section 3.6.2, "Flexible Parameter Passing" for more information.
Large (up to 32K) parameters passing
See Section 3.6.3, "Large Parameter Passing" for more information.
Note: mod_plsql handles multi-value variables by storing the values in a PL/SQL table. This enables you to be flexible about how many values the user can pick, and it makes it easy for you to process the user's selections as a unit. Each value is stored in a row in the PL/SQL table, starting at index 1. The first value (in the order that it appears in the query string) of a variable that has multiple values is placed at index 1, the second value of the same variable is placed at index 2, and so on. The PL/SQL application should not rely on the ordering of the arguments passed by mod_plsql, as it can change without notice. If the order of the values in the PL/SQL table is significant to your procedure, you need to modify your PL/SQL application to do the ordering internally.If you do not have variables with multiple values, the order in which the variables appear does not matter, because their values are passed to the procedure's parameters by name, and not by position. The PL/SQL tables used as parameters in the mod_plsql environment must have a base type of If you cannot guarantee that at least one value will be submitted to the PL/SQL table (for example, the user can select no options), use a hidden form element to provide the first value. Not providing a value for the PL/SQL table produces an error, and you cannot provide a default value for a PL/SQL table. |
Overloading allows multiple subprograms (procedures or functions) to have the same name, but differ in the number, order, or the datatype family of the parameters. When you call an overloaded subprogram, the PL/SQL compiler determines which subprogram to call based on the data types passed.
PL/SQL enables you to overload local or packaged subprograms. Standalone subprograms cannot be overloaded.
You must give parameters different names for overloaded subprograms that have the same number of parameters. Because HTML data is not associated with datatypes, mod_plsql does not know which version of the subprogram to call.
For example, although PL/SQL enables you to define two procedures using the same parameter names for the procedures, an error occurs if you use this with mod_plsql.
-- legal PL/SQL, but not for mod_plsql CREATE PACKAGE my_pkg AS PROCEDURE my_proc (val IN VARCHAR2); PROCEDURE my_proc (val IN NUMBER); END my_pkg;
To avoid the error, name the parameters differently. For example:
-- legal PL/SQL and also works for mod_plsql CREATE PACKAGE my_pkg AS PROCEDURE my_proc (valvc2 IN VARCHAR2); PROCEDURE my_proc (valnum IN NUMBER); END my_pkg;
The URL to invoke the first version of the procedure looks similar to:
http://www.acme.com:9000/pls/mydad/my_pkg.my_proc?valvc2=input
The URL to invoke the second version of the procedure looks similar to:
http://www.acme.com:9000/pls/mydad/my_pkg.my_proc?valnum=34
If you have overloaded PL/SQL procedures where the parameter names are identical, but the data type is owa_util.ident_arr
(a table of varchar2) for one procedure and a scalar type for another procedure, mod_plsql can still distinguish between the two procedures. For example, if you have the following procedures:
CREATE PACKAGE my_pkg AS PROCEDURE my_proc (val IN VARCHAR2); -- scalar data type PROCEDURE my_proc (val IN owa_util.ident_arr); -- array data type END my_pkg;
Each of these procedures has a single parameter of the same name, val
.
When mod_plsql gets a request that has only one value for the val
parameter, it invokes the procedure with the scalar data type, as shown in Example 3-4.
Example 3-4 Sending a URL to Execute the Scalar Version of a Procedure
Send the following URL to execute the scalar version of the procedure:
http://www.acme.com:9000/pls/mydad/my_proc?val=john
When mod_plsql gets a request with more than one value for the val
parameter, it then invokes the procedure with the array data type, as shown in Example 3-5.
Example 3-5 Sending a URL to Execute the Array Version of a Procedure
Send the following URL to execute the array version of the procedure:
http://www.acme.com:9000/pls/mydad/my_proc?val=john&val=sally
To ensure that the array version executes, use hidden form elements on your HTML page to send dummy values that are checked and discarded in your procedure.
You can have HTML forms from which users can select any number of elements. If these elements have different names, you would have to create overloaded procedures to handle each possible combination. Alternatively, you could insert hidden form elements to ensure that the names in the query string are consistent each time, regardless of what elements the user chooses. mod_plsql makes this operation easier by supporting flexible parameter passing to handle HTML forms where users can select any number of elements.
To use flexible parameter passing for a URL-based procedure invocation, prefix the procedure with an exclamation mark (!) in the URL. You can use two or four parameters. The two parameter interface provides improved performance with mod_plsql. The four parameter interface is supported for compatibility.
procedure [proc_name] (name_array IN [array_type], value_array IN [array_type]);
Table 3-2 lists parameters for two parameter interface.
Table 3-2 Two Parameter Interface Parameters
Parameter | Description |
---|---|
(required) |
The name of the PL/SQL procedure that you are invoking. |
|
The names from the query string (indexed from 1) in the order submitted. |
|
The values from the query string (indexed from 1) in the order submitted. |
(required) |
Any PL/SQL index-by table of varchar2 type (Example, owa.vc_arr). |
Example 3-6 shows the use of two parameter interface.
Example 3-6 Two Parameter Interface
If you send the following URL:
http://www.acme.com:9000/pls/mydad/!scott.my_proc?x=john&y=10&z=doe
The exclamation mark prefix (!) instructs mod_plsql to use flexible parameter passing. It invokes procedure scott.myproc
and passes it the following two arguments:
name_array ==> ('x', 'y', 'z') value_array ==> ('john', '10', 'doe')
Note: When using this style of Flexible Parameter Passing, the procedure must be defined with the parametersname_array and value_array . The datatypes of these arguments should match the datatypes shown in the example. |
The four parameter interface is supported for compatibility.
procedure [proc_name] (num_entires IN NUMBER, name_array IN [array_type], value_array IN [array_type], reserved in [array_type]);
Table 3-3 lists parameters for four parameter interface.