Implementation of WWW Applications using the Oracle Web Server on the Database application project Course in University of Helsinki

Harri Laine, 21.1.2000

Manuals and Guidebooks

• Oracle
• The Oracle database manuals (e.g. SQL and PL/SQL)
• PL/SQL Web toolkit Reference
• The Oracle Web Server FAQ
• HTML
• Jukka Korpela: Getting Started with HTML
• Jukka Korpela: Learning HTML 3.2 by examples
• The HTML 4.0 standard
• JavaScript
• Stefan Koch: Voodoo's Introduction to JavaScript
• Netscape's JavaScript Manual

Implementation Environment

Processing the Database with SQL

• Initialization (in server kontti):
• The environment needed for using the database is established by the commands

 
    setup o8
    setenv ORACLE_SID tktb

• Command setenv INFOTYO /home/group/infolab/tyo,
  sets the INFOTYO variable to indicate the directory that contains some utility programs needed for the exercise.

The commands above can be placed in the .login file so they do not have to be typed separately each time you log in the computer kontti. The variable INFOTYO will be needed to run some command files to be presented below.

• After initialization, interactive SQL is started with the command sqlplus /

  If entering this command gives you the message that the command sqlplus cannot be found, you have not completed the setup function above. Slash after sqlplus indicates that you want to login the database using an OPS$.xxxxx -style user account.

• You can run an SQL-script, say skripti.sql, with the command sqlplus / @skripti. If the script ends in the command exit, the control returns to the Unix command interpreter. Otherwise the control will remain in the SQL command interpreter (prompt 'SQL>').

Database Procedures

When using the Oracle Web Server, a database procedure is used to connect the database to a WWW application. A database procedure is a compiled database processing program saved in the database. Procedures may have parameters. Procedures are witten using PL/SQL programming language (an Ada based language). PL/SQL material is available in HTML format. In WWW applications, the database procedures are used for generating WWW pages or for processing the user input given in HTML-forms.

One way to implement the procedures is to put the source code for each procedure in separate file and by to compile each procedure separately. In this case, the structure of the source file is as follows:

     create or replace procedure procedure_name ( parameters  ) is 
        local data structures
     begin 
        procedure body
     end; 
     /
     show errors
     EXIT

If the name of the source file was pros1.pl, it would be run and the procedure compiled with the command

     sqlplus / @pros1.pl 

The correctness of the procedure may be tested in sqlplus-environment as follows:

SQL> set serveroutput on	database server should produce output
SQL> spool some.file	direct output to file some.file
SQL> execute procedure(parameters)	run the procedure
SQL> execute owa_util.showpage	produce the output

The result may be seen in the file (some.file above). It will also show on the display unless it is separately denied.

You may have the final HTML page checked at a Web page validation service. See, e.g., Heikki Kantola's list of validation services.

Connecting the Database to WWW pages

Referencing to tables and procedures

You should always refer to a database table so that the Oracle user account name of the owner of the table is included in the reference, e.g

     select column1 into variable 
     from ops$smith.test 
     where key=123
 

The owner's Oracle username must also be included in procedure calls, e.g.

     ops$smith.procedure1

Inclusion of the username is needed because the procedures are not run using your user account but a dedicated account www_user.



WWW_User execution priveleges

The execution environment for this exercise is buid up so that to run a procedure through WWW you must grant the execution privilege for the procedure to the username www_user. The simplest way to grant the privelege is to place a grant-statement at the end of the source file:

     create or replace procedure procedure_name ( parameters  ) is 
        plocal data structures
     begin 
        procedure body
     end; 
     / 
     grant execute on procedure_name to www_user;
     show errors
     EXIT

Though the execution privileges given for the database procedures are enough to execute the program, you must also grant at least the reading privilege to all your tables to the username www_user. This privilege is needed by the tools used by the the instructors of the exercise course. The read privilege is granted with the sql command

    grant select on table_name to www_user; 

The following Unix command files may also be used for access management

$INFOTYO/sallikaikki	Grant www_user all priveleges to all owner's tables and grant execution privelege to all owner's procedures
$INFOTYO/sallitaulu tablename	Grants www-user all operations on table tablename
$INFOTYO/salliluku tablename	Grants www-user read privelege on table tablename.
$INFOTYO/sallisuoritus procedurename	Grants www-user execution privelege on procedure procedurename
$INFOTYO/estakaikki	Revokes all priveleges you have granted to WWW_USER.
$INFOTYO/estataulu tablename	Revokes all priveleges you have granted to WWW_USER on table tablename

Note that the procedure's execution privilege must be renewed after each compilation. If you used the script sallikaikki to give access to all your database components, you can see in your working directory's file gluvat.sql what privileges you have given.

Procedure calls in a WWW Page

