1 Understanding mod_plsql

E xample 1-3 Invoking the Default Procedure Stored in the DAD Configuration

http
://www.acme.com:9000/pls/mydad 

The Web server running on w ww.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 configurati on 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 ex ample, the default home page for the mydad DAD (as specified in the DAD Configuration) is displayed.

POST, GET and HEAD Methods

The POST, GET and HEAD methods in the HTTP protocol instruct browsers on how to pass parameter data (usually in the form o f name-value pairs) to applications. The parameter data is generated by HTML forms.

mod_pls ql 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. G enerally, 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 l ength 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 only di fference is that only the HTTP status line and the HTTP headers are passed back. No content data is streamed back to the browser. Thi s 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' a nd '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"

1.4 Transaction Mode< /h2>

After processing a URL request for a procedure invocation, mod_plsql perfor ms a rollback if there were any errors. Otherwise, it performs a commit. This mechanism does not allow a transaction to span across m ultiple HTTP requests. In this stateless model, applications typically maintain state using HTTP cookies or database tables.

1.5 Supported Dat a Types

Because HTTP supports character streams only, mod_plsql supp orts the following subset of PL/SQL data types:

• NUMBER
VARCHAR2
• TABLE OF NUMBER
• TABLE OF VARCHAR2

Records are not s upported.

1.6 Parameter Passing

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 1.6.1, "Parameter Passing by Name (Overloaded Parameters)" for more information.

• Flexible parameter passing

  Procedures are prefixed by a ! character. See Section 1.6.2, "Flexible Parameter Passing" for more information.

• Large (up to 32K) parameters passing

  See Section 1.6.3, "Large Parameter Passing" for more information.

  Note: mod_pl sql 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 t able, 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. If the order of the values in the PL/SQL table is significant in your procedure, you need to determine the order in which the variables appear in the query string, or modify your PL/SQL application to do the ordering internally. If you do not have variables with mu ltiple 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 environ ment must have a base type of VARCHAR2. Oracle can convert VARCHAR2 to other data types such as NUMBE R, DATE, or LONG. The maximum length of a VARCHAR2 variable is 32K. < /a> If you cannot guarantee that at least one value will be submitted to the PL/SQL table (for example, the user ca n select no options), use a hidden form element to provide the first value. Not providing a value for the PL/SQL table produces an er ror, and you cannot provide a default value for a PL/SQL table.

1.6.1 Parameter Passing by Name (Overlo aded 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 ove rloaded 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 par ameters. 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 f or 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;

-- lega
l PL/SQL and also works for mod_plsql
CREATE PACKAGE my_pkg AS
  PROCEDURE my_proc (valvc
2 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 o ne 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 t
ype
END my_pkg;

Each of these procedures ha s 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.

Example 1-4 Send the following URL to execute the scalar version of t he procedure:

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

When mod_plsql gets a request with more than one value for the val pa rameter, it then invokes the procedure with the array data type.

Example 1-5 Send the following URL to execute the array version of the procedure:

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.

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 t hat the names in the query string are consistent each time, regardless of what elements the user chooses. mod_plsql makes this operat ion 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 exc lamation 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.

1.6.2.1 Two Parameter Interface

procedure [proc_name] 
     (name_array IN [array_type],
     valu
e_array IN  [array_type]);

Table 1-2 Two Parameter Interface Parameters

Example 1-6 If you send the following URL:

http://www.acme.com/pls/mydad/!sc
ott.my_proc?x=john&y=10&z=doe

The exclamation mark prefi x (!) 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')

1.6.2.2 Four Parameter Interface

The four parameter interface is supported for compatibility.

procedure [proc_name] 
     (num_entires IN NUMBER,
     nam
e_array  IN  [array_type],
     value_array IN  [array_type],
     reserved in [array_typ
e]);

Table 1-3 Four Parameter Interface Parameters

Example 1-7 If you send the following URL, wher e the query_string has duplicate occurrences of the name "x":

http://www.acme.
com/pls/mydad/!scott.my_pkg.my_proc?x=a&y=b&x=c

The excl amation 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_arr
ay ==> ('x', 'y', 'x');
value_array ==> ('a', 'b', 'c')
reserved ==> ()

CREATE TABLE [table_name] (
      
     NAME           VARCHAR2(256) UNIQUE NOT NULL,
     MIME_TYPE      VARCHAR2(12
8),
     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]]
);

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

/* older style document table definition
 (DEPRECATED) */
CREATE TABLE [table_name]
( 
    NAME         VARC
HAR2(128),
    MIME_TYPE    VARCHAR2(128),
    DOC_SIZE     NUMBER,
    CONTENT      LONG RAW

);

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

http:
//www.acme.com/pls/dad/docs/index.html

PlsqlDocumentTablename  [document_table_name]
<
/a>
PlsqlDocumentTablename  my_documents

<
a name="1005949">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

PlsqlUp
loadAsLongRaw  [file_extension]
PlsqlUploadAsLongRaw *

PlsqlUploadAsLongRaw  html 
PlsqlUploadAsLongRaw  txt
PlsqlUploadAsLongRaw  *

<html>
   <head>

   <title>test upload</title>
   </head>
   <body>
   <FORM       enctype="multipart/form-data"
      action="pls/mydad/write_info"
<
/a>      method="POST">
      <p>Author's Name:<INPUT type="text" name="who">

      <p>Description:<INPUT type="text" name="description"><br>
      <p>File to up
load:<INPUT type="file" name="file"><br>
      <p><INPUT type="submit">
   </FORM>
   </body>
</html>

<INPUT type="file" name="textfiles">
<INPUT type="file" name="textfiles">
<INPUT type="file" name="binaryfile">

procedure handle_text_and_binary_files(textfiles IN owa.vc_arr, binaryfile 
IN varchar2).

http://ww
w.acme.com:9000/pls/mydad/docs/myfile.htm

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 t
he 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;

PlsqlPathAlias   myalias 
PlsqlPathAliasProcedure   scott.my_path_alias_procedure 

owa_util.get_cgi_env(param_name in varchar2) return varchar2;

PlsqlCGIEnvironmentList DOCUMENT_ROOT

PlsqlCGIEnvironmentList SERVER_NAME=myhost.mycompany.com
Plsq
lCGIEnvironmentList REMOTE_USER=testuser

PlsqlCGIEnvironmentList MYENV_VAR=testing
PlsqlCGIEnvironmentList SERVER_NAME=
PlsqlCGIEnvironmentList REMOTE_USER=user2

owa_util.get_cgi_env('REQUEST_CHARSET');

---

Previous Next

Copyright © 1996, 2003 Oracle Corporation All Rights Reserved.

Home< /font> Book List Contents Ind ex Master Index Feedb ack