Building Oracle XML Applications

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Internet is driving an unprecedented demand for access to information. Seduced by the convenience of paying bills, booking flights, tracking stocks, checking prices, and getting everything from gifts to groceries online, consumers are hungry for more. Compelled by the lower costs of online outsourcing and the ability to inquire, day or night, "What's the status?," businesses are ramping up to reap the rewards. Excited by improved efficiency and universal public access, governments are considering how all kinds of raw data, from financial reports to federally funded research, can be published online in an easily reusable format.

More than ever before, database-savvy web application developers working to capitalize on these exciting Internet-inspired opportunities need to rapidly acquire, integrate, and repurpose information, as well as exchange it with other applications both inside and outside their companies. XML dramatically simplifies these tasks.

As with any new technology, you first need to understand what XML is, what you can do with it, and why you should use it. With all the new terms and acronyms to understand, XML can seem like a strange new planet to the uninitiated, so let's walk before we run. This chapter introduces "Planet XML" and the "moons" that orbit it, and provides a high-level overview of the tools and technology Oracle offers to exploit the combined strengths of XML and the Oracle database in your web applications.

First, let's look at some basic XML definitions and examples.

XML, which stands for the "Extensible Markup Language," defines a universal standard for electronic data exchange. It provides a rigorous set of rules enabling the structure inherent in data to be easily encoded and unambiguously interpreted using human-readable text documents. Example 1.1 shows what a stock market transaction might look like represented in XML.

Example 1.1. Stock Market Transaction Represented in 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 PDF of this chapter|Buy reprint rights for this chapter

First, let's look at some basic XML definitions and examples.

XML, which stands for the "Extensible Markup Language," defines a universal standard for electronic data exchange. It provides a rigorous set of rules enabling the structure inherent in data to be easily encoded and unambiguously interpreted using human-readable text documents. Example 1.1 shows what a stock market transaction might look like represented in XML.

Example 1.1. Stock Market Transaction Represented in XML

<?xml version="1.0"?>
<transaction>
  <account>89-344</account>
  <buy shares="100">
    <ticker exch="NASDAQ">WEBM</ticker>
  </buy>
  <sell shares="30">
    <ticker exch="NYSE">GE</ticker>
  </sell>
</transaction>

After an initial line that identifies the document as an XML document, the example begins with a <transaction> tag. Nested inside this opening tag and its matching </transaction> closing tag, other tags and text encode nested structure and data values respectively. Any tag can carry a list of one or more named attribute="value" entries as well, like shares=" nn " on <buy> and <sell> and exch=" xxx " on <ticker>.

XML's straightforward "text with tags" syntax should look immediately familiar if you have ever worked with HTML, which also uses tags, text, and attributes. A key difference between HTML and XML, however, lies in the kind of data each allows you to represent. What you can represent in an HTML document is constrained by the fixed set of HTML tags at your disposal—like <table>, <img>, and <a> for tables, images, and anchors. In contrast, with XML you can invent any set of meaningful tags to suit your current data encoding needs, or reuse an existing set that someone else has already defined. Using XML and an appropriate set of tags, you can encode data of any kind, from highly structured database query results like 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 PDF of this chapter|Buy reprint rights for this chapter

Beyond encoding data in a textual format, an XML document doesn't do much of anything on its own. The true power of XML lies in the tools that process it. In this section, we take a quick tour of the interesting ways to work with XML documents using tools and technologies widely available today from a number of different vendors.

Since an XML document is just text, you can:

View and edit it with vi, Emacs, Notepad, or your favorite text editor
Search it with grep, sed, findstr, or any other text-based utility
Source-control it using systems like CVS, ClearCase, or RCS

These and other tools can treat an XML file the same as any other text file for common development tasks.

More sophisticated XML editing tools read an XML DTD to understand the lexicon of legal tag names for a particular XML vocabulary, as well as the various constraints on valid element combinations expressed in the DTD. Using this information, the tools assist you in creating and editing XML documents that comply with that particular DTD. Many support multiple views of your XML document including a raw XML view, a WYSIWYG view, and a view that augments the WYSIWYG display by displaying each markup tag.

As an example, this book was created and edited entirely in XML using SoftQuad's XMetal 1.0 product in conjunction with the DocBook DTD, a standard XML vocabulary for authoring technical manuals. Figure 1.2 shows what XMetal looks like in its WYSIWYG view with tags turned on, displaying an earlier version of the XML source document for this very chapter.

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 PDF of this chapter|Buy reprint rights for this chapter

Why have vendors like Oracle, IBM, Microsoft, Sun, SAP, and many others moved so fast to support XML? After all, these companies have worked for many years to fine-tune the efficiency of their proprietary data formats and tools. The reason is simple: as a vendor-neutral, platform-neutral, language-neutral technology for web-based data exchange, the XML family of standards solves a key problem for these companies' customers. In a nutshell, XML simplifies the task of connecting applications and services over the Web.

