Navigating XML in Oracle

A few code snippets for navigating and extracting dynamically from XML in PL/SQL.

--The executable section of the traverse_and_display procedure. 
BEGIN
  -- get all elements
  nodes := xmldom.getElementsByTagName (doc, '*');

  -- loop through elements
  FOR node_index IN 0 .. xmldom.getLength (nodes) - 1
  LOOP
     one_node := xmldom.item (nodes, node_index);

     display_element (one_node);

     node_map := xmldom.getAttributes (one_node);

     FOR attr_index IN
        0 .. xmldom.getLength (node_map) - 1
     LOOP
        display_attribute (node_map, attr_index);
     END LOOP;
  END LOOP;
END traverse_and_display;



-- The code required for displaying the name and value of an element. 
PROCEDURE display_element (node IN xmldom.DOMNode)
IS
  one_element   xmldom.DOMElement;
  value_node    xmldom.DOMNode;
BEGIN
  one_element := xmldom.makeElement (node);
  DBMS_OUTPUT.put_line ('Element: ' ||
                       xmldom.getTagName (one_element
                       )
  );
  value_node := xmldom.getFirstChild (node);
  DBMS_OUTPUT.put_line ('Value: ' ||
                       xmldom.getNodeValue (value_node
                       )
  );
END;



--Displaying the value of an attribute. 
PROCEDURE display_attribute (
  node_map     IN   xmldom.DOMNamedNodeMap,
  attr_index   IN   PLS_INTEGER
)
IS
  one_node   xmldom.DOMNode;
  attrname   VARCHAR2 (100);
  attrval    VARCHAR2 (100);
BEGIN
  one_node := xmldom.item (node_map, attr_index);
  attrname := xmldom.getNodeName (one_node);
  attrval := xmldom.getNodeValue (one_node);
  DBMS_OUTPUT.put_line ('   ' || 
        attrname || ' = ' || attrval
  );
END;

Temp Tablespace

Default Temporary Tablespaces

If you CREATE USER and forget to include a TEMPORARY TABLESPACE clause, Oracle uses the SYSTEM tablespace for that user’s sorts. This hurts performance. 9i addresses this by allowing you to specify a system-wide default temporary tablespace. Specify the DEFAULT TEMPORARY TABLESPACE on the CREATE DATABASE statement. Or, define the new temporary tablespace by the CREATE TEMPORARY TABLESPACE statement, and make it the default by running:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE default_temp_ts ;

Automated UNDO Management

Automated Undo Management (AUM)

9i’s new feature Automated Undo Management (AUM) relieves you of the traditional, labor-intensive task of sizing and managing rollbacks. To use AUM, create a tablespace that will be used for rollbacks (the UNDO tablespace). Then start the instance with these two new 9i initialization parameters set to:

UNDO_MANAGEMENT = AUTO

UNDO_TABLESPACE = undo_tablespace_name

Once an instance is started with AUM, you can not and do not create or manage rollback segments manually. You can switch to another UNDO tablespace whenever you want, but you can not drop an UNDO tablespace while it has active transactions.