This chapter explains how to develop PL/SQL Server Pages (PSP), which let you include dynamic content in web pages.
PL/SQL Server Pages (PSP) are server-side scripts that include dynamic content, including the results of SQL queries, inside web pages. You can author the web pages in an HTML authoring tool and insert blocks of PL/SQL code.
Example 10-1 shows a simple PL/SQL server page called simple.psp.
<%@ page language="PL/SQL" %>
<%@ page contentType="text/html" %>
<%@ plsql procedure="show_employees" %>
<%-- This example displays the last name and first name of every
employee in the hr.employees table. --%>
<%!
CURSOR emp_cursor IS
SELECT last_name, first_name
FROM hr.employees
ORDER BY last_name;
%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html">
<title>List of Employees</title>
</head>
<body TEXT="#000000" BGCOLOR="#FFFFFF">
<h1>List of Employees</h1>
<table width="40%" border="1">
<tr>
<th align="left">Last Name</th>
<th align="left">First Name</th>
</tr>
<% FOR emp_record IN emp_cursor LOOP %>
<tr>
<td> <%= emp_record.last_name %> </td>
<td> <%= emp_record.first_name %> </td>
</tr>
<% END LOOP; %>
</table>
</body>
</html>
You can compile and load a PL/SQL server page into the database with the loadpsp command-line utility. This command loads simple.psp into the hr schema, replacing the show_employees procedure if it exists:
loadpsp -replace simple.psp
Enter Password: password
Browser users can run the show_employees procedure through a URL. An HTML page that displays the last and first names of employees in the hr.employees table is returned to the browser through the PL/SQL gateway.
Deploying content through PL/SQL Server Pages has these advantages:
For developers familiar with PL/SQL, the server pages are the easiest way to create professional web pages that include database-generated content. You can develop web pages as you usually do and then embed PL/SQL code in the HTML.
PL/SQL Server Pages can be more convenient than using the HTP and HTF packages to write out HTML content line by line.
Because processing is performed on the database server, the client browser receives a plain HTML page with no special script tags. You can support all browsers and browser levels equally.
Network traffic is efficient because use of PL/SQL Server Pages minimizes the number of database round-trips.
You can write content quickly and follow a rapid, iterative development process. You maintain central control of the software, with only a web browser required on the client system.
To develop and deploy PL/SQL server pages, you must meet these prerequisites:
To write a PL/SQL server page you need access to a text editor or HTML authoring tool for writing the script. No other development tool is required.
To load a PL/SQL server page you need:
An account on the database in which to load the server pages.
Execution rights to the loadpsp command-line utility, which is located in $ORACLE_HOME/bin.
To deploy the server pages you must use mod_plsql. As explained in "Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application", the gateway uses the PL/SQL Web Toolkit.
You can enable browser users to run PL/SQL units through HTTP in these ways:
By writing an HTML page with embedded PL/SQL code and compiling it as a PL/SQL server page. You might invoke subprograms from the PL/SQL Web Toolkit, but not to generate every line of HTML output.
By writing a complete stored subprogram that produces HTML by invoking the HTP and OWA_* packages in the PL/SQL Web Toolkit. For information about this technique, see "Generating HTML Output with PL/SQL".
Thus, you must choose which technique to use when writing your web application. The key factors in choosing between these techniques are:
What source are you using as a starting point?
If you have a large body of HTML, and want to include dynamic content or make it the front end of a database application, then use PL/SQL Server Pages.
If you have a large body of PL/SQL code that produces formatted output, then you might find it more convenient to produce HTML tags by changing your print statements to invoke the HTP package of the PL/SQL Web Toolkit.
What is the fastest and most convenient authoring environment for your group?
If most work is done using HTML authoring tools, then use PL/SQL Server Pages.
If you use authoring tools that produce PL/SQL code, then it might be less convenient to use PL/SQL Server Pages.
Scripting solutions can be client-side or server-side. JavaScript is a very popular client-side scripting languages. PL/SQL Server Pages fully support JavaScript. Because any kind of tags can be passed unchanged to the browser through a PL/SQL server page, you can include JavaScript or other client-side script code in a PL/SQL server page.
Java Server Pages (JSP) and Active Server Pages (ASP) are two of the most popular server-side scripting solutions. Compared to PL/SQL Server Pages:
Java server pages are loosely analogous to PL/SQL Server Pages pages; Java servlets are analogous to PL/SQL packages. PL/SQL Server Pages use the same script tag syntax as JSP to make it easy to switch back and forth.
PL/SQL Server Pages use syntax that is similar to ASP, although not identical. Typically, you must translate from VBScript or JScript to PL/SQL. The best candidates for migration are pages that use the Active Data Object (ADO) interface to perform database operations.
Note:
You cannot mix PL/SQL server pages with other server-side script features, such as server-side includes. Often, you can get the same results by using the corresponding PL/SQL Server Pages features.To develop a PL/SQL server page, you can start with an existing web page or with an existing stored subprogram. Either way, with a few additions and changes you can create dynamic web pages that perform database operations and display the results.
The file for a PL/SQL server page must have the extension .psp. It can contain whatever content you choose, with text and tags interspersed with PL/SQL Server Pages directives, declarations, and scriptlets. A server page can take these forms:
In the simplest case, it is an HTML file. Compiling it as a PL/SQL server page produces a stored subprogram that outputs the same HTML file.
In the most complex case, it is a PL/SQL subprogram that generates all the content of the web page, including the tags for title, body, and headings.
In the typical case, it is a mixture of HTML (providing the static parts of the page) and PL/SQL (providing the dynamic content).
The order and placement of the PL/SQL Server Pages directives and declarations is usually not significant. It becomes significant only when another file is included. For ease of maintenance, Oracle recommends that you put the directives and declarations near the beginning of the file.
Table 10-1 lists the PL/SQL Server Pages elements and directs you to the section that explains how to use them. The section "Using Quotation Marks and Escaping Strings in a PSP Script" describes how to use quotation marks in strings that are used in various PL/SQL Server Pages elements.
| PSP Element | Name | Specifies . . . | Section |
|---|---|---|---|
|
|
Page Directive |
Characteristics of the PL/SQL server page. |
|
|
|
Parameter Directive |
The name, and optionally the type and default, for each parameter expected by the PSP stored procedure. |
|
|
|
Procedure Directive |
The name of the stored procedure produced by the PSP file. |
|
|
|
Include Directive |
The name of a file to be included at a specific point in the PSP file. |
|
|
|
Declaration Block |
The declaration for a set of PL/SQL variables that are visible throughout the page, not just within the next |
|
|
|
Code Block |
A set of PL/SQL statements to be executed when the procedure is run. |
|
|
|
Expression Block |
A single PL/SQL expression, such as a string, arithmetic expression, function call, or combination of these. |
|
|
|
Comment |
A comment in a PSP script. |
Note:
If you are familiar with dynamic HTML, you can go directly to "Examples of PL/SQL Server Pages".Use the <%@ page ... %> directive to specify characteristics of the PL/SQL server page such as:
What scripting language it uses.
What type of information (MIME type) it produces.
What code to run to handle all uncaught exceptions. This might be an HTML file with a friendly message, renamed to a .psp file. You must specify this same file name in the loadpsp command that compiles the main PSP file. You must specify the same name in both the errorPage directive and in the loadpsp command, including any relative path name such as ../include/.
This code shows the syntax of the page directive (the attribute names contentType and errorPage are case-sensitive):
<%@ page language='PL/SQL' contentType='content_type_string' charset='encoding' errorPage='file.psp' %>
To identify a file as a PL/SQL server page, include this directive somewhere in the file:
<%@ page language="PL/SQL" %>
This directive is for compatibility with other scripting environments. Example 10-1 shows an example of a simple PL/SQL server page that includes the language directive.
The PL/SQL parts of a PL/SQL server page are enclosed within special delimiters. All other content is passed exactly as it is—including any white space—to the browser. To display text or HTML tags, write it as you would write a typical web page. You need not invoke any output functions. As illustration, the server page in Example 10-1 returns the HTML page shown in Example 10-2, except that it includes the table rows for the queried employees.
Example 10-2 Sample Returned HTML Page
<html> <head> <meta http-equiv="Content-Type" content="text/html"> <title>List of Employees</title> </head> <body TEXT="#000000" BGCOLOR="#FFFFFF"> <h1>List of Employees</h1> <table width="40%" border="1"> <tr> <th align="left">Last Name</th> <th align="left">First Name</th> </tr> <!-- result set of query of hr.employees inserted here --> </table> </body> </html>
Sometimes you might want to display one line of output or another, or change the value of an attribute, based on a condition. You can include control structures and variable substitution inside the PSP delimiters, as shown in this code fragment from Example 10-1:
<% FOR emp_record IN emp_cursor LOOP %> <tr> <td> <%= emp_record.last_name %> </td> <td> <%= emp_record.first_name %> </td> </tr> <% END LOOP; %>
By default, the PL/SQL gateway transmits files as HTML documents so that the browser interprets the HTML tags. If you want the browser to interpret the document as XML, plain text (with no formatting), or some other document type, then include this directive:
<%@ page contentType="MIMEtype" %>
The attribute name contentType is case-sensitive. Insert text/html, text/xml, text/plain, image/jpeg, or some other MIME type that the browser or other client program recognizes. Users might have to configure their browsers to recognize some MIME types. An example of a directive for an Excel spreadsheet is:
<%@ page contentType="application/vnd.ms-excel" %>
Typically, a PL/SQL server page is intended to be displayed in a web browser. It can also be retrieved and interpreted by a program that can make HTTP requests, such as a a Java or PERL client.
By default, the PL/SQL gateway transmits files with the character set defined by the PL/SQL gateway. To convert the data to a different character set for browser display, include this directive:
<%@ page charset="encoding" %>
Specify Shift_JIS, Big5, UTF-8, or another encoding that the client program recognizes.
You must also configure the character set setting in the database accessor descriptor (DAD) of the PL/SQL gateway. Users might have to select the same encoding in their browsers to see the data displayed properly. For example, a database in Japan might have a database character set that uses the EUC encoding, but the web browsers are configured to display Shift_JIS encoding.
When writing PL/SQL server pages, you can get these types of errors:
The browser handles these errors. The loadpsp utility does not check for them.
PL/SQL syntax errors
The loadpsp utility stops and displays the line number, column number, and a brief message. You must fix the error before continuing.
Any previous version of the stored subprogram can be erased when you attempt to replace it with a script that contains a syntax error. You might want to use one database for prototyping and debugging, and then load the final stored subprogram into a different database for production. You can switch databases using a command-line flag without changing any source code.
To handle database errors that occur when the script runs, you can include PL/SQL exception-handling code within a PSP file and have any unhandled exceptions start a special PL/SQL server page. Use the errorPage attribute (the name is case-sensitive) of the <%@ page ... %> directive to specify the page name.
The page for unhandled exceptions is a PL/SQL server page with extension .psp. The error subprogram does not receive any parameters, so to determine the cause of the error, it can invoke the SQLCODE and SQLERRM functions. You can also display a standard HTML page without any scripting when an error occurs, but you must still give it the extension .psp and load it into the database as a stored subprogram.
This line specifies errors.psp as the page to run when errors are encountered:
<%@ page language="PL/SQL" contentType="text/html" errorPage="errors.psp" %>
To set up parameter passing for a PL/SQL server page, include a directive with this syntax:
<%@ plsql parameter="parameter_name" [type="PL/SQL_type"] [default="value"] %>
The default PL/SQL_type is VARCHAR2. This directive specifies that the parameter p_employee_id is of the type NUMBER:
<%@ plsql parameter="p_employee_id" type="NUMBER" %>
Specifying a default value for a parameter makes the parameter optional. The default value is substituted directly into a PL/SQL statement, so any strings must be enclosed in single quotation marks, and you can use special values such as NULL. This directive specifies that the parameter p_last_name has the default value NULL:
<%@ plsql parameter="p_last_name" default="NULL" %>
User input comes encoded in the URL that retrieves the HTML page. You can generate the URL by hard-coding it in an HTML link, or by invoking your page as the action of an HTML form. Your page receives the input as parameters to a PL/SQL stored subprogram.
Example 10-3 is like Example 10-1, except that it uses a parameter, p_employee_id. If the PL/SQL gateway is configured so that you can run procedures by invoking http://www.host.com/pls/proc_name, where proc_name is the name of a procedure, then you can pass 200 for parameter p_employee_id as follows:
http://www.example.com/pls/show_employees?p_employee_id=200
Example 10-3 simplewithuserinput.psp
<%@ page language="PL/SQL" %> <%@ page contentType="text/html" %> <%@ plsql parameter="p_employee_id" default="null" type="NUMBER" %> <%@ plsql procedure="show_employees" %> <%-- This example displays the last name and first name of every employee in the hr.employees table. --%> <%! CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees WHERE employee_id = p_employee_id ORDER BY last_name; %> <html> <head> <meta http-equiv="Content-Type" content="text/html"> <title>List of Employees</title> </head> <body TEXT="#000000" BGCOLOR="#FFFFFF"> <h1>List of Employees</h1> <table width="40%" border="1"> <tr> <th align="left">Last Name</th> <th align="left">First Name</th> </tr> <% FOR emp_record IN emp_cursor LOOP %> <tr> <td> <%= emp_record.last_name %> </td> <td> <%= emp_record.first_name %> </td> </tr> <% END LOOP; %> </table> </body> </html>
Each top-level PL/SQL server page corresponds to a stored procedure within the server. When you load the page with loadpsp, the utility creates a PL/SQL stored procedure. If the server page is name.psp, the default procedure name is name. For example, if the server page is hello_world.psp, then the default procedure name is hello_world.
To specify a procedure name, use this directive, where procname is the name for the procedure:
<%@ plsql procedure="procname" %>
In Example 10-1, this directive gives the stored procedure the name show_employees:
<%@ plsql procedure="show_employees" %>
It is the name of the procedure, not the name of the PSP script, that you include in the URL.
You can set up an include mechanism to pull in the contents of other files, typically containing either static HTML content or more PL/SQL scripting code. Insert this directive at the point where the content of the other file is to appear, replacing filename with the name of the file to be included:
<%@ include file="filename" %>
The included file must have an extension other than .psp. You must specify the same name in both the include directive and in the loadpsp command, including any relative path name such as ../include/.
Because the files are processed when you load the stored procedure into the database, the substitution is performed only once, not whenever the page is served. Therefore, changes to the included files that occur after the page is loaded into the database are not displayed when the procedure is executed.
You can use the include feature to pull in libraries of code, such as a navigation banners, footers, tables of contents, and so forth into multiple files. Alternatively, you can use this feature as a macro capability to include the same section of script code in multiple places in a page. This example includes an HTML footer:
<%@ include file="footer.htm" %>
When you use included files:
You can use any names and extensions for the included files. For example, you can include a file called products.txt.
If the included files contain PL/SQL scripting code, then they do not need their own set of directives to identify the procedure name, character set, and so on.
When specifying the names of files to the loadpsp utility, you must include the names of all included files also. Specify the names of included files before the names of any .psp files.
You can use the <%! ... %> directive to define a set of PL/SQL variables that are visible throughout the page, not just within the next BEGIN/END block. This element typically spans multiple lines, with individual PL/SQL variable declarations ended by semicolons. The syntax for this directive is as follows:
<%! PL/SQL declaration; [ PL/SQL declaration; ] ... %>
The usual PL/SQL syntax is allowed within the block. The delimiters server as shorthand, enabling you to omit the DECLARE keyword. All declarations are available to the code later in the file. Example 10-1 includes this cursor declaration:
<%! CURSOR emp_cursor IS SELECT last_name, first_name FROM hr.employees ORDER BY last_name; %>
You can specify multiple declaration blocks; internally, they are all merged into a single block when the PSP file is created as a stored procedure.
You can also use explicit DECLARE blocks within the <% ... %> delimiters that are explained in "Specifying Executable Statements in a PSP Script". These declarations are only visible to the BEGIN/END block that follows them.
Note:
To make things easier to maintain, keep all your directives and declarations near the beginning of a PL/SQL server page.You can use the <% ... %> code block directive to run a set of PL/SQL statements when the stored procedure is run. This code shows the syntax for executable statements:
<% PL/SQL statement; [ PL/SQL statement; ] ... %>
This element typically spans multiple lines, with individual PL/SQL statements ended by semicolons. The statements can include complete blocks, as in this example, which invokes the OWA_UTIL.TABLEPRINT procedure:
<% OWA_UTIL.TABLEPRINT(CTABLE => 'hr.employees', CATTRIBUTES => 'border=2', CCOLUMNS => 'last_name,first_name', CCLAUSES => 'WHERE employee_id > 100'); %>
The statements can also be the bracketing parts of IF/THEN/ELSE or BEGIN/END blocks. When a code block is split into multiple directives, you can put HTML or other directives in the middle, and the middle pieces are conditionally executed when the stored procedure is run. This code from Example 10-11 provides an illustration of this technique:
<% FOR ITEM IN (SELECT product_name, list_price, catalog_url
FROM product_information
WHERE list_price IS NOT NULL
ORDER BY list_price DESC) LOOP
IF item.list_price > p_minprice THEN
v_color := '#CCCCFF';
ELSE
v_color := '#CCCCCC';
END IF;
%>
<TR BGCOLOR="<%= v_color %>">
<TD><A HREF="<%= item.catalog_url %>"><%= item.product_name %></A></TD>
<TD><BIG><%= item.list_price %></BIG></TD>
</TR>
<% END LOOP; %>
All the usual PL/SQL syntax is allowed within the block. The delimiters server as shorthand, letting you omit the DECLARE keyword. All the declarations are available to the code later on in the file.
Note:
To share procedures, constants, and types across different PL/SQL server pages, compile them into a package in the database by using a plain PL/SQL source file. Although you can reference package procedures, constants, and types from PSP scripts, the PSP scripts can only produce standalone procedures, not packages.An expression directive outputs a single PL/SQL expression, such as a string, arithmetic expression, function call, or combination of these things. The result is substituted as a string at that spot in the HTML page that is produced by the stored procedure. The expression result must be a string value or be able to be cast to a string. For any types that cannot be implicitly cast, such as DATE, pass the value to the PL/SQL TO_CHAR function.
The syntax of an expression directive is as follows, where the expression placeholder is replaced by the desired expression:
<%= expression %>
You need not end the PL/SQL expression with a semicolon.
Example 10-1 includes a directive to print the value of a variable in a row of a cursor:
<%= emp_record.last_name %>
Compare the preceding example to the equivalent htp.print call in this example (note especially the semicolon that ends the statement):
<% HTP.PRN (emp_record.last_name); %>
The content within the <%= ... %> delimiters is processed by the HTP.PRN function, which trims leading or trailing white space and requires that you enclose literal strings in single quotation marks.
You can use concatenation by using the twin pipe symbol (||) as in PL/SQL. This directive shows an example of concatenation:
<%= 'The employee last name is ' || emp_record.last_name %>
PSP attributes use double quotation marks to delimit data. When values specified in PSP attributes are used for PL/SQL operations, they are passed exactly as you specify them in the PSP file. Thus, if PL/SQL requires a string enclosed in single quotation marks, then you must specify the string enclosed in single quotation marks, and enclose the whole thing in double quotation marks.
For example, your PL/SQL procedure might use the string Babe Ruth as the default value for a variable. For the string to be used in PL/SQL, you must enclose it in single quotation marks as 'Babe Ruth'. If you specify this string in the default attribute of a PSP directive, you must enclose it in double quotation marks, like this:
<%@ plsql parameter="in_players" default="'Babe Ruth'" %>
You can also enclose strings that are enclosed in single quotation marks in another set of single quotation marks. In this case, you must escape the inner single quotation marks by specifying the sequence \'. For example:
<%@ plsql parameter="in_players" default="'Walter \'Big Train\' Johnson'" %>
You can include most characters and character sequences in a PSP file without having them changed by the PSP loader. To include the sequence %>, specify the escape sequence %\>. To include the sequence <%, specify the escape sequence <\%. For example:
<%= 'The %\> sequence is used in scripting language: ' || lang_name %> <%= 'The <\% sequence is used in scripting language: ' || lang_name %>
To put a comment in the HTML portion of a PL/SQL server page for the benefit of those reading the PSP source code, use this syntax:
<%-- PSP comment text --%>
Comments in the preceding form do not appear in the HTML output from the PSP and also do not appear when you query the PL/SQL source code in USER_OBJECTS.
To create a comment that is visible in the HTML output and in the USER_OBJECTS source, place the comment in the HTML and use the normal HTML comment syntax:
<!-- HTML comment text -->
To include a comment inside a PL/SQL block within a PSP, and to make the comment invisible in the HTML output but visible in USER_OBJECTS, use the normal PL/SQL comment syntax, as in this example:
-- Comment in PL/SQL code
Example 10-4 shows a fragment of a PSP file with the three types of comments.
Example 10-4 Sample Comments in a PSP File
<p>Today we introduce our new model XP-10. <%-- This is the project with code name "Secret Project". Users viewing the HTML page do not see this PSP script comment. The comment is not visible in the USER_OBJECTS source code. --%> <!-- Some pictures of the XP-10. Users viewing the HTML page source see this comment. The comment is also visible in the USER_OBJECTS source code. --> <% FOR image_file IN (SELECT pathname, width, height, description FROM image_library WHERE model_num = 'XP-10') -- Comments interspersed with PL/SQL statements. -- Users viewing the HTML page source do not see these PL/SQL comments. -- These comments are visible in the USER_OBJECTS source code. LOOP %> <img src="<%= image_file.pathname %>" width=<% image_file.width %> height=<% image_file.height %> alt="<% image_file.description %>"> <br> <% END LOOP; %>
Use the loadpsp utility, which is located in $ORACLE_HOME/bin, to load one or more PSP files into the database as stored procedures. Each .psp file corresponds to one stored procedure. The pages are compiled and loaded in one step, to speed up the development cycle. The syntax of the loadpsp utility is:
loadpsp [-replace] [include_file_name...] [error_file_name] psp_file_name... Enter Password: password
When you load a PSP file, the loader performs these actions:
Logs on to the database with the specified user name, password, and net service name
Creates the stored procedures in the user schema
-replace creates procedures with CREATE OR REPLACE syntax.
include_file_name is the name of a file that is specified in the PSP include directive.
error_file_name is the name of the file that is specified in the errorPage attribute of the PSP page directive.
psp_file_name is the name of a file that is specified in a PSP page directive.
The filenames on the loadpsp command line must exactly match the names specified in the PSP include and page directives, including any relative path name such as ../include/.
Example 10-5 shows a sample PSP load command.
Example 10-5 Loading PL/SQL Server Pages
loadpsp -replace -user joe/abc123@/db3 banner.inc error.psp display_order.psp
In Example 10-5:
The stored procedure is created in the database db3. The database is accessed as user joe with password abc123, both to create the stored procedure and when the stored procedure is executed.
banner.inc is a file containing boilerplate text and script code that is included by the .psp file. The inclusion occurs when the PSP is loaded into the database, not when the stored procedure is executed.
error.psp is a file containing code, text, or both that is processed when an unhandled exception occurs, to present a friendly page rather than an internal error message.
display_order.psp contains the main code and text for the web page. By default, the corresponding stored procedure is named display_order.
The code that loadpsp generates is different from the code in the source file. It has calls to the HTP package, which generates the HTML tags for the web page.
After loading a PSP file, you can see the generated source code by querying the static data dictionary views *_SOURCE. For example, suppose that you load the script in Example 10-1 with this command:
loadpsp -replace -user hr simple.psp
Enter Password: password
If you log on to the database as user hr, you can view the source code of the PSP as shown in Example 10-6.
Example 10-6 Querying PL/SQL Server Page Source Code
Query:
SELECT TEXT FROM USER_SOURCE WHERE NAME = 'SHOW_EMPLOYEES' ORDER BY LINE;
Result:
PROCEDURE show_employees AS
CURSOR emp_cursor IS
SELECT last_name, first_name
FROM hr.employees
ORDER BY last_name;
BEGIN NULL;
owa_util.mime_header('text/html'); htp.prn('
');
htp.prn('
');
htp.prn('
');
htp.prn('
');
htp.prn('
');
htp.prn('
<html>
<head>
<meta http-equiv="Content-Type" content="text/html">
<title>List of Employees</title>
</head>
<body TEXT="#000000" BGCOLOR="#FFFFFF">
<h1>List of Employees</h1>
<table width="40%" border="1">
<tr>
<th align="left">Last Name</th>
<th align="left">First Name</th>
</tr>
');
FOR emp_record IN emp_cursor LOOP
htp.prn('
<tr>
<td> ');
htp.prn( emp_record.last_name );
htp.prn(' </td>
<td> ');
htp.prn( emp_record.first_name );
htp.prn(' </td>
</tr>
');
END LOOP;
htp.prn('
</table>
</body>
</html>
');
END;
After the PL/SQL server page is turned into a stored procedure, you can run the procedure by retrieving an HTTP URL through a web browser or other Internet-aware client program. The virtual path in the URL depends on the way the PL/SQL gateway is configured.
The parameters to the stored procedure are passed through either the POST method or the GET method of the HTTP protocol. With the POST method, the parameters are passed directly from an HTML form and are not visible in the URL. With the GET method, the parameters are passed as name-value pairs in the query string of the URL, separated by & characters, with most nonalphanumeric characters in encoded format (such as %20 for a space). You can use the GET method to invoke a PSP page from an HTML form, or you can use a hard-coded HTML link to invoke the stored procedure with a given set of parameters.
Using METHOD=GET, the syntax of the URL looks something like this:
http://sitename/schemaname/procname?parmname1=value1&parmname2=value2
For example, this URL includes a p_lname and p_fname parameter:
http://www.example.com/pls/show_employees?p_lname=Ashdown&p_fname=Lance
Using METHOD=POST, the syntax of the URL does not show the parameters:
http://sitename/schemaname/procname
For example, this URL specifies a procedure name but does not pass parameters:
http://www.example.com/pls/show_employees
The METHOD=GET format is more convenient for debugging and allows visitors to pass the same parameters when they return to the page through a bookmark.
The METHOD=POST format allows a larger volume of parameter data, and is suitable for passing sensitive information that must not be displayed in the URL. (URLs linger on in the browser's history list and in the HTTP headers that are passed to the next-visited page.) It is not practical to bookmark pages that are invoked this way.
This section shows how you might start with a very simple PL/SQL server page, and produce progressively more complicated versions as you gain more confidence.
As you go through each step, you can follow the instructions in "Loading PL/SQL Server Pages into the Database" and "Running PL/SQL Server Pages Through URLs" to test the examples.
These examples use the PRODUCT_INFORMATION table in the OE schema, which is described as follows:
SQL*Plus command:
DESCRIBE PRODUCT_INFORMATION;
Result:
Name Null? Type ----------------------------------------- -------- ---------------------------- PRODUCT_ID NOT NULL NUMBER(6) PRODUCT_NAME VARCHAR2(50) PRODUCT_DESCRIPTION VARCHAR2(2000) CATEGORY_ID NUMBER(2) WEIGHT_CLASS NUMBER(1) WARRANTY_PERIOD INTERVAL YEAR(2) TO MONTH SUPPLIER_ID NUMBER(6) PRODUCT_STATUS VARCHAR2(20) LIST_PRICE NUMBER(8,2) MIN_PRICE NUMBER(8,2) CATALOG_URL VARCHAR2(50)
The examples assume:
You have set up mod_plsql as described in "Using mod_plsql Gateway to Map Client Requests to a PL/SQL Web Application".
You have created a DAD for static authentication of the OE user.
You can access PL/SQL stored procedures created in the OE schema through this URL, where proc_name is the name of a stored procedure:http://www.example.com/pls/proc_name
For debugging purposes, you can display the complete contents of a SQL table with a call to OWA_UTIL.TABLEPRINT, as in Example 10-7. Later examples show other techniques that give more control over the presentation.
Example 10-7 show_prod_simple.psp
<%@ plsql procedure="show_prod_simple" %>
<HTML>
<HEAD><TITLE>Show Contents of product_information (Complete Dump)</TITLE></HEAD>
<BODY>
<%
DECLARE
dummy BOOLEAN;
BEGIN
dummy := OWA_UTIL.TABLEPRINT('oe.product_information','border');
END;
%>
</BODY>
</HTML>
Load the PSP in Example 10-7 at the command line as follows:
loadpsp -replace -user oe/password show_prod_simple.psp Enter Password: password
Access the PSP through this URL:
http://www.example.com/pls/show_prod_simple
Example 10-7 loops through the items in the product_information table and adjusts the SELECT statement to retrieve only a subset of the rows or columns. This example uses a very simple presentation, a set of list items, to avoid any problems from mismatched or unclosed table tags.
Example 10-8 show_catalog_raw.psp
<%@ plsql procedure="show_prod_raw" %>
<HTML>
<HEAD><TITLE>Show Products (Raw Form)</TITLE></HEAD>
<BODY>
<UL>
<% FOR item IN (SELECT product_name, list_price, catalog_url
FROM product_information
WHERE list_price IS NOT NULL
ORDER BY list_price DESC) LOOP %>
<LI>
Item = <%= item.product_name %><BR>
Price = <%= item.list_price %><BR>
URL = <%= item.catalog_url %><BR>
<% END LOOP; %>
</UL>
</BODY>
</HTML>
Example 10-9 shows a more sophisticated variation of Example 10-8 in which formatting is added to the HTML to improve the presentation.
Example 10-9 show_catalog_pretty.psp
<%@ plsql procedure="show_prod_pretty" %>
<HTML>
<HEAD><TITLE>Show Products (Better Form)</TITLE></HEAD>
<BODY>
<UL>
<% FOR item IN (SELECT product_name, list_price, catalog_url
FROM product_information
WHERE list_price IS NOT NULL
ORDER BY list_price DESC) LOOP %>
<LI>
Item = <A HREF=<%= item.catalog_url %>><%= item.product_name %></A><BR>
Price = <BIG><%= item.list_price %></BIG><BR>
<% END LOOP; %>
</UL>
</BODY>
</HTML>
In Example 10-7, Example 10-8, and Example 10-9, the HTML page remains the same unless the PRODUCT_INFORMATION table is updated. Example 10-10:
Makes the HTML page accept a minimum price, and presents only the items that are more expensive. (Your customers' buying criteria might vary.)
Sets the default minimum price to 100 units of the appropriate currency.
Example 10-10 show_product_partial.psp
<%@ plsql procedure="show_product_partial" %>
<%@ plsql parameter="p_minprice" default="100" %>
<HTML>
<HEAD><TITLE>Show Items Greater Than Specified Price</TITLE></HEAD>
<BODY>
<P>This report shows the items whose price is greater than <%= p_minprice %>.
<UL>
<% FOR ITEM IN (SELECT product_name, list_price, catalog_url
FROM product_information
WHERE list_price > p_minprice
ORDER BY list_price DESC)
LOOP %>
<LI>
Item = <A HREF="<%= item.catalog_url %>"><%= item.product_name %></A><BR>
Price = <BIG><%= item.list_price %></BIG><BR>
<% END LOOP; %>
</UL>
</BODY>
</HTML>
After loading Example 10-10 into the database, you can pass a parameter to the show_product_partial procedure through a URL. This example specifies a minimum price of 250:
http://www.example.com/pls/show_product_partial?p_minprice=250
Filtering results is appropriate for applications such as search results, where users might be overwhelmed by choices. But in a retail situation, you might want to use the alternative technique illustrated in Example 10-11, so that customers can still choose to purchase other items:
Instead of filtering the results through a WHERE clause, retrieve the entire result set and then take different actions for different returned rows.
Change the HTML to highlight the output that meets their criteria. Example 10-11 uses the background color for an HTML table row. You can also insert a special icon, increase the font size, or use another technique to call attention to the most important rows.
Present the results in an HTML table.
Example 10-11 show_product_highlighed.psp
<%@ plsql procedure="show_product_highlighted" %>
<%@ plsql parameter="p_minprice" default="100" %>
<%! v_color VARCHAR2(7); %>
<HTML>
<HEAD><TITLE>Show Items Greater Than Specified Price</TITLE></HEAD>
<BODY>
<P>This report shows all items, highlighting those whose price is
greater than <%= p_minprice %>.
<P>
<TABLE BORDER>
<TR>
<TH>Product</TH>
<TH>Price</TH>
</TR>
<% FOR ITEM IN (SELECT product_name, list_price, catalog_url
FROM product_information
WHERE list_price IS NOT NULL
ORDER BY list_price DESC) LOOP
IF item.list_price > p_minprice THEN
v_color := '#CCCCFF';
ELSE
v_color := '#CCCCCC';
END IF;
%>
<TR BGCOLOR="<%= v_color %>">
<TD><A HREF="<%= item.catalog_url %>"><%= item.product_name %></A></TD>
<TD><BIG><%= item.list_price %></BIG></TD>
</TR>
<% END LOOP; %>
</TABLE>
</BODY>
</HTML>
Example 10-12 shows a bare-bones HTML form that allows the user to enter a price. The form invokes the show_product_partial stored procedure illustrated in Example 10-10 and passes it the entered value as the p_minprice parameter.
To avoid coding the entire URL of the stored procedure in the ACTION= attribute of the form, you can make the form a PSP file so that it resides in the same directory as the PSP file that it invokes. Even though this HTML file contains no PL/SQL code, you can give it a .psp extension and load it as a stored procedure into the database. When the product_form stored procedure is executed through a URL, it displays the HTML exactly as it appears in the file.
To produce an elaborate HTML file, perhaps including dynamic content such as JavaScript, you can simplify the source code by implementing it as a PSP. This technique avoids having to deal with nested quotation marks, escape characters, concatenated literals and variables, and indentation of the embedded content.
Example 10-13 shows a version of Example 10-10 that uses JavaScript to display the order status in the browser status bar when the user moves his or her mouse over the product URL.
Example 10-13 show_product_javascript.psp
<%@ plsql procedure="show_product_javascript" %>
<%@ plsql parameter="p_minprice" default="100" %>
<HTML>
<HEAD>
<TITLE>Show Items Greater Than Specified Price</TITLE>
<SCRIPT language="JavaScript">
<!--hide
var text=" ";
function overlink (text)
{
window.status=text;
}
function offlink (text)
{
window.status=text;
}
//-->
</SCRIPT>
</HEAD>
<BODY>
<P>This report shows the items whose price is greater than <%= p_minprice %>.
<P>
<UL>
<% FOR ITEM IN (SELECT product_name, list_price, catalog_url, product_status
FROM product_information
WHERE list_price > p_minprice
ORDER BY list_price DESC)
LOOP %>
<LI>
Item =
<A HREF="<%= item.catalog_url %>"
onMouseover="overlink('PRODUCT STATUS: <%= item.product_status %>');return true"
onMouseout="offlink(' ');return true">
<%= item.product_name %>
</A>
<BR>
Price = <BIG><%= item.list_price %></BIG><BR>
<% END LOOP; %>
</UL>
</BODY>
</HTML>
As you begin experimenting with PL/SQL Server Pages, and as you adapt your first simple pages into more elaborate ones, keep these guidelines in mind when you encounter problems:
The first step is to get all the PL/SQL syntax and PSP directive syntax right. If you make a mistake here, the file does not compile.
Use semicolons to terminate lines where required.
If a value must be quoted, quote it. You might need to enclose a value in single quotation marks (which PL/SQL needs) inside double quotation marks (which PSP needs).
Mistakes in the PSP directives are usually reported through PL/SQL syntax messages. Check that your directives use the right syntax, that directives are closed properly, and that you are using the right element (declaration, expression, or code block) depending on what goes inside it.
PSP attribute names are case-sensitive. Most are specified in all lowercase; contentType and errorPage must be specified as mixed-case.
When using a URL to request a PSP, you might get an error that the file is not found. In this case:
Be sure you are requesting the right virtual path, depending on the way the web gateway is configured. Typically, the path includes the host name, optionally a port number, the schema name, and the name of the stored procedure (with no .psp extension).
If you use the -replace option when compiling the file, the old version of the stored procedure is erased. So, after a failed compilation, you must fix the error or the page is not available. You might want to test scripts in a separate schema, then load them into the production schema.
If you copied the file from another file, remember to change any procedure name directives in the source to match the correct file name.
When you get one file-not-found error, request the latest version of the page the next time. The error page might be cached by the browser. You might need to force a page reload in the browser to bypass the cache.
When the PSP script is run, and the results come back to the browser, use standard debugging techniques to check for and correct wrong output. The difficult part is to configure the interface between different HTML forms, scripts, and CGI programs so that the right values are passed into your page. The page might return an error because of a parameter mismatch.
Guidelines:
To determine exactly what is being passed to your page, use METHOD=GET in the invoking form so that the parameters are visible in the URL.
Ensure that the form or CGI program that invokes your page passes the correct number of parameters, and that the names specified by the NAME= attributes on the form match the parameter names in the PSP file. If the form includes any hidden input fields, or uses the NAME= attribute on the Submit or Reset buttons, then the PSP file must declare equivalent parameters.
Ensure that the parameters can be cast from string into the correct PL/SQL types. For example, do not include alphabetic characters if the parameter in the PSP file is declared as a NUMBER.
Ensure that the query string of the URL consists of name-value pairs, separated by equals signs, especially if you are passing parameters by constructing a hard-coded link to the page.
If you are passing a lot of parameter data, such as large strings, you might exceed the volume that can be passed with METHOD=GET. You can switch to METHOD=POST in the invoking form without changing your PSP file.
Although the loadpsp command reports line numbers correctly when there is a syntax error in your source file, line numbers reported for runtime errors refer to a transformed version of the source and do not match the line numbers in the original source. When you encounter errors that produce an error trace instead of the expected web page, you must locate the error through exception handlers and by printing debug output.
Before putting your PSP application into production, consider issues such as usability and download speed:
Pages can be rendered faster in the browser if the HEIGHT= and WIDTH= attributes are specified for all images. You might standardize on picture sizes, or store the height and width of images in the database along with the data or URL.
For viewers who turn off graphics, or who use alternative browsers that read the text out loud, include a description of significant images using the ALT= attribute. You might store the description in the database along with the image.
Although an HTML table provides a good way to display data, a large table can make your application seem slow. Often, the reader sees a blank page until the entire table is downloaded. If the amount of data in an HTML table is large, consider splitting the output into multiple tables.
If you set text, font, or background colors, test your application with different combinations of browser color settings:
Test what happens if you override just the foreground color in the browser, or just the background color, or both.
If you set one color (such as the foreground text color), set all the colors through the <BODY> tag, to avoid hard-to-read combinations like white text on a white background.
If you use a background image, specify a similar background color to provide proper contrast for viewers who do not load graphics.
If the information conveyed by different colors is crucial, consider using an alternative technique. For example, you might put an icon next to special items in a table. Some users might see your page on a monochrome screen or on browsers that cannot represent different colors.
Providing context information prevents users from getting lost. Include a descriptive <TITLE> tag for your page. If the user is partway through a procedure, indicate which step is represented by your page. Provide links to logical points to continue with the procedure, return to a previous step, or cancel the procedure completely. Many pages might use a standard set of links that you embed using the include directive.
In any entry fields, users might enter incorrect values. Where possible, use SELECT lists to present a set of choices. Validate any text entered in a field before passing it to SQL. The earlier you can validate, the better; a JavaScript function can detect incorrect data and prompt the user to correct it before they press the Submit button and call the database.
Browsers tend to be lenient when displaying incorrect HTML. What looks OK in one browser might look bad or might not display at all in another browser.
Guidelines:
Pay attention to HTML rules for quotation marks, closing tags, and especially for anything to do with tables.
Minimize the dependence on tags that are only supported by a single browser. Sometimes you can provide an extra bonus using such tags, but your application must still be usable with other browsers.
You can check the validity, and even in some cases the usability, of your HTML for free at many sites on the World Wide Web.