Proprietary data formats undoubtedly represent data in a more efficient way, but what XML sacrifices in compactness, it gains many times over in flexibility. If you can publish an XML datagram on the "wire," anyone connected to the Internet can receive the datagram, parse it, manipulate it, search it, and transform it using a wide selection of freely available tools that implement the XML family of standards. Skeptics who dwell on XML's apparent inefficiencies ("It's more tags than data, for heaven's sake!," they say), haven't yet understood how its usefulness grows when it is combined with the Internet's universal reach. The HTML standard and the essentially free cost of HTML-savvy browsers sparked the creation of the Web as we know it today: a sea of information available 24 hours a day for a pair of human eyeballs focused on a browser to exploit. The XML family of standards and the low cost of many XML-savvy tools have already begun to spark an analogous "data web" of Internet-based machine-to-machine information exchanges. XML is on track to have an even greater impact on the computing landscape than HTML has had.

Critics are correct to point out that XML is no magic bullet, noting that companies still need to agree on common XML vocabularies for application data exchange. However, market forces are already quickly resolving these concerns. Due to the tremendous opportunities presented by business-to-business e-commerce, a burgeoning sector whose key players are already leveraging this new XML-based data web, the number of XML-based standards for domain-specific business messages is growing exponentially. By tapping into the XML data web, you can save money by leveraging outsourced business content and services, and generate new revenue by publishing slices of your own valuable data as web services to be "consumed" by other partners.

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 PDF of this chapter|Buy reprint rights for this chapter

Now that we understand what the XML family of standards is, what we can do with it, and why it is interesting to apply to database-driven web applications, let's get an overview of the tools and technologies that Oracle provides to implement Oracle XML applications. Figure 1.11 shows an example of the key Oracle XML components and how they relate to the XML standards we've discussed earlier.

Figure 1.11: Overview of key Oracle technologies for XML

Using the Oracle XML Parser, you can parse XML documents into their infoset, manipulate their contents, and return the modified infoset back into XML format. Using the Oracle XSLT processor, you can transform XML into XML, HTML, or text of another structure. Both the Oracle XML Parser and the Oracle XSLT processor share the common Oracle XPath Engine that enables querying an XML document's infoset to select interesting subsets of its information. The Oracle XML SQL Utility automates the tasks of producing XML from SQL query results and storing XML documents into tables in the database. Oracle interMedia Text enables indexing and querying of XML documents or document fragments, with rich support for leveraging the structure of the XML in the query criteria. These core components are used by more than 40 of Oracle's own internal development teams, so their quality, performance, and conformance to W3C standards are very high.

Building on these core Oracle XML technologies, the Oracle XSQL Pages system is an extensible XML publishing framework that makes combining the strengths of SQL, XML, and XSLT a declarative walk in the park. It simplifies the job of assembling XML data pages from multiple sources and transforming their information content for delivery using XSLT. Table 1.1 provides a summary of key Oracle XML technologies and the chapters that cover each one.

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 PDF of this chapter|Buy reprint rights for this chapter

In this chapter, I cover the essential technical details you will need to work with XML documents. I also devote a section to learning the powerful XPath language, which you can use to flexibly search XML documents.

Building on the high-level overview in Chapter 1, here we drill down in more detail to some of the specifics of working with XML documents.

As we saw in the examples in Chapter 1, XML can represent virtually any kind of structured information. A coherent set of elements and attributes that addresses a particular application need is called an XML vocabulary. The elements and attributes are the "words" in the vocabulary that enable communication of information on a certain subject. An XML vocabulary can be as simple as a single element—for example a <Task>, or can contain as many elements and attributes as you need. An example document that uses the <Task> vocabulary looks like this:

<Task Name="JDeveloper 3.1">
   <Task Name="Improved XML Support">
     <Task Name="Syntax-Check XML/XSL" Dev="Steve"/>
     <Task Name="Color-Coded Editing"  Dev="Yoshi"/>
     <Task Name="Run XSQL Pages"       Dev="Bret"/>
   </Task>
   <Task Name="Improved Debugging Support">
     <Task Name="Remote Debugging">
       <Task Name="JServer Debugging"      Dev="Jimmy"/>
       <Task Name="Apache JServ Debugging" Dev="Liz"/>
     </Task>
   </Task>
</Task>

One of the big attractions about working with XML is its low cost of admission. The specification is free to be used by anyone, and you only need a text editor to get started. One way to begin creating your own XML vocabulary is to simply start typing tags in a text file as they come to your mind. For example, if you've been assigned the task of managing a "Frequently Asked Questions" (FAQ) list, you might open up vi or Emacs and start typing the example shown in Figure 2.1.

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 PDF of this chapter|Buy reprint rights for this chapter

