Carview!

Content preview·Buy reprint rights for this chapter

Since it burst on the scene in the early 1990s, the World Wide Web has transformed from a way (to quote Homer Simpson) to "let us know some nerd's opinion on Star Trek" to a whole new way of doing business. Hardly an area in the information technology industry has been unaffected. Developers who only yesterday were using COBOL to write accounts payable systems are now being asked to create a broad range of new Internet-based applications, including electronic commerce (e-commerce) web sites, internal data warehouses, and enterprise resource planning (ERP) systems.

Unfortunately, the filesystem architecture of most web systems is beginning to show its age. The new breed of web application, which is quickly becoming critical to companies' survival, demands a platform that provides production-quality tools for content management, application development, and application integration.

The new release of the Oracle database, Oracle8i, attempts to meet these and other objectives by building web technology on top of a relational database system, rather than on a filesystem. This type of development enables companies to apply well-understood, reliable, production-quality database methodologies to web content management. Oracle8i also supports a wide variety of application development platforms and tools that are tightly integrated to the core database. Finally, Oracle8i supports technologies that help you tie your web-based systems to legacy applications.

In this chapter, I'll examine these issues in more detail. I'll start with a look at the new web applications and why current web technology isn't an ideal platform for building them. Next, I'll look at how Oracle8i and its related products attempt to address the failings of previous web technologies. Finally, I'll lay out a roadmap you can use to get started with Oracle8i web development, so you can take full advantage of the Web.

Flush with both the successes of the World Wide Web and its potential to generate new revenues, companies are scrambling madly to exploit Internet technologies. Internet technology is now commonly required for at least four different types of projects: internal application projects, like data warehousing; mobile application projects, like sales-force automation; electronic commerce, like Internet storefronts; and enterprise resource planning (ERP) systems for automating business operations.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Flush with both the successes of the World Wide Web and its potential to generate new revenues, companies are scrambling madly to exploit Internet technologies. Internet technology is now commonly required for at least four different types of projects: internal application projects, like data warehousing; mobile application projects, like sales-force automation; electronic commerce, like Internet storefronts; and enterprise resource planning (ERP) systems for automating business operations.

Internal applications are critical resources for a variety of users within a company. For example, developers must build web interfaces for data warehouses that often contain several terabytes of data. In addition to responding quickly, these systems must often meld several different kinds of data—table data, images, and even videos—into an attractive page.

Once these systems are in place and successful, developers are asked to create new systems called mobile applications that can extract subsets of the data warehouse for use on portable devices like laptops, PalmPilots, or other Personal Data Assistants (PDAs). These systems, often used by salesman or technical support personnel, allow users to work even when not connected to a network. The data these users enter is synchronized with production systems when the users reconnect to the main network.

Internet storefronts and other e-commerce applications let customers buy things over the Web. Increasingly, these applications are expected to integrate with existing order entry systems, provide continuous availability, and protect both the customer and the business from attacks by malicious hackers.

The most ambitious companies use Internet technologies to reduce costs through business-to-business enterprise resource planning systems, which let systems in one company communicate directly with systems in another company over the Internet. For example, an ERP system might let the purchasing system in company A place an order directly with the order entry system in company B. The goal is to automate everything from paying invoices to ordering paperclips. These hybrid sites must integrate many different application systems, from accounts receivable, to accounts payable, to order entry, into a single, cohesive unit that performs a complex series of transactions quickly, accurately, and securely.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Sure, anyone with Microsoft FrontPage can put a human resources policy manual on the Web, but creating production web sites with existing web technology is simply too much work. Three broad problem areas in current web technology make it hard to build these new applications:

Content management: Although web servers are good at presenting content, they are bad at managing it. This is partially due to their filesystem-based architecture, which often does not include the ability to build searchable, maintainable, and auditable information systems.
Application development: A production setting requires tools that can scale both up and down, fit the needs of a specific user base, and are part of a complete developmental framework. Few, if any, current development techniques meet these criteria.
Application integration and electronic data exchange: It's too hard to integrate different systems. To make e-commerce and ERP a reality, a platform must provide a simple method to link different applications, whether they all reside in one site or are spread across multiple sites. Current web servers are only just beginning to address this issue.

Let's look at these problems in detail.

Web servers are great for making information available to a wide audience. Unfortunately, they do very little to help web site developers manage all this information. An ideal platform would help us develop sites that make it easy for users to find what they are looking for, are easy to keep up to date, and allow easy tracking of site content changes.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

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.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There's way too much new stuff to learn! You could spend the rest of your life—including the additional 100 years you'll get because of Y2K—learning the technologies listed in Table 1.2 and still not master them all. Oracle Corporation is far ahead of most of us; we're lucky if we can keep our existing production systems running, much less learn dozens of new tools and methodologies.

Given the increasing importance of the Internet, though, we need development skills that we can use right now, not in the distant future. Additionally, some sites, for one reason or another, haven't even moved from Oracle version 7.3 (or even 7.0) to Oracle8, much less Oracle8i. What are they supposed to do?

In this section I'll suggest an approach to building web applications for Oracle8i that you can learn in just a week or two, even if you currently know nothing about the Web and even if you're still using Oracle7. Everything I'll cover will migrate smoothly to Oracle8i. In this way, you'll have time to start learning the other technologies even as you develop new systems. The approach I suggest here uses a subset of the technologies listed in Table 1.2: OAS, WebDB, HTML, PL/SQL, and XML. I'll also tell you why I think Java should be your second step.

The first thing you'll need to do is connect the Oracle database to the Web. The simplest way to do this is to use either OAS or WebDB. Both products work with Oracle 7.3 or above. I'll cover each in its own chapter, focusing on what you, as an application developer, need to know to use and understand the technology.

Once you've seen how to connect the database and the Web, you can start learning how to write web applications by combining HTML, the language used to create web pages, with PL/SQL, the SQL-like language used to develop Oracle stored procedures. The next three sections explore the reasons for choosing these tools.

Section 1.4.2.1: Why HTML?

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The ease with which you can develop applications on a PC has caused developers to pay far too little attention to the basic infrastructure in which the systems run. Developers often slap together a form, test it on a PC, and then roll it out to unsuspecting users. They fail to take into account that although they have tested the system on a LAN connection, users will use the system over a WAN connection. What seems fine in one setting is bad in another, and even the world's greatest application really stinks if it's deployed on an inappropriate infrastructure.

