600.315/415 Web Interface Information

Getting everything set up...

Before you can use the web interface with your own database, you'll need to get some things properly set up. Students intending to use the Oracle WWW interface for their projects should take the following steps:

1. Receive your new sql*plus password from John Shiles

For the Oracle web interface to work correctly, your password must be stored in in the OWA configuration file. To avoid privacy concerns, everyone will be emailed a new password chosen randomly by the computer.

Do hot alter this password (or your interface will no longer work).

3. Load the necessary PL/SQL packages into your tablespace

In order for the Web Server to work properly, several PL/SQL packages must be loaded into your database. These packages contain functions and procedures which the Web Server uses when processing queries.

To load the proper packages, enter the following command in sqlplus:

 start  /home/oracle/oracle/app/oracle/product/7.3.2/ows/admin/owains.sql ;

You will see some information about the packages being succesfully created, and then the sql prompt will return. The necessary packages have been loaded.

Once you follow these steps you should be ready to go. Further Oracle WWW documentation is available from the online Oracle Web Server manual (the URL for which is http://hops.cs.jhu.edu:8989/nwwsu.html).

Some examples

The following are examples of how you can create standard PL/SQL procedures in SQL PLUS which can be accessed through the web interface. Once your account is properly configured, the Oracle Web server can access PL/SQL functions and procedures via URLs.

example 1. Supose that Charles the TA has the following table defined in his tablespace:

   create table WEATHER (
   City       varchar2(11),
   Temperature number,
   Humidity    number,
   Condition  varchar2(9)
   );

and suppose that the following insertions have been made into this table:

   insert into WEATHER values ('LIMA',45,79,'RAIN');
   insert into WEATHER values ('PARIS',81,62,'CLOUDY');
   insert into WEATHER values ('ROME',91,85,'SUNNY');

If a procedure called showweather is defined in his tablespace using the following PL/SQL code:

   create or replace procedure showweather is
     ignore boolean;
    begin
     ignore := owa_util.tablePrint('weather', 'BORDER', OWA_UTIL.HTML_TABLE);
    end;
    /

Then the URL http://hops.cs.jhu.edu:8990/cschafer/owa/showweather will run the procedure showweather, which should display the following table in your web browser (try it!):

CITY	TEMPERATURE	HUMIDITY	CONDITION
LIMA	45	79	RAIN
PARIS	81	62	CLOUDY
ROME	91	85	SUNNY

Notice the string "cschafer" in the URL http://hops.cs.jhu.edu:8990/cschafer/owa/showweather; this is how the Oracle web agent knows to process the query in Charles' tablespace. Once you have everything properly configured, and have defined the table and procedure given above in your tablespace, you should be able to run the showweather procedure in your tablespace by accessing the URL http://hops.cs.jhu.edu:8990/<your username>/owa/showweather; i.e., by replacing "cschafer" with your username in the URL.

example 2. Suppose we have defined another procedure called showweather2 by running the following PL/SQL code:

   create or replace procedure showweather2 is
     ignore boolean;
    begin
     ignore := owa_util.tablePrint
	   ('weather', 'BORDER', OWA_UTIL.HTML_TABLE,
	    'city, temperature, humidity',
	    'where humidity>80');

   end;
   /

This procedure, which can be called in cschafer's tablespace by accessing the URL http://hops.cs.jhu.edu:8990/cschafer/owa/showweather2, prints out the table weather using the following SQL statement:

   select city, temperature, humidity
   from weather
   where humidity>80;

How, you may ask? Well, note the function call in showweather2:

   owa_util.tablePrint ('weather', 'BORDER', OWA_UTIL.HTML_TABLE,
	                'city, temperature, humidity',
	                'where humidity>80');

In PL/SQL, component functions and procedures of a given package are called by giving the package name, followed by a dot ("."), followed by the function or procedure name. Thus, we know that the above function call is a call to the function tablePrint, which is a member of the owa_util (remember those packages you loaded in step 3 of "getting started", above?, Well, owa_util is one of them). The values for the select statement are passed as arguments to owa_util.tablePrint; the correct select statement is generated by owa_util.tablePrint, using these arguments.

Would you like to see some more examples, which demonstrate how to pass values to PL/SQL procedures via the Web Interface?