Oracle Web Applications: PL/SQL Developer's Introduction

Oracle Web Applications: PL/SQL Developer's IntroductionSearch this book
Previous: 1.2 Current Web Techniques Are InadequateChapter 1
Next: 1.4 A Roadmap to Oracle8i

1.3 Oracle's Solution -- Oracle8i

To steal a phrase from James Carville, consultant to Bill Clinton's 1992 presidential campaign, "It's the data, stupid." Large companies have realized for years that filesystems are unsuited for sophisticated data management, and have instead relied on relational database management systems (RDBMSs).

These databases have quietly provided scalable, secure, and manageable access to the most critical corporate information for over a decade. Companies understand how to plan for auditing, disaster recovery, capacity, maintenance, and application development. There are well-understood tools and proven techniques, and developers know how to build database systems. Given that content will be king for the new generation of web sites and applications, doesn't it make sense to graft web server capabilities onto a database, rather than a filesystem?

Oracle has had over 20 years of experience designing information systems that manage the most important corporate data. As the largest database vendor in the world, they have (arguably) the world's most sophisticated and powerful database. Over the past several years, Oracle has moved diligently to apply professional data management concepts like scalability, security, auditability, disaster planning, and so on to an unruly world of Internet content management. With Oracle8i, the "Internet database," these plans have come to fruition.

Oracle8i is a soup-to-nuts platform for web site and web application development that addresses the pressing issues of content management, application development, and application integration by extending traditional database concepts to web content. Oracle8i replaces the traditional filesystem used by most web servers with a database management system, and it supports -- either directly or through various add-on products -- a mind-boggling variety of technologies. Table 1.1 summarizes the most important of these; asterisked items must be separately licensed from Oracle.

Table 1.1: Major Web Technologies Supported in Oracle8i



Internet File System (iFS)

An Oracle extension[2] that allows Oracle8i to store files inside the database. It combines this capability with a wide variety of networking protocols to let various clients use iFS as a native data store. These clients can include email products like Qualcomm Eudora, productivity products like Microsoft Excel or Word, and HTTP clients like Netscape Navigator. In addition, iFS supports sophisticated version control features, such as check-in and check-out for documents shared by multiple users.


An ASCII-based markup language used to create web pages. HTML is a non-proprietary specification.


An emerging standard for creating documents that contain structured information. XML, syntactically similar to HTML, allows you to define your own markup tags. XML is expected to be a key technology in electronic commerce systems because it simplifies data interchange among various systems.


A structured programming language similar to Ada that combines procedural constructs with standard SQL. PL/SQL also supports reusable components called packages; you can write your own packages and use those built into Oracle8i. Like Java, PL/SQL is executed directly in the database. Unlike Java, it's supported in Oracle8 and Oracle7.


An Oracle development environment for building and monitoring content-driven web sites and data-driven applications. WebDB allows users to use a web browser to access and store information in the Oracle8i database. It's also compatible with Oracle8 and Oracle7.

Oracle Application Server (OAS)*

An extensible web server that uses plug-in programs called cartridges. OAS allows you to develop database-integrated web systems in a variety of languages, including Java, Perl, and PL/SQL. It's also compatible with Oracle8 and Oracle7.


An object-oriented language similar to C++. Oracle8i includes a built-in Java(TM) Virtual Machine ( JVM) to allow Java programs to execute directly inside the database. Java is probably the single most important new technology in Oracle8i.


The collective name of a set of Oracle products for developing mobile Internet applications. These products are: Oracle Lite, a small footprint version of Oracle8i; EnterpriseSync Lite (ESL), a set of replication technologies that includes AQ Lite, a disconnected version of AQ; and the InternetLite (IL) server and API, a set of software products for replicating both data and applications to mobile applications.

Oracle interMedia*

The collective name of a set of Oracle cartridges for storing multimedia content inside Oracle8i. The cartridges include interMedia Text for storing text information, Visual Information Retrieval (VIR) for storing image and audio files, and Oracle Spatial for storing geographic data.

Advanced Queuing (AQ)

A queue-based messaging system that allows programs to communicate asynchronously. While Oracle8i is built on the AQ system available in Oracle8 and Oracle7, it supports a "publish/subscribe" model not available in the earlier versions.

[2] Available in Oracle8i Release 8.1.5.