Understanding the implications of the infrastructure is even more important in web development, and your designs must account for differences between the major Internet networking protocols (especially statelessness, which we'll look at shortly) and their client/server counterparts. Web systems are centered on a network, so you must account for network traffic in your designs. Even the way you connect your database to the Web has an important impact. You haven't yet written a line of code and you've already got dozens of problems to work out.

This chapter lays the foundations for a WebDB or an OAS application. I'll talk about these applications more specifically in Chapter 3, and Chapter 4.

An individual piece of content, whether it's a human resources manual or a phone list, is a resource in web parlance. There are two broad classes of resources: static and dynamic. Static resources are files in a certain format: HTML documents (HTML) created through a text or HTML editor, ASCII reports (TXT) created through a batch process, images (GIF, JPEG) created through an image editor, and even movies (AVI, MPEG) or sound (WAV, AU) created through a video or audio capture system. Almost any type of file becomes a static resource when placed in the proper directory on a machine running OAS or WebDB.

The second, much more interesting type of resource is a

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

An individual piece of content, whether it's a human resources manual or a phone list, is a resource in web parlance. There are two broad classes of resources: static and dynamic. Static resources are files in a certain format: HTML documents (HTML) created through a text or HTML editor, ASCII reports (TXT) created through a batch process, images (GIF, JPEG) created through an image editor, and even movies (AVI, MPEG) or sound (WAV, AU) created through a video or audio capture system. Almost any type of file becomes a static resource when placed in the proper directory on a machine running OAS or WebDB.

The second, much more interesting type of resource is a dynamic resource, a program that creates web content as it runs. For example, you could write a program to dynamically create a list of employee phone numbers from a human resources database. When a user visits this page, your program queries the database and builds the page as it runs. These sites are always up to date because they are built directly from the data's source, so they aren't subject to the vagaries of manual updating. OAS allows developers to use a number of languages, including PL/SQL, Java, Perl, and VRML (Virtual Reality Modeling Language), to develop dynamic resources; WebDB uses only PL/SQL. This book concentrates on developing dynamic resources using PL/SQL.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The Internet (or an intranet) is a network that links different computers together. Before we can start writing web applications, we must understand how the output from these systems actually gets from the server to the browser, which means that we have to learn a little about how the Internet and the Web work.

OAS and WebDB use standard Internet conventions and protocols to send resources to a client. The most important parts of this interchange are:

A TCP/IP network to connect the server to the client
A software communication port to serve as a collection point for incoming requests
A transfer protocol called HTTP to govern how server and client communicate
A client program called a web browser to allow users to request and receive resources from the OAS or WebDB server
A uniform resource locator (URL) to allow the browser to find a particular resource
A MIME type to tell the browser what to do with resources once received from the OAS or WebDB server

The following sections briefly describe each of these parts.

Browsers connect to an OAS or WebDB server using the TCP/IP networking protocol. Although there are a number of different types of networking protocols, such as DECNet or IPX, web systems only work with TCP/IP. Fortunately, more and more operating systems have this functionality built in, including Unix, Windows 95, Windows 98, Windows NT, OS/2, and Linux.

Every machine on a TCP/IP network is identified by a four-part IP address. Each number in the address can range from 0 to 255, and the four numbers are separated by periods. For example, 253.4.99.17 might be the address for the machine running the human resources department's web server. Every machine on a TCP/IP network has a unique IP address.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this section I'll explain how the OAS and WebDB servers apply the ideas introduced in the previous section to deliver content from the server to the client (e.g., URLs, virtual directories, ports, etc.).

Although the specific details vary, OAS and WebDB follow the same basic process model to deliver web content and rely on a virtual schema mapping called a Database Access Descriptor (DAD) to access the database. A DAD is similar to a virtual directory mapping; it creates a name, used within a URL, that links the request to a specific schema in the database.

Figure 2.1 illustrates the basic parts of the model.

Figure 2.1: Basic components of OAS and WebDB systems

A request begins when a user submits a URL or an HTML form to an OAS or WebDB server. A server component called the HTTP listener intercepts the request and extracts its path section. This step, called URL resolution, determines what type of resource the request is for and how it will be processed. If the URL's path section matches a virtual directory mapping, then the request is for a static resource and the HTTP listener returns the requested resource (if found) to the user's browser. If the path section matches a DAD stored in the server's configuration files, the request is for a dynamic resource and the HTTP listener forwards, or dispatches, the request to the PL/SQL gateway.

The PL/SQL gateway reparses the URL (or HTML form action attribute), extracting the DAD name and the name of the procedure to execute, which is found in the resource name section of the URL. The gateway also extracts any parameters that might have been passed as part of the request. If the request was made with a URL, the parameters are stored in the query string. If the request was made with a form, the parameters are stored in the form's named data entry fields.

The gateway uses the DAD configuration information to connect to the appropriate database schema, then executes the specified procedure, passing any parameters included in the call. The procedure, which executes directly inside the database, usually calls procedures in a set of packages called the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The HTTP listener and PL/SQL gateway are used to build web-enabled systems that provide tight integration with a backend Oracle database. PL/SQL-based OAS and WebDB applications are developed using a set of packages called the PL/SQL toolkit. In this section, we'll take a quick look at the toolkit and see an example procedure. The last section covers how to pass parameters.

WebDB and OAS both include the PL/SQL toolkit. The toolkit contains a variety of PL/SQL packages written and supplied by Oracle that perform a range of tasks, including generating HTML tags, manipulating cookies (name/value pairs used to save information throughout an entire session), and creating complex HTML structures based on information in a database table. In general, procedures built with the toolkit will work in either product, although you may run into minor database privilege issues that the DBA can help you resolve.

The packages in the toolkit (described in detail in Chapter 7) are:

HTP and HTF: HTP is a set of procedures that print syntactically correct HTML tags, which are returned to the user's web browser. HTF is an equivalent set of functions that return HTML strings whose output is returned to the program that called the function. In either package, procedures and functions correspond to specific HTML tags; their parameters correspond to tag attributes.
OWA_COOKIE: A set of data structures, procedures, and functions used to create and manipulate cookies.
OWA_IMAGE: A set of data structures, procedures, and functions used to manipulate image maps.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Even though security policies are developed and enforced by the DBA, you should still understand how database security issues can impact application design. For example, you should have a clear grasp of schemas and object privileges if you're going to secure your systems by allowing access only through a minimally privileged account.

Depending on the application, you might need to create an application-specific security scheme (unless you create a DAD for every account, which is a maintenance nightmare) to differentiate between users. For example, in Chapter 8, we'll look at a threaded discussion list application in which we create our own username and password list to allow users to post messages.

This section is a security refresher, covering security relationships among database users, database objects, object privileges, and roles. If these terms are new to you, or you need to dust off a few cobwebs, read on. Otherwise, feel free to skip to the next chapter.

A user account is the first line of defense in an Oracle database. Similar to an account on a Unix or NT system, each user account has an associated username and password. A user must log in to a particular account by providing the correct password before running scripts, inputting data, executing PL/SQL programs, or performing any other meaningful activity.

The term schema is often used synonymously with "user" or "account." Although the concepts are closely related, schema is slightly more specific and refers not only to the account itself, but also to the collection of objects (tables, indexes, packages, etc.) owned by the account.

The word object is one of the most overused in the computer world. Languages like Java and C++ create objects with wonderful properties like polymorphism, inheritance, and a slew of other four-syllable words. Object-relational databases like Oracle8

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

WebDB is a user-friendly tool for developing database-driven web applications and sites. You perform every WebDB task, from database administration to application development, using just a browser; everything in a WebDB site, from applications to content, can be stored inside a backend database.

Every WebDB component, from development tools to the database administrator toolkit, has an HTML user interface, eliminating the need for complex tools like Oracle Forms or Oracle Enterprise Manager (OEM). Because browsers are equipped with standard Internet protocols, WebDB client machines do not need SQL*Net or Net8; this allows WebDB sites to be run or administered from "thin-client" machines. WebDB can also take advantage of JavaScript-enabled browsers.

Everything in a WebDB site can be stored directly inside an Oracle database. This allows the site to be professionally administered and maintained using the same tools and techniques as for any other production Oracle database. As an added benefit, the site's performance can be monitored and improved through well-understood database tuning techniques. WebDB's "database-centric" approach helps application developers and DBAs leverage their current skills, rather than acquiring an entirely new and unfamiliar skill set.

How you use WebDB depends on your job. Database administrators can use WebDB to manage database objects, check database logs, and perform other DBA tasks. Application developers can use WebDB to create HTML-based web applications using a set of wizards that automatically build application components, like forms or reports. End users can use the WebDB components you create to view reports, fill out data entry forms, or view the content published by other users. Additionally, almost any user can use WebDB to publish web content on their own personal home page, as well as view content made available by other WebDB users.

Given the nature of the Web, these tasks are rarely mutually exclusive. For example, a DBA might want to upload "tips and tricks" to a page of her personal WebDB site. An application developer might want to monitor application performance or create database objects such as tables. An end user might want to create a report based on a SQL query (stranger things have been known to happen!).

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

WebDB is divided into three broad categories: database administration, application development, and content-driven web site management. In the next three sections, we'll briefly look at each area with an eye towards seeing what the product does, though not necessarily how you perform each task.

You can use WebDB to perform many routine database administration tasks using just a web browser, rather than a "fat client" like the Oracle Enterprise Manager suite of database management tools. For example, suppose you've gotten a call from Bob in accounting insisting that "my Internet doesn't work." Befuddled by your patient explanation that his statement makes absolutely no sense, he demands that you walk over to his desk and help him fix his problem. Since customer service has been added to your list of job performance metrics, you comply. When you arrive at Bob's desk, you realize that he has simply forgotten his password.

Since no one in his right mind would install SQL*Plus (much less OEM) on Bob's machine, you would normally have to trek back to your desk to reset his password, confirming Bob's darkest suspicions that you really don't know what you're doing. Fortunately, you can use WebDB's security management options to save yourself the trip. While you can't do everything from WebDB, you can perform many routine tasks, such as creating database objects, managing user accounts and security roles, and monitoring database performance. Since everything is accessible with just a browser, you can perform these tasks from almost any client, whether you're on-site or not.

The following sections briefly describe how you'd use WebDB to perform typical database administration tasks.

Section 3.1.1.1: Browse database objects

You can use WebDB to view, or browse, the definitions for database objects. The browse capability of WebDB applies to all objects in a particular schema. Once you select the schema, WebDB displays a list of all the object types in the schema, such as tables, indexes, and views. Figure 3.1 shows the WebDB "Browse Database Objects" option.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Because WebDB uses the database's native components, it is a scalable, powerful development tool for building content-driven sites on Oracle. Since it's essentially written in the database's native language, it eliminates the need for cumbersome layers like ODBC or JDBC.

WebDB's dirty little secret is that its wizards are really just PL/SQL code generators that act on normal database objects. When you create a user with WebDB, you are really creating a corresponding database schema, just as you would for any other Oracle user. When you use the table wizard, you are really just filling in the pieces of a CREATE TABLE command. When you build a form, you are really creating a PL/SQL package. The options you enter into these wizards tell WebDB how to create the corresponding database objects.

WebDB is a standalone product that contains everything you need to create a complete application. Two built-in components—the PL/SQL gateway and the HTTP listener—make this possible. Figure 3.9 illustrates the relationships among the database objects, UI components, shared components, roles, users, the PL/SQL gateway, and the HTTP listener.

Figure 3.9: The components of WebDB

In the following sections, we'll look at WebDB's PL/SQL gateway and its integrated HTTP listener.

Once we've created our WebDB user interface components, we can use the PL/SQL gateway to execute them from the Web. The gateway is situated between the database and the HTTP listener. The HTTP listener forwards a request for a component to the PL/SQL gateway, which executes the procedure and stores its output in a buffer. The HTTP listener then sends the contents of this buffer, which now contains the HTML instructions that create the component, back to the user's browser.

Users call a procedure using a URL that specifies the name of the package (which has the same name as the component), the procedure to execute, and any parameters required by the procedure. The PL/SQL gateway uses this information to call the correct procedure. Each procedure begins with a security check to make sure the user attempting to access the component has the required permissions.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle Application Server (OAS) is Oracle's enterprise web platform. While OAS performs all the functions of a normal web server, its main advantage is its tight integration with a backend Oracle database. After starting life with the name Oracle Webserver at version 1, then becoming Oracle Web Application Server at version 3, the Oracle Application Server, now at version 4, has steadily grown in size and features.

The resources required to run OAS have increased along with the new features. For example, the memory requirements (on NT, at least) went from 48 MB in version 3 to 128 MB in version 4.

In this chapter, we'll look at the architectural components of OAS as they relate to PL/SQL application development. Be sure that you've read Chapter 2, which introduces the basic concepts behind the web infrastructure on which OAS is built. We'll start with a discussion of how OAS returns web resources to a user's browser. Then we'll look at the PL/SQL cartridge, an OAS component we can use to develop PL/SQL applications.

OAS has three methods to return resources. The first simply uses a directory mapping system to send static files to the client's browser. The next two methods return dynamic resources: one executes resources using the standard CGI interface, and the other, the Web Request Broker (WRB), executes resources using a program called a cartridge.

As we saw in the previous chapter, the HTTP listener (renamed the Web listener in OAS) receives incoming requests either as URLs or as action attributes in an HTML form. If the virtual path maps to a CGI directory, the CGI interface is used. If it maps to a cartridge, the WRB method is used. Figure 4.1 shows the relationship between these components.

Figure 4.1: Overview of OAS components

In the next three sections, we'll look at how OAS handles requests for static files, CGI dynamic resources, and WRB dynamic resources.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

OAS has three methods to return resources. The first simply uses a directory mapping system to send static files to the client's browser. The next two methods return dynamic resources: one executes resources using the standard CGI interface, and the other, the Web Request Broker (WRB), executes resources using a program called a cartridge.

As we saw in the previous chapter, the HTTP listener (renamed the Web listener in OAS) receives incoming requests either as URLs or as action attributes in an HTML form. If the virtual path maps to a CGI directory, the CGI interface is used. If it maps to a cartridge, the WRB method is used. Figure 4.1 shows the relationship between these components.

Figure 4.1: Overview of OAS components

In the next three sections, we'll look at how OAS handles requests for static files, CGI dynamic resources, and WRB dynamic resources.

A static file is the simplest type of resource the OAS can deliver. A static resource is just a file that resides in a directory on the filesystem. To make the files accessible from the Web, OAS maintains a list of mappings between physical directories and symbolic aliases called virtual directories. A URL uses these aliases, along with the resource name, to retrieve the requested file. Figure 4.2 shows the virtual directory mapping screen for the OAS administration system.

Figure 4.2: Virtual directory mappings

The common gateway interface (CGI), the earliest web technology for developing dynamic resources, allows you to execute any kind of server-side program, whether it's written in a third-generation language like C, a scripting language like Perl, or a database language like PL/SQL. One of the advantages of CGI is that you can use it to do almost anything: create gateways between the Web and an email system, build a help system based on Unix's manpages, or execute scripting programs to play tic-tac-toe. The execution of a CGI program follows these steps:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Now that we have a basic understanding of how the WRB uses cartridges to execute different kinds of resources, let's look at the PL/SQL cartridge in more detail.

The PL/SQL cartridge allows us to use PL/SQL procedures to create dynamic resources. As with the CGI interface, these resources are called with a URL. Unlike CGI, a PL/SQL cartridge maintains a persistent connection to a database, so it executes almost instantaneously. In addition to producing lightning-fast performance, the cartridge resolves the two thorny problems with the CGI interface: connection management and parameter passing.

Following our discussion of the advantage of this cartridge, we'll bring up a few security caveats to keep in mind when using cartridges.

WRBX processes connect to one particular account within a database upon initialization. The configuration for the connection is divided into two parts: the Database Access Descriptor and the PL/SQL agent.

Section 4.2.1.1: Database Access Descriptor

A Database Access Descriptor (DAD) creates a unique alias for a database that is to be accessed over the Web. The DAD contains all the information needed to connect to the database, including the database name, its ORACLE_HOME directory, and its SQL*Net V2 service name. Figure 4.5 shows OAS's DAD configuration page.

Figure 4.5: Configuration screen for a DAD

Section 4.2.1.2: PL/SQL agent

The PL/SQL agent is a unique alias for a database account owned by a particular DAD that makes the account's procedures and packages accessible over the Web. This includes procedures and packages owned directly by the account, as well as those owned by other accounts that have granted EXECUTE permission to the schema.

The agent consists of:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Now that we've discussed WebDB and OAS, we're ready to begin building applications. As you learned in Computer Science 101, user applications have a user interface, whether it's a simple command line, like the one in DOS or Unix, or a full windowing system, like Windows or X. In this chapter you'll learn how to use HTML (HyperText Markup Language) to create an interface that's somewhere in between these two extremes.

This chapter, while by no means comprehensive, provides enough of an introduction to HTML to get you started building useful systems. We'll begin with the basics of HTML programming, covering how to best start learning the language (if you don't know it already) and how to use its tag- and attribute-based syntax. We'll then take a whirlwind tour of HTML, examining most of the major tags you'll use every day. You can find a listing of more complete reference works in Appendix A.

