Skip Headers
Oracle® Database Application Developer's Guide - Fundamentals
10g Release 2 (10.2)

Part Number B14251-01
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

12 Developing PL/SQL Server Pages

This section contains the following topics:

PL/SQL Server Pages: Overview

This section contains the following topics:

What Are PL/SQL Server Pages and Why Use Them?

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 12-1 shows a simple PL/SQL server page called simple.psp.

Example 12-1 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 this script into an Oracle database with the loadpsp command-line utility. The following example loads this server page into the hr schema, replacing the show_employees procedure if it already exists:

loadpsp -replace -user hr/hr simple.psp

Browser users can execute 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 the following advantages:

  • For developers familiar with PL/SQL, the server pages are the easiest way to create professional Web pages that included database-generated content. You can develop Web pages as normal and then embed PL/SQL code in the HTML.

  • PSP 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 PSP 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 machine.

Prerequisites for Developing and Deploying PL/SQL Server Pages

To develop and deploy PL/SQL server pages, you must meet the following 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 an Oracle 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 "PL/SQL Web Toolkit", the gateway makes use of the PL/SQL Web Toolkit.

PSP and the HTP Package

You can enable browser users to execute PL/SQL program units through HTTP in the following ways:

  • By writing an HTML page with embedded PL/SQL code and compiling it as a PL/SQL server page. You may call procedures from the PL/SQL Web Toolkit, but not to generate every line of HTML output.

  • By writing a complete stored procedure that produces HTML by calling the HTP and OWA_* packages in the PL/SQL Web Toolkit. This technique is described in "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 if you want to include dynamic content or make it the front end of a database application, then use PSP.

    • If you have a large body of PL/SQL code that produces formatted output, then you may find it more convenient to produce HTML tags by changing your print statements to call 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 PSP.

    • If you use authoring tools that produce PL/SQL code, then it might be less convenient to use PSP.

PSP and Other Scripting Solutions

Scripting solutions can be client-side or server-side. JavaScript is one of the most popular client-side scripting language. PSP fully supports 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. Note the following points of comparison with PSP:

  • Java server pages are loosely analogous to PSP pages; Java servlets are analogous to PL/SQL packages. PSP uses the same script tag syntax as JSP to make it easy to switch back and forth.

  • PSP uses 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. In many cases, you can get the same results by using the corresponding PSP features.

Writing a PL/SQL Server Page

To write a PL/SQL server page, you can start with an existing Web page or with an existing stored procedure. 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 PSP directives, declarations, and scriptlets. A server page can take the following forms:

The order and placement of the PSP directives and declarations is usually not significant. It becomes significant only when another file is included. For ease of maintenance, it is recommended that you place the directives and declarations together near the beginning of the file.

Table 12-1 lists the PSP elements and directs you to the section that discusses how to use them. The section "Quoting and Escaping Strings in a PSP Script" describes how to quote strings that are used in various PSP elements.

Table 12-1 PSP Elements

PSP Element Name Specifies . . . Section

<%@ page ... %>

Page Directive

Characteristics of the PL/SQL server page.

"Specifying Basic Server Page Characteristics"


<%@ parameter ... %>

Parameter Directive

The name, and optionally the type and default, for each parameter expected by the PSP stored procedure.

"Accepting User Input"


<%@ plsql ... %>

Procedure Directive

The name of the stored procedure produced by the PSP file.

"Naming the PL/SQL Stored Procedure"


<%@ include ... %>

Include Directive

The name of a file to be included at a specific point in the PSP file.

"Including the Contents of Other Files"


<%! ... %>

Declaration Block

The declaration for a set of PL/SQL variables that are visible throughout the page, not just within the next BEGIN/END block.

"Declaring Global Variables in a PSP Script"


<% ... %>

Code Block

A set of PL/SQL statements to be executed when the procedure is run.

"Specifying Executable Statements in a PSP Script"


<%= ... %>

Expression Block

A single PL/SQL expression, such as a string, arithmetic expression, function call, or combination of these.

"Substituting an Expression Result in a PSP Script"


<%-- ... --%>

Comment

A comment in a PSP script.

"Including Comments in a PSP Script"



Note:

If you are familiar with dynamic HTML and want to start coding right away, you can jump forward to"Examples of PL/SQL Server Pages".

Specifying Basic Server Page Characteristics

