1 Understanding mod_plsql

1.6.1 Parameter Passing by Name (Overloaded Parameters)

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

Overloading and PL/SQL Arrays

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

http://www.acme.com/pls/mydad/my_proc?val=john&val=sally

1.6.2 Flexible Parameter Passing

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

name_array ==> ('x', 'y', 'z')
value_array ==> ('john', '10', 'doe')

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

num_entries ==> 3 
name_array ==> ('x', 'y', 'x');
value_array ==> ('a', 'b', 'c')
reserved ==> ()

1.6.3 Large Parameter Passing

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.

1.7.1 Document Table Definition

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:

• If the content_column_type is LONG RAW, the content_column_name must be CONTENT.

• If the content_column_type is BLOB, the content_column_name must be BLOB_CONTENT.

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 ;
);

1.7.2 Old Style Document Table Definition

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

);

1.7.3 Configuration Parameters for Document Upload/Downloading

The following configuration parameters in the DAD affect a document upload/download operation:

• "PlsqlDocumentTablename"

• "PlsqlDocumentPath (Document Access Path)"

• "PlsqlDocumentProcedure (Document Access Procedure)"

• "PlsqlUploadAsLongRaw"

PlsqlDocumentTablename   scott.my_document_table
PlsqlUploadAsLongRaw   html
PlsqlDocumentPath   docs
PlsqlDocumentProcedure   scott.my_doc_download_procedure

• mod_plsql will retrieve data from, or store to a database table called my_document_table in the scott schema.

• All file extensions except .html will be uploaded to the document table as BLOBs. All files with .html extension will be uploaded as Long Raw.

• All URLs which have the keyword docs immediately following the DAD location will result in invocation of the procedure scott.my_doc_download_procedure.

  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.

PlsqlDocumentTablename  [document_table_name]

PlsqlDocumentTablename  my_documents

PlsqlDocumentTablename  scott.my_document_table

http://www.acme.com/pls/mydad/docs/myfile.htm 

PlsqlDocumentPath  [document_access_path_name]

PlsqlDocumentProcedure  [document_access_procedure_name]

PlsqlDocumentProcedure  my_access_procedure

PlsqlDocumentProcedure  scott.my_pkg.my_access_procedure

PlsqlUploadAsLongRaw  [file_extension]
PlsqlUploadAsLongRaw *

PlsqlUploadAsLongRaw  html 
PlsqlUploadAsLongRaw  txt
PlsqlUploadAsLongRaw  *

1.7.4 File Upload

To send files from a client machine to a database, create an HTML page that contains:

• A FORM tag whose enctype attribute is set to multipart/form-data and whose action attribute is associated with a mod_plsql procedure call, referred to as the "action procedure."

• An INPUT element whose type and name attributes are set to file. The 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:

1. The browser uploads the file specified by the user as well as other form data to the server.

2. mod_plsql stores the file contents in the database in the document storage table. The table name is derived from the PlsqlDocumentTablename DAD setting.

3. The action procedure specified in the action attribute of the FORM is run (similar to invoking a mod_plsql procedure without file upload).

   Note:

   The parsing of HTML documents is deprecated in mod_plsql. mod_plsql used to parse the content of an HTML file when it was uploaded, and identified other files that the HTML document was referring to. This information was then stored into a table. The table name was constructed by appending the name of the document table with "part". This functionality was found to be not of use to customers and has been deprecated, starting in version 9.0.4 of mod_plsql.

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:

1. The browser uploads the file listed in the INPUT type="file" element.

2. The write_info procedure then runs.

3. The procedure writes information from the form fields to a table in the database and returns a page to the user.

   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.

1.7.5 Specifying Attributes (Mime Types) of Uploaded Files

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.

1.7.6 Uploading Multiple Files

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).

1.7.7 File Download

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 path name of
  -- the file URL, and then returns the part of the path name
  -- 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 filename is 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.

1.7.8 Direct BLOB Download

You can also download contents stored as Binary Large Object (BLOB) data type.

1. Create a stored procedure that calls wpg_docload.download_file(blob) where blob is of data type BLOB. Since mod_plsql has no information about the contents in the BLOB, you must supply them.

2. Setup the Content-Type and other headers.

   Example: The following procedure uses the name from the argument to select a BLOB from a table and initiates the Direct BLOB download:

   create or replace procedure download_blob(varchar2 name) is 
   myblob blob; 
   begin 
   
   

   1. Select the BLOB out of mytable using the name argument

      select blob_data into myblob from mytable where blob_name = name; 
      
      

   2. Setup headers which describes the content

      owa_util.mime_header('text/html', FALSE); 
      htp.p('Content-Length: ' || dbms_lob.getlength(myblob)); 
      owa_util.http_header_close; 
      
      

   3. Initiate Direct BLOB download

      wpg_docload.download_file(myblob); 
      end; 
      
      

      The structure of the mytable table:

      create table mytable 
      ( 
      blob_name varchar2(128), 
      blob_data blob 
      ); 
      

3. The HTML page presented to the user has a link to a URL that calls this stored procedure with the correct argument(s).

4. When a Direct BLOB download is initiated, no other HTML (produced through the HTP interface) generated by the procedure is passed back to the browser.

1.9.1 Adding and Overriding CGI Environment Variables

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.

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
PlsqlCGIEnvironmentListREMOTE_USER=testuser

PlsqlCGIEnvironmentList MYENV_VAR=testing
PlsqlCGIEnvironmentList SERVER_NAME=
PlsqlCGIEnvironmentListREMOTE_USER=user2

1.9.2 PlsqlNLSLanguage

For mod_plsql, the Globalization Support 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 Globalization Support setting is picked up from the environment. If it does not exist, the default rules apply for NLS_LANG settings for Oracle.

owa_util.get_cgi_env('REQUEST_CHARSET');