Your company's human resources department may have its personnel policy on an internal web site. You can go to a main page and click on policies that cover various things HR types find important: dress codes, organization charts, inter-employee dating rules, and so on. Almost invariably, these documents have been converted from existing documents, such as Word or WordPerfect documents, using an editor like Microsoft FrontPage, Adobe PageMill, or Netscape Composer. While these tools are certainly useful, we must understand the actual HTML they generate before we can create a user interface for our web systems.

The first thing you need to know about HTML is that you don't need a fancy editor to create an HTML document. HTML is a text file format, so you can use any editor you want to create a document. The second thing to know is that, unlike many other Internet standards, HTML is fairly simple. You can learn much of what you'll need to know about HTML in an afternoon.

The best way to learn HTML is to create a skeleton document in your favorite editor, save it to a file, and view the results with a browser. You don't even have to be on the Web to see your creation; almost all browsers can open a file directly from your system. Once you get bored tinkering with the basic tags, you can justify hours of web surfing as an educational expense by using the "View Source" option to see the underlying HTML code (but not the source code of the dynamic resource that created the document) for the pages you visit. Of course, like any other language, HTML has a syntax you must master before you can use it. This is subject of the next section.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Your company's human resources department may have its personnel policy on an internal web site. You can go to a main page and click on policies that cover various things HR types find important: dress codes, organization charts, inter-employee dating rules, and so on. Almost invariably, these documents have been converted from existing documents, such as Word or WordPerfect documents, using an editor like Microsoft FrontPage, Adobe PageMill, or Netscape Composer. While these tools are certainly useful, we must understand the actual HTML they generate before we can create a user interface for our web systems.

