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

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.

9.4 The XML Parser		9.6 PLSXML Utilities and Demos

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