As you can see, Oracle8i supports an extensive number of products and technologies for developing web sites and Internet systems. We'll look at each product in a little more depth in the next several sections.

1.3.1 The Internet File System

The Internet File System (iFS) allows Oracle8i to masquerade as different types of data servers, including a file server, an FTP server, and an email server. This makes data accessible to almost any type of client, whether it's a Windows 95 workstation, a web browser, or an email client. i FS supports several networking protocols to accomplish this sleight of hand:


Allows Windows 95, NT, and 98 clients to treat files stored in Oracle8i as if they resided on a normal Windows file server


Allow FTP clients and web browsers to treat data stored in Oracle8i as if it resided on an FTP or web site


Allow email clients like Eudora and Microsoft Outlook to treat data stored in an Oracle8i database as if it resided on an email server

For example, a user on a Windows workstation can define a network drive like E:, F:, or O: that points to an Oracle8i database instead of to a file server. The user sees no discernible difference between an Oracle8i volume and a file server, and she can open, update, or save Word and Excel files in the usual way.

Although i FS is not available at the time of writing, Oracle has laid out the following basic model for its use in conjunction with XML:

  1. You create a TYP file (an XML document) to describe the structure of each type of document that can be stored in the iFS repository. Each element in the TYP file is mapped to a corresponding column in a database table.

  2. Users can use almost any client to access the iFS repository, including FTP and HTTP clients, email clients, and Windows (SMB) clients. The client treats the iFS repository as it would a native data server. For example, an email client can see Oracle8i as an email server, and a Windows client can see the same information as a network volume.

  3. iFS executes a server-based event, a chunk of code analogous to a database trigger, whenever a user inserts, deletes, updates, or views a document in the iFS repository. You can develop your own event servers, using Java and CORBA, to override basic iFS functionality. For example, you might want to send an email when a certain type of document, such as a purchase order, is saved to the repository. iFS also has a built-in XML parser to process XML documents.

Using iFS, you could define a purchase order document and associate it with various events. A customer could place an order electronically by emailing a purchase order document to the Oracle8i iFS repository. This could trigger a "Send Thank You" event that would send an email thanking the customer for the order and a "Process Order" event that would move the document into an order entry system.

1.3.2 HTML

HyperText Markup Language (HTML), the language used to create web pages, is a specification for marking up text documents using a fixed set of tags that control how the document is displayed in a web browser. For example, text enclosed between the <b> and </b> tags is displayed in bold, and text enclosed within <i> and </i> is displayed in italics. Tags can also have attributes, parameters that act like instructions. For example, the <a> tag, which is used to create a hyperlink within a document, has an attribute named href that specifies the location (the uniform resource locator, or URL) of the page the user visits when he clicks on the link.

HTML is also used to create simple data entry forms you can use to store information inside an Oracle database. Here, for example, is the HTML code needed to produce a guest book screen that asks for a web user's name, email address, and comments:

   <title>Sign the guest book</title>
      <form action="guestbook.insert_entry" method="post">
         <b>Name:</b>    <input name=i_name>  <p>
         <b>E-mail: </b> <input name=i_email> <p>
            <textarea name="i_comments" rows=5 cols=40>
         <input type=submit>

Figure 1.1 shows how the form is displayed in a web browser. You can learn more about HTML in Chapter 5, HTML.

Figure 1.1: An HTML form

Figure 1.1

1.3.3 XML

Extensible Markup Language (XML) is an emerging standard for creating structured documents using an HTML-like syntax. Although much of the current enthusiasm for XML is focused on its ability to create complex user interfaces for web systems, XML has much broader applications in the following areas:

Surprisingly, XML is also fairly easy to learn and use. The following example shows how you could use XML to create an electronic invoice:

<?xml version="1.0"?>
   <CUSTOMER>Megaplex Industries</CUSTOMER>
         <ITEM_NAME ITEM_NUM="PN-5342">Widget 1</ITEM_NAME>
         <ITEM_NAME ITEM_NUM="PN-6354">Widget 2</ITEM_NAME>

XML allows you to define your own tags and attributes, then set up rules that these tags must follow. An XML parser program reads each document to make sure that it follows these rules and, if it does, moves it into a hierarchical data structure called a document tree. You can then manipulate the structured information using Java or PL/SQL. You can learn more about XML in Chapter 9, XML.

