As the Web grows, a great deal of effort is being made toward writing applications in Java that interact with databases. Fortunately, JDBC provides an easy, database-vendor-independent way of writing such applications. While this approach works for a number of applications, there are limitations. For instance, if the application requires execution of a large number of SQL statements, efficiency becomes an issue since round-trips from the application to the database are costly. Also, since these statements aren't precompiled, the database system spends more time executing them than it would have if they were compiled and residing in the database.

This is where PL/SQL comes in. It's a database language that enhances SQL by providing structure to SQL programs. It's rich with elements like control structures that we're used to seeing in such languages as C and Java. Thus you can write arbitrarily complex routines that manipulate the database using PL/SQL. More important, you can compile PL/SQL programs in an Oracle database so they execute faster at runtime.

Oracle Application Server
The next question is, How do we execute PL/SQL programs from within our Java applications? Enter the world of the Oracle Application Server (OAS)! Web applications started with CGI programs running at the back end. With increased use, this approach started reaching its limits because most CGI programs consume a lot of resources on the host machine for execution. Application servers came on the market to solve this problem, among others. The Oracle Application Server is one such product that helps to execute your Web applications efficiently .

Since describing the OAS in detail isn't feasible here, I'll cover the basic items you'll need to understand the rest of the material. Broadly speaking, the OAS consists of a Web Request Broker (WRB) that handles Web requests that require execution of an application. The WRB routes these requests to the appropriate executable code. It provides load balancing and other services, such as transaction management, to the applications executing in the OAS environment.

Cartridges
The requests are served by programs called cartridges that in turn may execute applications written in languages such as Java, C and Perl. As a developer, you may write either a cartridge or a cartridge application, commonly referred to as a component.

The OAS comes with a number of cartridges, one of which is the JWeb cartridge. This cartridge is meant for executing Java applications. It maintains an instance of a Java Virtual Machine and executes the Java class specified in the request.

The OAS also comes with a PL/SQL cartridge, which can be used for executing PL/SQL applications directly. However, the focus of our current discussion is on integrating Java applications with database applications, so we won't discuss the PL/SQL cartridge here.

Virtual Paths
There is the concept of virtual paths in the OAS. A virtual path can be thought of as an arbitrary name that you specify through the administration screens of your OAS. Each virtual path maps to a particular cartridge. When a request comes in, the OAS checks to see if the URL contains a virtual path in it. If it does, the OAS determines which cartridge this path maps to and then directs an available instance of that cartridge to execute the code requested in the URL.

A virtual path also has a physical directory associated with it - this is where the executable code you've written needs to reside. The following example should make this clear.

Let's say you've declared a virtual path called "myjava" in the OAS that maps to the JWeb cartridge, and specified that the associated directory is /home/me/java_dir. If somebody sitting on a client such as a browser requests the URL http://yourmachine/myjava/Employees, the OAS will recognize myjava as a virtual path. It'll also determine that this maps to a JWeb cartridge. Accordingly, it'll direct an available instance of the JWeb cartridge to execute Employees.class. This is a class file created by you that resides in the directory /home/me/java_dir on the server where your OAS is running.

pl2java Utility
A utility called pl2java comes as part of the OAS package. If you invoke this utility on a PL/SQL package stored in your Oracle database, it produces a Java class file. You may then use this class file in your Java application to invoke procedures and functions in that package. (A PL/SQL package is a collection of PL/SQL procedures and functions grouped together.)

With this background about the OAS, we can move on to see how a program running in the JWeb cartridge can execute PL/SQL procedures and functions. To illustrate a typical usage, we'll develop a small application - one that allows people to register online on your Web site.

Developing a Sample Application
The registration process starts with a user accessing your registration URL. To keep things simple, let's assume this URL is a static HTML page. This page contains an HTML form in which the user fills out information such as first and last name, e-mail address, phone and so on. The "ACTION" of this form is an application that writes the information to an Oracle database and returns a thank you page.

Internally, this application is written in Java where variables from your form are extracted and error checking is done. A PL/SQL procedure that writes the information to the database and returns a success or failure code to the Java application is then invoked. Based on this result, the Java application returns a thank you or error page to the user.

Designing the Database
The first step in writing this application is to design the database where information (name, address, phone, e-mail address) will be stored. While it's quite reasonable to have just one table in which all the information will be stored, we'd be compromising extensibility of the database in doing so. Instead, let's see which things go together and which don't.

Obviously, the first and last name go together. So let's create a table called "Person" with two fields, First_Name and Last_Name, both of VARCHAR2 with max size of 50 each. Address can be in a separate table of the same name. Fields can be "Line_1", "Line_2", "City", "State", "Country" and "Zip". How about e-mail and phone? They're essentially telecom addresses. Keeping them in the same table will have the added advantage that if we want to expand the registration application later to include fax, mobile phone, etc., we won't have to write an additional table to store this new information. But we need to distinguish one type from another. So we'll have a "Type" field in this table that can take on values like "E-MAIL", "FAX" and "PHONE". This table would be "Telecom_Address" and would have the following fields: "Code" and "Type", both of which are VARCHARs.

