15
Developing Web Applications with PL/SQL

If you think that only new languages such as Java and Javascript can do network operations and produce dynamic web content, think again. PL/SQL has a number of features that you can use to web-enable your database and make your back-office data interactive and accessible to intranet users or even your customers.

Performing Network Operations with PL/SQL

While PL/SQL's built-in features are focused on traditional database operations and programming logic, Oracle supplies packages that open up Internet computing to PL/SQL programmers. You can find details and examples using most of these packages in the Oracle8i Supplied PL/SQL Packages Reference.

Sending Mail

You can send mail from a PL/SQL program or stored procedure using the UTL_SMTP package.

Getting a Host Name or Address

You can determine the hostname of the local machine, or the IP address of a given hostname from a PL/SQL program or stored procedure using the UTL_INADDR package. You use the results in calls to the UTL_TCP package.

Working with TCP/IP Connections

You can open TCP/IP connections to machines on the network, and read or write to the corresponding sockets, using the UTL_TCP package.

Retrieving the Contents of an HTTP URL

You can retrieve the contents of an HTTP URL using the UTL_HTTP package. The contents are typically in the form of HTML-tagged text, but may be plain text, a JPEG image, or any sort of file that is downloadable from a web server.

Working with Tables, Image Maps, Cookies, CGI Variables, and More

Packages for all of these functions are supplied with the Oracle web gateways, such as Oracle Application Server (OAS) and WebDB. You can format the results of a query in an HTML table, produce an image map, set and get HTTP cookies, check the values of CGI variables, and combine other typical web operations with a PL/SQL program.

For details, refer to the PL/SQL Web Toolkit documentation that comes with the web gateways.

Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)

To include dynamic content, including the results of SQL queries, inside web pages, you can use server-side scripting through PL/SQL Server Pages (PSP). You can author the web pages in a script-friendly HTML authoring tool, and drop the pieces of PL/SQL code into place. For cutting-edge web pages, you might find this technique more convenient than using the HTP and HTF packages to write out HTML content line by line.

Because the processing is done on the server -- in this case, the database server rather than the web server -- the browser receives a plain HTML page with no special script tags, and you can support all browsers and browser levels equally. It also makes network traffic efficient by minimizing the number of server roundtrips.

Embedding the PL/SQL code in the HTML page that you create lets you 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.

The steps to implement a web-based solution using PL/SQL server pages are:

• Choosing a Software Configuration

• Writing the Code and Content for the PL/SQL Server Page

• Loading the PL/SQL Server Page into the Database as a Stored Procedure

Choosing a Software Configuration

To develop and deploy PL/SQL Server Pages, you need the Oracle server at version 8.1.6 or later, together with a PL/SQL web gateway. Currently, the web gateways are the WebDB PL/SQL Gateway and the OAS PL/SQL Cartridge. Before you start with PSP, you should have access to both the database server and the web server for one of these gateways.

Choosing Between PSP and the PL/SQL Web Toolkit

You can produce the same results in different 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.

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, use PSP.

  • If you have a large body of PL/SQL code that produces formatted output, 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, use PSP.

  • If you use authoring tools that produce PL/SQL code for you, such as the page-building wizards in WebDB, then it might be less convenient to use PSP.

How PSP Relates to Other Scripting Solutions

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.

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.

PSP uses the same script tag syntax as Java Server Pages (JSP), to make it easy to switch back and forth.

PSP uses syntax similar to that of Active Server Pages (ASP), although the syntax is not identical and you must typically translate from VBScript or JScript to PL/SQL. The best candidates for migration are pages that use the Active Data Object (ADO) interface to do database operations.

Writing the Code and Content for the 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 Format of the PSP File

The file for a PL/SQL server page must have the extension .psp.

It can contain whatever content you like, with text and tags interspersed with PSP directives, declarations, and scriptlets:

• In the simplest case, it is nothing more than an HTML file. Compiling it as a PL/SQL server page produces a stored procedure that outputs the exact same HTML file.

• In the most complex case, it is a PL/SQL procedure that generates all the content of the web page, including the tags for title, body, and headings.

• In the typical case, it is a mix of HTML (providing the static parts of the page) and PL/SQL (filling in the dynamic content).

The order and placement of the PSP directives and declarations is not significant in most cases -- only when another file is being included. For ease of maintenance, we recommend placing the directives and declarations together near the beginning of the file.

The following sections discuss the way to produce various results using the PSP scripting elements. If you are familiar with dynamic HTML and want to start coding right away, you can jump forward to and .

Specifying the Scripting Language

To identify a file as a PL/SQL Server Page, include a <%@ page language="PL/SQL" %> directive somewhere in the file. This directive is for compatibility with other scripting environments.

Accepting User Input

User input comes encoded in the URL that retrieves the HTML page. You can generate the URL by hardcoding 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.

To set up parameter passing for a PL/SQL server page, include a <%@ plsql parameter="..." %> directive. By default, parameters are of type VARCHAR2. To use a different type, include a type="..." attribute within the directive. To set a default value, so that the parameter becomes optional, include a default="..." 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.

Displaying HTML

The PL/SQL parts of the 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 function.

Sometimes you might want to display one line of output or another, or change the value of an attribute, based on some condition. You can include IF/THEN logic and variable substitution inside the PSP delimiters, as shown in subsequent sections.

Returning XML, Text, or Other Document Types

By default, the PL/SQL gateway transmits files as HTML documents, so that the browser formats them according to the HTML tags. If you want the browser to interpret the document as XML, plain text (with no formatting), or some other document type, include a <%@ page contentType="..." %> directive. (The attribute name is case-sensitive, so be sure to capitalize it as contentType.) Specify 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.

Typically, a PL/SQL server page is intended to be displayed in a web browser. It could also be retrieved and interpreted by a program that can make HTTP requests, such as a Java or Perl application.

Returning Pages Containing Different Character Sets

By default, the PL/SQL gateway transmits files using the character set defined by the web gateway. To convert the data to a different character set for displaying in a browser, include a <%@ page charset="..." %> directive. Specify Shift_JIS, Big5, UTF-8, or other encoding that the browser or other client program recognizes.

You must also configure the character set setting in the database accessor descriptor (DAD) of the web gateway. Users may have to select the same encoding in their browsers to see the data displayed properly.

For example, a database in Japan may contain data in the EUC encoding, while the web browsers are set up to display Shift_JIS encoding.

Handling Script Errors

Any errors in HTML tagging are handled by the browser. The PSP loading process does not check for them.

If you make a syntax error in the PL/SQL code, the loader stops and 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 and the script 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.

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 page. The page for unhandled exceptions is another 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.

Naming the PL/SQL Stored Procedure

Each top-level PL/SQL server page corresponds to a stored procedure within the server. By default, the procedure is given the same name as the original file, with the .psp extension removed. To name the procedure something else, include a include a <%@ page procedure="..." %> directive.

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. Include a <%@ include file="..." %> directive at the point where the other file's content should appear. Because the files are processed at the point where you load the stored procedure into the database, the substitution is done only once, not whenever the page is served.

You can use any names and extensions for the included files. If the included files contain PL/SQL scripting code, 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 PSP loader, 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 this feature to pull in the same content, such as a navigation banner, into many different files. Or, you can use it as a macro capability to include the same section of script code in more than one place in a page.

Declaring Variables for Use in the Script

If you need to use global variables within the script, you can include a declaration block inside the delimiters <%! %>. 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.

You can specify multiple declaration blocks; internally, they are all merged into a single block when the PSP file is made into a stored procedure.

You can also use explicit DECLARE blocks within the <% %> delimiters that are explained later. These declarations are only visible to the following BEGIN/END block.

Specifying Statements to be Executed in the Script

You can include any PL/SQL statements within the delimiters <% %>. The statements can be complete, or clauses of a compound statement, such as the IF part of an IF-THEN-ELSE statement. Any variables declared within DECLARE blocks are only visible to the following BEGIN/END block.

Substituting an Expression Result in a Script

To include a value that depends upon the result of a PL/SQL expression, include the expression within the delimiters <%= %>. Because the result is always substituted in the middle of text or tags, it must be a string value or be able to be cast to a string. For any types that cannot be implicitly casted, such as DATE, pass the value to the PL/SQL TO_CHAR function.

The content between the <%= %> delimiters is processed by the HTM.PRN function, which trims any leading or trailing whitespace and requires that you quote any literal strings.

Conventions for Quoting and Escaping Strings

When values specified in PSP attributes are used for PL/SQL operations, they are passed exactly as you specify them in the PSP file. If PL/SQL requires a single-quoted string, you must specify the string with the single quotes around it -- and surround the whole thing with double quotes.

You can also nest single-quoted strings inside single quotes. In this case, you must escape the nested single quotes by specifying the sequence \'.

Most characters and character sequences can be included in a PSP file without being changed by the PSP loader. To include the sequence %>, specify the escape sequence %\>. To include the sequence <%, specify the escape sequence <\%.

Retrieving a Result Set from a Query

To retrieve a single row from the database, you can open a cursor, fetch data into it, and retrieve the columns from the cursor variable:

To display the results of a query that returns multiple rows, you can iterate through each row of the result set:

If you want to print out an entire table, you can call the OWA_UTIL.TABLEPRINT procedure from the PL/SQL web toolkit:

Updating the Database Based Upon User Input

You can run insert, update, and delete operations within a PL/SQL server page. As with any program that is expected to return results as HTML, such pages should include some output to confirm that the operation is successful or to show the updated state.

Coding Tips for PL/SQL Server Pages

To share procedures, constants, and types across different PL/SQL server pages, compile them into a separate package in the database (not using the PSP technique).

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

Syntax of PL/SQL Server Page Elements

You can find examples of many of these elements in "Examples of PL/SQL Server Pages".

Page Directive

Specifies characteristics of the PL/SQL server page:

• What scripting language it uses.

• What type of information (MIME type) it produces.

• What other PSP file handles all uncaught exceptions.

Note that the attribute names contentType and errorPage are case-sensitive.

Syntax

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

Procedure Directive

Specifies the name of the stored procedure produced by the PSP file. By default, the name is the filename without the .psp extension.

Syntax

<%@ plsql procedure="procedure name" %>

Parameter Directive

Specifies the name, and optionally the type and default, for each parameter expected by the PSP stored procedure. The parameters are passed using the CGI protocol, typically from an HTML form.

Syntax

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

Include Directive

Specifies the name of a file to be included at a specific point in the PSP file. The file must have an extension other than .psp. It can contain HTML, PSP script elements, or a combination of both. The name resolution and file inclusion happens when the PSP file is loaded into the database as a stored procedure, so any changes to the file after that are not reflected when the stored procedure is run.

Syntax

<%@ include file="path name" %>

Declaration Block

Declares 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.

Syntax

<%! PL/SQL declaration;
    [ PL/SQL declaration; ] ... %>

Code Block (Scriptlet)

Executes a set of PL/SQL statements when the stored procedure is run. This element typically spans multiple lines, with individual PL/SQL statements ended by semicolons. The statements can include complete blocks, or can be the bracketing parts of IF/THEN/ELSE or BEGIN/END blocks. When a code block is split into multiple scriptlets, you can put HTML or other directives in the middle, and those pieces are conditionally executed when the stored procedure is run.

Syntax

<% PL/SQL statement;
   [ PL/SQL statement; ] ... %>

Expression Block

Specifies a single PL/SQL expression, such as a string, arithmetic expression, function call, or combination of those things. The result is substituted as a string at that spot in the HTML page that is produced by the stored procedure. You do not need to end the PL/SQL expression with a semicolon.

Syntax

<%= PL/SQL expression %>

Loading the PL/SQL Server Page into the Database as a Stored Procedure

You 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:

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

To do a "create and replace" on the stored procedures, include the -replace flag.

The loader logs on to the database using the specified user name, password, and connect string. The stored procedures are created in the corresponding schema.

Include the names of all the include files (whose names do not have the .psp extension) before the names of the PL/SQL server pages (whose names have the .psp extension).

For example:

loadpsp -replace -user scott/tiger@WEBDB banner.inc display_order.psp

In this example:

• The stored procedure is created in the database WEBDB. The database is accessed as user scott with password tiger, both to create the stored procedure and when the stored procedure is executed.

• timestamp.inc and banner.inc are files containing boilerplate text and script code, that are included by the .psp file.

• display_order.psp contains the main code and text for the web page. By default, the corresponding stored procedure is named DISPLAY_ORDER.

Running a PL/SQL Server Page via a URL

Once the PL/SQL server page has been turned into a stored procedure, you can run it 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 web gateway is configured.

The parameters to the stored procedure are passed through the CGI mechanism, either the POST method or the GET method. 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 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 hardcoded HTML link to call the stored procedure with a given set of parameters.

Sample PSP URLs

Using METHOD=GET, the URL might look something like this:

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

Using METHOD=POST, the URL does not show the parameters:

http://sitename/schemaname/pspname

The METHOD=GET format is more convenient for debugging and allows visitors to pass exactly the same paramters 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 CGI variables 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 use the procedures in "Loading the PL/SQL Server Page into the Database as a Stored Procedure" and "Running a PL/SQL Server Page via a URL" to compile the PSP files and try them in a browser.

Sample Table

In this example, we use a very small table representing a product catalog. It holds the name of an item, the price, and URLs for a description and picture of the item.

Name       Type
---------- -------------
PRODUCT    VARCHAR2(100)
PRICE      NUMBER(7,2)
URL        VARCHAR2(200)
PICTURE    VARCHAR2(200)              

Guitar
455.5
http://auction.fictional_site.com/guitar.htm
http://auction.fictional_site.com/guitar.jpg
 
 
Brown shoe
79.95
http://retail.fictional_site.com/loafers.htm
http://retail.fictional_site.com/shoe.gif
 
Radio
9.95
http://promo.fictional_site.com/freegift.htm
http://promo.fictional_site.com/alarmclock.jpg

Dumping the Sample Table

For your own debugging, you might want to display the complete contents of an SQL table. You can do this with a single call to OWA_UTIL.TABLEPRINT. In subsequent iterations, we use other techniques to get more control over the presentation.

<@% page language="PL/SQL" %>
<%@ plsql procedure="show_catalog_simple" %>
<HTML>
<HEAD><TITLE>Show Contents of Catalog (Complete Dump)</TITLE></HEAD>
<BODY>
<% 
declare
dummy boolean;
begin
dummy := owa_util.tableprint('catalog','border');
end;
%>
</BODY>
</HTML>

Printing the Sample Table using a Loop

Next, we loop through the items in the table and explicitly print just the pieces we want.

• We could adjust the SELECT statement to retrieve only a subset of the rows or columns.

• We could change the HTML or the location of the expressions to change the appearance of each item, or the order in which the columns are shown.

• At this early stage, we pick a very simple presentation, a set of list items, to avoid any problems from mismatched or unclosed table tags.

  <@% page language="PL/SQL" %>
  <%@ plsql procedure="show_catalog_raw" %>
  <HTML>
  <HEAD><TITLE>Show Contents of Catalog (Raw Form)</TITLE></HEAD>
  <BODY>
  <UL>
  <% for item in (select * from catalog order by price desc) loop %>
  <LI>
  Item = <%= item.product %><BR>
  price = <%= item.price %><BR>
  URL = <I><%= item.url %></I><BR>
  picture = <I><%= item.picture %></I>
  <% end loop; %>
  </UL>
  </BODY>
  </HTML>
  
  

Once the previous simple example is working, we can display the contents in a more usable format.

• We use some HTML tags around certain values for emphasis.

• Instead of printing the URLs for the description and picture, we plug them into link and image tags so that the reader can see the picture and follow the link.

  <@% page language="PL/SQL" %>
  <%@ plsql procedure="show_catalog_pretty" %>
  <HTML>
  <HEAD><TITLE>Show Contents of Catalog (Better Form)</TITLE></HEAD>
  <BODY>
  <UL>
  <% for item in (select * from catalog order by price desc) loop %>
  <LI>
  Item = <A HREF="<%= item.url %>"><%= item.product %></A><BR>
  price = <BIG><%= item.price %></BIG><BR>
  <IMG SRC="<%= item.picture %>">
  <% end loop; %>
  </UL>
  </BODY>
  </HTML>
  
  
  

Allowing a User Selection

Now we have a dynamic page, but from the user's point of view it may still be dull. The results are always the same unless you update the catalog table.

• To liven up the page, we can make it accept a minimum price, and present only the items that are more expensive. (Your customers' buying criteria may vary.)

• When the page is displayed in a browser, by default the minimum price is 100 units of the appropriate currency. Later, we will see how to allow the user to pick a minimum price.

  <@% page language="PL/SQL" %>
  <%@ plsql procedure="show_catalog_partial" %>
  <%@ plsql parameter="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 <%= minprice %>.
  <UL>
  <% for item in (select * from catalog where price > minprice order by price 
  desc) loop %>
  <LI>
  Item = <A HREF="<%= item.url %>"><%= item.product %></A><BR>
  price = <BIG><%= item.price %></BIG><BR>
  <IMG SRC="<%= item.picture %>">
  <% end loop; %>
  </UL>
  </BODY>
  </HTML>
  
  

The above technique of filtering results is fine for some applications, such as search results, where users might worry about being overwhelmed by choices. But in a retail situation, you might want to use an alternative technique so that customers can still choose to purchase other items.

• Intead of filtering the results through a WHERE clause, we can retrieve the entire result set, 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.

• At this point, where we want to present a specific user experience, it becomes worth the trouble to lay out the results in an HTML table.

  <@% page language="PL/SQL" %>
  <%@ plsql procedure="show_catalog_highlighted" %>
  <%@ plsql parameter="minprice" default="100" %>
  <%! 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 <%= minprice %>.
  <TABLE BORDER>
  <TR>
  <TH>Product</TH>
  <TH>Price</TH>
  <TH>Picture</TH>
  </TR>
  <%
  for item in (select * from catalog order by price desc) loop
    if item.price > minprice then
      color := '#CCCCFF';
    else
      color := '#CCCCCC';
    end if;
  %>
  <TR BGCOLOR="<%= color %>">
  <TD><A HREF="<%= item.url %>"><%= item.product %></A></TD>
  <TD><BIG><%= item.price %></BIG></TD>
  <TD><IMG SRC="<%= item.picture %>"></TD>
  </TR>
  <% end loop; %>
  </TABLE>
  </BODY>
  </HTML>
  
  
  

Sample HTML Form to Call a PL/SQL Server Page

Here is a bare-bones HTML form that allows someone to enter a price, and then calls the SHOW_CATALOG_PARTIAL stored procedure passing the entered value as the 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 goes in the same directory as the PSP file it calls. Even though this HTML file has no PL/SQL code, we can give it a .psp extension and load it as a stored procedure into the database. When the stored procedure is run, it just displays the HTML exactly as it appears in the file.

<html>
<body>
<form method="POST" action="show_catalog_partial">
<p>Enter the minimum price you want to pay:
<input type="text" name="minprice">
<input type="submit" value="Submit">
</form>
</body>
</html>

Note: An HTML form is different from other forms you might produce with tools and programming languages. It is part of an HTML file, delimited by <FORM> and </FORM> tags, where someone can make choices and enter data, then transmit those choices to a server-side program using the CGI protocol. To produce a complete application using PSP, you might need to learn the syntax of <INPUT>, <SELECT>, and other HTML tags related to forms.

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:

• 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.
  • Make sure you use semicolons to terminate lines where required.

  • If a value must be quoted, quote it. You might need to enclose a single-quoted value (needed by PL/SQL) inside double quotes (needed by PSP).

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

• The next step is to run the PSP file by requesting its URL in a web browser. At this point, you might get an error that the file is not found.
  • Make sure you are requesting the right virtual path, depending on the way the web gateway is configured. Typically, the path includes the hostname, optionally a port number, the schema name, and the name of the stored procedure (with no .psp extension).

  • Remember, 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 new scripts in a separate schema until they are ready, 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 new file name.

  • Once you get one file-not-found error, make sure to request the latest version of the page the next time. The error page might be cached by the browser. You might need to press Shift-Reload in the browser to bypass its 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 tricky part is to set up the interface between different HTML forms, scripts, and CGI programs so that all the right values are passed into your page. The page might return an error because of a parameter mismatch.
  • To see exactly what is being passed to your page, use METHOD=GET in the calling form so that the parameters are visible in the URL.

  • Make sure that the form or CGI program that calls 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, the PSP file must declare equivalent parameters.

  • Make sure 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.

  • Make sure that the URL's query string consists of name-value pairs, separated by equals signs, especially if you are passing parameters by constructing a hardcoded 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 calling form without changing your PSP file.

Putting an Application using PL/SQL Server Pages into Production

When you start developing an application with PSP, you may spend most of your time getting the logic correct in the script. Before putting the application into production, consider other 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. That is another string you might store 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 viewer 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.

  • Generally, if you set one color (such as the foreground text color), you should 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 some other method instead of or in addition to the color change. For example, you might put a graphic icon next to special items in a table. Some of your viewers may see your page on a monochrome screen, or on browsers that cannot represent different colors. (Such browsers might fit in a shirt pocket and use a stylus for input.)

• 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 routine can detect incorrect data and prompt the user to correct it before they press the Submit button and make a call to the database.

• Browsers tend to be lenient when displaying incorrect HTML. But what looks OK in one browser might look bad or might not display at all in another browser.
  • 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 should 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.