| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 1 (10.1) Part Number B10795-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Fundamentals 10g Release 1 (10.1) Part Number B10795-01 |
|
|
View PDF |
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 your customers.
This chapter discusses the following topics:
Web applications written in PL/SQL are typically sets of stored procedures that interact with Web browsers through the HTTP protocol. A set of interlinked dynamic HTML pages forms the user interface of a web application.
When a Web browser user visits a Web page, follows a hypertext link, or presses a Submit button on an HTML form, a URL is sent to the Web (HTTP) server, which causes the database server to run a stored procedure. Information the user provides in an HTML form is encoded in the URL. The URL also encodes information to identify which procedure, in which database, to call. The Web server passes this information along to the database as a set of parameters for the stored procedure.
The stored procedure that is invoked from a URL calls subprograms from the PL/SQL Web Toolkit. Typically, some of these subprograms, such as Htp.Print, prepare an HTML page that is displayed in the Web browser as a response to the user.
This process dynamically generates Web pages. Code running inside the database server produces HTML on the fly, so the generated Web page can vary depending on the database contents and the input parameters.
Dynamic generation of HTML is to be distinguished from dynamic HTML (DHTML). With DHTML, code in JavaScript or some other scripting language is downloaded to the browser along with HTML code, and this script code is processed by the browser.
DHTML can be coded by hand, so that it is static code, or it can itself be generated by program. That is, the generation of Web pages using a database can be combined with downloading JavaScript or other DHTML script code. A PL/SQL Web application can dynamically create complex DHTML that would be tedious to produce manually. A typical stored procedure might print some header information, issue a database query, and loop through the result set, formatting the data in an HTML list or table.
This program flow is very similar to a Perl script that operates on a text file. CGI scripts and programs running on a Web server are often used to dynamically produce Web pages, but they are usually not the best choice for interacting with a database. Using PL/SQL stored procedures has the advantage of providing all the power and flexibility of database processing: it supports DML statements, dynamic SQL, and cursors. It also eliminates the memory overhead of forking a new CGI process to treat each URL.
A Web browser-based application can be implemented entirely in PL/SQL using the following Oracle Database components:
In Oracle Database 10g, mod_plsql is a plug-in to Oracle HTTP Server that implements PL/SQL Gateway (mod_plsql is also supplied as part of Oracle Application Server).
PL/SQL Gateway provides support for deploying PL/SQL-based database applications on the World-Wide Web. It is part of Oracle HTTP Server (OHS), which ships with Oracle Application Server and Oracle Database.
As part of the Oracle HTTP Server, it is the job of PL/SQL Gateway to interpret a URL sent by a Web browser to a Web server, call the appropriate PL/SQL subprograms to treat the browser request, then return the generated response to the browser. Typically, PL/SQL Gateway responds to a Web-browser HTTP request by constructing an HTML page to display. There are additional uses for the gateway, however. Here are two:
As a plug-in to Oracle HTTP Server, mod_plsql causes stored procedures to be executed in response to HTTP requests.
For each URL that is processed, mod_plsql either uses a database session from its connection pool, or creates a new session on the fly and pools it. In order that mod_plsql can invoke the appropriate database PL/SQL procedure in a URL-processing session, you must first configure a virtual path and associate that path with a Database Access Descriptor (DAD).
A DAD is a named set of configuration values that specify the information necessary to create a session for a specific database and a specific database user/password. This includes the database service name and the Globalization Support setting (language, currency symbol, and so on) for the session.
You can use PL/SQL Gateway to transfer files from a client machine to or from Oracle Database. You can upload and download text files or binary files.
To upload files, you must first define a document repository using the DAD configuration, and specify how to upload the content: as a BLOB or LONG RAW value. To initiate uploading, you define and submit a multipart/form-data form, following the RFC 1867 specification.
After you successfully upload a file, the procedure specified in the ACTION attribute of the multipart/form-data form is invoked. This invocation is similar to that of any regular PL/SQL Gateway procedure. Subsequently, you can download files that you have uploaded, delete uploaded files from the database, and read or write their attributes.
You can download a file from the database in several alternative ways:
wpg_docload.download_file(file_name) to download file file_name.wpg_docload.download_file(file_name) to download file file_name.BLOB from any database table. You do this by calling a PL/SQL procedure that streams the standard HTTP headers, such as mime-type and content-length, and then invokes wpg_docload.download_file(blob_name) to download BLOB blob_name.
To authenticate individual Web-browser users for database purposes, it would be cumbersome to provide a different Database Access Descriptor (DAD) and URL for each user. Typically, a single URL, corresponding to a single database user (schema), is used for all browser users.
This means that authentication of browser users is typically not performed by the database, but by the Web application. It is the application that determines if a given Web-browser user should have access to the database. It can also determine which database schema (hence which URL) to use for a given user. You use the OWA_CUSTOM package of the PL/SQL Web Toolkit to perform such custom user authentication in Web applications.
For example, suppose you have a purchasing application that is accessed by multiple third-party vendors. Instead of creating a separate schema for each vendor, load the application into a common schema where all users log in. Using OWA_CUSTOM, your application can authenticate each user, in any way it needs to. With OWA_CUSTOM, authentication is done only by the application, not by the database.
Custom authentication cannot be combined with dynamic username/password authentication; it needs to have a static username/password stored in the DAD configuration file. PL/SQL Gateway uses this DAD username/password to log in to the database.
Once mod_plsql is logged in, authentication control is passed back to the application, by calling an application-level PL/SQL hook. This callback function is implemented by the application developers. The value returned by the callback function determines whether authentication succeeds or fails: TRUE means success; FALSE means failure.
Depending on what kind of custom authentication is desired, you can place the authentication function in different locations:
For example, using Custom OWA, an authorization function might verify that a user has logged in as user guest with password welcome, or it might check the user's IP address to determine access.
To develop the stored procedures that are executed by PL/SQL Gateway at runtime, you use PL/SQL Web Toolkit: a set of PL/SQL packages that can be used to obtain information about an HTTP request; specify HTTP response headers, such as cookies, content-type, and mime-type, for HTTP headers; set cookies; and generate standard HTML tags for creating HTML pages.
Commonly used PL/SQL Toolkit packages are listed in Table 13-1.
| Package | Description |
|---|---|
|
|
|
|
|
Functions and procedures that enable the PL/SQL Gateway cache feature, to improve the performance of your PL/SQL Web application. You can use this package to enable expires-based and validation-based caching using the PL/SQL Gateway file system. |
|
|
Subprograms that send and retrieve HTTP cookies to and from a client Web browser. Cookies are strings a browser uses to maintain state between HTTP calls. State can be maintained throughout a client session or longer if a cookie expiration date is included. |
|
|
The authorize function used by cookies. See "Uploading and Downloading Files With PL/SQL Gateway". |
|
|
Subprograms that obtain the coordinates where a user clicked an image. Use this package when you have an image map whose destination links invoke a PL/SQL Gateway. |
|
|
Subprograms that impose database optimistic locking strategies, to prevent lost updates. Lost updates can otherwise occur if a user selects, and then attempts to update, a row whose values have been changed in the meantime by another user. |
|
|
Subprograms that perform string matching and string manipulation with regular expression functionality. |
|
|
Subprograms used by the PL/SQL Gateway for authenticating requests. |
|
|
Subprograms used by package |
|
|
Utility subprograms:
|
|
wpg_docload |
Subprograms that download documents from a document repository that you define using the DAD configuration. See "Uploading and Downloading Files With PL/SQL Gateway". |
Traditionally, PL/SQL Web applications have used function calls to generate each HTML tag for output, using the PL/SQL Web toolkit packages that come with Oracle Database:
owa_util.mime_header('text/html'); htp.htmlOpen; htp.headOpen; htp.title('Title of the HTML File'); htp.headClose; htp.bodyOpen( cattributes => 'TEXT="#000000" BGCOLOR="#FFFFFF"'); htp.header(1, 'Heading in the HTML File'); htp.para; htp.print('Some text in the HTML file.'); htp.bodyClose; htp.htmlClose;
You can learn the API calls corresponding to each tag, or just use some of the basic ones like HTP.PRINT to print the text and tags together:
htp.print('<html>'); htp.print('<head>'); htp.print('<meta http-equiv="Content-Type" content="text/html">'); htp.print('<title>Title of the HTML File</title>'); htp.print('</head>'); htp.print('<body TEXT="#000000" BGCOLOR="#FFFFFF">'); htp.print('<h1>Heading in the HTML File</h1>'); htp.print('<p>Some text in the HTML file.'); htp.print('</body>'); htp.print('</html>');
This chapter introduces an additional method, PL/SQL server pages, that lets you build on your knowledge of HTML tags, rather than learning a new set of function calls.
In an application written as a set of PL/SQL server pages, you can still use functions from the PL/SQL Web toolkit to simplify the processing involved in displaying tables, storing persistent data (cookies), and working with CGI protocol internals.
To be useful in a wide variety of situations, a Web application must be interactive enough to allow user choices. To keep the attention of impatient Web surfers, you should streamline the interaction so that users can specify these choices very simply, without a lot of decision-making or data entry.
The main methods of passing parameters to PL/SQL Web applications are:
Submit button on the page.List boxes and dropdown lists are implemented using the same HTML tag (<SELECT>).
Use a list box for a large number of choices, where the user might have to scroll to see them all, or to allow multiple selections. List boxes are good for showing items in alphabetical order, so that users can find an item quickly without reading all the choices.
Use a dropdown list for a small number of choices, or where screen space is limited, or for choices in an unusual order. The dropdown captures the first-time user's attention and makes them read the items. If you keep the choices and order consistent, users can memorize the motion of selecting an item from the dropdown list, allowing them to make selections quickly as they gain experience.
Radio buttons pass either a null value (if none of the radio buttons in a group is checked), or the value specified on the radio button that is checked.
To specify a default value for a set of radio buttons, you can include the CHECKED attribute in one of the INPUT tags, or include a DEFAULT clause on the parameter within the stored procedure. When setting up a group of radio buttons, be sure to include a choice that indicates "no preference", because once the user selects a radio button, they can still select a different one, but they cannot clear the selection completely. For example, include a "Don't Care" or "Don't Know" selection along with "Yes" and "No" choices, in case someone makes a selection and then realizes it was wrong.
Checkboxes need special handling, because your stored procedure might receive a null value, a single value, or multiple values:
All the checkboxes with the same NAME attribute make up a checkbox group. If none of the checkboxes in a group is checked, the stored procedure receives a null value for the corresponding parameter.
If one checkbox in a group is checked, the stored procedure receives a single VARCHAR2 parameter.
If more than one checkbox in a group is checked, the stored procedure receives a parameter with the PL/SQL type TABLE OF VARCHAR2. You must declare a type like this, or use a predefined one like OWA_UTIL.IDENT_ARR. To retrieve the values, use a loop:
CREATE OR REPLACE PROCEDURE handle_checkboxes ( checkboxes owa_util.ident_arr ) AS BEGIN ... FOR i IN 1..checkboxes.count LOOP htp.print('<p>Checkbox value: ' || checkboxes(i)); END LOOP; ... END; / show errors;
Entry fields require the most validation, because a user might enter data in the wrong format, out of range, and so on. If possible, validate the data on the client side using dynamic HTML or Java, and format it correctly for the user or prompt them to enter it again.
For example:
Because you cannot always rely on such validation to succeed, code the stored procedures to deal with these cases anyway. Rather than forcing the user to use the Back button when they enter wrong data, display a single page with an error message and the original form with all the other values filled in.
For sensitive information such as passwords, a special form of the entry field, <INPUT TYPE=PASSWORD>, hides the text as it is typed in.
For example, the following procedure accepts two strings as input. The first time it is called, the user sees a simple form prompting for the input values. When the user submits the information, the same procedure is called again to check if the input is correct. If the input is OK, the procedure processes it. If not, the procedure prompts for new input, filling in the original values for the user.
-- Store a name and associated zip code in the database. CREATE OR REPLACE PROCEDURE associate_name_with_zipcode ( name VARCHAR2 DEFAULT NULL, zip VARCHAR2 DEFAULT NULL ) AS booktitle VARCHAR2(256); BEGIN -- Both entry fields must contain a value. The zip code must be 6 characters. -- (In a real program you would perform more extensive checking.) IF name IS NOT NULL AND zip IS NOT NULL AND length(zip) = 6 THEN store_name_and_zipcode(name, zip); htp.print('<p>The person ' || name || ' has the zip code ' || zip || '.'); -- If the input was OK, we stop here and the user does not see the form again. RETURN; END IF; -- If some data was entered, but it is not correct, show the error message. IF (name IS NULL AND zip IS NOT NULL) OR (name IS NOT NULL AND zip IS NULL) OR (zip IS NOT NULL AND length(zip) != 6) THEN htp.print('<p><b>Please re-enter the data. Fill in all fields, and use a 6-digit zip code.</b>'); END IF; -- If the user has not entered any data, or entered bad data, prompt for -- input values. -- Make the form call the same procedure to check the input values. htp.formOpen( 'scott.associate_name_with_zipcode', 'GET'); htp.print('<p>Enter your name:</td>'); htp.print('<td valign=center><input type=text name=name value="' || name || '">'); htp.print('<p>Enter your zip code:</td>'); htp.print('<td valign=center><input type=text name=zip value="' || zip || '">'); htp.formSubmit(NULL, 'Submit'); htp.formClose; END; / show errors;
One technique for passing information through a sequence of stored procedures, without requiring the user to specify the same choices each time, is to include hidden parameters in the form that calls a stored procedure. The first stored procedure places information, such as a user name, into the HTML form that it generates. The value of the hidden parameter is passed to the next stored procedure, as if the user had entered it through a radio button or entry field.
Other techniques for passing information from one stored procedure to another include:
You can use an HTML form to choose a file on a client system, and transfer it to the server. A stored procedure can insert the file into the database as a CLOB, BLOB, or other type that can hold large amounts of data.
The PL/SQL Web toolkit and the PL/SQL Gateways like mod_plsql have the notion of a "document table" that holds uploaded files.
By default, an HTML form must have a Submit button, which transmits the data from the form to a stored procedure or CGI program. You can label this button with text of your choice, such as "Search", "Register", and so on.
You can have multiple forms on the same page, each with its own form elements and Submit button. You can even have forms consisting entirely of hidden parameters, where the user makes no choice other than clicking the button.
Using JavaScript or other scripting languages, you can do away with the Submit button and have the form submitted in response to some other action, such as selecting from a dropdown list. This technique is best when the user only makes a single selection, and the confirmation step of the Submit button is not essential.
When an HTML form is submitted, your stored procedure receives null parameters for any form elements that are not filled in. For example, null parameters can result from an empty entry field, a set of checkboxes, radio buttons, or list items with none checked, or a VALUE parameter of "" (empty quotation marks).
Regardless of any validation you do on the client side, always code stored procedures to handle the possibility that some parameters are null:
DEFAULT clause in all parameter declarations, to prevent an exception when the stored procedure is called with a missing form parameter. You can set the default to zero for numeric values (when that makes sense), and use DEFAULT NULL when you want to check whether or not the user actually specifies a value.DEFAULT NULL declaration, check if it is null.Web applications are particularly concerned with the idea of state, the set of data that is current at a particular moment in time. It is easy to lose state information when switching from one Web page to another, which might result in asking the user to make the same choices over and over.
You can pass state information between dynamic Web pages using HTML forms. The information is passed as a set of name-value pairs, which are turned into stored procedure parameters for you.
If the user has to make multiple selections, or one selection from many choices, or it is important to avoid an accidental selection, use an HTML form. After the user makes and reviews all the choices, they confirm the choices with the Submit button. Subsequent pages can use forms with hidden parameters (<INPUT TYPE=HIDDEN> tags) to pass these choices from one page to the next.
If the user is only considering one or two choices, or the decision points are scattered throughout the Web page, you can save the user from hunting around for the Submit button by representing actions as hyperlinks and including any necessary name-value pairs in the query string (the part following the ? within a URL).
An alternative way to main state information is to use Oracle Application Server and its mod_ose module. This approach lets you store state information in package variables that remain available as a user moves around a Web site.
| See Also:
the Oracle Application Server documentation set at |
While built-in PL/SQL features are focused on traditional database operations and programming logic, Oracle Database provides packages that open up Internet computing to PL/SQL programmers.
You can send e-mail from a PL/SQL program or stored procedure using the UTL_SMTP package. You can find details about this package in the PL/SQL Packages and Types Reference.
The following code example illustrates how the SMTP package might be used by an application to send email. The application connects to an SMTP server at port 25 and sends a simple text message.
PROCEDURE send_test_message IS mailhost VARCHAR2(64) := 'mailhost.fictional-domain.com'; sender VARCHAR2(64) := 'me@fictional-domain.com'; recipient VARCHAR2(64) := 'you@fictional-domain.com'; mail_conn utl_smtp.connection; BEGIN mail_conn := utl_smtp.open_connection(mailhost, 25); utl_smtp.helo(mail_conn, mailhost); utl_smtp.mail(mail_conn, sender); utl_smtp.rcpt(mail_conn, recipient); -- If we had the message in a single string, we could collapse -- open_data(), write_data(), and close_data() into a single call to data(). utl_smtp.open_data(mail_conn); utl_smtp.write_data(mail_conn, 'This is a test message.' || chr(13)); utl_smtp.write_data(mail_conn, 'This is line 2.' || chr(13)); utl_smtp.close_data(mail_conn); utl_smtp.quit(mail_conn); EXCEPTION WHEN OTHERS THEN -- Insert error-handling code here NULL; END;
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 can find details about this package in the PL/SQL Packages and Types Reference. You use the results in calls to the UTL_TCP package.
You can open TCP/IP connections to machines on the network, and read or write to the corresponding sockets, using the UTL_TCP package. You can find details about this package in the PL/SQL Packages and Types Reference.
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. You can find details about this package in the PL/SQL Packages and Types Reference.
The UTL_HTTP package lets you:
GET or POST methods.UTL_HTTP through the ESCAPE and UNESCAPE functions in the UTL_URL package.Typically, developers have used Java or Perl to perform these operations; this package lets you do them with PL/SQL.
CREATE OR REPLACE PROCEDURE show_url ( url IN VARCHAR2, username IN VARCHAR2 DEFAULT NULL, password IN VARCHAR2 DEFAULT NULL ) AS req utl_http.req; resp utl_http.resp; name VARCHAR2(256); value VARCHAR2(1024); data VARCHAR2(255); my_scheme VARCHAR2(256); my_realm VARCHAR2(256); my_proxy BOOLEAN; BEGIN -- When going through a firewall, pass requests through this host. -- Specify sites inside the firewall that don't need the proxy host. utl_http.set_proxy('proxy.my-company.com', 'corp.my-company.com'); -- Ask UTL_HTTP not to raise an exception for 4xx and 5xx status codes, -- rather than just returning the text of the error page. utl_http.set_response_error_check(FALSE); -- Begin retrieving this Web page. req := utl_http.begin_request(url); -- Identify ourselves. Some sites serve special pages for particular browsers. utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0'); -- Specify a user ID and password for pages that require them. IF (username IS NOT NULL) THEN utl_http.set_authentication(req, username, password); END IF; BEGIN -- Start receiving the HTML text. resp := utl_http.get_response(req); -- Show the status codes and reason phrase of the response. dbms_output.put_line('HTTP response status code: ' || resp.status_code); dbms_output.put_line('HTTP response reason phrase: ' || resp.reason_phrase); -- Look for client-side error and report it. IF (resp.status_code >= 400) AND (resp.status_code <= 499) THEN -- Detect whether the page is password protected, and we didn't supply -- the right authorization. IF (resp.status_code = utl_http.HTTP_UNAUTHORIZED) THEN utl_http.get_authentication(resp, my_scheme, my_realm, my_proxy); IF (my_proxy) THEN dbms_output.put_line('Web proxy server is protected.'); dbms_output.put('Please supply the required ' || my_scheme || ' authentication username/password for realm ' || my_realm || ' for the proxy server.'); ELSE dbms_output.put_line('Web page ' || url || ' is protected.'); dbms_output.put('Please supplied the required ' || my_scheme || ' authentication username/password for realm ' || my_realm || ' for the Web page.'); END IF; ELSE dbms_output.put_line('Check the URL.'); END IF; utl_http.end_response(resp); RETURN; -- Look for server-side error and report it. ELSIF (resp.status_code >= 500) AND (resp.status_code <= 599) THEN dbms_output.put_line('Check if the Web site is up.'); utl_http.end_response(resp); RETURN; END IF; -- The HTTP header lines contain information about cookies, character sets, -- and other data that client and server can use to customize each session. FOR i IN 1..utl_http.get_header_count(resp) LOOP utl_http.get_header(resp, i, name, value); dbms_output.put_line(name || ': ' || value); END LOOP; -- Keep reading lines until no more are left and an exception is raised. LOOP utl_http.read_line(resp, value); dbms_output.put_line(value); END LOOP; EXCEPTION WHEN utl_http.end_of_body THEN utl_http.end_response(resp); END; END; / SET serveroutput ON -- The following URLs illustrate the use of this procedure, -- but these pages do not actually exist. To test, substitute -- URLs from your own Web server. exec show_url('http://www.oracle.com/no-such-page.html') exec show_url('http://www.oracle.com/protected-page.html') exec show_url('http://www.oracle.com/protected-page.html', 'scott', 'tiger')
Packages for all of these functions are supplied with Oracle8i and higher. You use these packages in combination with the mod_plsql plug-in of Oracle HTTP Server (OHS). 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.
Documentation for these packages is not part of the database documentation library. The location of the documentation depends on the particular application server you are running. To get started with these packages, look at their procedure names and parameters using the SQL*Plus DESCRIBE command:
DESCRIBE HTP; DESCRIBE HTF; DESCRIBE OWA_UTIL;
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 round-trips.
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:
To develop and deploy PL/SQL Server Pages, you need Oracle Database version 8.1.6 or later, together with the mod_plsql plug-in of Oracle HTTP Server (OHS).
You can produce the same results in different ways:
HTP and OWA_* packages in the PL/SQL Web Toolkit.The key factors in choosing between these techniques are:
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.
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 like, with text and tags interspersed with PSP directives, declarations, and scriptlets:
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 Syntax of PL/SQL Server Page Elements and "Examples of PL/SQL Server Pages".
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.
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.
To set up parameter passing for a PL/SQL server page, include a <%@ plsql parameter="varname" %> directive. By default, parameters are of type VARCHAR2. To use a different type, include a type="typename" attribute within the directive. 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.
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.
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="MIMEtype" %> 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.
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="encoding" %> directive. Specify Shift_JIS, Big5, UTF-8, or another 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 might have a database character set that uses the EUC encoding, while the Web browsers are set up to display Shift_JIS encoding.
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.
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 <%@ plsql procedure="procname" %> directive.
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="filename" %> 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.
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.
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.
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 cast, such as DATE, pass the value to the PL/SQL TO_CHAR function.
The content between the <%= %> delimiters is processed by the HTP.PRN function, 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 \'.
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 <\%.
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 --%>
These comments do not appear in the HTML output from the PSP.
To create a comment that is visible in the HTML output, place the comment in the HTML portion and use the regular HTML comment syntax:
<!-- Comment text -->
To include a comment inside a PL/SQL block within a PSP, you can use the normal PL/SQL comment syntax.
For example, here is part of a PSP file showing several kinds of comments:
<p>Today we introduce our new model XP-10. <%-- This is the project with code name "Secret Project". People viewing the HTML page will not see this comment. --%> <!-- Some pictures of the XP-10. People viewing the HTML page will see this comment. --> <% for image_file in (select pathname, width, height, description from image_library where model_num = 'XP-10') -- Comments interspersed with PL/SQL statements. -- People viewing the HTML page will not see this comment. loop %> <img src="<%= image_file.pathname %>" width=<% image_file.width %> height=<% image_file.height %> alt="<% image_file.description %>"> <br> <% end loop; %>
If your background is in HTML design, here are a few examples of retrieving data from the database and displaying it.
To display the results of a query that returns multiple rows, you can iterate through each row of the result set, printing the appropriate columns using HTML list or table tags:
<% FOR item IN (SELECT * FROM some_table) LOOP %> <TR> <TD><%= item.col1 %></TD> <TD><%= item.col2 %></TD> </TR> <% END LOOP; %>
If you want to print out an entire database table in one operation, you can call the OWA_UTIL.TABLEPRINT or OWA_UTIL.CELLSPRINT procedures from the PL/SQL Web toolkit:
<% OWA_UTIL.TABLEPRINT(CTABLE => 'some_table', CATTRIBUTES => 'border=2', CCOLUMNS => 'col1, col2', CCLAUSES => 'WHERE col1 > 5'); %> htp.tableOpen('border=2'); owa_util.cellsprint( 'select col1, col2 from some_table where col1 > 5'); htp.tableClose;
To share procedures, constants, and types across different PL/SQL server pages, compile them into a separate package in the database 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.
To make things easier to maintain, keep all your directives and declarations together near the beginning of a PL/SQL server page.
You can find examples of many of these elements in "Examples of PL/SQL Server Pages".
Specifies characteristics of the PL/SQL server page:
.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/.Note that the attribute names contentType and errorPage are case-sensitive.
<%@ page [language="PL/SQL"] [contentType="content type string"] charset="encoding" [errorPage="file.psp"] %>
Specifies the name of the stored procedure produced by the PSP file. By default, the name is the filename without the .psp extension.
<%@ plsql procedure="procedure name" %>
Specifies the name, and optionally the type and default, for each parameter expected by the PSP stored procedure. The parameters are passed using name-value pairs, typically from an HTML form. To specify a default value of a character type, use single quotes around the value, inside the double quotes required by the directive. For example:
<%@ parameter="username" type="varchar2" default="'nobody'" %>
<%@ plsql parameter="parameter name" [type="PL/SQL type"] [default="value"] %>
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.
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/.
<%@ include file="path name" %>
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.
<%! PL/SQL declaration; [ PL/SQL declaration; ] ... %>
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.
<% PL/SQL statement; [ PL/SQL statement; ] ... %>
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.
<%= PL/SQL expression %>
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 ... ] [ error_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). 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/.
For example:
loadpsp -replace -user scott/tiger@orcl banner.inc error.psp display_order.psp
In this example:
orcl. The database is accessed as user scott with password tiger, both to create the stored procedure and when the stored procedure is executed.banner.inc is a file containing boilerplate text and script code, that is included by the .psp file. The inclusion happens when the PSP is loaded into the database, not when the stored procedure is executed.error.psp is a file containing code, text, or both that is processed when an unhandled exception occurs, to present a friendly page rather than an internal error message.display_order.psp contains the main code and text for the Web page. By default, the corresponding stored procedure is named DISPLAY_ORDER.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 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 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 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.
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 Through a URL" to compile the PSP files and try them in a browser.
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
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.
<%@ 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>
Next, we loop through the items in the table and explicitly print just the pieces we want.
SELECT statement to retrieve only a subset of the rows or columns.<%@ 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.
<%@ 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>
We have a dynamic page, but from a user point of view it may still be dull. The results are always the same unless you update the catalog table.
<%@ 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>
This 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.
WHERE clause, we can retrieve the entire result set, then take different actions for different returned rows.<%@ 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>
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>
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.
For example, here is how an HTML file containing JavaScript might be generated using a PSP:
<%@ page language="PL/SQL" %> <%@ plsql procedure="graph" %> <%! -- Begin with a date that does not exist in the audit table last_timestamp date := sysdate + 1; %> <html> <head> <title>Usage Statistics</title> <script language="JavaScript"> <!-- d=document // Draw a horizontal graph line using a graphic that is stretched // by a scaling factor. function graph(howmuch) { preamble = "<img src='/images/graph_line.gif' height='8' width='" climax = howmuch * 4; denouement = "'> (" + howmuch + ")\n" d.write( preamble + climax + denouement ) } // --> </script> </head> <body text="#000000" bgcolor="#FFFFFF"> <h1>Usage Statistics</h1> <table border=1> <% -- For each day, count how many times each procedure was called. for item in (select trunc(time_stamp) t, count(*) n, procname p from audit_table group by trunc(time_stamp), procname order by trunc(time_stamp) desc, procname) loop -- At the start of each day's data, print the date. if item.t != last_timestamp then htp.print('<tr><td colspan=2><font size="+2">'); htp.print(htf.bold(item.t)); htp.print('</font></td></tr>'); last_timestamp := item.t; end if; %> <tr><td><%= item.p %></a>: <td> <!-- Render an image of variable width to represent the data value. --> <script language="JavaScript"> <!-- graph(<%= item.n %>) // --> </script> </td> </tr> <% end loop; %> </table> </body> </html>
Coding this procedure as a regular PL/SQL stored procedure would result in convoluted lines with doubled apostrophes, such as this:
htp.print('preamble = "<img src=''/images/graph_line.gif'' height=''8'' width=''"');
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:
contentType and errorPage must be specified as mixed-case..psp extension).-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.METHOD=GET in the calling form so that the parameters are visible in the URL.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.NUMBER.METHOD=GET. You can switch to METHOD=POST in the calling form without changing your PSP file.loadpsp command reports line numbers correctly when there is a syntax error in your source file, line numbers reported for runtime errors refer to a transformed version of the source and do not match the line numbers in the original source. When you encounter errors like these, that produce an error trace instead of the expected Web page, you will need to locate the error through exception handlers and by printing debug output.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:
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.ALT= attribute. You might store the description in the database along with the image.<BODY> tag, to avoid hard-to-read combinations like white text on a white background.<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.Submit button and make a call to the database.You might find that a PL/SQL Web application needs to accept data in XML format, or produce tagged output that is XML rather than HTML.
When displaying output, you can set the MIME type of the Web page to text/xml so that an XML-enabled browser or other Web client software can render it as XML.
You can also use a number of built-in features like the XMLTYPE type, DBMS_XMLQUERY and DBMS_XMLSAVE packages, and SYS_XMLGEN and SYS_XMLAGG functions within your application. For information about these features, see Oracle XML Developer's Kit Programmer's Guide.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|