The first thing you need to know about HTML is that you don't need a fancy editor to create an HTML document. HTML is a text file format, so you can use any editor you want to create a document. The second thing to know is that, unlike many other Internet standards, HTML is fairly simple. You can learn much of what you'll need to know about HTML in an afternoon.

The best way to learn HTML is to create a skeleton document in your favorite editor, save it to a file, and view the results with a browser. You don't even have to be on the Web to see your creation; almost all browsers can open a file directly from your system. Once you get bored tinkering with the basic tags, you can justify hours of web surfing as an educational expense by using the "View Source" option to see the underlying HTML code (but not the source code of the dynamic resource that created the document) for the pages you visit. Of course, like any other language, HTML has a syntax you must master before you can use it. This is subject of the next section.

HTML consists of plain ASCII text that is marked up using special instructions called tags that define the document's structure and format. It's a very forgiving language: errors that in other languages would be devastating (like misspelling a reserved word) are usually ignored. It's not case sensitive, the instructions can appear in practically any order or combination, and most browsers are now smart enough to fill in anything you might mistakenly omit.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Although somewhat artificial, it's useful to draw a distinction between text and content when talking about HTML documents. For example, in HTML, you can create a list of items. These items might be simple text, but they can also be HTML tags, such as hyperlinks.

The following sections present some common tags you will need to build the content of your user interfaces. They are listed here with their functions:

Structural tags: Delineate the part of an HTML document (head, body, comments) to which the content belongs.
Text formatting tags: Change the size and appearance of the text within a document.
Content flow tags: Delineate line and paragraph breaks within a document.
Anchor tags: Create links within a document to other documents on the Web.
List tags: Create a variety of useful formats for listing content more effectively.
Table tags: Break the normal vertical flow of a document to present content information in a grid of columns and rows.
Form tags: Create various types of input structures to facilitate user interaction with the site.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

With HTML safely out of the way, we can turn our attention to the second half of our web development platform: PL/SQL, Oracle's proprietary extension to structured query language (SQL). The PL stands for Procedural Language, since PL/SQL is used to create procedural constructs (loops, variables, etc.) on top of the relational constructs of SQL.

Although it has some object-oriented features, PL/SQL is based largely on Ada, a structured programming language used heavily by the Department of Defense. As such, PL/SQL has more in common with languages like C, Pascal, or COBOL than it does with C++ or Java. Although it follows an older design model (structured versus object), PL/SQL has the advantages of being easy to learn, tightly integrated to the Oracle database, and extensible. If you know how to write a SQL script and know at least one 3GL language, you can learn to develop useful PL/SQL programs in just a few hours.

In the next three sections we'll cover what you need to know to start developing in PL/SQL: how to structure a PL/SQL program, how to fill in its major programming constructs, and how to create reusable modules called packages. In the last section we'll look at two third-party tools, TOAD and PL/Formatter, that make PL/SQL development much more enjoyable and productive.

There is much more to say about the PL/SQL language. For complete information, see Steven Feuerstein and Bill Pribyl's Oracle PL/SQL Programming (O'Reilly & Associates).

The idea behind structured, or modular, programming is that complex problems can be broken down into smaller, more manageable pieces. For example, I can break the daunting task of driving from Boston to New York into four simpler steps: find I-95 south in Boston, drive four hours, exit in New York, and find a parking space. I repeat this process on each of the previous steps, breaking each one into even smaller units until I eventually reach a level of complexity that I can reasonably handle. For example, I can break "find a parking spot" into the steps: drive around aimlessly, yell at somebody, honk my horn, and then turn around and go home. Once I have identified all these simpler steps, I can solve the original problem.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The idea behind structured, or modular, programming is that complex problems can be broken down into smaller, more manageable pieces. For example, I can break the daunting task of driving from Boston to New York into four simpler steps: find I-95 south in Boston, drive four hours, exit in New York, and find a parking space. I repeat this process on each of the previous steps, breaking each one into even smaller units until I eventually reach a level of complexity that I can reasonably handle. For example, I can break "find a parking spot" into the steps: drive around aimlessly, yell at somebody, honk my horn, and then turn around and go home. Once I have identified all these simpler steps, I can solve the original problem.

The structure of a PL/SQL program reflects this underlying philosophy. A complex program is made up of units called blocks (as in building blocks) that can contain variables, SQL and PL/SQL instructions, error handling routines, and even other blocks. Each block may have four distinct parts: an optional header, optional variable declarations, executable instructions, and optional error handling code. These parts are described in the following list:

