Oracle Web Applications: PL/SQL Developer's Intro

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	`http://www.castsoftware.com`
FROG (Funky Resource for Oracle Gorillas)	`http://www.507pm.com/pcs`
Oracle Procedure Builder	`http://www.oracle.com`
PLEdit	`http://www.benthicsoftware.com`
PL/Formatter	`http://www.revealnet.com`
SQL/Expediter	`http://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 dir

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