| Oracle® HTTP Server mod_plsql User's Guide 10g Release 1 (10.1) Part Number B12303-01 |
|
|
View PDF |
| Oracle® HTTP Server mod_plsql User's Guide 10g Release 1 (10.1) Part Number B12303-01 |
|
|
View PDF |
mod_plsql provides support for building PL/SQL-based applications on the Web. PL/SQL stored procedures retrieve data from a database and generate HTTP responses containing data and code to display in a Web browser. mod_plsql also supports other Oracle products such as OracleAS Portal.
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 provides an overview of what steps occur when a server receives a client request:
Text description of the illustration overview.gif
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:
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 HTTP Server Administrator's Guide 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]]
| Parameter | Description |
|---|---|
|
protocol |
Either |
|
hostname |
The machine where the Web server is running. |
|
(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 1.6.2, "Flexible Parameter Passing" for more information. |
|
(optional) |
The database schema name. If omitted, name resolution for package.proc_name occurs based on the database user that the URL request is processed as. |
|
(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. |
|
(optional) |
The parameters for the stored procedure. The string follows the format of the GET method. For example:
|
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.
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.
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).
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:
http://host:port/pls/DAD/foo?a=v&b=1
http://host:port/pls/DAD/foo, POST data="a=v&b=1"
http://host:port/pls/DAD/foo?a=v, POST data="b=1"
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:
Records are not supported.
mod_plsql supports:
Each parameter in a URL that invokes procedure or functions identified by a unique name. Overloaded parameters are supported. See Section 1.6.1, "Parameter Passing by Name (Overloaded Parameters)" for more information.
Procedures are prefixed by a ! character. See Section 1.6.2, "Flexible Parameter Passing" for more information.
See Section 1.6.3, "Large Parameter Passing" for more information.
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/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/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.
http://www.acme.com/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.
http://www.acme.com/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]);
http://www.acme.com/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')
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]);
http://www.acme.com/pls/mydad/!scott.my_pkg.my_proc?x=a&y=b&x=c
The exclamation mark prefix (!) instructs mod_plsql to use flexible parameter passing. It invokes procedure scott.my_pkg.myproc and passes it the following arguments:
num_entries ==> 3 name_array ==> ('x', 'y', 'x'); value_array ==> ('a', 'b', 'c') reserved ==> ()
The values passed as scalar arguments and the values passed as elements to the index-by table of varchar2 arguments can be up to 32K in size.
For example, when using flexible parameter passing (described in Section 1.6.2, "Flexible Parameter Passing"), each name or value in the query_string portion of the URL gets passed as an element of the name_array or value_array argument to the procedure being invoked. These names or values can be up to 32KB in size.
mod_plsql enables you to:
http://www.acme.com:9000/pls/mydad/docs/cs250/lecture1.htm
This is required to support uploading a set of files that have relative URL references to each other.
This section discusses the following:
You can specify the document storage table for each DAD. The document storage table must have the following definition:
CREATE TABLE [table_name] ( NAME VARCHAR2(256) UNIQUE NOT NULL, MIME_TYPE VARCHAR2(128), DOC_SIZE NUMBER, DAD_CHARSET VARCHAR2(128), LAST_UPDATED DATE, CONTENT_TYPE VARCHAR2(128), [content_column_name] [content_column_type] [ , [content_column_name] [content_column_type]] );
Users can choose the table_name. The content_column_type type must be either LONG RAW or BLOB.
The content_column_name depends on the corresponding content_column_type:
An example of legal document table definition is:
CREATE TABLE MYDOCTABLE ( NAME VARCHAR(128) UNIQUE NOT NULL, MIME_TYPE VARCHAR(128), DOC_SIZE NUMBER, DAD_CHARSET VARCHAR(128), LAST_UPDATED DATE, CONTENT_TYPE VARCHAR(128), CONTENT LONG RAW, BLOB_CONTENT BLOB ; );
The contents of the table are stored in a content column. There can be more than one content column in a document table. However, for each row in the document table, only one of the content columns is used. The other content columns are set to NULL.
The content_type column tracks in which content column the document is stored. When a document is uploaded, mod_plsql sets the value of this column to the type name.
For example, if a document was uploaded into the BLOB_CONTENT column, then the CONTENT_TYPE column for the document is set to the string 'BLOB'.
The LAST_UPDATED column reflects a document's creation or last modified time. When a document is uploaded, mod_plsql sets the LAST_UPDATED column for the document to the database server time.
If an application then modifies the contents or attributes of the document, it must also update the LAST_UPDATED time.
mod_plsql uses the LAST_UPDATED column to check and indicate to the HTTP client (browser) if the browser can use a previously cached version of the document. This reduces network traffic and improves server performance.
The DAD_CHARSET column keeps track of the character set setting at the time of the file upload. This column is reserved for future use.
For backward capability with the document model used by older releases of WebDB 2.x, mod_plsql also supports the following old definition of the document storage table where the CONTENT_TYPE, DAD_CHARSET and LAST_UPDATED columns are not present.
/* older style document table definition (DEPRECATED) */ CREATE TABLE [table_name] ( NAME VARCHAR2(128), MIME_TYPE VARCHAR2(128), DOC_SIZE NUMBER, CONTENT LONG RAW );
The following configuration parameters in the DAD affect a document upload/download operation:
If the configuration for these parameters in a DAD is as follows:
PlsqlDocumentTablename scott.my_document_table PlsqlUploadAsLongRaw html PlsqlDocumentPath docs PlsqlDocumentProcedure scott.my_doc_download_procedure
then:
.html will be uploaded to the document table as BLOBs. All files with .html extension will be uploaded as Long Raw.Typically, this procedure will call wpg_docload.download_file to initiate a file download for a file whose name is based on the URL specification.
A simple example with the preceding configuration is:
http://www.acme.com/pls/dad/docs/index.html
This results in downloading of the file index.html from the Long Raw column of the database table scott.my_document_table. Note that the application procedure has full control on the file download to initiate, and has the flexibility to define a more complex PlsqlDocumentProcedure that implements file-level access controls and versioning.
|
Note: The application defined procedure scott.my_doc_download_procedure has to be defined without arguments, and should rely on the CGI environment variables to process the request. |
The PlsqlDocumentTablename parameter specifies the table for storing documents when file uploads are performed through this DAD.
Syntax:
PlsqlDocumentTablename [document_table_name] PlsqlDocumentTablename my_documents
or,
PlsqlDocumentTablename scott.my_document_table
The PlsqlDocumentPath parameter specifies the path element to access a document. The PlsqlDocumentPath parameter follows the DAD name in the URL. For example, if the document access path is docs, then the URL would look similar to:
http://www.acme.com/pls/mydad/docs/myfile.htm
The mydad is the DAD name and myfile.htm is the file name.
Syntax:
PlsqlDocumentPath [document_access_path_name]
The PlsqlDocumentProcedure procedure is an application-specified procedure. It has no parameters and processes a URL request with the document access path. The document access procedure calls wpg_docload.download_file(filename) to download a file. It knows the filename based on the URL specification. For example, an application can use this to implement file-level access controls and versioning. An example of this is in Section 1.7.7, "File Download".
Syntax:
PlsqlDocumentProcedure [document_access_procedure_name]
PlsqlDocumentProcedure my_access_procedure
or,
PlsqlDocumentProcedure scott.my_pkg.my_access_procedure
The DAD parameter, PlsqlUploadAsLongRaw, configures file uploads based on their file extensions. The value of a PlsqlUploadAsLongRaw DAD parameter is a one-entry-for-each-line list of file extensions. Files with these extensions are uploaded by mod_plsql into the content column of LONG RAW type in the document table. Files with other extensions are uploaded into the BLOB content column.
The file extensions can be text literals (jpeg, gif, and so on) or an asterisk (*) matches any file whose extension has not been listed in the PlsqlUploadAsLongRaw setting.
Syntax:
PlsqlUploadAsLongRaw [file_extension] PlsqlUploadAsLongRaw *
[file_extension] is an extension for a file (with or without the '.' character, for example, 'txt' or '.txt') or the wildcard character *.
PlsqlUploadAsLongRaw html PlsqlUploadAsLongRaw txt PlsqlUploadAsLongRaw *
To send files from a client machine to a database, create an HTML page that contains:
multipart/form-data and whose action attribute is associated with a mod_plsql procedure call, referred to as the "action procedure."INPUT type="file" element enables a user to browse and select files from the file system.
When a user clicks Submit, the following events occur:
PlsqlDocumentTablename DAD setting.The following example shows an HTML form that lets a user select a file from the file system to upload. The form contains other fields to provide information about the file.
<html> <head> <title>test upload</title> </head> <body> <FORM enctype="multipart/form-data" action="pls/mydad/write_info" method="POST"> <p>Author's Name:<INPUT type="text" name="who"> <p>Description:<INPUT type="text" name="description"><br> <p>File to upload:<INPUT type="file" name="file"><br> <p><INPUT type="submit"> </FORM> </body> </html>
When a user clicks Submit on the form:
INPUT type="file" element.write_info procedure then runs.|
Note: The action procedure does not have to return anything to the user, but it is a good idea to let the user know whether the Submit succeeded or failed, as shown subsequently. |
procedure write_info ( who in varchar2, description in varchar2, file in varchar2) as begin insert into myTable values (who, description, file); htp.htmlopen; htp.headopen; htp.title('File Uploaded'); htp.headclose; htp.bodyopen; htp.header(1, 'Upload Status'); htp.print('Uploaded ' || file || ' successfully'); htp.bodyclose; htp.htmlclose; end;
The filename obtained from the browser is prefixed with a generated directory name to reduce the possibility of name conflicts. The "action procedure" specified in the form renames this name. So, for example, when /private/minutes.txt is uploaded, the name stored in the table by the mod_plsql is F9080/private/minutes.txt. The application can rename this in the called stored procedure. For example, the application can rename it to scott/minutes.txt.
In addition to renaming the uploaded file, the stored procedure can alter other file attributes. For example, the form in the example from Section 1.7.4, "File Upload" could display a field for allowing the user to input the uploaded document's Multipurpose Internet Mail Extension (MIME) type.
The MIME type can be received as a parameter in write_info. The document table would then store the mime type for the document instead of the default mime type that is parsed from the multipart form by mod_plsql when uploading the file.
To send multiple files in a single submit, the upload form must include multiple <INPUT type="file" name="file"> elements. If more than one file INPUT element defines name to be of the same name, then the action procedure must declare that parameter name to be of type owa.vc_arr. The names defined in the file INPUT elements could also be unique, in which case, the action procedure must declare each of them to be of varchar2. For example, if a form contained the following elements:
<INPUT type="file" name="textfiles"> <INPUT type="file" name="textfiles"> <INPUT type="file" name="binaryfile">
As a result, the action procedure must contain the following parameters:
procedure handle_text_and_binary_files(textfiles IN owa.vc_arr, binaryfile IN varchar2).
After you have sent files to the database, you can download them, delete them from the database, and read and write their attributes.
To download a file, create a stored procedure without parameters that calls wpg_docload.download_file (file_name) to initiate the download.
The HTML page presented to the user simply has a link to a URL, which includes the Document Access Path and specifies the file to be downloaded.
For example, if the DAD specifies that the Document Access Path is docs and the Document Access Procedure is mydad.process_download, then the mydad.process_download procedure is called when the user clicks on the URL:
http://www.acme.com:9000/pls/mydad/docs/myfile.htm
An example implementation of process_download is:
procedure process_download is v_filename varchar2(255); begin -- getfilepath() uses the SCRIPT_NAME and PATH_INFO cgi -- environment variables to construct the full pathname of -- the file URL, and then returns the part of the pathname -- following '/docs/' v_filename := getfilepath; select name into v_filename from plsql_gateway_doc where UPPER(name) = UPPER(v_filename); -- now we call docload.download_file to initiate -- the download. wpg_docload.download_file(v_filename); exception when others then v_filename := null; end process_download;
Any time you call wpg_docload.download_file(filename) from a procedure running in mod_plsql, a download of the file filenameis initiated. However, when a file download begins, no other HTML (produced through HTP interfaces) generated by the procedure, is passed back to the browser.
mod_plsql looks for the filename in the document table. There must be a unique row in the document table whose NAME column matches the filename. mod_plsql generates the HTTP response headers based on the information in the MIME_TYPE column of the document table. The content_type column's value determines which content columns the document's content comes from. The contents of the document are sent as the body of the HTTP response.
You can also download contents stored as Binary Large Object (BLOB) data type.
Example: The following procedure uses the name from the argument to select a BLOB from a table and initiates the Direct BLOB download:
procedure download_blob(varchar2 name) is myblob blob; begin
select blob_data into myblob from mytable where blob_name = name;
owa_util.mime_header('text/html', FALSE); htp.p('Content-Length: ' || dbms_lob.getlength(myblob)); owa_util.http_header_close;
wpg_docload.download_file(myblob); end;
The structure of the mytable table:
create table mytable ( blob_name varchar2(128), blob_data blob );
Path Aliasing enables applications using mod_plsql to provide direct reference to its objects using simple URLs. The Path Aliasing functionality is a generalization of how the document download functionality is provided. The following configuration parameters in the DAD are used for Path Aliasing:
For Example, if the configuration for these parameters in a DAD is as follows:
PlsqlPathAlias myalias PlsqlPathAliasProcedure scott.my_path_alias_procedure
then, all URLs that have the keyword myalias immediately following the DAD location will invoke the procedure scott.my_path_alias_procedure. Based on the URL specification, this procedure can initiate an appropriate response.
The OWA_UTIL package provides an API to get the values of CGI environment variables. The variables provide context to the procedure being executed through mod_plsql. Although mod_plsql is not operated through CGI, the PL/SQL application invoked from mod_plsql can access these CGI environment variables.
The list of CGI environment variables is as follows:
A PL/SQL application can get the value of a CGI environment variable using the owa_util.get_cgi_env interface.
Syntax:
owa_util.get_cgi_env(param_name in varchar2) return varchar2;
param_name is the name of the CGI environment variable. param_name is case-insensitive.
The PlsqlCGIEnvironmentList DAD parameter is a one-entry-for-each-line list of name and value pairs that can override any environment variables or add new ones. If the name is one of the original environment variables (as listed in Section 1.9, "Common Gateway Interface (CGI) Environment Variables"), that environment variable is overridden with the given value. If the name is not in the original list, a new environment variable is added into the list with that same name and value given in the parameter.
|
Note: Refer to the Oracle HTTP Server Administrator's Guide for information about the mod_plsql Configuration Files. |
If no value is specified for the parameter, then the value is obtained from the Oracle HTTP Server. With Oracle HTTP Server, you can pass the DOCUMENT_ROOT CGI Environment variable by specifying:
PlsqlCGIEnvironmentList DOCUMENT_ROOT
New environment variables passed in through this configuration parameter are available to the PL/SQL application through the owa_util.get_cgi_env interface.
PlsqlCGIEnvironmentList SERVER_NAME=myhost.mycompany.com PlsqlCGIEnvironmentList REMOTE_USER=testuser
This example overrides the SERVER_NAME and the REMOTE_USER CGI environment variables with the given values since they are part of the original list.
PlsqlCGIEnvironmentList MYENV_VAR=testing PlsqlCGIEnvironmentList SERVER_NAME= PlsqlCGIEnvironmentList REMOTE_USER=user2
This example overrides the SERVER_NAME and the REMOTE_USER variables. The SERVER_NAME variable is deleted since there is no value given to it. A new environment variable called MYENV_VAR is added since it is not part of the original list. It is assigned the value of "testing".
For mod_plsql, the National Language Support (NLS) variable (PlsqlNLSLanguage) can be set either as an environment variable or at the DAD level, the PlsqlNLSLanguage parameter of the database must match that of the Oracle HTTP Server, or the PlsqlNLSLanguage parameter of the database and Oracle HTTP Server, must be of fixed character width and both must be the same size.
If PlsqlNLSLanguage is not configured at the DAD level, the NLS setting is picked up from the environment. If it does not exist, the default rules apply for NLS_LANG settings for Oracle.
Every request to mod_plsql is associated with a DAD. The CGI environment variable REQUEST_CHARSET is set as follows:
The PL/SQL application can access this information by a function call of the form:
owa_util.get_cgi_env('REQUEST_CHARSET');
This is the IANA (Internet Assigned Number Authority) equivalent of the REQUEST_CHARSET CGI environment variable. IANA is an authority that globally coordinates the standards for charsets on the Internet.
The following restrictions exist in mod_plsql:
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|