Use the <%@ page ... %> directive to specify characteristics of the PL/SQL server page such as the following:

  • 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 exactly the same name in both the errorPage directive and in the loadpsp command, including any relative path name such as ../include/.

The following code shows the syntax of the page directive (note that the attribute names contentType and errorPage are case-sensitive):

<%@ page [language="PL/SQL"] [contentType="content type string"] charset="encoding" [errorPage="file.psp"] %>

Specifying the Scripting Language

To identify a file as a PL/SQL server page, include the following directive somewhere in the file:

<%@ page language="PL/SQL" %>

This directive is for compatibility with other scripting environments. Example 12-1 shows an example of a simple PL/SQL server page that includes the language directive.

Returning Data to the Client

You have the following basic options when specifying the type of data to return to the client browser:

Returning HTML

The PL/SQL parts of a PL/SQL server page are enclosed within special delimiters. All other content is passed along verbatim—including any whitespace—to the browser. To display text or HTML tags, write it as you would a typical Web page. You do not need to call any output functions. As illustration, the server page in Example 12-1 returns the HTML page shown in Example 12-2, except that it includes the table rows for the queried employees.

Example 12-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 the following code fragment from Example 12-1:

<%  FOR emp_record IN emp_cursor LOOP %>
  <tr>
  <td> <%= emp_record.last_name %> </td>
  <td> <%= emp_record.first_name %> </td>
  </tr>
<%  END LOOP; %>
Returning XML, Text, or Other Document Types

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 the following directive:

<%@ page contentType="MIMEtype" %>

The attribute name is case-sensitive, so be sure to capitalize it as contentType. Insert text/html, text/xml, text/plain, image/jpeg, or some other MIME type that the browser or other client program recognizes. Users may have to configure their browsers to recognize some MIME types. The following shows an example of a directive for an Excel spreadsheet:

<%@ 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.

Returning Pages Containing Different Character Sets

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 the following 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 may 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.

Handling Script Errors

