Skip Headers

Oracle9i Application Developer's Guide - XML
Release 1 (9.0.1)
Part Number A88894-01
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index

Go to previous page Go to next page

H
XML SQL Utility (XSU) Specifications and Cheat Sheets

This appendix contains the following sections:

Installing XML SQL Utility

Contents of the XSU Distribution

Table H-1 lists XML SQL Utility (XSU) distribution archive (zip file) contents.

Table H-1 XSU Distribution Contents 
File (with relative location)  Description 

relnotes.html 

The release notes 

env.csh 

This files is a helper csh shell script which can set up all the environmental variables needed to run the utility correctly. The user must setup the directory information correctly (for example, point to the installed area for the JDK etc.) 

env.bat 

This file is the same as the env.csh except that it is written for the Windows platform. 

lib/oraclexmlsql.jar 

The jar file containing all the Java functions for the utility. 

lib/xmlparserv2.jar 

The Oracle XML parser V2 packaged with the utility. 

lib/oraclexmlsqlload.csh (Unix)

lib/oraclexmlsqlload.bat (Windows) 

A csh and bat script to help load the utility into an Oracle database. These scripts call loadjava to load the jar file into the database and then run the xmlgenpkg.sql to create the PL/SQL front-end wrappers. 

lib/xmlgenpkg.sql

 

This file contains the sql script for creating the PL/SQL front-end wrappers. 

Installing XML SQL Utility: Procedure

To install XML SQL Utility (XSU) follow these steps:

  1. Requirements. Check that you have the correct software requirements loaded.

  2. Extract the XSU files

  3. Set Up Your Environment Correctly: Client Side

    • CLASSPATH Settings

    • Ensure the database is up

  4. Set Up Your Environment Correctly: Server Side

Installing XSU Downloaded from OTN

