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.
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.
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.
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,
>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);
CREATE PACKAGE BODY Person_Pkg AS
PROCEDURE add(I_FNAME IN VARCHAR2,
I_LNAME IN VARCHAR2,
I_ID IN NUMBER)
INSERT INTO Person(FNAME, LNAME, ID)
VALUES (I_FNAME, I_LNAME, I_ID);
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.
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/[email protected] 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:
<INPUT TYPE='text' NAME='fName' VALUE=''>
<INPUT TYPE='text' NAME='lName' VALUE=''>
<INPUT TYPE='text' NAME='line1' VALUE=''>
..... Other fields
<INPUT TYPE='text' NAME='email' VALUE=''>
<INPUT TYPE='text' NAME='phone' VALUE=''>
<INPUT TYPE='submit' NAME='Submit' VALUE='Submit'>
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.
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.)
Vivek Sharma is a
software developer at Oracle Corporation. He has a BS in computer
science and some six years of software research and
development experience. Interests include development of Web-based tools/technologies and technical writing. He can be reached at
add(F_NAME IN VARCHAR2,
.... -- Other things like l_name, address etc.
PHONE IN VARCHAR2,
RESULT_VAL OUT NUMBER) IS
/* Temp_Result is a temporary variable that
indicates to the calling Java application
whether the user information was recorded
successfully or not. 1 indicates success */
Temp_Result := 1;
/* Create a unique Id for this user */
SELECT Person_Seq.NEXTVAL FROM Dual INTO Temp_Seq;
/* We insert name of the person along with the ID
generated above by calling the add() procedure
in the Person_Pkg package created above */
Person_Pkg.add(F_NAME, L_NAME, ID);
/* Similarly the address is added. The ID this
time goes into the Person_Id field of the
Address table */
Address_Pkg.add(LINE_1, ...... ID);
/* We need to add the phone and e-mail as 2
separate records */
Telecom_Address_Pkg.add(PHONE, 'PHONE', ID);
Telecom_Address_Pkg.add(EMAIL, 'EMAIL', ID);
/* If an exception occurs, result is set to
0 so the Java application can treat it
accordingly. Note that you can add similar
exception handlers in the other packages
also to have better control over generation
of error messages.*/
WHEN OTHERS THEN
Temp_Result := 0;
/*The OAS comes with a number of packages that allow
you to decode the FORM variables as well as generate HTML.
The "rdbms" package allows you to establish a Session with
the database for invoking PL/SQL procedures/functions. */
public class Register
/*We’re declaring a variable ‘s’ of the type Session -
here Session is a class that comes in the rdbms package.
It represents the connection we’re establishing with the
database. We’re declaring this as a global ‘static’ variable
so that the same Session can be used by multiple requests,
increasing program efficiency. */
static Session s = null;
public static void main(String args)
Register r = new Register();
public void start()
/* Declare a variable of HTTP.class - a class that comes
as part of the OAS and can be used within Java applications
to get information about the current HTTP request such as FORM
String fName = http.getURLParameter("fName);
/* Similarly, get all other parameters such as lName, etc.
Check if values are valid by calling a method value sAreValid
(not shown). You could check things like whether the e-mail
entered by the user has a missing '@' and other things like
if(valuesAreValid(fName, lName ........))
writeToDB(fName, lName ......);
void writeToDB(String fName, ......)
// Before we create a Session, we need to inform it of the
/* Check if there is a session already established with the
database. If so, use that Session else create a new Session */
if(s == null)
s = new Session("userName",
/* As we can see here, if a session has already been established
in this instance, the same session can be used by other HTTP
requests handled by this instance. Since a good deal of time
is established in creating connections to the database, we
have created an optimization that will ensure the session
need not be reestablished for every new request. */
/* Reg_Api is the class generated by pl2java */
/* Create an instance of Reg_Api and pass the instance of
the session created above so the class knows which database/schema
it needs to work against */
ra = new Reg_Api(s);
/* Since there are no datatypes in Java that map directly to
PL/SQL datatypes, Oracle has provided mapping classes in the
plsql package. For a VARCHAR2, you need to convert your String
to a PStringBuffer() before calling a PL/SQL procedure that
requires a VARCHAR2 as input. Similarly for NUMBER, you must
create a PDouble. Likewise there are equivalent datatypes for
most PL/SQL datatypes. */
PStringBuffer pFName = new PStringBuffer(fName);
// Similarly PStringBuffers for all variables to be passed
to add() in Reg_Api
/* The result, however, is a NUMBER type, so we create a PDouble */
PDouble result = new PDouble(1);
/* Now invoke the procedure as if you were calling a normal
PL/SQL procedure */
ra.add(pFName, pLName, ....pPhone, ... result);
/* result will now contain the value stored in this variable
by the add() procedure. We need to examine this. Before that
we convert it to an int by calling a method in PDouble */
if(ra.intValue() == 0)
/* Rollback this transaction as it failed */
/* Commit this transaction */