Building on the high-level overview in Chapter 1, here we drill down in more detail to some of the specifics of working with XML documents.

As we saw in the examples in Chapter 1, XML can represent virtually any kind of structured information. A coherent set of elements and attributes that addresses a particular application need is called an XML vocabulary. The elements and attributes are the "words" in the vocabulary that enable communication of information on a certain subject. An XML vocabulary can be as simple as a single element—for example a <Task>, or can contain as many elements and attributes as you need. An example document that uses the <Task> vocabulary looks like this:

<Task Name="JDeveloper 3.1">
   <Task Name="Improved XML Support">
     <Task Name="Syntax-Check XML/XSL" Dev="Steve"/>
     <Task Name="Color-Coded Editing"  Dev="Yoshi"/>
     <Task Name="Run XSQL Pages"       Dev="Bret"/>
   </Task>
   <Task Name="Improved Debugging Support">
     <Task Name="Remote Debugging">
       <Task Name="JServer Debugging"      Dev="Jimmy"/>
       <Task Name="Apache JServ Debugging" Dev="Liz"/>
     </Task>
   </Task>
</Task>

One of the big attractions about working with XML is its low cost of admission. The specification is free to be used by anyone, and you only need a text editor to get started. One way to begin creating your own XML vocabulary is to simply start typing tags in a text file as they come to your mind. For example, if you've been assigned the task of managing a "Frequently Asked Questions" (FAQ) list, you might open up vi or Emacs and start typing the example shown in Figure 2.1.

Figure 2.1: Creating a new XML document using Emacs

It's very useful to just prototype your vocabulary of tags by working directly on an example document. It makes the process easy to think about. As ideas pop into your head—for example, "I'm going to need to keep track of who

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 PDF of this chapter|Buy reprint rights for this chapter

XML entities and namespaces provide two techniques to modularize the contents of XML documents. In this section we study simple examples of both.

Entities are a mechanism for defining named string substitution variables in your XML file. They can save typing repetitive text in your documents. For example, if the Frequently Asked Questions document that we're working on is for the Oracle JDeveloper 3.1 product, the text Oracle JDeveloper and the current version number of the product might appear in many of the questions and answers. We can define two entities to represent this repetitive text using the syntax:

<!ENTITY jdev "Oracle JDeveloper">
<!ENTITY ver  "3.1">

With these in place we can refer to the entities by name in the contents of our FAQ.xml document with the syntax:

<Question>What is the current version of &jdev;?</Question>
<Answer>The current version is &jdev; &ver;</Answer>

The syntax for user-defined entities is identical to that of the built-in entities we saw earlier (<, >, ', and "). References to user-defined entities like &jdev; start with an ampersand character and end with a semicolon, with the entity name in between. Like the default attribute values we saw earlier, these entities can be defined in an external DTD file, or right in the current document by including them in the local DTD section known as the internal subset . The syntax for including these entity definitions in the document you're working on (when you do not control the DTD or when the entities are not interesting enough to be placed there for global use) looks like this:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd"[
  <!-- Internal subset adds local definitions -->
  <!ENTITY jdev "Oracle JDeveloper">
  <!ENTITY ver  "3.1">
]>
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="smuench@oracle.com" Level="Beginner">
    <Question>What is the current version of &jdev;?</Question>
    <Answer>The current version is &jdev; &ver;</Answer>
  </FAQ>
</FAQ-List>

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 PDF of this chapter|Buy reprint rights for this chapter

When you organize files into directories on a disk, you use the directories to give some meaningful structure to your files. Familiar commands like dir or ls allow you to search for matching files that have been organized into those nested directories. Consider the directory hierarchy of drive M: as shown in Figure 2.9 using the Windows Explorer.

Figure 2.9: Browsing hierarchical directory structure

