9.1 PLSQL | Oracle Application Server 10g Essentials

9.1 PL/SQL

The Procedure Language extension to SQL (PL/SQL) is Oracle's proprietary programming language. It is used both server-side and client-side:

On the server side: PL/SQL is used in the Oracle database to program stored procedures and triggers.
On the client side: PL/SQL is used in Oracle's declarative development tools, Oracle Forms and Oracle Reports, to program behavior.

PL/SQL's strengths lie in its seamless integration with SQL and in its excellent server-side performance. Because Oracle's tools, Oracle Forms and Oracle Reports, also use PL/SQL as their programming language, these tools can leverage both seamless integration with the database and existing PL/SQL database programmer expertise for client-side development.

9.1.1 Using PL/SQL

PL/SQL uses the same datatypes as SQL in the database; hence, no datatype conversions are necessary. PL/SQL effectively adds procedural language constructs, such as IF-THEN-ELSE logic, FOR loops , and so on, to SQL. Because PL/SQL stored procedures are executed in the database, this capability eliminates the data access delays caused by moving data across a network. This makes PL/SQL the superior choice for data- intensive manipulations in the database.

Oracle Application Server provides a way to produce dynamic content via Oracle database stored procedures. The execution of stored procedures for web applications is facilitated through the Oracle HTTP Server module mod_plsql ; the Oracle HTTP Server was described in detail in Chapter 5.

PL/SQL is a rich and powerful language that we only touch upon in this book. For much more detailed information, see Oracle PL/SQL Programming (O'Reilly).

9.1.2 PL/SQL Web Applications

Oracle HTTP Server uses the mod_plsql module to route stored procedure requests to an Oracle database. mod_plsql provides a CGI environment for stored procedures. A request URI routed to mod_plsql from the Oracle HTTP Server is mapped to a corresponding stored procedure in the database as follows :

The name of the stored procedure ”schema, package name, and procedure name (as required to eliminate ambiguity) ”is specified in the URI.
Any HTTP parameter values are mapped to stored procedure parameters with exactly the same names as the HTTP parameters.
Extra path information, cookies, and CGI environment variables are also made available.

The logic of a stored procedure can use any of this information to formulate a response in the form of text that is then streamed back to the requester through mod_plsql to the Oracle HTTP Server, and so on. Two software development kits are available to aid the developer: the PL/SQL Web Toolkit and PL/SQL Server Pages. If you're familiar with Java, note that these can be compared to Java servlets and JSPs, respectively.

PL/SQL Web Toolkit: This toolkit provides a set of Oracle stored procedure packages (e.g., HTP , HTF , OWA_UTIL ) that allow a PL/SQL programmer to call procedures or functions to produce HTML output. These packages also provide access to CGI environment variables and cookies. Using the PL/SQL Web Toolkit, a programmer writes a stored procedure that outputs HTML tags and data.
PL/SQL Server Pages: In contrast, PL/SQL Server Pages are HTML files with embedded PL/SQL scriptlets. The scriptlets (small pieces of PL/SQL code) may call PL/SQL Web Toolkit procedures and functions. PSPs are compiled into stored procedures when they are loaded into the database using the loadpsp utility.

The difference between using the PL/SQL Web Toolkit and using PL/SQL Server Pages is that PL/SQL Web Toolkit procedures are PL/SQL programs with embedded HTML, while PL/SQL Server Pages are HTML files with embedded PL/SQL. The PL/SQL Web Toolkit is typically a more appropriate choice for programmers, whereas PSPs are a better fit for web designers. Ultimately, every PSP becomes a stored procedure.