1.3.4 PL/SQL

PL/SQL is Oracle's procedural language extension to the SQL language. PL/SQL is a structured language that has been extended in Oracle8 and Oracle8i to handle object types and support other object-like features. PL/SQL is especially well suited to modular programming since it allows you to build stored procedures, functions, and packages to perform database operations. PL/SQL provides a rich set of datatypes and supports conditional processing, loops, cursors (for row-at-a-time processing), and collections (PL/SQL's version of arrays, formerly called PL/SQL tables).

Packages are an especially powerful PL/SQL construct. A package is a container for other PL/SQL elements, such as variables, constants, procedures, functions, and datatype definitions. Packages let you build standard code libraries with well-defined APIs. In the web environment, for example, you might create standard libraries to handle security, formatting, and other reusable functionality.

You can learn more about PL/SQL in Chapter 6, PL/SQL.

1.3.5 WebDB

WebDB is an excellent tool for developing database-driven web applications and sites. WebDB lets you perform everything from database administration to application development using only a web browser. Your applications and content area are stored inside the database. WebDB's capabilities are divided into these broad categories:

Database administration

WebDB lets you use a web browser, rather than a "fat" client like Oracle Enterprise Manager (OEM), to perform routine database administration tasks. These tasks might include viewing the definitions of database objects, administering WebDB and database security, and monitoring database and application performance.

Application development

WebDB provides wizards that simplify the development of database objects (e.g., tables and views) and user interface components (e.g., forms and reports).

Content-driven web management

WebDB lets you use a browser to build and edit complex sites, add content (e.g., PDF, presentations, papers), and integrate other WebDB applications. You, and better yet, your end users, can add web content directly from a browser and can manage it like any other information.

You can learn more about WebDB in Chapter 3, WebDB.

1.3.6 Oracle Application Server

Oracle Application Server (OAS) is another good tool for building web applications. Whereas WebDB may be the most appropriate tool for quickly building and deploying Internet applications, OAS is probably best for electronic commerce and enterprise resource planning applications. OAS performs all the functions of a traditional web server, but in addition, it provides tight integration to an Oracle database.

OAS is built on a system of plug-in cartridges used to execute certain kinds of resources. Several language cartridges come with OAS: PL/SQL, used to execute PL/SQL stored procedures; Java, used to execute server-side Java programs; and Perl, used to execute Perl scripts. Other cartridges are also available; for example, the ODBC cartridge executes ODBC (Open Database Connectivity) statements and returns the results directly to your browser.

You can learn more about OAS in Chapter 4, Oracle Application Server (OAS).

1.3.7 Java

Java, a popular object-oriented language, is becoming a good choice for developing and deploying Oracle-based web applications. Oracle8i is completely integrated with Java, and supports a wide range of data access and development models. These include:


The standard specification for interaction between Java and relational databases, as defined by Sun Microsystems. JDBC is the Java version of ODBC. Programmers can take advantage of Oracle's extensions to JDBC, such as convenient access to Oracle-specific datatypes like ROWID.


A precompiler technology (similar to Pro*C or Pro*COBOL) that allows the programmer to embed static SQL statements directly into Java code. The SQLJ translator and runtime libraries are available both inside and outside the Oracle8i server. SQLJ also provides access to Oracle-specific datatypes.

Java stored procedures (JSPs)

Stored programs that let you invoke static Java methods from Oracle's SQL or PL/SQL languages. The mechanism for publishing Java methods in this fashion is proprietary to Oracle.

CORBA server objects

Objects that are developed according to Object Management Group (OMG) specifications and that can be distributed. They can communicate with other objects regardless of location. Using CORBA, you can integrate both Java and non-Java applications. CORBA server objects in Oracle8i can both call and be called by CORBA objects outside the server. CORBA is supported by a variety of languages and environments.

Enterprise Java Beans(TM) (EJBs)

An approach especially helpful in large distributed systems. EJBs are coarse-grained, reusable components that comply with Sun's EJB specification; they rely on the Oracle8i EJB "execution container" for services such as component location, activation, security, and transaction support. EJBs can be used with non-Java applications.

Java servlets