In order to search this hierarchy, you use a path notation to indicate what files you are trying to find. The path notation for searching a Unix filesystem, for example, allows you to use the notation /MovieList/Movie/Cast to refer to the Cast subdirectory under the Movie subdirectory of MovieList, or /MovieList/Movie/Cast/*to refer to any file in that directory.

To list all the files in the Actor subdirectory, you can issue the command:

ls /MovieList/Movie/Cast/Actor/*

which produces this list of matching files:

/MovieList/Movie/Cast/Actor/First
/MovieList/Movie/Cast/Actor/Last
/MovieList/Movie/Cast/Actor/Award

As we saw in Chapter 1, XML documents also have an industry-standard path notation for searching their content, called XPath. You'll find its syntax easy to understand because of its basic similarity to what we saw earlier for files in directories. We'll see with several examples (and throughout this book) that the searches you can do with the XPath notation over an XML document are much more powerful than the simple file listings illustrated above.

Table 2.1 summarizes the syntax for the most common XPath expressions.

Table 2.1: Syntax Summary for the Most Common XPath Expressions
XPath Expression Syntax

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 PDF of this chapter|Buy reprint rights for this chapter

In this chapter, we'll get a taste for what the rest of the book is about: making XML a lot more interesting by combining it with the power of your Oracle database. We explore a scenario of a growing company that needs to integrate and share information for a frequently asked questions "knowledge bank" and put the information to use in a variety of formats.

Don't worry if we move quickly through a lot of the details here. You'll learn how to do everything we cover in this rapid-fire walkthrough—and much, much more—in the numerous examples throughout the rest of the book. This chapter is meant as a preview. So buckle yourself in—the ride is about to start.

Recall the frequently asked questions document from Chapter 2:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd">
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="derek@spinaltap.com" Level="Advanced">
    <Question>Are we going to play Stonehenge?</Question>
    <Answer>But of course</Answer>
  </FAQ>
</FAQ-List>

Let's assume that we're working at a small Internet startup company. We start out with a single, small XML file using the FAQ-List.dtd vocabulary similar to the one described earlier. In the beginning, only a single person is in charge of editing the FAQ file; the number of questions is small, and the number of products we sell is tiny, too. Then, as we develop and ship newer products, we naturally hire more people to help in the process, and now each product team wants to manage its own XML FAQ file. So we split up the frequently asked questions into one XML FAQ file per product team—all still sharing the FAQ-List.dtd format. But inevitably:

Our products get more and more popular.
Our list of frequently asked questions begins to grow.

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 PDF of this chapter|Buy reprint rights for this chapter

Recall the frequently asked questions document from Chapter 2:

<?xml version="1.0"?>
<!DOCTYPE FAQ-List SYSTEM "FAQ-List.dtd">
<FAQ-List>
  <FAQ Submitter="smuench@oracle.com">
    <Question>Is it easy to get started with XML?</Question>
    <Answer>Yes!</Answer>
  </FAQ>
  <FAQ Submitter="derek@spinaltap.com" Level="Advanced">
    <Question>Are we going to play Stonehenge?</Question>
    <Answer>But of course</Answer>
  </FAQ>
</FAQ-List>

Let's assume that we're working at a small Internet startup company. We start out with a single, small XML file using the FAQ-List.dtd vocabulary similar to the one described earlier. In the beginning, only a single person is in charge of editing the FAQ file; the number of questions is small, and the number of products we sell is tiny, too. Then, as we develop and ship newer products, we naturally hire more people to help in the process, and now each product team wants to manage its own XML FAQ file. So we split up the frequently asked questions into one XML FAQ file per product team—all still sharing the FAQ-List.dtd format. But inevitably:

Our products get more and more popular.
Our list of frequently asked questions begins to grow.
Our customers begin demanding to search FAQs across all products.
Our customers insist on flexible, fast searching on multiple criteria.
Our competition launches a personalized FAQ portal page for web developers.

Help! We need a powerful database to go with our XML. So we create a faq_table to hold all of our frequently asked questions:

create table faq_table(
  category     VARCHAR2(30),
  question     VARCHAR2(4000),
  answer       VARCHAR2(4000),
  qlevel       NUMBER,
  submitted_by VARCHAR2(80)
);

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 PDF of this chapter|Buy reprint rights for this chapter

We see something interesting in the Oracle XSQL Pages material explaining that we can create a new XSQL page that reuses the dynamic XML content produced by another page with a tag called <xsql:include-xsql> . We also notice that by including one extra line at the top of our page, like this:

<?xml-stylesheet type="text/xsl" href="FAQ-In-XML.xsl"?>

we can get the XSQL page processor to handle the XSLT transformation automatically before it returns the results to the requester. So we combine these two little pearls of information to create a new XSQL page called FAQXML.xsql:

<?xml version="1.0"?>
<!-- FAQXML.xsql: Show FAQ.xsql in Standard FAQ-List.dtd Vocabulary  -->
<?xml-stylesheet type="text/xsl" href="FAQ-In-XML.xsl"?>
<xsql:include-xsql href="FAQ.xsql" xmlns:xsql="urn:oracle-xsql"/>

This uses <xsql:include-xsql> to include the XML results from our previous work, and associates the FAQ-In-XML.xsl stylesheet we created above to this page. Now, when we try from our browser to request the new XSQL page for the jdev category of frequently asked questions, as shown in Figure 3.2, we see our dynamic information in our <FAQ-List> format with no manual steps involved. Pretty neat.

Figure 3.2: Viewing the dynamic FAQ in the FAQ-List XML format

Just then, we learn that our CEO is expanding into Europe by acquiring another promising startup in London. In a parallel universe, the London company had also been working with XML to keep track of customer questions and answers. However, they were following an established British standard XML vocabulary that they found on the Web, described by the Frequently-Posed-Queries.dtd.

We quickly conclude that this is not a problem and offer to host all of their customers' frequently posed queries in our faq_table. We cobble together a FAQ-In-UKFAQ.xsl XSLT stylesheet to transform our

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 PDF of this chapter|Buy reprint rights for this chapter

We check out the ServletsFAQ.xml file on Sun's site, which they update periodically, and notice that it has a format like this:

<?xml version = '1.0' encoding = 'UTF-8'?> 
<Servlets-FAQ>
  <FAQ>
    <Q>What's the next major release of the Servlet API?</Q>
    <A>Servlet API 2.2</A>
  </FAQ>
  <FAQ>
    <Q>How do I set the age of a cookie?</Q>
    <A>yourCookie.setAge(120);</A>
  </FAQ>
  <FAQ>
    <Q>How do I save a variable in a per-user session?</Q>
    <A>request.getSession(true).putValue('varname','value');</A>
  </FAQ>
</Servlets-FAQ>

We want this Servlet FAQ content and others like it from Sun's site to reside in our database so we can search over it together with all of our own content. We learn that the Oracle XML SQL Utility can insert XML documents automatically into our faq_table if they have the canonical <ROWSET>/<ROW> structure, so we create another XSLT stylesheet to transform a document in Sun's <Servlets-FAQ> vocabulary into a <ROWSET>/<ROW> document:

<ROWSET xsl:version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:for-each select="/Servlets-FAQ/FAQ">
    <ROW>
      <CATEGORY>SERVLETS</CATEGORY>
      <QLEVEL>1</QLEVEL>
      <QUESTION><xsl:value-of select="Q"/></QUESTION>
      <ANSWER><xsl:value-of select="A"/></ANSWER>
      <SUBMITTED_BY>Sun</SUBMITTED_BY>
    </ROW>
  </xsl:for-each>
</ROWSET>

This time, the tables are turned and our XPath expressions in <xsl:for-each> are looping over Sun's XML format and constructing the <ROWSET>, <ROW>, and column-name elements required for the automatic insert. As in all of our other stylesheets, we use <xsl:value-of> to plug in the Q and A children element values from the current /Servlets-FAQ/FAQ element as the values of our <QUESTION> and <ANSWER> elements. Note that because Sun doesn't keep track of the difficulty level in the FAQ format, we're using a constant value of 1 in our stylesheet to default to a value that makes sense to our system. Also, we use a constant value of SERVLETS for the category to make sure that the information from FAQs for Servlets is placed in an appropriate category for searching through our faq_table.

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 PDF of this chapter|Buy reprint rights for this chapter

Whether you want to just work with XML and XSL files or you are a hardcore Java or PL/SQL developer, you'll find that JDeveloper 3.1 has lots of features to make your life easier. A few of the features that I personally use every single day of my life are:

Color-coded syntax highlighting for XML/XSLT editing
Auto-indenting and block indent/unindent to keep XML looking nice
Built-in XML syntax checking
Native support for running XSQL pages
Ability to browse all Oracle8i schema objects, including Java classes
Context-sensitive help as you type for methods and arguments
Fast jumping to source and JavaDoc for any class that pops into your head
Robust remote debugging support for Apache JServ, Tomcat, and others
Robust remote "in-the-database" debugging support for JServer

We'll cover what you need to know to exploit these JDeveloper 3.1 features in this chapter.

JDeveloper 3.1 ships with a number of helpful XML samples. These include sample XSQL pages, Java programs, and servlets to help you make sure your environment is properly set up to run all the examples in this book. Open the JDeveloperXMLExamples.jws workspace in the .\samples\xmlsamples subdirectory of your JDeveloper installation home directory to take a look.

This section describes the many ways JDeveloper helps you work with XML, XSQL and JavaServer Pages.

JDeveloper allows you to create workspaces to facilitate working on many different projects at once. The contents of all the projects in your current workspace are always visible and organized in alphabetical order in the project navigator, as shown in Figure 4.1. At any time, you can click your mouse in the project navigator and begin typing the initial letters of a file you are looking for—for example, the letters

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 PDF of this chapter|Buy reprint rights for this chapter

This section describes the many ways JDeveloper helps you work with XML, XSQL and JavaServer Pages.

JDeveloper allows you to create workspaces to facilitate working on many different projects at once. The contents of all the projects in your current workspace are always visible and organized in alphabetical order in the project navigator, as shown in Figure 4.1. At any time, you can click your mouse in the project navigator and begin typing the initial letters of a file you are looking for—for example, the letters myi—and a Search for pop-up appears. JDeveloper incrementally jumps you to the first file matching the letters you've typed in so far, as illustrated in the figure, so typically only a few letters are required to jump to the file you want.

Figure 4.1: Incrementally search for a file in a project

By selecting a project in the navigator and choosing Add Folder... from the right mouse button menu, you can create additional folders to further organize your files within a project.

To add new or existing files to a project, select the target project to make it active (displayed in bold in the navigator). Then click the Add File icon—the folder with a plus sign—at the top of the navigator pane. The File Open/Create dialog appears. Select an existing file or type the name of a new file you want to create, and click the Open button to dismiss the file dialog and add the file to your project.

If you select an existing file in your project before clicking the Add File icon, the file dialog will use the existing file's directory as the current directory for the File Open/Create dialog. This is handy if you'll be adding a file from the same directory as another file in your project.

To delete a file from your project, select the file in the project navigator. Then click the Delete File icon—the folder with a minus sign—at the top of the navigator pane. Confirm 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 PDF of this chapter|Buy reprint rights for this chapter

All of the examples in this book use a database account named XMLBOOK that you'll need to create to follow along and try out any code we discuss. In this section, we create an XMLBOOK user and explore JDeveloper 3.1's features for working with database objects in the development environment.

To create the XMLBOOK user, connect to your Oracle database as a DBA account like SYS or SYSTEM using the SQL*Plus tool and issue the following commands:

SQL> CREATE USER xmlbook IDENTIFIED BY xmlbook;
User created.
SQL> GRANT connect, resource, query rewrite TO xmlbook;
Grant succeeded.

Then try to connect as the new XMLBOOK user and try a simple SELECT statement by doing the following:

SQL> CONNECT xmlbook/xmlbook
Connected
SQL> SELECT sysdate FROM dual;
SYSDATE
---------
23-APR-00

If this works, then you're ready to go create a named connection for XMLBOOK inside the JDeveloper 3.1 environment.

JDeveloper 3.1 has a number of built-in features for working more easily with Oracle database objects. You can define any number of commonly used database connections that are then available for all workspaces and projects. After starting JDeveloper 3.1, you'll notice a Connections folder at the top of the project navigator. Double-clicking on this folder or selecting Connections... from the right mouse button menu option on the folder brings up the JDeveloper Connection Manager dialog. From here you can create, edit, delete, import, and export connection definitions for databases you frequently work with during development. Click the New... button to define a new connection to work with our XMLBOOK user. The Connection dialog shown in Figure 4.8 appears.

Figure 4.8: Defining a new named connection

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 PDF of this chapter|Buy reprint rights for this chapter

Oracle's XML Developer's Kit (XDK) includes many of the enabling XML technologies that we'll be using in the rest of this book. Among other components, it contains Java and PL/SQL versions of the following:

Oracle XML Parser
Oracle XSLT Processor
Oracle XML SQL Utility

In addition, it includes an Oracle XSQL Servlet that comes with a Java API for adding your own so-called action handlers and programmatically processing XSQL Pages templates. In the following sections, we'll discuss the basics of setting up JDeveloper to work with these Oracle XDK components in Java. While JDeveloper 3.1 does offer color-coded syntax editing of PL/SQL and the ability to browse stored procedures and run the SQL scripts against a named database connection, most of the true developer productivity features in the product target Java development (as you'd assume from the "J" in JDeveloper).

To dramatically simplify working with libraries of Java code in your projects, JDeveloper 3.1 has a facility called named libraries . Each library consists of:

A user-friendly library name like "Oracle XML Parser 2.0."
A Class path comprising one or more .jar files, .zip files, or directories separated by semicolons, containing the executable classes to support the library; for example, J:\lib\xmlparserv2.jar.
An optional Source path comprising one or more .jar files, .zip files, or directories containing the Java source code for the library.

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 PDF of this chapter|Buy reprint rights for this chapter

PL/SQL is Oracle's procedural language extension to SQL, and is the database programming language familiar to nearly all Oracle DBAs and application developers. In this chapter, we'll study lots of examples that illustrate how to perform basic XML processing using PL/SQL. In particular, we'll learn how to:

Load external XML files into the database
Parse XML using the Oracle XML Parser for PL/SQL
Search XML documents in memory using XPath expressions
Post an XML message to another server and get an XML response back
Enqueue and dequeue XML messages from Oracle Advanced Queuing (AQ) queues

In addition, in this chapter we'll cover the basic mechanics of producing XML automatically from SQL queries and transforming the results into any desired XML format using XSLT stylesheets. Both of these topics are covered in full in their own chapters later in the book. For an abbreviated overview of PL/SQL itself, see Chapter 10.

Along the way, we'll build up several useful PL/SQL packages that you can use to simplify basic XML processing from within your own PL/SQL stored procedures, functions, packages, and database triggers. The full source code for these XML helper packages appears in Appendix A, and is available on the O'Reilly web site. Let's dive right in.

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, "How do I get XML files in there?" While in Chapter 12, we explore numerous techniques available for storing XML in the database, here we'll start simple and work through the steps of loading an existing XML file into a CLOB column.

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 PDF of this chapter|Buy reprint rights for this chapter

When a developer is setting out to store the contents of an XML document in the database to perform queries over its content and structure, one of the first questions that arises is, "How do I get XML files in there?" While in Chapter 12, we explore numerous techniques available for storing XML in the database, here we'll start simple and work through the steps of loading an existing XML file into a CLOB column.

CLOB (pronounced "klob" or "see-lob") stands for C haracter L arge OB ject. You can think of a column of type CLOB as a very, very large VARCHAR2. It can hold character-based data like XML documents as large as four gigabytes (4GB).

Assume that we have an XML document like claim77804.xml below in a directory named C:\XMLFILES on the filesystem of the machine where our database is installed:

<!-- claim77804.xml -->
<Claim>
  <ClaimId>77804</ClaimId>
  <Policy>12345</Policy>
  <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
  </Settlements>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

Since operating systems differ in their file and directory naming conventions, Oracle abstracts these details with a logical directory object. You create a logical directory with the command:

CREATE DIRECTORY directoryname AS 'OS-Specific-Dirname';

You then use the logical directory name directoryname when working with files inside PL/SQL. In order to create a logical directory, use SQL*Plus to connect to the database as a user with the CREATE ANY DIRECTORY privilege. The SYS and SYSTEM users have this privilege by default, so the easiest way to proceed is to connect as SYS or SYSTEM and create the directory with:

CREATE DIRECTORY xmlfiles AS 'C:\xmlfiles';

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 PDF of this chapter|Buy reprint rights for this chapter

The Oracle XML Parser for PL/SQL provides PL/SQL developers with a set of APIs for parsing, manipulating, and transforming XML documents inside the database. As we'll see in Chapter 6, these same APIs are available to Java programmers as well using the Oracle XML Parser for Java. As illustrated in Figure 5.1, in Oracle8i Releases 1, 2, and 3—server versions 8.1.5, 8.1.6, and 8.1.7, respectively—the Oracle XML Parser for PL/SQL is a set of PL/SQL packages that expose the underlying functionality of the XML Parser for Java. In the 8.1.7 release, the XML Parser for Java is natively compiled inside the server for better performance.

Figure 5.1: XML Parser for PL/SQL wraps the XML Parser for Java

The packages included in the Oracle XML Parser for PL/SQL are listed in Table 5.1.

Table 5.1: Key PL/SQL Packages for Working with XML
PL/SQL Package Name	Description
`xmlparser`	Contains datatypes, functions, and procedures for parsing XML
`xmldom`	Contains datatypes, functions, and procedures for creating and manipulating the members of an XML document's object model like elements, attributes, text, comments, etc.
`xslprocessor`	Contains datatypes, functions, and procedures for transforming XML documents using XSLT stylesheets and searching an in-memory XML document using XPath expressions

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 PDF of this chapter|Buy reprint rights for this chapter

While processing XML documents and datagrams inside the database, you'll frequently find it necessary and convenient to search the content of the document you're processing. We've noted that the xmldom package provides a completely programmatic way of hunting through the entire tree of nodes of an XML document to find what you're looking for. We'll see in this section how XPath's searching of in-memory XML makes it all declarative and so much easier.

We saw in Chapter 2, that XPath expressions provide a compact, declarative syntax to describe any parts of an XML document you would like to address. Let's explore what opportunities exist for the PL/SQL developer to exploit XPath expressions to search in-memory documents.

Let's look again at our simple insurance claim XML document:

<!-- claim77804.xml -->
<Claim>
  <ClaimId>77804</ClaimId>
  <Policy>12345</Policy>
  <Settlements>
    <Payment Approver="JCOX">1000</Payment>
    <Payment Approver="PSMITH">1850</Payment>
  </Settlements>
  <DamageReport>
    The insured's <Vehicle Make="Volks">Beetle</Vehicle>
    broke through the guard rail and plummeted into a ravine.
    The cause was determined to be <Cause>faulty brakes</Cause>.
    Amazingly there were no casualties.
  </DamageReport>
</Claim>

We might want to answer the following questions about this document:

What is the value of the policy number for this claim?

We may want to store the policy number for this XML-based insurance claim in its own database column so we can more easily search, sort, and group by that key piece of information. To do this, we need the ability to retrieve the value of the XPath expression:

/Claim/Policy

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 PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll investigate examples of using XML as a structured data exchange mechanism between applications. First we'll look at synchronous approaches like posting and getting XML messages over HTTP; then we'll learn the basics of Oracle's Advanced Queuing (AQ) mechanism to support asynchronous XML message passing between applications using reliable queues.

Since XML can represent structured data in an open, standard way, it is quickly becoming the preferred method of data exchange over the Web. In a year or two, it will be the dominant method. Sites that serve up information in HTML—useful primarily to human eyeballs—will add the ability to retrieve the information in an XML-based format that will allow other servers to use that data more easily. Businesses whose current web-based applications only allow human interaction through web-based HTML forms are scrambling to add the ability to accept posted requests in XML formats to enable Business-to-Business automation.

In this way, the Web will rapidly evolve to offer a business application developer a sea of XML-based information services, where your application can check on the status of an order, cancel a reservation, or book a flight simply by sending and receiving appropriate XML datagrams. It goes without saying that the ability to write programs that post and receive XML is a core competence for any developer building the next generation of web applications. Here we'll explore how these key activities can be done with PL/SQL inside Oracle8i.

To be very precise, when we talk about "posting XML to another server over the Web," what we mean is sending an HTTP POST request to that server, containing an XML document in the request body with a MIME Content-Type of text/xml.

Therefore, a fundamental ingredient in posting XML over the Web is the ability to send an HTTP POST request. Since the HTTP protocol is a set of conventions layered on top of TCP/IP, we can make use of the 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 PDF of this chapter|Buy reprint rights for this chapter

In this section, we'll briefly cover the following mechanisms available to PL/SQL in Oracle8i for producing XML from SQL queries and for transforming XML using XSLT transformations:

The XML SQL Utility provides capabilities to automatically deliver the results of any valid SELECT statement as an XML document.
The Oracle XSLT processor implements a transformation engine for XML documents that is compliant with the W3C XSLT 1.0 Recommendation (see http://www.w3.org/TR/1999/REC-xslt-19991116), and that allows you to transform XML in one format into XML, HTML, or plain text of another format.

These topics are covered in detail in Chapter 7, and Chapter 9, so here we will focus mostly on the basic PL/SQL syntax of working with the XML SQL Utility and the Oracle XSLT processor. First, we'll cover the steps required to verify that these facilities are properly installed in your database, then we'll cover simple examples of their use.

First, check to see if the Oracle XML SQL Utility is already installed in your Oracle8i database by doing the following:

Connect to your Oracle8i database with SQL*Plus:
```
sqlplus xmlbook/xmlbook
```

Check the status of the oracle.xml.sql.query.OracleXMLQuery class by running the following SQL statement:

SELECT SUBSTR(dbms_java.longname(object_name),1,35) AS class, status
  FROM all_objects 
 WHERE object_type = 'JAVA CLASS'
   AND object_name = dbms_java.shortname('oracle/xml/sql/query/OracleXMLQuery')

You should see the result:

CLASS                               STATUS
----------------------------------- -------
oracle/xml/sql/query/OracleXMLQuery VALID

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 PDF of this chapter|Buy reprint rights for this chapter

In its relatively brief history, Java has become a dominant programming language for new software development projects and the main language taught to waves of new programmers in universities. Initially conceived as a portable language for client-side agents and user interfaces, Java's most rapid adoption has been for writing complex, server-side applications. Since nearly any interesting server-side application makes heavy use of a relational database, Oracle responded to the strong demand for server-side Java and database integration by introducing Oracle8i 's JServer product and has moved quickly to provide support for Java servlets and Java Server Pages ( JSPs) in its application server offerings. Starting with Oracle8i version 8.1.5, JServer has been provided with the database.

XML emerged in the age of Java and has been nearly inseparable from it. It is frequently said that, " Java is portable code, and XML is portable data"—a natural fit. In fact, from the beginning, the majority of software tools available for processing XML have been Java-based, and that tradition continues today. Vendors like Oracle and IBM—as well as organizations like the Apache Software Foundation—have done all of their XML innovation in Java first, with other language implementations—C, C++, PL/SQL, Perl, and others—being delivered in later phases. Given these dynamics, it's not hard to figure out why Oracle8i 's integration of rich server-side support for the industry's new standard for information exchange (XML) with the most popular server-side programming language ( Java) and the existing standard for data access and manipulation (SQL) has caught a lot of developers' attention. The fact that Java and PL/SQL can be used together seamlessly inside Oracle8i means that existing Oracle developers and DBAs can learn Java at their own pace while new college grads dive headlong into Java.

By the end of this chapter, you'll understand how to combine Java, JDBC, SQL, and XML—both outside and inside Oracle8i—in order to:

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·

Table of Contents