Oracle8i Application Developer's Guide - XML Release 3 (8.1.7) Part Number A86030-01 |
Library | Service | Contents | Index

Using XML-SQL Utility (XSU), 18 of 26

## Using the XML-SQL Utility for PL/SQL

The XML-SQL Utility PL/SQL API reflects the Java API in the generation and storage. The `DBMS_XMLQuery` and `DBMS_XMLSave` are the two packages that reflect the functions in the java classes - OracleXMLQuery and `OracleXMLSave`.

Both these packages have a context handle associated with them. Create a context by calling one of the constructor-like functions to get the handle and then use the handle in all subsequent calls.

### Generating XML with DBMS_XMLQuery

Generating XML results in a CLOB that contains the XML document. The steps involved in using the generation engine follow:

1. Create a context handle by calling the DBMS_XMLQuery.getCtx function and supplying it the query (either as a CLOB or a VARCHAR2)

2. Bind possible values to the query using the DBMS_XMLQuery.bind function. The binds work by binding a name to the position. For example, the query can be something like, select * from emp where empno = :EMPNO_VAR. Here the user binds the value for the EMPNO_VAR using the `setBindValue` function.

3. Set optional arguments like the ROW tag name, the ROWSET tag name or the number of rows to fetch etc.

4. Fetch the XML as a CLOB using the getXML() functions. The getXML can be called to generate the XML with or without a DTD.

5. Close the context.

Here are some examples that use this PL/SQL package.

### XSU Example 13: Generating XML From Simple Queries (PL/SQL)

In this example, we will try to select rows from the emp table and get a XML document as a CLOB. We first get the context handle by passing in a query and then call the `getXMLClob` routine to get the CLOB value. The document will be in the same encoding as that of the database character set.

    declare
      queryCtx DBMS_XMLquery.ctxType;
      result CLOB;
    begin

      -- set up the query context...!
      queryCtx := DBMS_XMLQuery.newContext('select * from emp');

      -- get the result..!
      result := DBMS_XMLQuery.getXML(queryCtx);
      -- Now you can use the result to put it in tables/send as messages..
      printClobOut(result);
      DBMS_XMLQuery.closeContext(queryCtx);  -- you must close the query handle..
    end;
    /

### XSU Example 13a: Printing CLOB to Output Buffer

The `printClobOut`() is a simple procedure that prints the CLOB to the output buffer. If you run this PL/SQL code in SQL*Plus, you will see the result of the CLOB being printed out to screen. Set the `serveroutput` to on in order to see the results.

The `printClobOut` is shown below:-

    /CREATE OR REPLACE PROCEDURE printClobOut(result IN OUT NOCOPY CLOB) is
    xmlstr varchar2(32767);
    line varchar2(2000);
    begin
      xmlstr := dbms_lob.SUBSTR(result,32767);
      loop
        exit when xmlstr is null;
        line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
        dbms_output.put_line('| '||line);
        xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
      end loop;
    end;
    /

### XSU Example 14: Changing ROW and ROWSET Tag Names (PL/SQL)

The PL/SQL APIs also provide the ability to change the ROW and the ROWSET tag names. These are the default names that are put around each row of the result and around the whole document respectively. The procedures, `setRowTagName` and `setRowSetTagName` accomplish this as shown below:

    --Setting the ROW tag names

    declare
       queryCtx DBMS_XMLQuery.ctxType;
       result CLOB;
    begin
       -- set the query context.
       queryCtx := DBMS_XMLQuery.newContext('select * from emp');

       DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
       DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name

       result := DBMS_XMLQuery.getXML(queryCtx); -- get the result

       printClobOut(result);  -- print the result..!
       DBMS_XMLQuery.closeContext(queryCtx);  -- close the query handle;
    end;
    /

The resulting XML document has an EMPSET document element and each row separated using the EMP tag.

### XSU Example 15: Paginating Results Using setMaxRows() and setSkipRows()

The results from the query generation can be paginated by using the `setMaxRows` and `setSkipRows` functions. The former sets the maximum number of rows to be converted to XML. This is relative to the current row position from which the last result was generated. The skipRows parameter specifies the number of rows to skip before converting the row values to XML. For example, to skip the first 3 rows of the emp table and then print out the rest of the rows 10 at a time, we can set the skipRows to 3 for the first batch of 10 rows and then set skipRows to 0 for the rest of the batches.

As in the case of the XML-SQL Utility Java API, call the keepObjectOpen() function to make sure that the state is maintained between fetches. The default behavior is to close the state after a fetch is done. In the case of multiple fetches, we need to figure out when there are no more rows to fetch. This can be done by setting the `setRaiseNoRowsException`(). This causes an exception to be raised if no rows are written to the CLOB. This can be caught and used as the termination condition.

    -- Pagination of results

    declare
      queryCtx DBMS_XMLquery.ctxType;
      result CLOB;
    begin

      -- set up the query context...!
      queryCtx := DBMS_XMLQuery.newContext('select * from emp');

      DBMS_XMLQuery.setSkipRows(queryCtx,3); -- set the number of rows to skip
      DBMS_XMLQuery.setMaxRows(queryCtx,10); -- set the max number of rows per fetch

      result := DBMS_XMLQuery.getXML(queryCtx); -- get the first result..!

      printClobOut(result); -- print the result out.. This is you own routine..!
      DBMS_XMLQuery.setSkipRows(queryCtx,0); -- from now don't skip any more rows..!

      DBMS_XMLQuery.setRaiseNoRowsException(queryCtx,true);
                                             -- raise no rows exception..!
      begin
        loop  -- loop forever..!
          result := DBMS_XMLQuery.getXML(queryCtx); -- get the next batch
          printClobOut(result);             -- print the next batch of 10 rows..!
        end loop;
      exception
        when others then
        -- dbms_output.put_line(sqlerrm);
           null; -- termination condition, nothing to do;
      end;
      DBMS_XMLQuery.closeContext(queryCtx);  -- close the handle..!
    end;
    /

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index