Java programs that generate HTML for presentation in a web browser. The Oracle8i server provides HTTP service and a servlet execution environment by incorporating a special version of Sun's Java(TM) Web Server(TM).[3] Servlets can read and write database data using any convenient database access model ( JDBC, SQLJ, etc.) and generate any form of HTML.

[3] This capability is in beta form in Oracle8i's initial release.

Consult the appendix for a list of references concerning Java development.

1.3.8 InternetLite

InternetLite is a toolkit for building mobile applications that allow users to work while disconnected from a network. A mobile application has two basic parts: a master site and a snapshot site. The master site is usually a complete, production Oracle database. When a user needs to disconnect from the network and use the database on the road, he copies a subset of the production data from the master site to his own local database, the snapshot site. The user makes various changes to the snapshot site, each of which is recorded in a log, until he is ready to reconnect to the master site. At this point, the snapshot site and master site must be synchronized so that changes on the snapshot site are applied to the master site, and vice versa. The logs are reset once the master site and snapshot site are in synch.

As you can imagine, handcoding the mechanics for each of these steps can be a tedious, difficult process. The various InternetLite products act as a sort of operating system for distributed computing that provides these services automatically; it handles data and application replication issues, allowing you to concentrate on designing your application without worrying about lower-level details. There are four individual products in the InternetLite product suite: Oracle Lite, EnterpriseSync Lite, AQ Lite, and the InternetLite server and API.

Since it's helpful to look at each product in the context of a specific example, let's suppose you want to create a mobile expense sheet application. The system should allow users to record their expenses while they're on the road and, when they return to the office, automatically upload these expense items into the production database. Oracle Lite

Oracle Lite functions as a miniature version of the full Oracle8i database, which runs in just under one megabyte of memory and supports the major database application objects, such as tables, indexes, and sequences. The Oracle Lite database is used to maintain the snapshot site in a mobile application.

Oracle Lite supports two modes for application development: client/server and Internet. Client/server mode allows developers to use the Oracle Call Interface (OCI) to write C programs, Open Client Adapter (OCA) to write Developer/2000 applications, and ODBC to write applications using Visual Basic, Access, PowerBuilder, etc. Internet mode supports two access methods: JDBC or the Java Access Classes ( JAC), an API for creating data-aware Java servlets.

To return to our expense report example: Oracle Lite is the application data store that contains the expense items. Our first step in developing the application is to define the various tables, such as the different types of expenses (lodging, mileage, food) and the actual expense items (person submitting the item, date, expense type, dollar amount). We can use Oracle Forms, Java, and an ODBC client such as Microsoft Access to write the application and then use EnterpriseSync Lite to develop a replication strategy to move data between the master and snapshot sites. EnterpriseSync Lite

EnterpriseSync Lite (ESL) is the second product in the InternetLite suite. As its name implies, ESL is used to handle the synchronization phase of a mobile application. ESL provides a replication API, called REPAPI, that defines how the table data is moved between the master and snapshot sites.

ESL is based on Oracle's database table snapshot technology. A snapshot is basically a copy of a table that's based on a SQL query. For example, to create a snapshot of the expense item table, I could use the command:

CREATE SNAPSHOT expense_item_snap AS

Periodically, the snapshot must be refreshed to reload the information from its base query. There are two refresh options: complete and fast. A complete refresh will reload the entire table. A fast refresh will reload only the rows that have been changed or added since the last refresh.

TIP: As a rule of thumb, the fast refresh is faster only when fewer than 10% of the rows in the underlying master table have been changed. Otherwise, the complete refresh is faster.

ESL automates the process of creating the snapshot site by allowing you to define how and when the application loads and refreshes the snapshot data. Hooking your program into the REPAPI provides a behind the scenes way to move data from the snapshot site into the master site and vice versa. ESL supports two replication modes: synchronous and asynchronous. In synchronous mode, the user must be connected directly to the database over a standard SQL*Net (Net8) connection; data is transmitted using the standard Oracle protocol. In asynchronous mode, the user uses a file transfer process, such as email or FTP, to send an export file of her snapshot log and receive an import file of snapshot refresh data. The advantage of this approach is that users can synchronize their systems off-site using standard products like Qualcomm Eudora or Microsoft Outlook. AQ Lite

EnterpriseSync Lite also includes AQ Lite, a scaled-down version of Advanced Queuing (AQ, covered later in this chapter), that's used to create distributed messaging services. Messages are queued to the snapshot site's local data store and sent to the production queues when the user synchronizes. InternetLite server and API