A quick recap - we have three tables:

Person - First_Name, Last_Name
Address - Line_1, Line_2, City, State, Zip, Country.
Telecom_Address - Code, Type.

We also need to establish a relationship between these tables so we can link the records. In the Person table we add a field called "Id". This will have a unique value for each person who uses the registration system. In the Address and Telecom_Address tables we add a field called "Person_Id" that points to a record in the Person table.

To illustrate how it'll work, assume that five people have registered. For the first user our application stores "1" in the ID field of record created for this user in the Person table, "2" for the second one and so on. It also stores the same number in the Person_Id field of records created in the Address and Telecom_Address table. To find the name, address and e-mail of the second person, we can issue the following SQL statement:

SELECT Person.First_Name, Person.Last_Name,
Address.Line_1, Address.Line_2, Address.City,
Telecom_Address.code
>FROM Person, Address, Telecom_Address
WHERE Person.Id = 2 AND Person.Id = Address.Person_Id
AND Person.Id = Telecom_Address.Person_Id
AND Telecom_Address.type = 'EMAIL';

PL/SQL for Manipulating the Database
Next, we'll write some PL/SQL packages that will insert records into the database. But as we saw, we need a way to generate a unique number for the ID field of Person. For this we'll use the concept of sequences. For our purposes let's create a sequence of the name "Person_Seq" by issuing the following SQL statement:

CREATE SEQUENCE Person_Seq START WITH 1;

As far as packages are concerned, we'll create four packages - one for handling insertion into each of the three tables, and a top-level package that calls routines in the other packages. We do this so only one API will be consistently exposed to the Java application that needs to call these routines.

I won't go into much detail about packages and PL/SQL in general - suffice it to say that a package consists of a declaration followed by an implementation, also known as the package body. A sample package for adding stuff to the Person table would look like:

CREATE PACKAGE Person_Pkg AS
PROCEDURE add(I_FNAME IN VARCHAR2,
I_LNAME IN VARCHAR2,
I_ID IN NUMBER);
END Person_Pkg;
CREATE PACKAGE BODY Person_Pkg AS
PROCEDURE add(I_FNAME IN VARCHAR2,
I_LNAME IN VARCHAR2,
I_ID IN NUMBER)
IS
BEGIN
INSERT INTO Person(FNAME, LNAME, ID)
VALUES (I_FNAME, I_LNAME, I_ID);
END add;
END;

Packages for manipulating the other two tables would look similar.

Our top-level package is Reg_Api. The body of this package contains the procedure add(), which looks like Listing 1.

The next step is to compile these packages by using something like SQL*Plus. Once these packages are compiled, we can use the pl2java utility to generate a class file.

Invoking pl2java
If we've compiled these packages in a database in the schema "myschema/ myschema", with the connect string as "myConnect", we can invoke the pl2java utility from the command line as follows:

pl2java myschema/myschema@myConnect Reg_Api

This will generate a class called Reg_Api.class that can be used in our Java application.

Note that pl2java accepts some command-line parameters that enable you to do things like changing the name of the output class file.

HTML Page for the Application
Now that we know where and how we're going to store information, we'll take a top-down approach, starting with the HTML form. Instead of showing the complete HTML, I'll show some relevant portions:

<FORM ACTION='http://yourserver/myjava/Register'>
<INPUT TYPE='text' NAME='fName' VALUE=''>
<INPUT TYPE='text' NAME='lName' VALUE=''>
<P>
<INPUT TYPE='text' NAME='line1' VALUE=''>
..... Other fields
<INPUT TYPE='text' NAME='email' VALUE=''>
<INPUT TYPE='text' NAME='phone' VALUE=''>
<P>
<INPUT TYPE='submit' NAME='Submit' VALUE='Submit'>
</FORM>

This HTML file can be put in the document root directory of the machine hosting the OAS. We now need to create a Java application - specifically, a class called Register. After we've created it, we need to move it to the directory that corresponds to the virtual path "myjava".

The Java Application
Our Java application should first decode the values typed by the user and check to make sure they're valid. Then it should invoke the PL/SQL procedure developed above. Based on the result value returned by the PL/SQL procedure, it should commit the transaction and present a thank you page, or roll back and present an appropriate error page.

Listing 2 shows what our Java program looks like.

Conclusion
As we've seen, PL/SQL procedures can be invoked easily through an OAS environment. This saves us unnecessary round-trips to the database, and gives us an added advantage in that the SQL code is precompiled and would thus execute faster. All in all, by using the pl2java utility of the OAS, you can create a seamless interface between your Java applications and PL/SQL procedures to build powerful Web applications that interact with the database.

(The opinions and statements expressed in this article are my own and don't necessarily represent those of Oracle Corporation.)