home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


9.5 Example: Generating an XML Invoice from Oracle

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

9.5.1 Specification

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

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

END;

9.5.2 Body

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

/* Formatted by PL/Formatter v.1.1.13 */
CREATE OR REPLACE PACKAGE BODY xml_invoice_pkg
IS

   -- Include code annotated below

END;

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


Table 9.2: XML_INVOICE_PKG Procedures and Functions

Procedure/Function

Parameters

Description

get_attribute

attr_name IN VARCHAR2

attr_val IN VARCHAR2

Private function that returns a well-formed attribute tag:

attr_name = "attr_val" .

print_xml_tag

tag_name IN VARCHAR2

tag_value IN VARCHAR2

tag_attr IN VARCHAR2

DEFAULT NULL

Private procedure that prints a well-formed XML tag:

<tag_name tag_attr>tag_value</tag_name) .

print_items

i_invoice_id IN NUMBER

o_invoice_total OUT NUMBER

Private procedure to print the individual items on the invoice. The OUT parameter returns the total dollar value for all items.

print_invoice

i_invoice_number IN VARCHAR2

DEFAULT NULL

Public procedure, called from the Web, that generates the XML invoice.

The get_attribute function accepts an attribute and a value. It formats this information to the XML specification ( attr_name = "attr_val" ) and returns a string. Here's the function:

/*
|| Function to return an attribute tag
*/
FUNCTION get_attribute (
   attr_name IN VARCHAR2,
   attr_val IN VARCHAR2
   )
   RETURN VARCHAR2
IS
BEGIN
   RETURN attr_name || '=' || '"' || attr_val || '"';
END;

The print_xml_tag procedure has a similar purpose. It accepts a tag name, a tag value, and an optional string for tag attributes. The procedure then formats these parameters into a well-formed XML element. The HTP.PRINT procedure sends this element back to the browser. Here's the procedure:

/*
|| Simple wrapper procedure to print a tag
*/
PROCEDURE print_xml_tag (
   tag_name IN VARCHAR2,
   tag_value IN VARCHAR2,
   tag_attr IN VARCHAR2 DEFAULT NULL
   )
IS
   
   xml_str VARCHAR2(5000);

BEGIN
   IF tag_attr IS NULL
   THEN
      xml_str := '<' || tag_name || '>';
   ELSE
      xml_str := '<' || tag_name || ' ' || tag_attr || ' >';
   END IF;
   xml_str := xml_str || tag_value;
   xml_str := xml_str || '</' || tag_name || '>';
   HTP.print (xml_str);
END;

The last local procedure, print_items, uses the previous function and procedure to generate the <INVOICE_ITEMS> section of the XML invoice. Like the other HTML procedures we've seen, it simply opens a cursor, loops, and prints each row by calling print_xml_tag. In addition, the procedure uses an OUT parameter to keep a running total of the dollar amount of each item. The value is passed back to the caller when the procedure completes. Here's the code:

/*
|| Print the items for the selected invoice. Return
|| the total of the invoice item using an OUT parameter.
*/
PROCEDURE print_items (
   i_invoice_id IN NUMBER,
   o_invoice_total OUT NUMBER
   )
IS
      
   CURSOR item_cur
   IS
      SELECT p.part_num, p.part_name, i.quantity, i.unit_cost
        FROM xml_invoice_items i, xml_parts p
       WHERE i.part_id = p.part_id
         AND i.invoice_id = i_invoice_id;
   
   item_rec item_cur%ROWTYPE;
    
   part_num_attr VARCHAR2(500);

BEGIN
   o_invoice_total := 0;
   OPEN item_cur;
   HTP.print ('<INVOICE_ITEMS>');
   LOOP
      FETCH item_cur INTO item_rec;
      EXIT WHEN item_cur%notfound;
      -- Accumulate costs
      o_invoice_total :=
         o_invoice_total +
         item_rec.quantity * item_rec.unit_cost;
      -- Generate XML tags
      HTP.print ('<ITEM>');
      part_num_attr :=
         get_attribute ('ITEM_NUM', item_rec.part_num);
      print_xml_tag (
         'ITEM_NAME',
         item_rec.part_name,
         part_num_attr
      );
      print_xml_tag ('QUANTITY', item_rec.quantity);
      print_xml_tag ('PRICE', item_rec.unit_cost);
      HTP.print ('</ITEM>');
   END LOOP;
   CLOSE item_cur;
   HTP.print ('</INVOICE_ITEMS>');
END;

The main public procedure, print_invoice, uses the local procedure to actually create the invoice. Here is the implementation:

/*
|| Main procedure to print the invoice.
*/
PROCEDURE print_invoice (
   i_invoice_number IN VARCHAR2 DEFAULT NULL
   )
IS
      
   CURSOR inv_cur
   IS
      SELECT i.invoice_id, i.invoice_date, c.customer_name
        FROM xml_invoice i, xml_customers c
       WHERE i.customer_id = c.customer_id
         AND i.invoice_number = i_invoice_number;
     
   inv_rec inv_cur%ROWTYPE;
   
   inv_total NUMBER DEFAULT 0;

BEGIN
   -- Set MIME type to XML
   OWA_UTIL.mime_header('text/xml', TRUE); 
   inv_total := 0;
   OPEN inv_cur;
   HTP.print ('<?xml version="1.0"?>');
   -- Note: the DTD is defined in a file stored on server
   -- The URL has been omitted for space
   HTP.print ('<!DOCTYPE INVOICE SYSTEM "invoice.dtd">');
   HTP.print ('<INVOICE>');
   FETCH inv_cur INTO inv_rec;
   IF NOT inv_cur%notfound
   THEN
      print_xml_tag ('INVOICE_NUMBER', i_invoice_number);
      print_xml_tag ('DATE', inv_rec.invoice_date);
      print_xml_tag ('CUSTOMER', inv_rec.customer_name);
      print_items (inv_rec.invoice_id, inv_total);
      print_xml_tag ('TOTAL', inv_total);
   END IF;
   CLOSE inv_cur;
   HTP.print ('</INVOICE>');
END;

Figure 9.3 shows the XML output of the procedure.

NOTE: You must use an XML-compliant browser such as Microsoft Internet Explorer version 5 to view XML documents.

Figure 9.3: Output of XML_INVOICE_PKG.print_invoice

Figure 9.3

The XML_INVOICE_PKG is a very simple example of how to link XML and Oracle. In the next section, we'll look at a set of packages that really illustrate XML's potential.


Previous: 9.4 The XML Parser Oracle Web Applications: PL/SQL Developer's Introduction Next: 9.6 PLSXML Utilities and Demos
9.4 The XML Parser Book Index 9.6 PLSXML Utilities and Demos

The Oracle Library Navigation

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

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