600.315/415 Web Interface Information (contd.)

Passing values to PL/SQL procedures via URL strings

To allow for truly interactive use of the Web interface (WI), Oracle provides a simple method for passing parameters to PL/SQL procedures via the WI. This method again uses URL strings to process queries; in fact, it is a generalization of the method described in the previous examples.

Consider the following PL/SQL procedure, which takes one argument:

create or replace procedure cityweather (querycity in varchar2) is
   temp integer;
begin
   select temperature into temp
   from weather
   where city=querycity;

   htp.print('The temperature in '||querycity||' is '||temp||' degrees.');
end;
/

This procedure selects the temperature in the city specified by the input to the procedure, and outputs the result in appropriate HTML text using the htp.print procedure.

To call this procedure in Charles Schafer's tablespace with the argument "LIMA", you just need to open the following URL by clicking on it:

http: //hops.cs.jhu.edu:8990/cschafer/owa/cityweather?querycity=LIMA.

Note how the username cschafer is specified in the string, and how the particular procedure name cityweather is given at the end of the string. Most importantly, note how the querycity variable is explicitly instantiated with LIMA, by the assignment querycity=LIMA in the URL string, seperated from the procedure name with the ? symbol.

Passing values to procedures with HTML forms

You can get much fancier than this, since the WI also supports the use of HTML forms. Supposing that we want to allow the WI user to specify the argument for cityweather, the following form is easily implemented:

Please enter a city name (all caps):
To print the temperature for this city, press this button: .

Here is the HTML code for this form:

<UL>
<LI>
<FORM METHOD="POST"
      ACTION="http://hops.cs.jhu.edu:8990/cschafer/owa/cityweather">
      Please enter a city name (all caps): 
      <INPUT TYPE="text" NAME="querycity">
 <P>
 To print the temperature for this city, press this button: 
 <INPUT TYPE="submit" VALUE="Submit Query">. <P>
</FORM>
</UL>

Note that the METHOD of the form is POST, the ACTION is the URL for the cityweather procedure in cschafer's tablespace, and that the NAME of the text INPUT tag is querycity. Whenever text <text> is entered into the above form and submitted, the browser will parse the form into the URL string

http://hops.cs.jhu.edu:8990/cschafer/owa/cityweather?querycity=<text>

The browser will then connect to this URL.

HTML forms and procedures with more than one parameter

In general, values are passed to PL/SQL procedures via HTML forms by giving the text input fields the same names as the parameter names in the definition of the procedure. Order doesn't matter; you just need to name parameters consistently. Of course, you always need to supply all necessary parameters for a procedure.

For example, consider the following procedure, which takes 4 arguments:

create or replace procedure inputweather (vcity in varchar2,
					  vcond in varchar2,
					  vtemp in integer,
					  vhum in integer) is
  ignore boolean;
begin
  insert into weather values (vcity, vtemp, vhum, vcond);
  ignore := owa_util.tablePrint('weather', 'BORDER', OWA_UTIL.HTML_TABLE);
end;
/

The following form provides an interface to this procedure, allowing the user to specify what information is to be entered into the database:

New City Name:
New Conditions:
New Temperature:
New Humidity:
To insert these values into the weather table, press this button:

The HTML code for this form is as follows:

<UL>
<LI>
<FORM METHOD="POST"
      ACTION="http://hops.cs.jhu.edu:8990/cschafer/owa/inputweather">
      New City Name:
      <INPUT TYPE="text" NAME="vcity">
  <P> New Conditions:
      <INPUT TYPE="text" NAME="vcond" SIZE=8>
  <P> New Temperature:
      <INPUT TYPE="text" NAME="vtemp" SIZE=3>
  <P> New Humidity:
      <INPUT TYPE="text" NAME="vhum" SIZE=3>
 <P>
 To insert these values into the weather table, press this button: 
 <INPUT TYPE="submit" VALUE="Insert Values"> <P>
</FORM>
</UL>

Note that there is a named input field corresponding to each parameter of the procedure inputweather.