The InternetLite server allows you to synchronize both data and applications on mobile clients, eliminating the problem of installing the correct version of an application on mobile clients. The advantages of this approach should be clear to anyone who has ever tried to provide phone support to an irate user (usually calling from the client's site!) who has a corrupted database or a Dynamic Link Library (DLL) conflict.

The catch is that the applications must follow the Internet development model; the client/server model isn't supported. The development process works something like this:

  1. The developer defines the master and snapshot sites using Oracle Lite as a local data store.

  2. She then writes the application using Java servlets. Typically, these applications use an HTML user interface to access the underlying database tables.

  3. Next, she sets up a replication profile for each mobile client that defines the snapshot tables and application components that are replicated.

  4. When the user connects to the IL server, it receives the data in the master site as well as all the Java servlets required for the application. IL replicates everything the user needs to run the application in disconnected mode, including the Oracle Lite database engine and the necessary Java classes.

The InternetLite server, which performs these operations, is a plug-in cartridge for OAS version 4.0. Figure 1.2 shows the architecture of an IL-based system.

Figure 1.2: Architecture of an InternetLite-based mobile application

Figure 1.2

Consult the appendix for a list of resources that will help you learn more about the InternetLite product suite and building distributed systems in general.

1.3.9 Oracle interMedia

Oracle8i has three plug-in cartridges that can manage multimedia data: the interMedia Text cartridge, the Video Information Retrieval (VIR) cartridge, and the Oracle Spatial cartridge. These three products are collectively called Oracle interMedia, and allow Oracle to manage text, multimedia, and spatial data. Text data

The Oracle interMedia Text cartridge is used to manage documents (either inside or outside the database) by automatically indexing them with smart attributes. You can then use SQL to perform a variety of complex searches, such as searching for an exact phrase or performing a fuzzy search to find the closest matches for the search criteria. Text can index nontext documents, such as Word, Excel, PowerPoint, WordPerfect, Adobe PDF, HTML, and XML, using a filter that converts the document from its native format into one the database can understand. Currently, there are more than 100 such filters. Multimedia data

interMedia can manage traditional multimedia files, such as video or audio clips, as well as static image files. Video Information Retrieval (VIR) can store video clips in a variety of formats, including AVI, QuickTime, and MPEG. It can store audio clips in AUF, AIFF, AIFF-C, and WAV formats. These clips are accessible through any streaming server, such as RealNetworks or Oracle Audio/Video Server. interMedia can also store image files in a variety of popular formats, including TIFF, GIF, and JPEG. Audio, video, and image data are all compatible with popular authoring tools like Symantec Visual Page or FrontPage, via the interMedia clipboard. Spatial data

interMedia's Spatial cartridge provides support for a range of geocoding systems that specify a latitude and longitude with a specific piece of information, such as a zip code or an address. This information can be used to calculate distances between locations or to represent information in geographic information systems (GIS). For example, using this information, you could create a query system for a bank that returned the ATM locations closest to a specific address.

1.3.10 Advanced Queuing

Advanced Queuing (AQ), first introduced in Oracle8, is a message-based queuing system you can use to bind a variety of different systems together. A universally accessible API used to send complex messages from one system to another, AQ is built on procedures and functions stored directly in the database. This architecture allows applications in any language or platform, from COBOL to PL/SQL to Java, to communicate through a system of queues maintained in the database.

For example, an OAS storefront could use AQ to send an order from its order entry system. This system, perhaps written in C, could use the AQ API to retrieve and process the request. This universal application-to-application communication eliminates the need for clunky import and export routines.

As an example of AQ in action, suppose you want to write a simple web site that lets registered customers buy or sell stocks over the Internet. The customer can use a variety of clients, such as a Java applet, an HTML browser, or an Oracle Forms application, to place an order to buy or sell stock. Another program, perhaps written in Pro*COBOL, periodically processes and fulfills the orders placed so far. The next sections illustrate how to design an AQ-based solution. Figure 1.3 illustrates its basic architecture.

Figure 1.3: Basic architecture of an AQ-based stock system

Figure 1.3 Define the message payload

The first step is to define the structure of the message contained in the queue, which is done with the SQL command, CREATE TYPE. Here, for example, is how we might define a simple payload for the stock example:

CREATE TYPE aq.customer_order AS OBJECT (
   customer_id NUMBER,
   stock_symbol VARCHAR2(20),
   num_shares NUMBER ); Create and start the queue tables

The next step is to use the AQ administrative API to create the queues inside the Oracle database. Each queue is associated with a payload definition and (by default) follows the first-in-first-out protocol. In our example, we want to create two message queues: one for "buy" messages and one for "sell" orders. After you create the queues, you can start and stop them to control when they can receive messages. The following code snippet illustrates these steps for our example:

-- Create BUY and SELL Queues
   queue_table => 'aq.BUY_QUEUE',
   queue_payload_type => 'aq.customer_order');

   queue_table => 'aq.SELL_QUEUE',
   queue_payload_type => 'aq.customer_order');

