Chapter 12. Working with PL/SQL Server Pages (PSPs)
Chapter Objectives
In this chapter, you will learn about:
Before Oracle 8i Release 2, there was virtually only one way to serve dynamic data directly from the database to the Web using PL/SQL. It was to use the PL/SQL cartridge and associated PL/SQL Web Toolkit provided with the Oracle Application Server. With the advent of Oracle’s WebDB, and its Dynamic Pages, developers were then given the ability to create entire Web sites inside an Oracle database. The most recent version of WebDB has been renamed as Portal. And the most recent development in the use of PL/SQL for providing dynamic database content to Web pages is PL/SQL Server Pages (PSPs).
Oracle 9i’s iAS (and actually Oracle 8i version 8.1.6 and above) provide the capability to implement PL/SQL Server Pages (PSPs). This is a powerful new feature available for developers. A PL/SQL Server Page, PSP, is a server-side Web page (one that is stored in, loaded, and run from the database server, as opposed to a client-side Web page that runs from a Web browser client) that contains PL/SQL code within predefined tags. When loaded into the database using Oracle’s loadpsp utility, it becomes a database procedure that runs on the database server to produce and output dynamic content in your Web page/Web application.
 | In the 8.1.6 version of the Oracle database, the loadpsp utility has a file load limitation of 32 KB (kilobytes). Attempting to load a file larger than this generates an error. |
Oracle’s version of a server page technology enables you to distinguish the site design’s presentation layer from the application/code-development layer. The benefit of PSPs over the PL/SQL Web Toolkit packaging method is that PSPs can provide a friendlier programming interface for an HTML programmer. They are simply HTML code with PL/SQL code embedded in them. Therefore, PSPs can be edited using a GUI HTML development tool such as FrontPage™ or Dreamweaver™. This way, the presentation design can be initially developed separately from and, therefore, can be distinguished from the application logic/business component design.
Using a tool such as WebAlchemy, a free (as of the writing of this book) program by Alan Hobbs available for download at http://www.users.bigpond.com/ ahobbs/), you can convert HTML into PL/SQL Web Toolkit code. Many critics of server page technologies consider them inefficient since most server page technologies are neither compiled nor cacheable. Fortunately, PSPs can not only be edited in an HTML editor, but the code is stored as compiled PL/SQL code, and is, therefore, cached in the SGA (System Global Area) of an Oracle database when invoked. These added features make PSPs similar to, but better than, Dynamic Pages in WebDB.
This chapter takes you through the use, anatomy, and creation of PSPs. You not only learn to develop PSPs, but you load them into the Oracle database using the loadpsp utility.