Download the correct XSU distribution archive from the Oracle Technology Network web-site (http://otn.oracle.com). Expand the downloaded archive. Depending on the usage scenario, perform the following install tasks:

To use the XSU's client side front-end or its java API, you need to:

  1. Setup the environment (that is, set CLASSPATH...):

    • Unix users: make sure that the path names in env.csh are correct; source the env.csh. If you are using a shell other than csh or tcsh, you will have to edit the file to use your shell's syntax.

    • Windows users: make sure that the path names in env.bat are correct; execute the file.

To use XSU's PL/SQL API, or write java stored procedures on top of XSU's java API, you need to:

  1. Confirm that the USER_PASSWORD macro in xsulload.xxx names the desired schema into which the XSU is to be loaded (default "scott/tiger").

    • Unix users: look into xsulload.csh

    • Windows users: look into xsuload.bat

  2. Confirm that the Oracle DB into which you are planning to load the XSU is up and java enabled.

  3. Execute the appropriate xsuload.xxx file. This will:

  4. Load Oracle's XML parser for Java into the database. If the parser is already loaded into the database, you can comment out the line in xsuload.xxx that loads the parser.

  5. Load XSU Java classes (that is, load xsu12.jar or xsu111.jar). Load the XSU PL/SQL API (that is, execute the dbmsxsu.sql PL/SQL script)

Requirements for Running XML SQL Utility

There are two versions of the utility, xsu111.jar and xsu12.jar, one compatible for JDK 1.1.x and the other with JDK1.2 respectively.

XML SQL Utility (XSU) is packaged with Oracle8i (8.1.7 and later) and Oracle9i. XSU is made up of three files:

By default the Oracle9i installer installs XSU on your hard drive in the locations specified above. It also loads it into the database.

If during initial installation you choose to not install XSU, you can install it later, but the installation becomes less simple. To install XSU later, first install XSU and its dependent components on your system. You can accomplish this using Oracle Installer. Next perform the following steps:

  1. If you have not yet loaded XML Parser for Java in the database, go to $ORACLE_HOME/xdk/lib. Here you will find xmlparserv2.jar that you need to load into the database. To do this, see "Loading JAVA Classes" in the Oracle9i Java Stored Procedures Developer's Guide

  2. Go to $ORACLE_HOME/admin and execute catxsu.sql

    Note:

    XML SQL Utility (XSU) is also available on OTN at: http://otn.oracle.com/tech/xml Check here for XSU updates. 

XSU Requirements

Before installing the utility make sure that you choose the right version of the utility depending on your particular needs. For example, if you can only use the JDK1.1.x version, then download the xsu111.jar file. Ensure that you have the JDK and the JDBC drivers correctly downloaded and installed, if not already available.

Extract the XSU Files

After downloading the zip file, simply extract the contents to a directory of your choice, say C:\xml. The files will get expanded in to a subdirectory called xsu111 or xsu112 depending on the version of the utility.

XML SQL Utility (XSU) for Java, Cheat Sheets

The following tables summarize XSU Java API classes and members:

XML SQL Utility (XSU) for PL/SQL, Cheat Sheets

XML SQL Utility (XSU) for PL/SQL offers the following PL/SQL packages:

DBMS_XMLQuery PL/SQL Package

Table H-6 lists DBMS_XMLQuery procedures, functions, and constants.


Table H-6 DBMS_XMLQuery Procedures, Functions, Types, and Constants  
PROCEDURE (Unless Noted Otherwise)  Description 

TYPE: ctxType 

Type of query context handle. This is the return type of "DBMS_ XMLQuery.newContext()".  

CONSTANTS

DEFAULT_ROWSETTAG

DEFAULT_ERRORTAG

DEFAULT_ROWIDATTR

DEFAULT_ROWTAG

DEFAULT_DATE_FORMAT

ALL_ROWS

NONE

DTD

LOWER_CASE

UPPER_CASE 

Mostly this is the root node tag name,ROWSET

ERROR

NUM

ROW

'MM/dd/yyyy HH:mm:ss'

All rows are needed in the output

For example, no DTD

DTD generation required

Use lower case tags

User upper case tags 

closeContext(ctxType) 

Closes/deallocates a particular query context 

FUNCTION:

getDTD(ctxType, BOOLEAN := false) 

 

getDTD(ctxType, CLOB, BOOLEAN := false) 

Generates the DTD based on the SQL query used to init.  

getExceptionContent(ctxType, NUMBER, VARCHAR2) 

 

FUNCTION:

getXML(ctxType, NUMBER := NONE) 

 

getXML(ctxType, CLOB, NUMBER := NONE) 

Generates the XML document.  

FUNCTION:

newContext(VARCHAR2) --> RETURN -- ctxType 

Creates a query context, and it returns the context handle.  

FUNCTION:

newContext(CLOB) ---> RETURN ---> ctxType 

Creates a query context, and it returns the context handle.  

propagateOriginalException(ctxType, BOOLEAN) 

Tells XSU if an exception is raised, and is being thrown, that XSU should throw the exception raised; rather then, wrapping it with an OracleXMLSQLException.  

setBindValue(ctxType, VARCHAR2, VARCHAR2) 

Sets a value for a particular bind name.  

setCollIdAttrName(ctxType, VARCHAR2 

Sets the name of the id attribute of the collection element's separator tag.  

setDataHeader(ctxType, CLOB := null, VARCHAR2 := null)  

Sets the XML data header.  

setDateFormat(ctxType, VARCHAR2) 

Sets the format of the generated dates in the XML document.  

setErrorTag(ctxType, VARCHAR2) 

Sets the tag to be used to enclose the xml error docs.  

setMaxRows (ctxType, NUMBER) 

Sets the max number of rows to be converted to XML.  

setMetaHeader(ctxType, CLOB := null) 

Sets the XML meta header.  

setRaiseException(ctxType, BOOLEAN) 

Tells the XSU to throw the raised exceptions.  

setRaiseNoRowsException(ctxType, BOOLEAN) 

Tells the XSU to throw or not to throw an OracleXMLNoRowsException in the case when for one reason or another, the XML document generated is empty.  

setRowIdAttrName(ctxType, VARCHAR2) 

Sets the name of the id attribute of the row enclosing tag.  

setRowIdAttrValue(ctxType, VARCHAR2)  

Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag.  

setRowsetTag(ctxType, VARCHAR2) 

Sets the tag to be used to enclose the XML dataset.  

setRowTag(ctxType, VARCHAR2) 

Sets the tag to be used to enclose the xml element corresponding to a database.  

setSkipRows(ctxType, NUMBER) 

Sets the number of rows to skip.  

setStylesheetHeader(ctxType, VARCHAR2, VARCHAR2 := 'text/xsl') 

Sets the stylesheet header 

setTagCase(ctxType, NUMBER) 

Specified the case of the generated XML tags.  

setXSLT(ctxType, VARCHAR2, VARCHAR2 := null) 

Registers a stylesheet to be applied to generated XML.  

setXSLT(ctxType, CLOB, VARCHAR2 := null) 

Registers a stylesheet to be applied to generated XML.  

useNullAttributeIndicator(ctxType, BOOLEAN) 

Specified weather to use an XML attribute to indicate NULLness, or to do it by omitting the inclusion of the particular entity in the XML document.  

DBMS_XMLSave PL/SQL Package

Table H-7 lists DBMS_XMLSave procedures, functions, types, and constants.


Table H-7 DBMS_XMLSave Procedures, Functions, Types, and Constants 
PROCEDURE (Unless Noted Otherwise)  Description 

TYPE: ctxType 

Type of query context handle. The return type of "DBMS_ XMLSave.newContext()". 

CONSTANTS:

DEFAULT_ROWTAG

DEFAULT_DATE_FORMAT

MATCH_CASE

IGNORE_CASE 

The default tag name for the element cooresponding to db. records. -- ROW

Default date mask. -- 'MM/dd/yyyy HH:mm:ss'

Used to specify that when mapping XML elements to DB. entities the XSU should be case sensitive

Used to specify that when mapping XML elements to DB. entities the XSU should be case insensitive 

clearKeyColumnList(ctxType) 

Clears the key colubmn list.  

clearUpdateColumnList(ctxType) 

Clears the update column list.  

closeContext(ctxType) 

Closes/deallocates a particular save context  

FUNCTION: deleteXML(ctxType, CLOB)

RETURN

NUMBER  

Deletes records specified by data from the XML document, from the table specified at the context creation time. 

FUNCTION: deleteXML(ctxType, VARCHAR2)

RETURN

NUMBER 

Deletes records specified by data from the XML document, from the table specified at the context creation time.

 

getExceptionContent(ctxType, NUMBER, VARCHAR2) 

Via its arguments, this method returns the thrown exception's error code and error message 

FUNCTION: insertXML(ctxType, CLOB)

RETURN

NUMBER  

Inserts the XML document into the table specified at the context creation time. 

FUNCTION: insertXML(ctxType, VARCHAR2)

RETURN

NUMBER .  

Inserts the XML document into the table specified at the context creation time 

FUNCTION: newContext(targetTable IN VARCHAR2)

RETURN ctxType  

Creates a save context, and it returns the context handle. 

propagateOriginalException(ctxType, BOOLEAN) 

Tells the XSU that if an exception is raised, and is being thrown, the XSU should throw the very exception raised; rather then, wrapping it with an OracleXMLSQLException.  

setBatchSize(ctxType, NUMBER) 

Changes the batch size used during DML operations.  

setCommitBatch(ctxType, NUMBER) 

Sets the commit batch size.  

setDateFormat(ctxType, VARCHAR2) 

Describes to the XSU the format of the dates in the XML document.  

setIgnoreCase(ctxType, NUMBER) 

XSU maps XML elements to the database.  

setKeyColumn(ctxType, VARCHAR2) 

Adds a column to the "key column list".  

setRowTag(ctxType, VARCHAR2) 

Names the tag used in the XML document, to enclose the XML elements corresponding to the database.  

setUpdateColumn(ctxType, VARCHAR2) 

Adds a column to the "update column list".  

getExceptionContent(ctxType, NUMBER, VARCHAR2) 

Updates the table specified at the context creation time with data from the XML document.  

propagateOriginalException(ctxType, BOOLEAN) 

Updates the table specified at the context creation time with data from the XML document.  

FUNCTION: newContext(targetTable IN VARCHAR2) 

 

RETURN ctxType 

Creates a save context, and it returns the context handle.  

Parameter 

targetTable Target table to load XML document to. 

Returns:  

The context handle.  

closeContext(ctxHdl IN ctxType) 

Closes/deallocates a particular save context. 

Parameter ctxHdl - Context handle  

 

setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2) 

Names the tag used in the XML document, to enclose the XML elements corresponding to the database records.  

Parameters 

ctxHdl - Context handle, tag - Tag name 

setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER) 