URL for a database procedure

In our environment a database procedure is referred in a Web page using an URL of the following format:

   http://kontti.helsinki.fi:8011/tktb/plsql/username.prosedure_name

The beginning of the URL above (http://kontti.helsinki.fi:8011/tktb/plsql/) identifies the database connection to be used. This part is the same for all links. The final part identifies the database procedure to be activated, e.g. ops$smith.prossu1. References in this form may be used in any WWW page.

When the procedure that generates the page was activated by the same connection you want to use in activating new procedures, you may omit the connection part of the URL and use only the local procedure name username.procedure_name.

• Parameters in URL

  If the procedure to be called has parameters, they may be transmitted to the procedure by adding a parameter list to the URL. The parameter list takes the form:

       ?pName1=value1<&pName2=value2 ...
  

  The parameter name (e.g. pName1 above) must exist in the signature of the procedure to be called. Its value must be compatible with the data type given in the procedure's signature. The order of the parameters does not have to be the same as in the signature. All the parameters that have not been assigned a default value in the signature must be provided a value when calling.

• Parameters on an HTML form

The parameters can also be given on an HTML form, in which case the reference to the procedure to be activated is given as an ACTION-attribute in the FORM tag. The METHOD-attribute in the FORM tag describes the delivery method for parameters: the GET method delivers the parameters as the value of the environment variable QUERY_STRING (in this case, the parameters will show in the browser's address field). The POST method delivers the parameters in the standard input stream. As regards the procedure, it does not matter which delivery method is used. However, the size of the environment variable is limited, so if there are many parameters with long values, the POST method is recommended.

Parameter values are entered using the fields of the forms. A field is specified with INPUT, TEXTAREA or SELECT clauses in HTML. The NAME attribute contained in the clause will connect the field to the corresponding parameter of the procedure. The value of the NAME-attribute must be the name of the parameter. There are various input fields in HTML. The TYPE attribute in the INPUT clause defines the type of the input field. Such types are:

• checkbox (for setting options)
• image (atcs as a submit button)
• hidden (an invisible field)
• password (a text field that does not show what is written)
• radio (excluding option button)
• submit (button that causes to form to be submitted and the procedure to be activated)

The value delivered as the value of a parameter is either the value entered in the text field or if there is no textfield the value of the VALUE-attribute of the field definition. There are also other attributes in the INPUT tag. They control e.g. the size of the field. You should study them from some HTML guide e.g. Heikki Korpela's Leaning HTML by example -guide.

Example:

<FORM ACTION="http://kontti.helsinki.fi:8011/tktb/plsql/info1.expl1" METHOD="POST"> 
<INPUT TYPE="text" NAME="oname" SIZE=40 MAXLENGTH=40 VALUE=""> 
<INPUT TYPE="submit" VALUE="HAE"> 
</FORM>

This will produce a one-field form. The value written into the field 'oname' is delivered as the value of parameter 'oname' to the procedure expl1 of owner info1. The structure of procedure esim1 is:

     create or replace procedure expl1 (in oname varchar2) is
     begin
        do_the_job;
     end;

You may also deliver arrays of values as parameters to a procedure. See the tips page for more information on this.

Generating a Web Page in a Database Procedure

The purpose of a database procedure is often to generate a new WWW page containing data from the database, such as the answer to a query. To produce this kind of pages, various library routines are available. They are gathered into packages. The most central package is htp, which contains procedures needed to produce HTML elements for the object page. The htp package contains a procedure for each HTML clause. The package is described in a htp guide (in Adobe Acrobat pdf-format) and there is a 20-page summary in the same format (Adobe Acrobat pdf). There is also a htp-guide in HTML. See the tips page for more information on producing HTML in the PL/SQL language.

Library Package IHT

Harri Laine has programmed the library package IHT based on Juhani Kuittinen's procedure collection. IHT contains various useful procedures, especially for processing forms. You will save yourself a great deal of writing by using these procedures. In addition to the table below, the transparency material contains more information on the procedures in this package. From spring 1999, you can use the new IHT2 package instead of the IHT. The IHT2 is more clearr and offers more varied tools, such as the generation of JavaScript checks. The package source code will give you the procedure specifications.

The procedures are the following:

back(n int := -1);

Attaches Javascript links to the document for transference to the previous and the following document in history. The corresponding buttons should be included with the header procedure, so this should not be needed.

footer (cdate varchar2, csignature varchar2);

The date and csignature final text in small letters at the bottom of the page

formFieldsOpen (curl varchar2, cmethod varchar2);

Starts the form and attaches a processor (curl) and parameter relay method (get, post - the latter is recommended)

formfieldsClose;

Quits the field part of the form. The buttons should be arranged into separate button lines after this. The form should be ended with the command formEnd.

formField(cprompt varchar2, cname varchar2, csize integer, cmaxlength integer, cvalue varchar2);

Specifies the form field. cprompt is a prompt that appears at the left side, cname the name of the field and cvalue the source value.

formStandardButtons2(csubmit varchar2, creset varchar2);

Specifies the two buttons submit and reset, and their texts

formButtonRowOpen

Starts the button row

formButtonRowItem(ctype varchar2, cname varchar2, cvalue varchar2);

Sets a button in the button row; ctype is the type of button (submit/reset), cname the name of the button (if a submit button is given a name, the button will send a parameter to the procedure called. This can be avoided by giving the parameter the value NULL), cvalue is the text of the button and possibly a parameter value.

formButtonRowClose

Closes the button row

formEnd

Ends the form

formFieldText (cprompt varchar2, cname varchar2, cvalue varchar2, nrows int := 3, ncols int := 40);

Like formField, this specifies an input field with several rows formStandardButton24; Specifies four buttons; one reset (source value) and three

submit (name=optype, value=Update, New record, Remove).

An optype parameter answering to the submit button is included in the call.

header(ctitle varchar2, cinst_url varchar2, cbgr varchar2);

Specifies the title of the form, ctitle is the title text. cinst_url is a link to the instructions text, and cbgr a link to the background image. Attaches image links to the previous and the following document in history.

shortMessage(Cmsg varchar2);

Message in bold letters

Package IHT2

IHT2 is an improved and extended version of the IHT package. A new feature is that JavaScript tools are available for use in the HTML page. The version 0.1 of the package was completed on February 14 1999. The package offers a numeric field, a value restricted numeric field and a date field the values of which are checked in the browser with JavaScript. The values of these fields are checked when leaving the field (onBlur). This means that you cannot leave the field if the value is wrong. In order not to jam the system, an empty field must be accepted for these fields. To check the mandatory (non empty) fields, a check on submission should be attached to the form (onSubmit). Since each field is checked separately, it is enough to check that the field has some value at the point of submission (this is based on the assumption that the original values are valid). There is a function to generate a test for a mandatory value, and to gather these tests into a checking package.

IHT2 also offers also some form field types without checks. The fields can be placed at will on the form. There are functions in the package (e.g. form_field, add_any_js) that you may be used to implement your own extensions. The package source code is appended, on one hand to describe the package functions in detail, on the other as a basis and model for your own extensions.

As an example of the use of the package, you can take a look at the test program testi8, which places the title and various fields on a form. The form is processed with program t8r that uses the package to print the parameters. You may also try the test application.



Examples:

1. Form Ophaku.html to look up for students according to name

Use your browser's 'View source code' funtion to see the source code of the form. The database procedure opiskelija_data processes the query form. It produces a results page with the data on students matching the query. You might try the query vith value 'Ville%', for example.

2. The query form ophaku2.html is otherwise the same as in Example 1, but the activated procedure opiskelija_sel works differently. It produces the names of students as links. Clicking on the link will activate the procedure opiskelija_data.
3. Here is an example of the use of the IHT library package. The questionnaire ophaku3.html is otherwise simiral to Example 2 but the procedure opiskelija_sel3, attaches a different procedure to be activated when student name links are clicked. Now, the procedure opiskelija_crf processes the choices from the list. This procedure creates a maintenance form for the maintenance of the student data. At the end of the form, there is a row of buttons with one reset button and three submit buttons. The procedure opiskelija_upd processes the pressing of a submit button (update, new record, undo). This procedure investigates which of the submit buttons that has been pressed and, based on this investigation, carries out the function. Undo does not work, a change is possible. The procedure shows the row to be processed with the help of the sub-procedure opiskelija_nayta. For additions, a new form is created with the procedure opiskelija_uusi. The filled-in form is processed with the procedure opiskelija_ins, which transfers the data to the database.

The PL/SQL Procedures

This is a brief summary of the features of the PL/SQL language, focussing on the specification of procedures. See manuals for more information.

A procedure is divided into five parts

• name of the procedure
• parameter declaration
• declaration of local data structures
• algorithm
• exceptions

  
       create or replace procedure procedure_name
          ( parameters ) is
          local data structures
       begin
          algorithm
       exception
          exceptions
       end;
  
  

  Parameters, local data structures and exceptions might not be present.

Parameters

Parameters of a PL/SQL procedure may be input, output, or both input and output parameters. When declaring parameters you may specify

• the usage mode (in / out / in out)
• name,
• data type and
• default value. As data types, the SQL data types and the PL/SQL data types come into question.

      create or replace procedure testi (text1 in char,
         counter1 in integer) is ....
  

Local data

Simple variables, records, tables and cursors can be specified as local data structures. When specifying a simple variable, its name and data type must be given. The available data types are the ones used in SQL: integer, number, char, varchar and date. They are specified as they are in SQL. In addition, the Boolean data type is available. Data type of a variable may also be specified with the help of another variable or a column in database table. The expression

      s1copy ops$smith.tableA.column1%TYPE

desclares the variable s1copy as being the same type as the column column1 in the table tableA, owned by the user ops$smith.

A record declaration can be based on a table or a query.

      row1 ops$smith.TableA%ROWTYPE; 
      cursor c1 is 
        select * 
        from ops$smith.TableB; 
      row2 c1%ROWTYPE;

Queries are specified with cursors, as in an embedded SQL. PL/SQL provides a cursor loop to go through the rows retrieved with the cursor, simplifying the process compared to the embedded SQL.

Algorithm

The algoritmic part of PL/SQL program uses the operations and structures commonly found in all programming languages:

• assign operation (counter := 19 )
• expressions
• standard arithmetic operations (v1 :=v2 * 2 + v3 )
• concatenation of strings (FullName:= FirstName || ' ' || LastName))
• procedures and functions
  All functions of the Oracle SQL may be used as PL/SQL functions

      Initial := substr(LastName,1,1);
      DayOfWeek:= to_char(sysdate,’DAY’) 
  

  ;
• conditional structure as if-expressions

      if condition then
         action;
      end if;
  
      if condition then
         action1;
      else
         action2;
         action3;
      end if;
  
      if condition1 then
         action1;
      elsif condition2 then
         action2;
         action3;
      else
         action4;
      end if;
  

  Conditional structures can be nested as in other programming languages.

      if condition then
         if condition2 then
            action1;
         elsif condition3 then
            action2;
            action3;
         end if;
      else
         action4;
      end if;
  

• Loops
• while loop:
  executed if the condition is true

     while condition loop
         action;
     end loop;
  

• for loop;
  executed on all the values of the value sequence

  
     for variable in lower_bound .. upper_bound loop
       action;
     end loop;
  

• cursor loop;
  executed for each resulting row of the query

  
      for row_variable in cursor loop
         action;
      end loop;
  

  In the cursor loop, a query is first executed, then the rows are fetched and processed, and finally the cursor is closed. The row variable is set to point a fetched row. The variable is declared in the loop statement. You should not declare the same variable separately. References to the columns of the current row are done as row_variable.column_name

      cursor c1 is 
         select somecolumn from sometable;
  
      for r1 in c1 loop
         a:= r1.somecolumn;
         ...
      end loop;
  

  The rows resulting from a query can also be examined in a while loop. In this case, the cursor must first be opened with the statement Open, then the values of the currenct row are fetched into program variables with a Fetch statement as in an embedded SQL.

Exceptions

Various error situations may in PL/SQL be processed as exceptions, in which case the actual program logic might remain somewhat clearer. The table below enumerates some pre-defined exceptions fpr which you can define a handler.

CURSOR_ALREADY_OPEN	is raised if you try to OPEN an already open cursor
DUP_VAL_ON_INDEX	is raised if you try to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR	is raised if you try an illegal cursor operation. For example, if you try to CLOSE an unopened cursor.
INVALID_NUMBER	is raised in a SQL statement if the conversion of a character string to a number fails.
LOGIN_DENIED	is raised if you try logging on to ORACLE with an invalid username/password.
NO_DATA_FOUND	is raised if a SELECT INTO statement returns no rows or if you reference an uninitialized row in a PL/SQL table
NOT_LOGGED_ON	is raised if your PL/SQL program issues a database call without being logged on to ORACLE
PROGRAM_ERROR	is raised if PL/SQL has an internal problem.
TIMEOUT_ON_RESOURCE	is raised if a timeout occurs while ORACLE is waiting for a resource.
TOO_MANY_ROWS	is raised if a SELECT INTO statement returns more than one row.
VALUE_ERROR	is raised if an arithmetic, conversion, truncation, or constraint error occurs.
ZERO_DIVIDE	is raised if you try to divide a number by zero.

Exception handlers may be defined at the end of each progran block. If your program contains only one block then the handlers are defined at the end of the program.

   exception
     when exception1 then
        code for exception_handler1
     when exception2 then
        code for exception_handler2

You may also specify your own exceptions. You cause an exeption with the statement raise own_exception. If you cause an exception of your own you must write also a handler for it. See the manual for more information. You should always define an exception handler for the exception others at the end of a procedure. The handler attached to this exception will be executed if no specific handler is defined for the raised exception. This handler might give an error message, such as

exception
  when others then 
     htp.p('<h3>Procedure pname caused 
         the error</h3>'); 
     htp.p('<font color=red>'|| sqlerrm || 
        '</font>');