The HTTP listener and PL/SQL gateway are used to build web-enabled systems that provide tight integration with a backend Oracle database. PL/SQL-based OAS and WebDB applications are developed using a set of packages called the PL/SQL toolkit. In this section, we'll take a quick look at the toolkit and see an example procedure. The last section covers how to pass parameters.
WebDB and OAS both include the PL/SQL toolkit. The toolkit contains a variety of PL/SQL packages written and supplied by Oracle that perform a range of tasks, including generating HTML tags, manipulating cookies (name/value pairs used to save information throughout an entire session), and creating complex HTML structures based on information in a database table. In general, procedures built with the toolkit will work in either product, although you may run into minor database privilege issues that the DBA can help you resolve.
The packages in the toolkit (described in detail in Chapter 7 ) are:
The following example gives the flavor of how the toolkit creates web content. The example is a relatively simple PL/SQL procedure that displays rows in an employee table. The output is formatted into HTML using the procedures in the toolkit's HTP package:
/* Formatted by PL/Formatter v.1.1.13 */ PROCEDURE show_emps ( i_job IN VARCHAR2 DEFAULT 'SALESMAN' ) AS CURSOR emp_cur IS SELECT * FROM scott.emp WHERE job LIKE i_job ORDER BY ename; emp_rec emp_cur%ROWTYPE; BEGIN HTP.title ('Employees in the EMP table'); HTP.tableopen (cattributes => 'border=1 width=100%'); OPEN emp_cur; LOOP FETCH emp_cur INTO emp_rec; EXIT WHEN emp_cur%notfound; HTP.tablerowopen; HTP.tabledata (emp_rec.ename); HTP.tabledata (emp_rec.job); HTP.tabledata (emp_rec.hiredate); HTP.tabledata (emp_rec.sal); HTP.tablerowclose; END LOOP; CLOSE emp_cur; HTP.tableclose; END;
You can pass parameters to a WebDB or an OAS PL/SQL procedure by including them either in the query string of a URL or as named elements on an HTML form. These parameters are mapped to the procedure's formal argument list using named notation. For example, let's suppose we want to develop a web page that inserts a new user into a table. The procedure we want to call is defined as:
/* Formatted by PL/Formatter v.1.1.13 */ PROCEDURE add ( lname IN VARCHAR2 DEFAULT NULL, fname IN VARCHAR2 DEFAULT NULL, dpt_code IN VARCHAR2 DEFAULT NULL ) IS BEGIN INSERT INTO emp_table (last_name,first_name,dept) VALUES (lname, fname, dpt_code); COMMIT; HTP.print ('User was inserted'); EXCEPTION WHEN OTHERS THEN HTP.print ('Sorry, could not insert user.'); END;
The first way to call the procedure is to embed the parameter values in the URL's query string. Recall that the query string is made up of sets of name/value pairs. When we call a PL/SQL procedure, the "name" part of the pair selects the formal parameter to which we are assigning a value. The "value" part specifies the actual value to pass. The URL to call the procedure is:
http:// server / DAD /add?lname=odewahn&fname=andrew&dpt_code=MIS
We can call the same procedure with an HTML form. In this case, the form's
<form action=http://wilma/hr/plsql/add> First Name: <input type=text name=fname><br> Last Name: <input type=text name=lname><br> Department: <select name=dpt_code> <option value=HR>Human Resources <option value=MIS>Computer department <option value=ACCT>Accounting </select> </form>
Sometimes it is desirable to process multiple values for the same parameter, such as when you want to allow a user to enter multiple rows of data in a single form. In a query string, this is accomplished by giving the same name to multiple name/value pairs. In a form, it is accomplished by using the same name for multiple input elements. On the PL/SQL side, the corresponding parameter for the procedure must be declared as an array datatype. We'll see an example of this in Chapter 8 .
Calling a procedure from the Web circumvents the compiler safeguards that occur in normal procedure calls. When the gateway receives a URL to execute, it will try to do so whether the URL represents a syntactically correct call or not. If the call contains even the slightest error, the listener bombs out and presents an ugly error page to the user. Some of the most common sources of errors are:
The following guidelines help minimize these and other errors:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.