Oracle Web Applications: PL/SQL Developer's Intro

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.

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;

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 ...