When writing PL/SQL server pages, be mindful of the following types of errors:

  • HTML syntax errors. Any errors in HTML markup are handled by the browser. The loadpsp utility does not check for them.

  • PL/SQL syntax errors. If you make a syntax error in the PL/SQL code, the loadpsp utility stops and displays the line number, column number, and a brief message. You must fix the error before continuing. Note that any previous version of the stored procedure 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, then load the final stored procedure into a different database for production. You can switch databases using a command-line flag without changing any source code.

  • Runtime errors. 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 bring up a special PL/SQL server page. Use the errorPage attribute (note that 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 procedure does not receive any parameters, so to determine the cause of the error, it can call 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 procedure.

The following example shows a directive that specifies errors.psp as the page to run when errors are encountered:

<%@ page language="PL/SQL" contentType="text/html" errorPage="errors.psp" %> 

Accepting User Input

To set up parameter passing for a PL/SQL server page, include a directive with the following syntax:

<%@ plsql parameter="parameter name" [type="PL/SQL type"] [default="value"] %>

Example 12-9 shows an example of a script that includes the parameter directive.

By default, parameters are of type VARCHAR2. To use a different type, include a type="PL/SQL type" attribute within the directive, as in the following example:

<%@ plsql parameter="p_employee_id" type="NUMBER" %>

To set a default value, so that the parameter becomes optional, include a default="expression" attribute in the directive. The values for this attribute are substituted directly into a PL/SQL statement, so any strings must be single-quoted, and you can use special values such as null, as in the following example:

<%@ 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 calling your page as the action of an HTML form. Your page receives the input as parameters to a PL/SQL stored procedure. For example, assume that you change the first few lines of Example 12-1 to include a parameter directive as follows, and then load it into the database:

<%@ page language="PL/SQL" %>
<%@ page contentType="text/html" %>
<%@ plsql parameter="p_employee_id" default="null" type="NUMBER" %>
<%@ plsql procedure="show_employees" %> 
<%!
  CURSOR emp_cursor IS
  SELECT last_name, first_name
  FROM hr.employees
  WHERE employee_id = p_employee_id
  ORDER BY last_name;
%>

If the PL/SQL gateway were configured so that you could execute procedures by calling http://www.host.com/pls/proc_name, where proc_name is the name of a procedure, then you could pass 200 for parameter p_employee_id as follows:

http://www.host.com/pls/show_employees?p_employee_id=200

Naming the PL/SQL Stored Procedure

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. By default, the procedure is given the same name as the PSP script, except with the .psp extension removed. Thus, if your script is named hello_world.psp, then by default the utility creates a procedure named hello_world.

To give the procedure a name that is different from the script name, include the following directive, where procname is the name of a procedure:

<%@ plsql procedure="procname" %>

Example 12-1 includes the following directive, which gives the stored procedure the name show_employees:

<%@ plsql procedure="show_employees" %>

Thus, you could name the file empnames.psp or anything else that ends with *.psp, but the procedure is created as show_employees. Note that it is the name of the procedure, not the name of the PSP script, that you include in the URL.

Including the Contents of Other Files

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 the following directive at the point where the content of the other file should 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 exactly 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 more than one place in a page. The following example includes an HTML footer:

<%@ include file="footer.htm" %>

Note the following characteristics of included files:

  • You can use any names and extensions for the included files. For example, you could 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.

Declaring Global Variables in a PSP Script

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 12-1 includes the following 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 following BEGIN/END block.

Note:

To make things easier to maintain, keep all your directives and declarations together near the beginning of a PL/SQL server page.

Specifying Executable Statements in a PSP Script

You can use the <% ... %> code block directive to execute a set of PL/SQL statements when the stored procedure is run. The following 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 the following example, which calls 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. The following code from Example 12-10 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 packaged procedures, constants, and types from PSP scripts, the PSP scripts can only produce standalone procedures, not packages.

Substituting an Expression Result in a PSP Script

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 %>

Note that you do not need to end the PL/SQL expression with a semicolon.

Example 12-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 the following 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 whitespace and requires that you quote literal strings.

Note that you can use concatenation by using the twin pipe symbol (||) as you would in PL/SQL. The following directive shows an example of concatenation:

<%= 'The employee last name is ' || emp_record.last_name %>

Quoting and Escaping Strings in a PSP Script

PSP attributes use double quotes 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 single-quoted string, then you must specify the string with the single quotes around it—and surround the whole thing with double quotes.

For example, your PL/SQL procedure may use the string Babe Ruth as the default value for a variable. Because the string will be used in PL/SQL, you must enclose it in single quotes as 'Babe Ruth'. If you specify this single-quoted string in the default attribute of a PSP directive, then you must surround it in double quotes as in the following example:

<%@ plsql parameter="in_players" default="'Babe Ruth'" %>

You can also nest single-quoted strings inside single quotes. In this case, you must escape the nested single quotes 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 %>

Including Comments in a PSP Script

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 the following 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 the following example:

-- Comment in PL/SQL code

Example 12-3 shows a fragment of a PSP file with the three types of comments.

Example 12-3 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 will 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 will 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 will 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; %>

Loading a PL/SQL Server Page into the Database

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 as follows:

loadpsp [ -replace ] -user username/password[@connect_string] 
    [ include_file_name ... ] [ error_file_name ] psp_file_name ...

To create procedures with CREATE OR REPLACE syntax, use the -replace flag.

When you load a PSP file, the loader performs the following actions:

  1. Logs on to the database with the specified user name, password, and net service name

  2. Creates the stored procedures in the user schema

Include the names of all the include files before the names of the PL/SQL server pages. Also include the name of the file specified in the errorPage attribute of the page directive. These filenames on the loadpsp command line must match exactly the names specified within the PSP include and page directives, including any relative path name such as ../include/. Example 12-4 shows a sample PSP load command.

Example 12-4 Loading a PSP

loadpsp -replace -user hr/hr@orcl banner.inc error.psp display_order.psp

Note the following characteristics of Example 12-4:

Querying PSP Source Code

After you have loaded a PSP file, you can view the source code in the USER_SOURCE or DBA_SOURCE tables in the data dictionary. For example, suppose that you load the script in Example 12-1 with the following command:

loadpsp -replace -user hr/hr simple.psp

If you log on to the database as user hr, then you can execute the following query in SQL*Plus to view the source code of the PSP:

SET HEADING OFF

SELECT TEXT 
FROM   USER_SOURCE 
WHERE  NAME = 'SHOW_EMPLOYEES' 
ORDER BY LINE;

Sample output is shown in Example 12-5. Note that the code generated by loadpsp is different from the code in the source file. The loadpsp utility has added extra code, mainly calls to the HTP package, to the PSP code. The HTP package generates the HTML tags for the web page.

Example 12-5 Output from Query of USER_SOURCE

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('
<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;

Executing a PL/SQL Server Page Through a URL

After the PL/SQL server page has been 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 non-alphanumeric characters in encoded format (such as %20 for a space). You can use the GET method to call a PSP page from an HTML form, or you can use a hard-coded HTML link to call the stored procedure with a given set of parameters.

Using METHOD=GET, the syntax of the URL looks something like the following:

http://sitename/schemaname/procname?parmname1=value1&parmname2=value2

For example, the following URL includes a p_lname and p_fname parameter:

http://www.host.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, the following URL specifies a procedure name but does not pass parameters:

http://www.host.com/pls/show_employees

The METHOD=GET format is more convenient for debugging and allows visitors to pass exactly 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 should 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 called this way.

Examples of PL/SQL Server Pages

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 a PL/SQL Server Page into the Database" and "Executing a PL/SQL Server Page Through a URL" to test the examples.

This section contains the following topics:

Setup for PL/SQL Server Pages Examples

These examples use the product_information table in the oe schema, which is described as follows:

Table PRODUCT_INFORMATION
 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 the following:

  • Youn have set up mod_plsql as described in "Using the mod_plsql Gateway".

  • 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 the following URL, where proc_name is the name of a stored procedure: http://www.host.com/pls/proc_name

For debugging purposes, you can display the complete contents of an SQL table. You can do this with a single call to OWA_UTIL.TABLEPRINT as illustrated in Example 12-6. In subsequent iterations, we use other techniques to gain more control over the presentation.

Example 12-6 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 12-6 at the command line as follows:

loadpsp -replace -user oe/oe show_prod_simple.psp

Access the PSP through the following URL:

http://www.host.com/pls/show_prod_simple

Printing the Sample Table with a Loop

Example 12-6 loops through the items in the product_information table and adjusts the SELECT statement to retrieve only a subset of the rows or columns. In this example, we pick a very simple presentation, a set of list items, to avoid any problems from mismatched or unclosed table tags.

Example 12-7 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 12-8 shows a more sophisticated variation of Example 12-7 in which formatting is added to the HTML to improve the presentation.

Example 12-8 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>

Allowing a User Selection

In the previous examples, the HTML page remains the same unless the product_information table is updated. Example 12-9 livens up the page by:

  • Making it accept a minimum price, and present only the items that are more expensive. (Your customers' buying criteria may vary.)

  • Setting the default minimum price to 100 units of the appropriate currency. Later, we see how to allow the user to pick a minimum price.

Example 12-9 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 12-9 into the database, you can pass a parameter to the show_product_partial procedure through a URL. The following example specifies a minimum price of 250:

http://www.host.com/pls/show_product_partial?p_minprice=250

This technique of filtering results is fine for some applications, such as search results, in which users might worry about being overwhelmed by choices. But in a retail situation, you might want to use the alternative technique illustrated in Example 12-10 so that customers can still choose to purchase other items. Note the following features of this example:

  • Instead of filtering the results through a WHERE clause, we can retrieve the entire result set and then take different actions for different returned rows.

  • We can change the HTML to highlight the output that meets their criteria. In this case, we use the background color for an HTML table row. We could also insert a special icon, increase the font size, or use some other technique to call attention to the most important rows.

  • We can present the results in an HTML table.

Example 12-10 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>

Using an HTML Form to Call a PL/SQL Server Page

Example 12-11 shows a bare-bones HTML form that allows the user to enter a price. The form calls the show_product_partial stored procedure illustrated in Example 12-9 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, we can make the form a PSP file so that it resides in the same directory as the PSP file that it calls. Even though this HTML file contains no PL/SQL code, we 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.

Example 12-11 product_form.psp

<HTML>
<BODY>
<FORM method="POST" action="show_product_partial">
  <P>Enter the minimum price you want to pay:
  <INPUT type="text" name="p_minprice">
  <INPUT type="submit" value="Submit">
</FORM>
</BODY>
</HTML>

Including JavaScript in a PSP 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 12-12 shows a version of Example 12-9 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 12-12 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>

Debugging PL/SQL Server Page Problems

As you begin experimenting with PSP, and as you adapt your first simple pages into more elaborate ones, keep these guidelines in mind when you encounter problems:

Putting PL/SQL Server Pages into Production

Before putting your PSP application into production, consider issues such as usability and download speed: