Unit 12.4. Developing PSPs

Whether you start with an existing stored procedure or an existing Web page, you see that with just a few changes and additions, you can create dynamic Web pages that perform database queries and other operations, and display the results from a Web browser. First things first, the file for a PL/SQL server page must have the extension .psp. Table 12.1 provides a summary for the four types of PSP Server Page elements.

PSP Directives

A PSP directive is simply a statement that provides the PSP compiler information about the page it is about to load into the database. Here is the syntax for a PSP directive:

<%@ directive attribute="value" [ attribute="value" ... ] %> 

Don’t forget the % inside the <, and just before the >. This differentiates a PSP tag from an HTML tag to the compiler.

As Table 12.2 illustrates, there are currently four directives available.

PSP Page Directives

As Table 12.3 illustrates, the page directive can be created with three possible attributes.

This directive is for compatibility with other scripting environments. Here is the syntax for the page directive:

<%@ page [language="programming language"][contentType="content 
type"] [errorPage="error page"] %> 

and a sample page directive is:

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

The attribute names contentType and errorPage are case-sensitive.

Watch your typing! Accidentally inserting a space on either side of your equal signs (=) will cause a compiler error. Type both operands flush against the = operator.

XML, JPEG, GIF, TEXT, or Other Document Types

The PL/SQL gateway’s default behavior for transmitting files is to transmit them as HTML documents, so that the browser renders them according to the HTML tags. If you’d like the browser to interpret the document as XML, plain text (with no formatting), or some other document type, include a <%@ page contentType="MIMEtype " %> directive. 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. Though 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, like a Perl or Java application.

Handling Different Character Sets

The PL/SQL gateway’s default behavior for transmitting files is also to transmit files using the character set defined by the Web gateway. If you’d like to convert the data to a different character set for displaying in a browser, include a <%@ page charset="charset_encoding " %> directive. Specify UTF-8 (Unicode), Shift_JIS, Big5, or some other encoding that the browser or other client program recognizes. Remember to also configure the character set setting in the database access 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, your current Web browser may be set up to display Western European (Windows) encoding.

Handling Errors

Any HTML errors are handled by the Web browser. The PSP loading process does not check for HTML coding errors. If you make a syntax error in the PL/SQL code, the loader stops and you must fix the error before continuing.

Any previous version of the stored procedure can be erased when you attempt to replace it and the script contains a syntax error.

You can include PL/SQL exception-handling code within a PSP file to handle database errors that occur when the script runs and therefore have any unhandled exceptions bring up a special page. The page for unhandled exceptions is another PL/SQL Server Page with the 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 using the loadpsp utility.

When you create such an error page for one of your procedures, you must take care when loading the procedure PSP and its error page PSP into the database. In your loadpsp utility command, you must specify the name of the error page PSP before the name of the procedure PSP the error page is to support. Unit 12.3, “Loading PSPs into the Database,” explains how to do this.

PSP Procedure Directives

The procedure directive can currently be created with only one possible attribute: the name of your procedure. It specifies the name of the stored procedure produced by the PSP file. By default, the name is the filename without the .psp extension.

And a sample procedure directive is:

<%@ plsql procedure="search_student" %> 

There is a new keyword of sorts here: “plsql.” It’s just hanging out, by itself, or so it would seem. This keyword must appear in both the procedure directive (if specified) and the parameter directive (introduced next), or you will receive a compile error.

PSP Parameter Directives

The parameter directive, like the page directive, can currently be created with three possible attributes, as illustrated in Table 12.4.

And a sample parameter directive is:

<%@ plsql parameter="p_student_id" type="number" default="null" %> 

<%@ plsql parameter=”p_last_name” 
type=”varchar2” default=”’Patel’” %> 

The parameters are passed using name–value pairs, typically from an HTML form. The values for the default 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. The URL that calls this page would typically look like the following:

http://myserver/pls/any/search_student?p_student_id=120 

Adding another parameter requires adding another parameter directive. Consider the following statements:

<%@ plsql parameter="p_student_id" type="number" %> 
<%@ plsql parameter="p_first_name" type="varchar2" 
default="'Seema'" %> 

Then the URL that calls this page (from a form, using METHOD=“get,” remember the “post” method places parameters in the HTTP Header and they will therefore not display as part of the query string in the URL) would look like the following:

http://myserver/pls/any/search_student?p_student_id=120&first_name='Seema' 

In this URL, the /pls/ portion indicates that the Apache Web Server is to dispatch this request to the mod_plsql module to process this request. The /any/ indicates which DAD to use, and the search_student part is the procedure being requested. By the time this PSP is invoked through a Web client, it has already been loaded into the Oracle database through the loadpsp utility, so the .psp extension is not needed. It is not part of the procedure name inside the database.

If you are using anchored data types, be careful with the length of those types. The TYPE attribute can currently only handle a maximum of 30 characters, including the %TYPE appendix. For example, the following statement:

type="student.student_firstname%TYPE" 

does not impede your PSP file from compiling as long as there is a column called “student_firstname” in the STUDENT table. The value for the TYPE attribute specified in this example is exactly 30 characters long. However, the following statement:

type="student.student_first_name%TYPE" 

which happens to supply a value to the TYPE attribute that is 31 characters long, raises the following Oracle error message during the execution of the loadpsp command:

ORA-06502: PL/SQL: numeric or value error: character 
string buffer too small 

If you avoid creating long table and column names for your database objects, this message should not appear frequently when you are loading your PSPs into the database.

Last but not least, the include directive, like the procedure directive, can currently be created with only one possible attribute: the file name to be included with your PSP procedure. This file can be composed of any browser-compatible material, for example, HTML, XML, and JavaScript. It 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. Substitutions are performed once, at the time of loading/creating, not each time the page is served by the 9iAS.

Include a <%@ include file="name_of_include " %> 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 (loadpsp utility), you must include the names of all included files. 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, background image, page footer, toolbar, and so on, 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. You must specify exactly the same name in both the include directive and in the loadpsp command, including any relative pathname such as ../include_dir/. And a sample include directive is:

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

The following sample code illustrates creating a PSP that is a simple HTML page with an image file of type GIF.

<%@ page contentType="image/gif" %> 
<% 
-----------------------------------------------------
-- FILENAME:     include_gif.psp 
-- FILEDATE:     02.11.2002 
-- CREATED BY:   Melanie Caffrey 
-- DESCRIPTION:  Simple HTML Page with Image 
-- MODIFIED: 
-----------------------------------------------------
%> 
<HTML> 
<HEAD> 
<TITLE>Include GIF</TITLE> 
</HEAD> 
<BODY BGCOLOR="#99CCCC"> 
<IMG SRC="my_image.gif", ALT="My Image", BORDER=0> 
</BODY> 
</HTML> 

Then in the following piece of example code, the PSP just created, include_gif.psp, is included as an include file inside another PSP.

<%@ page language="PL/SQL"%> 
<%@ plsql procedure="splash_page" %> 
<% 
-----------------------------------------------------
-- FILENAME:     splash_page.psp 
-- FILEDATE:     02.11.2002 
-- CREATED BY:   Melanie Caffrey 
-- DESCRIPTION:  Splash Page Using Include Directive 
--               with Include File of Type PSP 
-- MODIFIED: 
-----------------------------------------------------
%> 
<HTML> 
<HEAD> 
<TITLE>Splash Page</TITLE> 
</HEAD> 
<BODY BGCOLOR="#99CCCC"> 
<%@ include file="include_gif.psp" %> 
</BODY> 
</HTML> 

If you prefer to call a PL/SQL procedure, it might be easier than using an include file. Unlike the case with include files, changes to the called PL/SQL code will be reflected each time the PSP is served. To call a PL/SQL procedure in a PSP, you can use the following syntax:

<% student_personal_info; %> 

PSP Declarations

A PSP declaration is just like the declaration section of a PL/SQL code block. It is the place where you define variables, constants, and cursors. PSP declarations are optional, in that variables, constants, and cursors can also be defined in PSP scriptlets (defined next). However, your code will be much more manageable if you remember to include a PSP declaration and define the corresponding PL/SQL elements within it accordingly.

A PSP declarative block comes in especially handy if you wish to use global variables within the script. All the usual PL/SQL syntax is allowed within the block. The delimiters, to an extent, serve as shorthand, allowing you to omit the DECLARE keyword. All the declarations are available to the code later on in the file. You can also use explicit DECLARE blocks within the scriptlet <% %> delimiters that are explained later. These declarations are only visible to the following BEGIN/END block.

The basic syntax is:

<%! variable_namevariable_datatype(variable_datatype_size) 
%> 

So, a declarative statement might look like the following:

<%! 
student_id NUMBER; 
city VARCHAR2(60); 
zipcode VARCHAR2(10); 
   %> 

Note the exclamation point within this PSP element!

The order and placement of the PSP directives and declarations is not significant in most cases—only when another file is being included. However, for ease of maintenance, this book recommends that you place the directives and declarations together near the beginning of the file.

PSP Scriptlets

Any PL/SQL or SQL code that one would normally see placed between a BEGIN and END statement in a PL/SQL code block can be embedded inside the scriptlet element. The basic syntax of a scriptlet element is:

<% PL/SQL script %> 

This element doesn’t require any characters after the first % sign. No @ and no ! are required!

So, deeply embedded within lots of HTML code, a PSP can include PL/SQL code with a scriptlet similar to the following:

<% FOR rec IN student_cursor LOOP %> 

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.

PSP Expressions

Using this element is similar to what you write in PL/SQL when you wish to include an actual database value inside a quoted string that is fed to, for example, the DBMS_OUTPUT.PUT_LINE procedure. (NOTE: For more information on the Oracle built-in DBMS_OUTPUT.PUT_LINE procedure, refer to the Oracle PL/SQL Interactive Workbook by Ben Rosenzweig and Elena Silvestrova.) For instance, when using the DBMS_OUTPUT.PUT_LINE procedure, you may write a line of code that looks similar to the following:

DBMS_OUTPUT.PUT_LINE('The Student ID value for David 
Ricciardi is:  '||v_student_id||'.'), 

In this line, the actual database value is included by means of concatenation. The actual value for the student ID is contained in the variable v_student_id. It is concatenated into the textual string when it is placed between two sets of double pipe, || ||, delimiters. At runtime, the actual database value contained in v_student_id is placed inside the string and output to the screen by the DBMS_OUTPUT.PUT_LINE procedure. For example, the result of running this line of code may look similar to the following:

The Student ID value for David Ricciardi is 123. 

Similarly, to include a value that depends upon the result of a PL/SQL expression, include the expression within the delimiters <%= %>. Due to the fact that 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 cast, such as DATE, the value must then be passed to the PL/SQL TO_CHAR function.

The content between the <%= %> delimiters is processed by the HTP.PRN procedure, which trims any leading or trailing whitespace and requires that you quote any literal 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 '. Thankfully, most characters and character sequences can be included in a PSP file without being changed by the PSP loader.

An example of the general syntax for a PSP expression element is:

<%= a PL/SQL expression %> 

Note the equals (=) sign within this PSP element!

And an example of using the expression element when, for instance, your user is asked to choose a student from a drop-down (HTML SELECT list), is:

<% FOR rec IN student_cursor LOOP %> 
<OPTION VALUE="<%= rec.student_id %>"><%= rec.last_name 
%>, <%= rec.first_name> </OPTION> 
<% END LOOP; %> 

Since the = sign in this element is not part of a name/value pair, it is not necessary to ensure that it is written flush against the value supplied. To make the expression element code clear, this book recommends that you leave a single space between the = sign and the expression value. However, the = sign should be written flush against the % character immediately to its left.

Using Comments in PSP Files

To put a comment in the HTML portion of a PL/SQL Server Page for the benefit of people reading the PSP source code, use the syntax:

<%-- Comment text for people reading the PSP source code --%> 

These comments do not appear in the HTML output (source code) from the PSP.

To create a comment that is visible in the HTML output, place the comment in the HTML portion of your PSP and use the regular HTML comment syntax:

<!-- Comment text for people reading the HTML source code--> 

To include a comment inside a PL/SQL block within a PSP, you can use the normal PL/SQL comment syntax.

Start Coding PSPs

The following PSP file provides an example of much of what has been discussed in this chapter thus far:

1 <%@ page language="PL/SQL" %> 
2 <%@ plsql procedure="student_personal_info" %> 
3 <%@ plsql parameter="p_first_name" default="null" %> 
4 <%@ plsql parameter="p_last_name" default="null" %> 
5 <% 
6 ------------------------------------------------------
7  -- FILENAME:     student_personal_info.psp 
8  -- FILEDATE:     7.15.2001 
9  -- CREATED BY:   Melanie Caffrey 
10 -- DESCRIPTION:  Student Personal Info 
11 -- MODIFIED: 
12 -- This PSP is specified as using the PL/SQL language 
13 -- in the page directive. 
14 -- The name, student_personal_info is provided in the 
15 -- procedure directive. 
16 -- This procedure takes two input parameters, (PSPs 
17 -- do not currently provide the ability for creating 
18 -- output parameters), p_first_name and p_last_name. 
19 -- The default value for both of these input 
20 -- parameters is null.  And, if it isn't obvious by 
21 -- now, you see that one can create comments inside 
22 -- PSP scriptlet, <% %> tags. 
23 -- Below, several variables are declared between PSP 
24 -- declarative tags to hold information about the 
25 -- particular student your user is editing (updating) 
26 -- or entering (inserting). 
27 %> 
28 <%! v_student_id        student.student_id%TYPE; 
29     v_salutation        student.salutation%TYPE; 
30     v_first_name        student.first_name%TYPE; 
31     v_last_name         student.last_name%TYPE; 
32     v_street_address    student.street_address%TYPE; 
33     v_city              zipcode.city%TYPE; 
34     v_state             zipcode.state%TYPE; 
35     v_zip               student.zip%TYPE; 
36     v_phone             student.phone%TYPE; 
37     v_employer          student.employer%TYPE; 
38     v_registration_date 
39                       student.registration_date%TYPE; 
40     v_count             INTEGER := 0; 
41 %> 
42 <%! 
43 -- One can also create comments inside of PSP 
44 -- declarative, tags. 
45 -- The below SELECT statement obtains information 
46 -- about the student whose first and last name match 
47 -- that of the input parameters. (Later in this 
48 -- book, you create PSPs that handle the situation 
49 -- where more than one person exists in the 
50 -- STUDENT table with the same first name/last name 
51 -- combination.) 
52 -- The information for that particular student is 
53 -- used to populate the variables declared above. 
54 %> 
55 <% 
56   SELECT COUNT(*) 
57     INTO v_count 
58     FROM student 
59    WHERE first_name = p_first_name 
60      AND last_name  = p_last_name; 
61 
62      IF v_count > 0 
63      THEN 
64 
65   SELECT student_id, 
66          salutation, 
67          first_name, 
68          last_name, 
69          street_address, 
70          city, 
71          state, 
72          s.zip, 
73          phone, 
74          employer, 
75          registration_date 
76     INTO v_student_id, 
77          v_salutation, 
78          v_first_name, 
79          v_last_name, 
80          v_street_address, 
81          v_city, 
82          v_state, 
83          v_zip, 
84          v_phone, 
85          v_employer, 
86          v_registration_date 
87     FROM student s, zipcode z 
88    WHERE first_name = p_first_name 
89      AND last_name  = p_last_name 
90      AND s.zip      = z.zip; 
91 
92    ELSE 
93 
94         SELECT MAX(student_id) + 1 
95           INTO v_student_id 
96           FROM student; 
97 
98      END IF; 
99 %> 
100 
101 <% 
102 -- Note the use of the COUNT function used to check 
103 -- how many students exist in the STUDENT table with 
104 –- the first name and last name entered in the 
105 -- p_first_name and p_last_name parameters. 
106 -- The v_count variable is initialized to a value of 
107 -- zero (0) when it is declared. 
108 -- Since the COUNT function always returns some 
109 -- value, even if it is zero (0), it provides a way 
110 -- to sidestep the formidable NO DATA FOUND error 
111 -- message when preparing to perform a SELECT 
112 -- statement. 
113 -- IF the COUNT function returns a value of zero, 
114 –- there is no need to perform the subsequent SELECT 
115 –- statement from the STUDENT table, since doing so, 
116 -- if the value of v_count is zero, will surely 
117 -- result in a NO DATA FOUND message. 
118 -- Therefore, it is better to check first, then 
119 –- provide an alternative piece of logic in your 
120 -- code if no such student exists. 
121 -- If no such student exists, then this example 
122 –- assumes that both input parameters have been left 
123 -- with their default values of null, and therefore, 
124 -- the user wishes to create (insert) a new user. 
125 -- The ELSE portion of the above conditional 
126 –- statement readies the code for such a situation 
127 -- by obtaining a new student ID value from the 
128 -- database using the MAX function. 
129 %> 
130 <HTML> 
131 <HEAD> 
132 <TITLE>Student Personal Info</TITLE> 
133 </HEAD> 
134 <BODY BGCOLOR="#99CCCC"> 
135 <% 
136 -- Display the student ID, first name and last name 
137 -- values for the entered student, if available, as 
138 –- a heading.  Otherwise, display a default heading. 
139 %> 
140 <% IF v_count > 0 
141   THEN 
142 %> 
143 <H2>Personal Information For Student <%= 
144 v_student_id %> - <%= v_first_name %> <%= 
145 v_last_name %></H2> 
146 <% ELSE %> 
147 <H2>Personal Information For New Student</H2> 
148 <% END IF; %> 
149 <% 
150 -- Paint the form using the values from the 
151 –- variables populated with the SELECT statement, if 
152 -- available. 
153 %> 
154 <FORM NAME="student_personal_form" 
155 ACTION="update_student_info" METHOD="post"> 
156 <INPUT TYPE="hidden" NAME="p_student_id" VALUE="<%= 
157 v_student_id %>"> 
158 <TABLE> 
159 <TR> 
160 <TD>Salutation:</TD> 
161 <TD><INPUT TYPE="text" NAME="p_salutation" 
162 VALUE="<%= v_salutation %>"></TD> 
162 </TR> 
163 <TR> 
164 <TD>First Name:</TD> 
165 <TD><INPUT TYPE="text" NAME="p_first_name" 
166 VALUE="<%= v_first_name %>"></TD> 
167 </TR> 
168 <TR> 
169 <TD>Last Name:</TD> 
170 <TD><INPUT TYPE="text" NAME="p_last_name" VALUE="<%= 
171 v_last_name %>"></TD> 
172 </TR> 
173 <TR> 
174 <TD>Street Address:</TD> 
175 <TD><INPUT TYPE="text" NAME="p_street_address" 
176 VALUE="<%= v_street_address %>"></TD> 
177 </TR> 
178 <% IF v_count > 0 
179   THEN 
180 %> 
181 <TR> 
182 <% 
183 -- If your user is editing an existing student's 
184 -- personal information, then display city and state 
185 -- information for that student, and ensure that the 
186 -- button to change zipcode information reads 
187 -- "Change Zipcode."  Otherwise, generically display 
188 -- the words "City, State and Zipcode", and create 
189 -- the button to change zipcode information with the 
190 –- displayed value, "Select City, State and 
191 -- Zipcode." 
192 %> 
193 <TD><%= v_city %>, <%= v_state %> </TD> 
194 <TD><INPUT TYPE="text" NAME="p_zip" VALUE="<%= v_zip 
195 %>"></TD> 
196 <TD><INPUT TYPE="button" VALUE="Change Zipcode" 
197 onClick="javascript:void(window.open(' 
198 student_zipcode?p_student_id=<%= v_student_id 
199 %>&p_zip=<%= v_zip %>', 'zipwindow', 'width=740, 
200 height=550, scrollbars=yes, resizable=yes'));"></TD> 
201 </TR> 
202 <% ELSE %> 
203 <TR> 
204 <TD>City, State and Zipcode</TD> 
205 <TD><INPUT TYPE="text" NAME="p_zip" VALUE="<%= v_zip 
206 %>"></TD> 
207 <TD><INPUT TYPE="button" VALUE="Select City, State 
208 and Zipcode" 
209 onClick="javascript:void(window.open(' 
210 student_zipcode?p_student_id=<%= v_student_id 
211 %>&p_zip=<%= v_zip %>', 'zipwindow', 'width=740, 
212 height=550, scrollbars=yes, resizable=yes'));"></TD> 
213 </TR> 
214 <% END IF; %> 
215 <TR> 
216 <TD>Phone:</TD> 
217 <TD><INPUT TYPE="text" NAME="p_phone" VALUE="<%= 
218 v_phone %>"></TD> 
219 </TR> 
220 <TR> 
221 <TD>Employer:</TD> 
222 <TD><INPUT TYPE="text" NAME="p_employer" VALUE="<%= 
223 v_employer %>"></TD> 
224 </TR> 
225 <TR> 
226 <TD>Registration Date:</TD> 
227 <TD><INPUT TYPE="text" NAME="p_registration_date" 
228 VALUE="<%= v_registration_date %>"></TD> 
229 </TR> 
230 <TR> 
231 <TD></TD> 
232 <TD ALIGN="left"><INPUT TYPE="submit" 
233 VALUE="Submit">   <INPUT TYPE="reset" VALUE="Reset"> 
234 </TD> 
235 </TR> 
236 </TABLE> 
237 </BODY> 
238 </HTML> 

The above script at first looks like any other ordinary HTML file. What makes it different from a plain HTML source file is the embedded PSP scripting tags outlined in boldface. Taking a look at each line of this file where boldface exists you can see how PSP enables this HTML static Web form to provide dynamic database content to the user.

The first four lines provide the PSP directives. Every directive but the include directive is represented in this file. Next, the file documentation is enclosed as one long bit of commented text inside a scriptlet tag <% ... %>. A PSP declarative block immediately follows the file documentation and is represented as a list of variable declarations enclosed within the declarative <%! ... %> tags. All of these variables are global to this procedure.

Take careful note of the scriptlet tags and expression tags interspersed throughout the PSP file. Any PL/SQL code can be placed within these tags to query an Oracle database and return dynamic data to each point in the file where this code is called. In other words, the personal information for a fictitious student in the above student_personal_info PSP procedure can be changed dynamically based on the first_name and last_name parameter values entered into the interface for this PSP.

Remember that you can embed other technologies inside a PSP, as evidenced by the inclusion of JavaScript code in this PSP, which can, in turn, call another PSP or a PL/SQL Web Toolkit procedure, as is being done with a call to a procedure named “student_zipcode” from the student_personal_info PSP. To produce an elaborate HTML file, 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. Consider the following JavaScript code from the student_personal_info PSP file:

<TD><INPUT TYPE="button" VALUE="Change Zipcode" 
onClick="javascript:void(window.open('student_zipcode?p_student_id= 
<%= v_student_id %>&p_zip=<%= v_zip %>', 'zipwindow', 'width=740, 
height=550, scrollbars=yes, resizable=yes'));"></TD> 

Since JavaScript allows for mixing of double quotes with single quotes, and vice versa, this code is considerably easier to follow than its PL/SQL Web Toolkit counterpart, which would resemble the following code example:

htp.p('<TD><INPUT TYPE="button" VALUE="Change Zipcode" 
onClick="javascript:void(window.open(''student_zipcode?p_student_id= 
'||v_student_id||'&p_zip='||v_zip||''', ''zipwindow'', ''width=740, 
height=550, scrollbars=yes, resizable=yes''));"></TD>'), 

As you can see, coding this procedure as a PL/SQL Web Toolkit procedure results in lines with doubled single quotes, which makes the embedded JavaScript code harder to read.

Debugging PSP

As you begin developing with PSP, keep these guidelines in mind when you encounter errors or other problems:

• You must have all the PL/SQL syntax and PSP directive syntax right. If you make a mistake here, your file does not compile.

  • Quote your values. You might need to enclose a single-quoted value (needed by PL/SQL) inside double quotes (needed by PSP), as may be the case with specifying values for parameters.

  • Make sure you use semicolons to terminate lines where required.

  • Mistakes in the PSP directives are usually reported through PL/SQL syntax error messages. Ensure that your directives use the right syntax, that directives are closed properly (it’s both easy and common to forget a %> tag), and that you are using the right element (expression, declaration, or scriptlet) depending on what should be placed inside it.

  • PSP attribute names are case-sensitive. Most are specified in all lowercase, however, contentType and errorPage must be specified as mixed-case.

• 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 how 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 (remember to not include a .psp extension when you type the name of the stored procedure.)

  • 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 should 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 file 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 (or, Ctrl-R) in the browser to fully refresh the page and bypass the browser’s cache.

If you are using Internet Explorer, it is a good idea to ensure that the page is fully refreshed with each visit. You can set this option automatically in your browser. Click on the “Tools” menu option and select “Internet Options.” Select the “General” tab and click the button that reads “Settings.” In the radio button selection that allows you to “Check for newer versions of stored pages,” select the option that reads “Every visit to the page.”

• 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 and scripts 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 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 a form includes any hidden input fields, or mistakenly uses the NAME= attribute on the Submit or Reset buttons, the PSP file handling that form 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 (p_student_id=123), and that those name–value pairs in turn are separated with ampersands (p_student_id=123&p_first_name=Mary), especially if you are passing parameters by constructing a hard-coded link to the page.

  Try to avoid unnecessary whitespace. Spaces on either side of the equals (=) sign separating a name–value pair can potentially break the query string, particularly when you are using JavaScript. This is why it is good coding practice to always surround the values passed to the HTML VALUE attribute with double quotes, that is, <INPUT TYPE=“text”VALUE=“My text here”>.

  
  
  
  

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

Occasionally, PSP compilation error messages may not provide descriptive information about PL/SQL coding errors. When a PSP is loaded into the database, it is loaded as a PL/SQL Web Toolkit procedure. If the PSP is loaded into the database without compiling successfully, you can view the source code of the PSP through SQL*Plus by using a SELECT statement similar to the following:

SELECT line, text 
  FROM user_source 
WHERE name = <PSP Name>; 

Substitute the PSP name in brackets, <>, with your PSP name. Try to compile this procedure again (using the code returned from the above SQL statement because you cannot compile PSPs with SQLPlus.) You may find that you receive more descriptive and helpful error messages about mistakes in your PL/SQL code.

A Few Noteworthy Items

Using OWA_UTIL.SHOWSOURCE

If you use the OWA_UTIL.SHOWSOURCE utility procedure to view the source code of your PSP procedure once you’ve loaded it into the database, you will notice something worth mentioning. Your code, once primarily PL/SQL code embedded in HTML tags, is converted by the modplsql module of the Apache Web Server to PL/SQL Web Toolkit commands. In other words, you cannot see PSP directives in the source file of a procedure stored in the database. The only place to see these directives is in the original text file that was used to create the PSP. You learn more about the OWA_UTIL.SHOWSOURCE procedure in Chapter 16, “The OWA_UTIL Package: Dynamic SQL, HTML, Date Utilities, and More.”

Packaging PSPs

To share procedures, constants, and types across different PL/SQL server pages, you can compile them into a separate package in the database using a normal PL/SQL source code file. Although you can reference packaged procedures, constants, and types from PSP scripts, the PSP scripts can only produce standalone procedures, not packages. There is currently no way to package your PSPs. Each procedure created as a PSP is stored individually in the database.

Out Parameters

There is currently no way to pass output parameters using PSPs. If you must use output parameters, then you must convert your stored procedure to a PL/SQL Web Toolkit procedure.

Unit 12.4 Exercises

Unit 12.4 Exercise Answers

1 <%@ page language="PL/SQL" 
2 errorPage="search_student_error.psp" %> 
3 <%@ plsql procedure="search_student" %> 
4 <% 
5  -------------------------------------------------------
6  -- FILENAME: search_student 
7  -- FILEDATE: 02.02.2002 
8  -- CREATED BY: Melanie Caffrey 
9  -- DESCRIPTION: Search Student 
10 -- URL: http://localhost/pls/any/search_student 
11 -------------------------------------------------------
12 %> 
13<HTML> 
14 <HEAD> 
15 <TITLE>Search Student</TITLE> 
16 </HEAD> 
17 <BODY BGCOLOR="#99CCCC"> 
18 <P></P> 
19 <H2 ALIGN=center>Please enter your Student/Instructor 
20 Search criteria.</H2> 
21 <P></P> 
22 <FORM METHOD="post" ACTION="get_student" 
23 NAME="student_search"> 
24 <TABLE BORDER=0> 
25 <TR> 
26 <TD ALIGN="right"><FONT FACE="Arial">First 
27 Name</FONT></TD> 
28 <TD> 
29 <INPUT TYPE="text" NAME="p_first_name" SIZE="30" > 
30 </TD> 
31 </TR> 
32 <TR> 
33 <TD ALIGN="right"><FONT FACE="Arial">Last 
34 Name</FONT></TD> 
35 <TD> 
36 <INPUT TYPE="text" NAME="p_last_name" SIZE="30" > 
37 </TD> 
38 </TR> 
39 <TR> 
40 <TD></TD> 
41 <TD ALIGN="left"><INPUT TYPE="submit" VALUE="Search"> 
42 <INPUT TYPE="reset" VALUE="Reset"> 
43 </TD> 
44 </TR> 
45 </TABLE> 
46 </FORM> 
47 </BODY> 
48 </HTML> 

1 <%@ page language="PL/SQL" %> 
2 <%@ plsql procedure="get_student" %> 
3 <%@ plsql parameter="p_first_name" default="null" %> 
4 <%@ plsql parameter="p_last_name" default="null" %> 
5 <% 
6  --------------------------------------------------------
7  -- FILENAME:     get_student.psp 
8  -- FILEDATE:     02.02.2002 
9  -- CREATED BY:   Melanie Caffrey 
10 -- DESCRIPTION:  Get Student 
11 -- URL        :  http://local_host/pls/any/get_student 
12 --------------------------------------------------------
13 %> 
14 <%! CURSOR get_student IS 
15     SELECT first_name, last_name 
16       FROM student 
17      WHERE NVL(UPPER(first_name), 'QQ') LIKE 
18            NVL(UPPER('%'||p_first_name||'%'), 'QQ') 
19        AND UPPER(last_name)             LIKE 
20            NVL(UPPER('%'||p_last_name||'%'), 
21            UPPER(last_name)); 
22 
23 v_counter    INTEGER := 0; 
24 v_last_name  student.last_name%TYPE; 
25 v_first_name student.first_name%TYPE; 
26 %> 
27 <HTML> 
28 <HEAD> 
29 <TITLE>Get Student</TITLE> 
30 </HEAD> 
31 <BODY BGCOLOR="#99CCCC"> 
32 <% FOR rec IN get_student 
33   LOOP 
34      v_counter := v_counter + 1; 
35      v_last_name := rec.last_name; 
36      v_first_name := rec.first_name; 
37   END LOOP; 
38   IF v_counter > 1 
39   THEN 
40      student_list(p_first_name, p_last_name); 
41   ELSIF v_counter = 1 
42   THEN 
43      student_personal_info(v_first_name, v_last_name); 
44    ELSE 
45 %> 
46 <SCRIPT language="JavaScript"> 
47   alert("Sorry.  No Records Match Your Search 
48          Criteria."); 
49 </SCRIPT> 
50 <%  search_student; 
51    END IF; 
52 %> 
53 </BODY> 
54 </HTML> 

1 <%@ page language="PL/SQL" %> 
2 <%@ plsql procedure="student_list" %> 
3 <%@ plsql parameter="p_first_name" %> 
4 <%@ plsql parameter="p_last_name" %> 
5 <% 
6  --------------------------------------------------------
7  -- FILENAME:     student_list.psp 
8  -- FILEDATE:     02.02.2002 
9  -- CREATED BY:   Melanie Caffrey 
10 -- DESCRIPTION:  Student List 
11 -- URL        :  http://local_host/pls/any/student_list 
12 --------------------------------------------------------
13 %> 
14 <%! CURSOR get_student IS 
15     SELECT first_name, last_name 
16       FROM student 
17      WHERE NVL(UPPER(first_name), 'QQ') LIKE 
18            NVL(UPPER('%'||p_first_name||'%'), 'QQ') 
19        AND UPPER(last_name)             LIKE 
20            NVL(UPPER('%'||p_last_name||'%'), 
21            UPPER(last_name)) 
22     ORDER BY last_name, first_name; 
23 %> 
24 <HTML> 
25 <HEAD> 
26 <TITLE>Student List</TITLE> 
27 </HEAD> 
28 <BODY BGCOLOR="#99CCCC"> 
29 <CENTER> 
30 <H2>List of Students</H2> 
31 <TABLE BORDER="3" BORDERCOLOR="midnight blue" 
32  CELLPADDING="5"> 
33 <TR> 
34 <TH ALIGN=center>Student Names</TH> 
35 </TR> 
36 <% FOR rec IN get_student 
37    LOOP 
38 %> 
39 <TR> 
40 <TD> 
41 <A HREF="student_personal_info?p_first_name=<%= 
42  rec.first_name %>&p_last_name=<%= rec.last_name %>"> 
43 <FONT FACE="Arial"> 
44 <%= rec.last_name||', '||rec.first_name %> 
45 </FONT></A> 
46 </TD> 
47 </TR> 
48 <% END LOOP; %> 
49 </TABLE> 
50 </CENTER> 
51 </BODY> 
52 </HTML>