Header section: This section, also known as the specification, comes at the beginning of a block. It defines the block's name, its type, and any parameters it requires. If the header is omitted, the block is called an anonymous block because it does not have a name. The header is required for procedures and functions, but is replaced with a simple DECLARE keyword for anonymous blocks. Anonymous blocks typically include just an executable section, and are often found as the executable portions of a conditional statement.
Declaration section: This section contains declarations for all local variables and structures used in the block. Variables can include simple numbers and strings, as well as more complex structures, like cursors and arrays. The declaration section is optional; your program does not have to use any variables. However, PL/SQL is

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Most programs are built out of a fairly standard set of programming constructs. For example, to write a useful program, I need to be able to store values in variables, test these values against a condition, or loop through a set of instructions a certain number of times. In this section, we'll see how to use these and other constructs in PL/SQL. Specifically, we'll cover comments, variables, conditionals, loops, cursors, and index-by tables (PL/SQL's version of an array).

Comments allow you to document your PL/SQL programs. These comments are stored in the database along with the rest of the PL/SQL code. PL/SQL has two types of comments: multiline and single-line.

Multiline comments are enclosed between the delimiters /* and */. Here's an example:

/*
|| The following procedure unconditionally deletes all
|| rows from the customer's table.
*/
PROCEDURE delete_all_customers is
...

Single-line comments are denoted by two consecutive dashes. The comment can appear either on its own line or after a PL/SQL instruction, as illustrated in the following example:

CREATE OR REPLACE PROCEDURE delete_all_customers
IS
BEGIN
   -- The delete statement blows away all customers
   DELETE
     FROM customers;
   COMMIT;   -- Confirm changes
END;

The second construct, variables, allows you to save values in memory. For example, you may want to keep a counter inside a loop, or store a string value for processing. In this section, we'll see how to declare a variable and assign it a value. We'll also look at how to turn a variable into a constant by permanently fixing its value.

Section 6.2.2.1: Declaring a variable

The syntax for a variable declaration is:

                  name  
                  datatype(size) DEFAULT default_value;

You can also assign the default value using the := operator. In this case, the syntax is:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Now that we've looked at PL/SQL's structure and its most common programming constructs, we're ready to group these elements into tidy little structures called packages. A package is a container (hence the name) for other PL/SQL elements, such as variables and constants, procedures and functions, and datatype definitions.

A package has two parts: a specification and a body. The specification is a sort of table of contents that lists the items in the package. The body contains the implementations for each item. For example, the specification tells us "This package contains a procedure named `foo', which has the following parameters." The body of the package contains the actual implementation of foo.

Packages are the most powerful and useful PL/SQL constructs because they help us build standard code libraries with well-defined application programming interfaces (APIs). In a web environment, for example, you can create standard libraries to handle security, page formatting, or list of values (LOV) generation. Each time you build a new application, you can just plunk in calls to these standard libraries, rather than reinventing them for each new system. Packages are also excellent for building abstract data types (ADTs), a fancy terminology for structures like stacks, lists, and queues.

Prebuilt packages with clear APIs encourage software reuse, the Holy Grail of software engineering. In this final section, we'll learn how to use packages effectively. We'll start by looking at the structure of the specification and the body, and then move on to how to use a package within other programs. After that, we'll look at how to hide the implementation details of a package to create a "black box." Finally, we'll look at package persistence.

The specification defines the package's API, which governs every aspect of how the package is used. The specification lists the headers of the procedures and functions in the API, as well as any variables, types, cursors, or constants necessary to interface with the package. These last items are global variables, accessible both from inside and outside the package. The headers and declarations in the specification are called

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many tools you can use to improve your PL/SQL development productivity. Table 6.2 shows some of the more popular, along with the web sites where they can be found, so you can download and experiment. In the following subsections, we'll look at two of the most helpful tools, TOAD and PL/Formatter.

Table 6.2: Some Handy PL/SQL Development Tools
Tool	Web Site
CAST Workbench	`https://www.castsoftware.com`
FROG (Funky Resource for Oracle Gorillas)	`https://www.507pm.com/pcs`
Oracle Procedure Builder	`https://www.oracle.com`
PLEdit	`https://www.benthicsoftware.com`
PL/Formatter	`https://www.revealnet.com`
SQL/Expediter	`https://www.compuware.com`
SQL Navigator

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The PL/SQL toolkit is a set of PL/SQL packages supplied by Oracle for use in developing web applications. These packages are used to generate HTML dynamically, perform text operations, and improve developer productivity. Table 7.1 shows an alphabetical listing of the packages included in the PL/SQL toolkit, along with an explanation of their uses.

Table 7.1: PL/SQL Toolkit Packages
Package Name	Use
HTF	Parses HTML
HTP	Generates HTML
OWA_COOKIE	Stores cookies
OWA_OPT_LOCK	Performs record locking
OWA_PATTERN	Searches and replaces text
OWA_SEC	Manages security
OWA_TEXT	Represents text
OWA_UTIL	Improves productivity

The sections that follow group these packages in categories according to their functionality. HTF and HTP are used for communicating with the outside world; OWA_TEXT and OWA_PATTERN are used for text processing; OWA_COOKIE and OWA_OPT_LOCK are used for maintaining state. The last two packages, OWA_UTIL and OWA_SEC, are used for maintaining productivity and security.

In addition to learning how to use dozens of procedures, we'll keep an eye on what these packages can teach us about good design. After all, the developers who created these packages are some of the most talented PL/SQL programmers in the world. We would be wise to learn from their examples.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

When scripting languages like Perl are used to develop dynamic resources, their output is sent to the standard output (stdout) device, then funneled back to the browser. Because PL/SQL cannot communicate directly with stdout, the toolkit includes a package, called HTP, that mimics this behavior.

The HTP package is a sort of web-enabled version of DBMS_OUTPUT, a built-in package that provides basic output capabilities such as printing text. Like DBMS_OUTPUT, HTP contains commands that store text in a buffer. When a procedure using the package terminates, the contents of the output buffer are "printed" and returned to the user. This buffering is one difference between PL/SQL toolkit programs and standard CGI programs, which immediately return output to the user. As such, the size of the buffer limits the size of a page. In most cases, this is not a problem; however, you should be aware that if you choose to dump a million-row table onto a single page, you will quickly encounter this limit. Table 7.2 shows the procedures included in the HTP package; HTP also includes a large number of wrapper procedures that correspond to various HTML tags.

Table 7.2: Various HTP Procedures
Procedure	Parameters	Description
ANCHOR	Depend on tag	Generates an anchor tag
PRINT	Any value	Outputs any value passed as a parameter

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The enormous popularity of Perl is due in large part to its sophisticated text processing capabilities. A single Perl command can replace dozens of PL/SQL INSTR and SUBSTR operations. Additionally, Perl's pattern matching capabilities are well suited for processing and validating the text entered into HTML forms.

The PL/SQL toolkit has two packages that bring a subset of these capabilities to PL/SQL. The first, OWA_TEXT, manipulates large chunks of text. The second, OWA_PATTERN, allows developers to use sophisticated search patterns to perform many of the text operations found in Perl.

Beyond their mere utility, these two packages are interesting examples of good package design. OWA_TEXT is similar to the abstract datatypes described in Chapter 6. OWA_PATTERN builds on OWA_TEXT to create dozens of variations of the search and replace procedure, each useful in particular circumstances.

The largest PL/SQL string can contain 32,767 characters. Unfortunately, there are many cases where we might like to process larger chunks of text. A document indexing system, for example, must almost certainly process files much larger than 32K. The OWA_TEXT package overcomes the 32K limitation by breaking text streams into smaller pieces that are stored as elements in a PL/SQL array.

Additionally, it is often useful to treat the components of a string as a single entity (e.g., a sentence as an array of words). OWA_TEXT is ideal for this type of application. In practice, though, you'll probably not use OWA_TEXT directly; instead, it's used to provide more flexibility to the OWA_PATTERN package discussed later in this chapter.

Section 7.2.1.1: Data structures

OWA_TEXT's specification declares four data structures. The first two, vc_arr and int_arr, are PL/SQL arrays that are the building blocks of the more complex types. vc_arr is a 32K string array; int_arr holds indexes to the interesting rows of vc_arr. The declarations for these two datatypes are:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The inability to save information, or state, throughout a session is one of HTML's major limitations. HTML has no client/server type variables that remember things as the user moves from page to page. For example, if we want to use a piece of information entered by a user on the first page of a web system, we must save it somehow and be able to recall it later. There are four basic ways to do this: saving the information as part of a query string in a URL, saving it in a hidden field, saving it in a database table, or saving it in a cookie file by using the OWA_COOKIE package, described later in this section. In this section, we'll look at each method and discuss possible problems you may run into.

The first way to maintain state, using a query string of a URL, is the most straightforward: you simply build the string as you go, placing the information you want to pass from screen to screen in name/value pairs. These values are then passed to the procedure specified in the URL's href attribute when the user clicks on the hyperlink. The disadvantages to this approach include the following:

Depending on the system, the maximum length of the URL is limited to 256 characters.
Each value must be encoded to the CGI specification. It can be easy to forget to do this if you're in a rush.

The second way to save state information is to store it in hidden fields. A hidden field is simply an invisible input element that is part of an HTML form. Although the user cannot see the value on the screen, the hidden field is stored as part of the underlying HTML code. To maintain a value across multiple sessions, all we have to do is include the value as a hidden field on the form. You create a hidden field by setting the type attribute of an <input> element to "hidden," as in the following example:

<input type=hidden name=user_id value="10235">

The disadvantages of this approach include the following:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The final two packages, OWA_UTIL and OWA_SEC, let you perform a variety of administrative and security-related tasks that help improve overall productivity.

The OWA_UTIL package is a grab-bag of useful procedures and functions that simplify many complex tasks. It contains procedures to query the web server environment, simplify debugging, change the default HTTP header, and simplify HTML development.

Table 7.15 shows the various functions and procedures contained in the OWA_UTIL package, which are grouped in categories in the following sections according to their uses.

I've attempted to classify the OWA_UTIL procedures into broad, general categories (debugging, querying the environment, representing dates, etc.). These classifications reflect my own experience and are not intended to limit other possible uses.

Table 7.15: Various OWA_UTIL Procedures and Functions
Procedure/Function	Description
BIND_VARIABLES	Creates complex HTML structures
CALENDARPRINT	Creates complex HTML structures
CELLSPRINT	Creates complex HTML structures
CHOOSE_DATE	Represents dates
DATETYPE	Represents dates
GET_CGI_ENV

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In the last chapter, we learned about the individual packages of the PL/SQL toolkit. In this chapter, we'll use these packages to create two realistic web applications. The first application allows us to create and distribute anonymous surveys; the second allows users to communicate over the Web in a threaded discussion forum.

Before looking at these two systems, however, let's take a quick look at four simple steps that can improve the design process. When you are confronted with any design task, it can greatly help to break the process down into manageable pieces, as follows:

Evaluate your development options; be sure PL/SQL and HTML are really the right tools for the job.
Create a storyboard that will help you visualize the relationship between an application's various web pages.
Create a data model to help you create the screens.
Use PL/SQL packages to actually structure the code.

The next few sections describe these steps in more detail.

Choosing the right development language is always the critical first step in building any application. While you can use HTML and PL/SQL to create very powerful systems, there are still some things you should think about before you start coding. For example:

Does the proposed application require a lot of sophisticated data entry screens?
Will users accept a new kind of interface?
Do all users have modern browsers?
Is the system brand-new, or is it replacing an existing system you could easily retrofit for the Web? For example, you could take an existing Oracle Forms application and access it using the web-enabled version of Developer.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Before looking at these two systems, however, let's take a quick look at four simple steps that can improve the design process. When you are confronted with any design task, it can greatly help to break the process down into manageable pieces, as follows:

Evaluate your development options; be sure PL/SQL and HTML are really the right tools for the job.
Create a storyboard that will help you visualize the relationship between an application's various web pages.
Create a data model to help you create the screens.
Use PL/SQL packages to actually structure the code.

The next few sections describe these steps in more detail.

Choosing the right development language is always the critical first step in building any application. While you can use HTML and PL/SQL to create very powerful systems, there are still some things you should think about before you start coding. For example:

Does the proposed application require a lot of sophisticated data entry screens?
Will users accept a new kind of interface?
Do all users have modern browsers?
Is the system brand-new, or is it replacing an existing system you could easily retrofit for the Web? For example, you could take an existing Oracle Forms application and access it using the web-enabled version of Developer.

The first of these questions is the most important. As a rule of thumb, it's probably better to use Java or client/server than HTML for mission-critical applications with sophisticated data entry screens requiring lots of user interactions, which would be very difficult to code from scratch. For example, you would not want to develop an accounts payable system in HTML. In my own experience, the most effective use of PL/SQL and HTML is for creating systems that generate information from a database, not those that put information into a database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Our first application will let users fill out anonymous surveys using a web browser. Surveys and opinion polls are some of the most common web applications, and usually consist of a list of questions. Users respond by selecting an answer from a small list of options. Most systems also have an option that lets users see the tabulated results for each survey.

This section walks through a simple process you can use to create a generic survey system. Our first step is to design a storyboard to define each screen in the system. This sketch helps us in the next step: designing a generic data model that we can use to construct each page. Our last step is to actually code the system.

In a typical survey application, the first screen presents a list of all available surveys. There are usually two options for each survey: to answer it or to view its tabulated results. If the user decides to answer a survey, she's presented with a bunch of questions and a corresponding list of possible answers. She then answers the questions and presses "Submit" to save the responses in a database table. If the user chooses to view the results of a survey, she's presented with a table summarizing all the previous responses. Figure 8.2 is a simple storyboard that captures these functions.

Figure 8.2: The storyboard for the anonymous survey

We can use the storyboard to design a data model. The first storyboard screen tells us that we need some sort of table to hold the survey list. The screen used to respond to a survey suggests three more tables. The first table holds the text of each question, the second holds the possible answers for each question, and the third holds the actual user responses. The "view results" screen queries these tables.

Figure 8.3 shows a data model that uses these four tables. The SURVEY table contains information about the survey itself, such as its name, description, and the date range during which it is available. The rows in QUESTIONS represent the individual questions on a survey. ANSWERS defines the list of valid responses for each question. The final table, RESPONSES, holds the actual responses given by the respondents.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Our second, more complex example allows users to post messages in a browser-based threaded discussion forum. The granddaddy of all discussion lists, Usenet, allows the Internet community to share its views on literally thousands of topics, ranging from C programming to The X-Files. A more focused list, run by RevealNet at https://www.revealnet.com, is geared to PL/SQL developers.

The system presented here is based loosely on the forums run by the online version of InfoWorld computer magazine (https://www.infoworld.com). Each week, the editors at InfoWorld select a number of topical issues in the computer industry for its readers to discuss. These forums allow registered users to express their opinions on both the forum topic and the posts from other readers. Figure 8.8 shows the user interface for the discussion forum we'll develop in this section.

Figure 8.8: A typical discussion list

A vibrant internal discussion forum is a great way for users and developers to communicate simply and efficiently. For example, a forum about an IS application provides users with the ability to report bugs (e.g., "the total dollars on this report should be double-underlined") and suggest new, potentially interesting ideas ("It'd be cool if we could click on the client number and get an AR report"). Monitoring these discussions can help you design better systems and establish your department, or at least you, as responsive and customer friendly.

Our system follows a well-established format. Users begin at a login screen where they provide a username and password. To allow users to express both personality and anonymity in their postings, we'll create our own list of users and not use the OWA_SEC package described in Chapter 7. (This also saves the DBA from user maintenance.) Of course, the downside is that you now have a new list of users, possibly increasing maintenance. However, building maintenance features into the system (such as allowing a user to create a new account) helps shift the burden some so that users are maintaining their own information.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Extensible Markup Language (XML) is an emerging standard closely related to Standardized General Markup Language (SGML), the granddaddy of all markup languages, which was designed by the U.S. government to create complex documents. Realizing that SGML was simply too complicated for his purposes, Tim Berners-Lee (the inventor of the Web) used SGML to create HTML, and the rest is history.

Now that the Web has matured, however, developers are starting to miss some of SGML's capabilities. XML is an attempt to find a middle ground between the complexities of SGML and the ease of use of HTML. Like HTML, XML employs a tag-based syntax to mark up ASCII text. Unlike HTML, which controls the appearance of a document, XML describes the meaning and structure of a document by defining a syntax and grammar for creating new tags. XML is extensible because it lets you define your own tag vocabulary (as long as it follows the rules of the XML specification) for creating meaningful documents.

Although it's currently being touted as "HTML done right," XML is actually a lot more. It has a number of potential uses as a tool for integrating disparate systems and building electronic commerce systems. The XML specification provides an open framework for exchanging complex, structured documents (such as purchase orders, invoices, insurance claims, etc.) among different computer systems. In one fell swoop, XML eliminates network dependencies such as TCP/IP or IPX, protocol dependencies such as SQL*Net or ODBC, hardware dependencies such as Intel or Alpha, operating system dependencies such as Windows NT or Unix, and even database dependencies such as Oracle or SQL Server. In fact, the implications of XML are so profound that it even threatens the Fort Knox of the database world—delimited flat files!

While you might expect XML to be enormously complicated, it's really just a formal implementation of a wonderfully simple idea: that the structure and meaning of a document's contents should be indicated inside, not outside, the text of the document itself. An example can help make this idea clear. Suppose you receive the following comma-delimited file:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

You probably noticed that the invoice example looks remarkably similar to a standard HTML document, except that there are a lot of new tags. These similarities are intentional. The XML specification was created in response to the evolution (some would say devolution) of HTML.

HTML started as a simple way to define the structure of a document. The <head> and <body> tags separate descriptive information from the main text. The header tags (<h1>, <h2>, etc.) break the text into logical sections, much like the A and B headings in an outline. The emphasis tag <em> denotes particularly important information.

As the Web has evolved, however, the original intent of these tags has been lost. They are now used to control a document's appearance, rather than its structure. Browser vendors have exacerbated this trend by adding new tags explicitly for formatting. Some of these tags have been good (<table>), some not so good (<blink>), but the net effect is that HTML no longer has much to say about the purpose of the information it presents.

While this trend is not particularly important for many applications, such as creating attractive user interfaces for our PL/SQL systems, there are several reasons why it has been a change for the worse:

HTML is no longer simple.
HTML designers place more emphasis on a document's appearance than on its content.
HTML documents are very difficult for computers to understand.

The last of these problems is probably one of the most important motivations for XML. As the Web becomes increasingly automated, it has become more and more important that software "robots" understand and interpret a variety of documents. If we're ever going to make a search engine smart enough so that the query "Where can I buy a leather attaché case?" doesn't turn up links to an S&M site, we must create online catalogs a computer can easily parse and understand. HTML is simply not designed to provide this type of information. XML is.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

XML achieves its flexibility by allowing you to extend a base markup language (the XML specification itself) with tags of your own design. You create tags to structure the text within a document so that its underlying meaning is clearly presented. For example, to denote an item on an invoice, you could use an <ITEM> tag.

While XML and HTML documents look a lot alike, there are several important syntactical differences. HTML is fairly flexible. You can omit end tags from many of an HTML document's most important structures, such as list items, and most browsers will happily display the document as best they can. XML documents, however, must meet a more rigid set of requirements:

A document must begin with a line that identifies it as XML. It must also include the XML specification with which it complies. Since XML is a brand-new standard, this line is currently <?xml version="1.0"?>.
Tags are case sensitive. For example, <INVOICE_NUMBER> and <invoice_number> are not the same. In general, the convention is to always use uppercase.
All attribute values must appear in quotes, as in <CUSTOMER CUST_ID="12345">.
A start tag must always have a corresponding end tag. The combination of a start tag (plus any attributes), an end tag, and any intervening text is called an element.
Elements cannot overlap. For example, the following set of markups is illegal: <INVOICE_ITEM><PART_NUM>PN-1234</INVOICE_ITEM></PART_NUM>.
"Empty" tags that don't mark up any text, like HTML's <p> or <br>) must have corresponding end tags. For example, if you want to use a <PAID_IN_FULL> tag to indicate that an invoice has been paid, you must end with a </PAID_IN_FULL> tag, even though there is no text in between. XML also has an alternative notation for empty tags that lets you simply append a "/" to the end of the start tag (for example,

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

If we're going to use XML to exchange documents electronically, we must be able to judge whether a document meets a certain set of necessary requirements. For example, an electronic invoice must, at minimum, include an invoice number, a date, and at least one item. Our systems should be smart enough to reject an invoice if it doesn't contain the required information. Additionally, we should be able to create these requirements ourselves.

You can associate a document type definition (DTD) with an XML document to enforce these sorts of rules. You can either create a DTD or use one that already exists. A major goal of XML is to encourage various groups (industry, community, academic, etc.) to form standards bodies to define collective DTDs. Eventually, these DTDs will form the basis for a variety of electronic data exchange systems.

A DTD is a lot like a database schema. Just as you would define the columns in a database table, you can use a DTD to define the name and datatype of every element that can appear in an XML document. Just as you define a column constraint, you can require that particular elements appear within the document. Just as you would normalize a set of database tables into one-to-many or one-to-one relationships, you can create the same relationships by defining how the elements can be hierarchically nested.

Let's revisit the invoice example from the beginning of this chapter. If we were to simply model a basic invoice using an entity relationship diagram (ERD), we might wind up with something like Figure 9.2.

Figure 9.2: An ERD for a simple invoice

We can use this diagram as a guide to constructing a corresponding DTD. For clarity, though, we'll start with the finished DTD and work backwards:

<!ELEMENT INVOICE (INVOICE_NUMBER, DATE, CUSTOMER+,INVOICE_ITEMS,TOTAL?)>
   <!ELEMENT INVOICE_NUMBER (#PCDATA)>
   <!ELEMENT DATE (#PCDATA)>
   <!ELEMENT CUSTOMER (#PCDATA)>
   <!ELEMENT INVOICE_ITEMS (ITEM+)>
      <!ELEMENT ITEM (ITEM_NAME, QUANTITY, PRICE)>
         <!ELEMENT ITEM_NAME (#PCDATA)>
         <!ATTLIST ITEM_NAME
            ITEM_NUM CDATA #REQUIRED>
      <!ELEMENT QUANTITY (#PCDATA)>
      <!ELEMENT PRICE (#PCDATA)>
   <!ELEMENT TOTAL (#PCDATA)>

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

The XML parser is responsible for reading an XML document and making sure it complies with the necessary rules. There are two kinds of parsers: non-validating and validating. The non-validating parser is the simpler of the two, and simply checks to see if a document is well-formed. The more complex validating parser will not only check for well-formedness, but also for validity (i.e., that the document actually follows all the rules laid out in an associated DTD).

If the document passes these tests, then the parser breaks it into a structure called a document tree. As the name implies, a document tree is simply a hierarchical data structure created from the nested elements in the document. The left-hand side of Figure 9.1 illustrates a typical document tree.

Once the document is parsed and loaded, you can use a wide variety of languages (such as Java, JavaScript, etc.) to write programs that use the Document Object Model (DOM) API to traverse and manipulate the information in the tree. For example, you could write a JavaScript program to build a hierarchical view of an XML invoice by programmatically expanding and collapsing the tree's branches. This sort of client-side manipulation is very fast because it acts on information stored in memory, rather than having to requery the server each time a user requests a new view of the same information.

In the next two sections, we'll see how you can generate XML documents from information stored in the Oracle database. In the first, we'll develop a package to build an XML invoice. In the second, we'll look at a set of experimental packages that you can use to build a variety of XML applications.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Like HTML, XML is stored in plain ASCII documents. Consequently, we can use the PL/SQL toolkit to generate almost any XML document. In this section we'll write a package called XML_INVOICE_PKG to generate the XML invoice we've been discussing.

We'll start, as always, with the package specification. For this particular application, we'll need just one procedure: print_invoice. The procedure will accept the invoice number for a particular invoice and generate the corresponding XML invoice. Here's the code:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE xml_invoice_pkg
IS
   
   PROCEDURE print_invoice (
      i_invoice_number IN VARCHAR2 DEFAULT NULL
   );
END;

The next step is to define the package body, as follows:

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE BODY xml_invoice_pkg
IS
   -- Include code annotated below
END;

In addition to the print_invoice procedure defined in the specification, we'll need a private function, get_attribute, and two private procedures, print_xml_tags and print_items. The first two items are needed to format the output to the XML specification, since the PL/SQL toolkit doesn't have functions or procedures specifically for XML. The other local procedure fetches the invoice items from the database and prints them to the web browser. Table 9.2 lists the procedures and functions required in the package body.

Table 9.2: XML_INVOICE_PKG Procedures and Functions
Procedure/Function	Parameters	Description
get_attribute

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Steve Muench, Oracle Corporation's "XML evangelist," has developed a very interesting set of PL/SQL packages called the PLSXML utilities and demos. While they are still experimental, they can give you a clear idea of the power of generating XML inside the database. Additionally, there are several examples of how to use JavaScript and XML to create rich interfaces. The PLSXML packages are:

DBXML: Uses dynamic SQL to automatically create XML documents from a SQL query. Based on the same technology as WebDB, DBXML reduces the package we developed earlier to a single procedure call.
DBDOM: A PL/SQL implementation of the Document Object Model (DOM, the model used to create document trees) API that allows you to create, parse, and search XML documents.
DBXSL: Generates formatting instructions called XSL stylesheets that control how the browser renders an XML document.

You can download the PLSXML and its full documentation (it's excellent) from:

https://www.oracle.com/xml/plsxml/index.html

In the next section, we'll look at a product that takes full advantage of XML.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Oracle8i's Internet File System (iFS) has a built-in XML parser you can use to store XML documents directly in the database. As more and more vendors (including Microsoft, a big proponent of the XML standard) "XML-enable" their products, the iFS parser will become more and more useful. Widespread adoption of the XML format, as opposed to proprietary formats, will help alleviate the common, frustrating, and usually contentious problem of importing data from end user productivity tools into relational databases.

For instance, take spreadsheets. There is no denying the fact that these wonderful tools can help even the most unsophisticated (at least in terms of computer experience) user perform meaningful and important tasks. An analyst might use a spreadsheet to solve a finance problem, a manager might use one to schedule the phases of a project, and an accountant might use one to do almost anything. Some companies even use spreadsheets to create client invoices. Because they are easy to use and widely available, spreadsheets have become primary business tools, perhaps second only to word processors. While spreadsheets have many benefits, however, they have also created difficult information management problems.

For example, companies have spent millions of dollars on relational databases only to see them circumvented by spreadsheets. End users complain, sometimes quite correctly, that systems developed by IS are too complex or time consuming. Consequently, users simply create their own offline versions out of a ragtag collection of spreadsheets. As a result, the critical business information these database systems are designed to collect is strewn randomly throughout hundreds of spreadsheets on dozens of machines. This data is decentralized, unanalyzed, and insecure.

To remedy this problem, many companies try to force users to adopt standards. However, as anyone who has worked in IS knows, there are few issues that stir up more controversy than trying to replace a tool users like, such as a spreadsheet, with one that they don't like, such as a database. Any attempt to do so usually winds up in an "us versus them" battle. End users see IS taking away the tools they need to do their job, and IS sees end users wasting valuable company resources (time, money, sanity, etc.) by refusing to even consider the benefits of different approaches. In most cases, IS is on the losing end of these political battles.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

In this appendix, I've pulled together a variety of resources—both online and offline—that you'll find helpful as you develop Oracle web applications.

Section A.1: Books

Section A.2: Other Publications

Section A.3: Organizations

Section A.4: Web Sites

Section A.5: Discussion Groups

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

There are many books on the market describing Oracle and the Web. I list here those titles I find the most helpful and accurate:

Arnold, Ken, and James Gosling. The Java Programming Language (Addison-Wesley, 1997). Coauthored by the creator of Java, this valuable introduction explains much of the rationale behind the language.

Brown, Bradley D., Richard J.Niemiec, and Joseph C.Trezzo. Oracle Application Server Web Toolkit Reference (Oracle Press, 1998). This large book contains a lot of helpful information about OAS and Oracle's other web products.

Dynamic Information Systems, LLC. Oracle Web Application Server Handbook (Oracle Press, 1997). This book is not current with the latest Oracle software, but it contains useful discussions of building web systems with PL/SQL and other tools.

Eckel, Bruce. Thinking in Java (Prentice Hall Computer Books, 1998).A popular and comprehensive introduction to Java that doesn't pull any punches when it comes to criticizing aspects of the language that could be improved.

Feuerstein, Steven, and Bill Pribyl.Oracle PL/SQL Programming, Second Edition (O'Reilly & Associates, 1997). Hands down, the definitive guide to PL/SQL: if you need information about PL/SQL, it's in this book. The second edition has been updated for Oracle8.

Feuerstein, Steven. Oracle PL/SQL Programming: Guide to Oracle8i Features, (O'Reilly & Associates, 1999). This small book supplements Oracle PL/SQL Programming by providing an up-to-date discussion of the Oracle8i enhancements to PL/SQL.

Feuerstein, Steven, Charles Dye, and John Beresniewicz. Oracle Built-in Packages, O'Reilly & Associates, 1998. The follow-up volume to Oracle PL/SQL Programming. Presents detailed information about the vast array of packages built into the Oracle database.

Flanagan, David. Java in a Nutshell, Second Edition, (O'Reilly & Associates, 1997). Provides a compact reference to the classes, methods, and variables in the Java API, practical real-world example programs, and a comprehensive overview of Java.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

Select: This publication, produced as a membership benefit by the International Oracle Users Group-Americas (IOUG-A), contains a variety of articles and columns on web issues, as well as web-related tips, techniques, and practices.
Oracle Magazine: This magazine, published by Oracle Corporation, is primarily a marketing tool, but also carries articles (often by Oracle technical staff) on current web issues.

In addition, many Oracle user groups and special interest groups publish newsletters that contain useful information for Oracle web developers.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

International Oracle Users Group-Americas (IOUG-A)

401 North Michigan Avenue
Chicago, IL 60611
Voice: 1-312-245-1579
Fax: 1-312-527-6785
Email: iouga@ioug.org

Asia-Pacific Oracle Users Group

PO Box 3046
The Pines, Doncaster East
VIC 3109, Australia
Voice: +61 3 9842 3246
Fax: +61 3 9842 3050
Email: 100242.1746@compuserve.com

European Oracle Users Group (EOUG)

Brigittenauer Lände 50-54
A-1203 Vienna, Austria
Voice: +43 1 33777 870
Fax: +43 1 33777 873
Email: eoug@at.oracle.com

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

https://www.oracle.com: The web site of Oracle Corporation. Contains a wide variety of pages of interest to Oracle web developers, as well as links to other web development resources, including Oracle Support.
https://www.ioug.org: Operated by the IOUG-A. Contains technical articles from Select magazine, papers from IOUG-Alive conferences, a technical discussion forum, and other areas of interest to Oracle web developers.
https://www.eoug.org: The web site of the EOUG. Contains information of general interest to Oracle web developers, information about EOUG conferences and educational events, and information about European, Middle Eastern, and African user groups.
https://apoug.oracle.com.sg: The web site of the Asia Pacific Oracle Users Group. Contains information of interest to Oracle users in the Asia and Pacific Rim regions, as well as useful links to other Oracle resources.
https://www.oug.com: Operated by a consortium of Oracle user groups and special interest groups. Contains a number of useful Oracle-related links.
https://www.revealnet.com: An online resource with useful articles and discussion forums for DB2 and Oracle developers.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy reprint rights for this chapter

news:comp.databases.oracle.server: This discussion group features a wide variety of information relating to Oracle server technology. It should be of interest to both new and experienced Oracle web developers
https://www.ioug.org: This web site includes a discussion area where items of interest to Oracle web developers are posted and discussed.
https://www.revealnet.com: Operated by a commercial vendor, RevealNet, but contains many free and useful resources. See especially the PL/SQL Pipeline, a discussion forum for PL/SQL developers. Includes archives full of papers and software, monthly tips and puzzles, and other useful links.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Table of Contents

Section 1.4.2.1: Why HTML?

Section 3.1.1.1: Browse database objects

Section 4.2.1.1: Database Access Descriptor

Section 4.2.1.2: PL/SQL agent

Section 6.2.2.1: Declaring a variable

Section 7.2.1.1: Data structures