XSU maps XML elements to the database columns/attributes based on element names (XML tags). This function tells XSU to do this match case insensitive.  

Parameters  

ctxHdl- context handle, flag - ignore tag case in the XML document? 0-false 1-true 

setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2) 

Describes to XSU the format of the dates in the XML document. The syntax of the date format pattern (that is, the date mask), should conform to the requirements of the java.text.SimpleDateFormat class. Setting the mask to null or an empty string, results the use of the default mask -- OracleXMLCore.DATE_FORMAT.  

Parameters ctxHdl - Context handle, mask - Date mask 

 

setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER); 

Changes the batch size used during DML operations. When performing inserts, updates or deletes, it is better to batch the operations so that they get executed in one shot rather than as separate statements. The flip side is that more memory is needed to buffer all the bind values. When batching is used, a commit occurs only after a batch is executed. So if one of the statement inside a batch fails, the whole batch is rolled back. This is a small price to pay considering the performance gain; nevertheless, if this behaviour is unaccepatable, set the batch size to 1. See Also: DEFAULT_BATCH_SIZE  

Parameters  

ctxHdl - Context handle, batchSize - Batch size 

setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER); 

Sets the commit batch size. Commit batch size refers to the number or records inserted after which a commit should follow. If commitBatch is < 1 or the session is in "auto-commit" mode then XSU does not make any explicit commit's. By default the commit-batch size is 0.  