-- Start the Queues
   queue_name => 'BUY_QUEUE');

   queue_name => 'SELL_QUEUE'); Enqueue and dequeue messages to/from a queue

Once you've created and defined the queues, you can begin enqueuing (inserting) and dequeuing (retrieving) messages. To create a message, you create an object based on the queue payload, set the values you want to insert, and call AQ's ENQUEUE procedure. For example, a browser-based client could enqueue an order at any time by calling the following PL/SQL procedure:

PROCEDURE buy_stock (
   i_customer_id  IN VARCHAR2 DEFAULT NULL,
   i_stock_symbol IN VARCHAR2 DEFAULT NULL,
   i_num_shares   IN VARCHAR2 DEFAULT NULL
   the_order aq.customer_order;
   queueopts dbms_aq.enqueue_options_t;
   msgprops dbms_aq.enqueue_properties_t;
   msg_id RAW(16);

   the_order :=
      message_type (
      queue_name => 'BUY_QUEUE',
      payload => the_order,
      enqueue_options => queueopts,
      message_properties => msgprops,
      msg_id => msg_handle

Dequeuing reverses the process by extracting the item from the queue. To dequeue a message, you create a payload variable and then use the AQ's DEQUEUE procedure to extract the first item off the queue. In our example, we could fairly easily retrofit our legacy system (for example, a Pro*COBOL program) to loop through each item on the BUY and SELL queue.

You can learn more about AQ in Oracle Built-in Packages, by Steven Feuerstein, Charles Dye, and John Beresniewicz (O'Reilly & Associates).

1.3.11 Functional Summary of Oracle8i Web Products

Table 1.2 illustrates the role each Oracle8i web product plays in fulfilling the requirements for the new generation of systems laid out at the beginning of this chapter.

Table 1.2: Uses for Oracle8i Web Technology



i FS








Content management











Application development











Application integration











Electronic data exchange











In addition to supporting a range of tools for content management, application development, and application integration, Oracle8i (as well as Oracle7 and Oracle8) scales across three related dimensions: performance, platform, and price. Oracle8i's multithreaded architecture ensures high performance through clustering, connection pooling, and multiplexing; it also has a resource management system to precisely control the CPU time given to a user or a group of users. Oracle8i runs on an enormous number of hardware and software platforms, which can range from a palmtop (via Oracle Lite), to a workgroup server, to a mainframe; porting an application from one platform to another is often as simple as exporting and importing the schema. Finally, since Oracle8i is supported on so many different systems, you can decide how much you're willing to spend on an application without locking yourself into a platform that can't, if necessary, scale up.

Finally, Oracle8i addresses the pressing problem of development fragmentation by allowing developers to master a single platform that can meet most foreseeable future demands. Of course, there's just one little problem.

Previous: 1.2 Current Web Techniques Are InadequateOracle Web Applications: PL/SQL Developer's IntroductionNext: 1.4 A Roadmap to Oracle8i
1.2 Current Web Techniques Are InadequateBook Index1.4 A Roadmap to Oracle8i

The Oracle Library Navigation

Copyright (c) 2000 O'Reilly & Associates. All rights reserved.

Library Home Oracle PL/SQL Programming, 2nd. Ed. Guide to Oracle 8i Features Oracle Built-in Packages Advanced PL/SQL Programming with Packages Oracle Web Applications Oracle PL/SQL Language Pocket Reference Oracle PL/SQL Built-ins Pocket Reference