Chapter 7: The PLSQL Web Toolkit and PSPs

Amidst a dizzying array of web technologies such as Java, JavaScript, JavaServer Pages (JSP), Hypertext Preprocessor (PHP), eXtensible Markup Language (XML), ActiveX, Visual Basic (VB) Script, Standard Generalized Markup Language (SGML), Hypertext Markup Language (HTML), Dynamic HTML (DHTML), and Common Gateway Interface (CGI), just to name a few, Oracle long ago threw its own support behind the Web by providing developers with the ability to use PL/SQL (Procedural Language extension to Structured Query Language) as a medium for dynamic web content generation. With the advent of WebDB, the Oracle Application Server and the PL/SQL cartridge, Oracle has provided developers who are already well versed in PL/SQL and HTML with the ability to create dynamic web applications based on data driven content without the need to learn an entirely new language.

Today, the platform for this capability is the Apache-based Oracle Application Server 10 g (formerly known as Oracle9 i Application Server) and the mod_plsql Apache module, but the basic concept for accomplishing the task works the same.

How PL/SQL Works as a Web Technology

Oracle offers two PL/SQL technologies for web content generation: the PL/SQL Web Toolkit and PL/SQL Server Pages (PSPs). As we ll discuss in this chapter, these two technologies are tightly integrated. In fact, PSPs require the PL/SQL Web Toolkit and are essentially another way to make use of the PL/SQL Web Toolkit.

Regardless of the technology used, web content reaches a web browser in the same manner. In order for PL/SQL to do its work, several components need to be in place and configured properly. First and foremost, a client machine with an installed web browser that can reach the network where the web application server resides is necessary. This is the easiest task given it is hard to find a computer in operation today that doesn t have a web browser installed. A web application server is required, in this case Oracle Application Server 10 g , to serve the HTML to the client s web browser. The Application Server 10 g install also includes an Apache module called mod_plsql (also referred to as the PL/SQL gateway) that allows Apache to communicate directly with the Oracle database s PL/SQL engine. A database access descriptor (DAD) that maps a virtual path to a database connection also needs to be configured. Finally, an Oracle database with the PL/SQL Web Toolkit installed (installed by default on versions 8.1.7 and later) is required, which provides the mechanism of generating HTML from PL/SQL.

Figure 7-1 shows a typical PL/SQL web application architecture. The key to having all of these components work correctly is in the Uniform Resource Locator (URL) requested by the web browser. The URL for a PL/SQL web application equates to a PL/SQL procedure on the target database server. When a client requests a URL for a PL/SQL web application (or when an HTML link from a static page provides it), Apache determines that a PL/SQL subprogram is being called and routes the request through the mod_plsql module. The mod_plsql module examines the URL, determines the correct DAD to use (there can be multiple DADs configured on a single application server), and opens a connection to the database, if the connection is not already established. Once the connection is open , mod_plsql passes any parameters specified and control to the target database s PL/SQL engine, which executes the procedure specified in the URL. The HTML results are then returned back through mod_plsql and then on to the client s web browser. An example URL looks like this:

Figure 7-1: A typical PL/SQL web application architecture

 http://hostname/pls/DADname/progname 

or

 http://hostname/pls/DADname/<package>.<procedure> 

The hostname part of the URL is the hostname of the web application server, the pls in the URL tells Apache to hand the URL off to mod_plsql to handle the call. DADname is the name of the Database Access Descriptor (DAD), which provides the database connection information, and progname is the name of the PL/SQL procedure being called.

If the installation and configuration of Application Server 10 g is too daunting for the purposes of getting your feet wet or access to an installation of Application Server is not available, there is an alternative. If a development instance of the Oracle database is available, then all of the components needed to run the examples in this chapter are available. By default, all versions of the Oracle database since 8.1.7 include the Apache web server, the mod_plsql module, and the PL/SQL Web Toolkit. The only tasks required are the creation of a schema in the database for your PL/SQL web application subprograms, and the configuration of a DAD using the Apache server.