Parameters  

ctxHdl - Context handle, ParambatchSize - Commit batch size 

setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2); 

Adds a column to the "update column list". In inserts, the default is to insert values to all the columns in the table. For updates, the default is to only update the columns corresponding to the tags present in the ROW element of the XML document. When the update column list is specified, the columns making up this list alone will get updated or inserted into.  

Parameters  

ctxHdl - Context handle, colName - Column to be added to the update column list 

clearUpdateColumnList(ctxHdl IN ctxType) 

Clears the update column list. See Also: setUpdateColumn  

Parameters  

ctxHdl - Context handle 

setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2) 

Adds a column to the "key column list". In update or delete, it is the columns in the key column list that make up the WHERE clause of the UPDATE/DELETE statement. The key columns list must be specified before updates can be done; yet, it is only optional for delete operations.  

Parameters  

ctxHdl - Context handle, colName - Column to be added to the key column list 

clearKeyColumnList(ctxHdl IN ctxType) 

Clears the key column list. See Also: setKeyColumn  

Parameters  

ctxHdl - Context handle 

FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) 

 

RETURN NUMBER 

Inserts the XML document into the table specified at the context creation time.  

Parameters  

ctxHdl - Context handle, xDoc - String containing the XML document 

Returns 

The number of rows inserted.  

FUNCTION insertXML(ctxHdl IN ctxType, xDoc IN CLOB) 

 

RETURN NUMBER 

Inserts the XML document into the table specified at the context creation time.  

Parameters  

ctxHdl - Context handle, xDocl - String containing the XML document 

Returns 

The number of rows inserted.  

FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) 

 

RETURN NUMBER 

Updates the table specified at the context creation time with data from the XML document. 

Parameters  

ctxHdl - Context handle, xDoc - String containing the XML document 

Returns 

The number of rows updated.  

FUNCTION updateXML(ctxHdl IN ctxType, xDoc IN CLOB) 

 

RETURN NUMBER 

Updates the table specified at the context creation time with data from the XML document.  

Parameters  

ctxHdl - context handle, xDocl - string containing the XML document 

Returns 

The number of rows updated.  

FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) 

 

RETURN NUMBER 

Deletes records specified by data from the XML document, from the table specified at the context creation time.  

Parameters 

ctxHdl - context handle, xDoc - string containing the XML document  

Returns 

The number of rows deleted.  

FUNCTION deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) 

 

RETURN NUMBER 

Deletes records specified by data from the XML document, from the table specified at the context creation time.  

Parameters  

ctxHdl - context handle, xDocl - string containing the XML document 

Returns 

The number of rows deleted.  

propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN) 

Tells XSU that if an exception is raised, and is being thrown, XSU should throw the exception raised; rather then, wrapping it with OracleXMLSQLException.  

Parameters  

ctxHdl - Context handle, flag - Propagate original exception? 0-false 1-true 

getExceptionContent(ctxHdl IN ctxType, errNo OUT NUMBER, errMsg OUT VARCHAR2) 

Via its arguments, this returns the thrown exception's error code and error message (that is, SQL error code). This is to get around the fact that the JVM throws an exception on top of whatever exception was raised; PL/SQL is unable to access the original exception.  

Parameters  

ctxHdl - Context handle, errNo - Error number, errMsg - Error message 

See Also:

 
Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.
All Rights Reserved.
Go To Documentation Library
Home		 Go To Product List
Solution Area		 Go To Table Of Contents
Contents		 Go To Index
Index