Oracle8i XML Reference Guide Release 3 (8.1.7) Part Number A83730-01 |
Library | Service | Contents | Index

# 10
XSU's PL/SQL API

XSU's PL/SQL API consists of two packages:

- DBMS_XMLQuery -- provides DB_to_XML type functionality.

- DBMS_XMLSave -- provides XML_to_DB type functionality.

## DBMS_XMLQuery

### Types:

### ctxType

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

### Constants:

### DEFAULT_ROWSETTAG

The tag name for the element enclosing the XML generated from the result set (i.e. for most cases the root node tag name) -- ROWSET

### DEFAULT_ERRORTAG

The default tag to enclose raised errors -- ERROR.

### DEFAULT_ROWIDATTR

The default name for the cardinality attribute of XML elements coresponding to db. records. -- NUM

### DEFAULT_ROWTAG

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

### DEFAULT_DATE_FORMAT

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

### ALL_ROWS

The ALL_ROWS parameter is to indicate that all rows are needed in the output.

### NONE

Used to specifies that the output should not coOracle8iXML Reference Guidentain any XML metadata (e.g. no DTD).

### DTD

Used to specify that the generation of the DTD is desired.

### LOWER_CASE

Use lower cased tag names.

### UPPER_CASE

Use upper case tag names.

## Function and Procedure Index:

#### PROCEDURE

    closeContext(ctxType)

It closes/deallocates a particular query context

#### FUNCTION

    getDTD(ctxType, BOOLEAN := false)

#### RETURN

    CLOB

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

#### PROCEDURE

    getDTD(ctxType, CLOB, BOOLEAN := false)

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

#### PROCEDURE

    getExceptionContent(ctxType, NUMBER, VARCHAR2)

#### FUNCTION

    getXML(ctxType, NUMBER := NONE)

#### RETURN

    CLOB

Generates the XML doc.

#### PROCEDURE

    getXML(ctxType, CLOB, NUMBER := NONE)

Generates the XML doc.

#### FUNCTION

    newContext(VARCHAR2)

#### RETURN

    ctxType

It creates a query context, and it returns the context handle.

#### FUNCTION

    newContext(CLOB)

#### RETURN

    ctxType

It creates a query context, and it returns the context handle.

#### PROCEDURE

    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.

#### PROCEDURE

    setBindValue(ctxType, VARCHAR2, VARCHAR2)

Sets a value for a particular bind name.

#### PROCEDURE

    setCollIdAttrName(ctxType, VARCHAR2

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

#### PROCEDURE

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

Sets the xml data header.

#### PROCEDURE

    setDateFormat(ctxType, VARCHAR2)

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

#### PROCEDURE

    setErrorTag(ctxType, VARCHAR2)

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

#### PROCEDURE

    setMaxRows (ctxType, NUMBER)

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

#### PROCEDURE

    setMetaHeader(ctxType, CLOB := null)

Sets the XML meta header.

#### PROCEDURE

    setRaiseException(ctxType, BOOLEAN)

Tells the XSU to throw the raised exceptions.

#### PROCEDURE

    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 doc generated is empty.

#### PROCEDURE

    setRowIdAttrName(ctxType, VARCHAR2)

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

#### PROCEDURE

    setRowIdAttrValue(ctxType, VARCHAR2)

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

#### PROCEDURE

    setRowsetTag(ctxType, VARCHAR2)

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

#### PROCEDURE

    setRowTag(ctxType, VARCHAR2)

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

#### PROCEDURE

    setSkipRows(ctxType, NUMBER)

Sets the number of rows to skip.

#### PROCEDURE

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

Sets the stylesheet header (i.e.

#### PROCEDURE

    setTagCase(ctxType, NUMBER)

Specified the case of the generated XML tags.

#### PROCEDURE

    setXSLT(ctxType, VARCHAR2, VARCHAR2 := null)

Registers a stylesheet to be applied to generated XML.

#### PROCEDURE

    setXSLT(ctxType, CLOB, VARCHAR2 := null)

Registers a stylesheet to be applied to generated XML.

#### PROCEDURE

    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.

## Functions and Procedures:

### newContext

#### FUNCTION

    newContext(sqlQuery IN VARCHAR2)

#### RETURN

    ctxType

It creates a query context, and it returns the context handle.

#### Parameters

sqlQuery - SQL query, the results of which to convert to XML

#### Returns:

The context handle.

### newContext

#### FUNCTION

    newContext(sqlQuery IN CLOB)

#### RETURN

    ctxType

It creates a query context, and it returns the context handle.

#### Parameters

sqlQuery - SQL query, the results of which to convert to XML

#### Returns:

The context handle.

### closeContext

#### PROCEDURE

    closeContext(ctxHdl IN ctxType)

It closes/deallocates a particular query context

#### Parameters

ctxHdl - context handle

### setRowsetTag

#### PROCEDURE

    setRowsetTag(ctxHdl IN ctxType, tag IN VARCHAR2)

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

#### Parameters

    ctxHdl - context handle

tag - tag name

setRowTag

#### PROCEDURE

    setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)

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

#### Parameters

    ctxHdl - context handle

tag - tag name

setErrorTag

#### PROCEDURE

    setErrorTag(ctxHdl IN ctxType, tag IN VARCHAR2)

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

#### Parameters

ctxHdl - context handle

tag - tag name

### setRowIdAttrName

#### PROCEDURE

    setRowIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)

Sets the name of the id attribute of the row enclosing tag. Passing null or an empty string for the tag results the row id attribute to be omitted.

#### Parameters

    ctxHdl - context handle
    attrName - attribute name

### setRowIdAttrValue

#### PROCEDURE

    setRowIdAttrValue(ctxHdl IN ctxType, colName IN VARCHAR2)

Specifies the scalar column whose value is to be assigned to the id attribute of the row enclosing tag. Passing null or an empty string for the colName results the row id attribute being assigned the row count value (i.e. 0, 1, 2 and so on).

#### Parameters

ctxHdl - context handle

colName - column whose value is to be assigned to the row id attr

### setCollIdAttrName

#### PROCEDURE

    setCollIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)

Sets the name of the id attribute of the collection element's separator tag. Passing null or an empty string for the tag results the row id attribute to be omitted.

#### Parameters

ctxHdl - context handle

attrName - attribute name

useNullAttributeIndicator

#### PROCEDURE

    useNullAttributeIndicator(ctxHdl IN ctxType, flag IN 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.

#### Parameters

ctxHdl - context handle

flag - use attribute to indicate null?

### setTagCase

#### PROCEDURE

    setTagCase(ctxHdl IN ctxType, tCase IN NUMBER)

Specified the case of the generated XML tags.

#### Parameters

ctxHdl - context handle

tCase - the tag's case (0-asAre, 1-lower, 2-upper)

### setDateFormat

#### PROCEDURE

    setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)

Sets the format of the generated dates in the XML doc. The syntax of the date format patern (i.e. 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 -- DEFAULT_DATE_FORMAT.

#### Parameters

ctxHdl - context handle

mask - the date mask

### setMaxRows

#### PROCEDURE

    setMaxRows (ctxHdl IN ctxType, rows IN NUMBER)

Sets the max number of rows to be converted to XML. By default there is no max set.

#### Parameters

ctxHdl - context handle

rows - max number of rows to generate

### setSkipRows

#### PROCEDURE

    setSkipRows(ctxHdl IN ctxType, rows IN NUMBER)

Sets the number of rows to skip. By default 0 rows are skipped.

#### Parameters

ctxHdl - context handle

rows - number of rows to skip

### setStylesheetHeader

#### PROCEDURE

    setStylesheetHeader(ctxHdl IN ctxType, uri IN VARCHAR2, type IN VARCHAR2 :=
    'text/xsl')

Sets the stylesheet header (i.e. stylesheet processing instructions) in the generated XML doc. Note: Passing null for the uri argument will unset the stylesheet header and the stylesheet type.

#### Parameters

ctxHdl - context handle

uri - stylesheet URI

type - stylesheet type; defaults to 'text/xsl'

### setXSLT

#### PROCEDURE

    setXSLT(ctxHdl IN ctxType, uri IN VARCHAR2, ref IN VARCHAR2 := null)

Registers a stylesheet to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null for the uri argument.

#### Parameters

ctxHdl - context handle

uri - stylesheet URI

ref - URL for include, import and external entities

### setXSLT

#### PROCEDURE

    setXSLT(ctxHdl IN ctxType, stylesheet CLOB, ref IN VARCHAR2 := null)

Registers a stylesheet to be applied to generated XML. If a stylesheet was already registered, it gets replaced by the new one. To un-register the stylesheet pass in a null or an empty string for the stylesheet argument.

#### Parameters

ctxHdl - context handle

stylesheet - the stylesheet

ref - URL for include, import and external entities

### setBindValue

#### PROCEDURE

    setBindValue(ctxHdl IN ctxType, bindName IN VARCHAR2, bindValue IN VARCHAR2)

Sets a value for a particular bind name.

#### Parameters

ctxHdl - context handle

bindName - bind name

bindValue - bind value

### setMetaHeader

#### PROCEDURE

    setMetaHeader(ctxHdl IN ctxType, header IN CLOB := null)

Sets the XML meta header. When set, the header is inserted at the begining of the metadata part (DTD or XMLSchema) of each XML document generated by this object. Note that the last meta header specified is the one that is used; furthermore, passing in null for the header, parameter unsets the meta header.

#### Parameters

ctxHdl - context handle

header - header

### setDataHeader

#### PROCEDURE

    setDataHeader(ctxHdl IN ctxType, header IN CLOB := null, tag IN VARCHAR2 :=
    null)

Sets the xml data header. The data header is an XML entity which is appended at the begining of the query-generated xml entity (ie. rowset). The two entities are enclosed by the tag specified via the docTag argument. Note that the last data header specified is the one that is used; furthermore, passing in null for the header, parameter unsets the data header.

#### Parameters

ctxHdl - context handle

header - header

tag - tag used to enclose the data header and the rowset

### setRaiseException

#### PROCEDURE

    setRaiseException(ctxHdl IN ctxType, flag IN BOOLEAN)

Tells the XSU to throw the raised exceptions. If this call isn't made or if false is passed to the flag argument, the XSU catches the SQL exceptions and generates an XML doc out of the exception's message.

#### Parameters

ctxHdl - context handle

flag - throw raised exceptions?

### setRaiseNoRowsException

#### PROCEDURE

    setRaiseNoRowsException(ctxHdl IN ctxType, flag IN BOOLEAN)

Tells the XSU to throw or not to throw an OracleXMLNoRowsException in the case when for one reason or another, the XML doc generated is empty. By default, the exception is not thrown.

#### Parameters

ctxHdl - context handle

flag - throw OracleXMLNoRowsException if no data?

### propagateOriginalException

#### PROCEDURE

    propagateOriginalException(ctxHdl IN ctxType, flag IN 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.

#### Parameters

ctxHdl - context handle

flag - propagate original exception?

### getExceptionContent

#### PROCEDURE

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

Via its arguments, this method returns the thrown exception's error code and error message (i.e. sql error code) This is to get around the fact that the jvm throws an exception on top of whatever exception was raised; thus, rendering pl/sql unable to access the original exception.

#### Parameters

ctxHdl - context handle

errNo - error number

errMsg - error message

### getDTD

#### FUNCTION

    getDTD(ctxHdl IN ctxType, withVer IN BOOLEAN := false)

#### RETURN

CLOB

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

#### Parameters

ctxHdl - context handle

withVer - generate the version info?

#### Returns:

The DTD.

### getDTD

#### PROCEDURE

    getDTD(ctx IN ctxType, xDoc IN CLOB, withVer IN BOOLEAN := false)

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

#### Parameters

ctxHdl - context handle

xDoc - lob into which to write the generated XML doc

withVer - generate the version info?

### getXML

#### FUNCTION

    getXML(ctxHdl IN ctxType, metaType IN NUMBER := NONE)

#### RETURN

CLOB

Generates the XML doc. based on the SQL query used to init. the context.

#### Parameters

ctxHdl - context handle

metaType - xml metadata type (i.e. none or DTD)

#### Returns:

The XML document.

### getXML

#### PROCEDURE

    getXML(ctxHdl IN ctxType, xDoc IN CLOB, metaType IN NUMBER := NONE)

Generates the XML doc. based on the SQL query used to init. the context.

#### Parameters

ctxHdl - context handle

xDoc - lob into which to write the generated XML doc

metaType - xml metadata type (i.e. none or DTD)

## DBMS_XMLSave

### Types

### ctxType

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

### Constants:

### DEFAULT_ROWTAG

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

### DEFAULT_DATE_FORMAT

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

### MATCH_CASE

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

### IGNORE_CASE

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

### Function and Procedure Index:

#### PROCEDURE

    clearKeyColumnList(ctxType)

Clears the key colubmn list.

#### PROCEDURE

    clearUpdateColumnList(ctxType)

Clears the update column list.

#### PROCEDURE

    closeContext(ctxType)

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

#### PROCEDURE

    getExceptionContent(ctxType, NUMBER, VARCHAR2)

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

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

It creates a save context, and it returns the context handle.

#### PROCEDURE

    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.

#### PROCEDURE

    setBatchSize(ctxType, NUMBER)

Changes the batch size used during DML operations.

#### PROCEDURE

    setCommitBatch(ctxType, NUMBER)

Sets the commit batch size.

#### PROCEDURE

    setDateFormat(ctxType, VARCHAR2)

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

#### PROCEDURE

    setIgnoreCase(ctxType, NUMBER)

The XSU does mapping of XML elements to db.

#### PROCEDURE

    setKeyColumn(ctxType, VARCHAR2)

This methods adds a column to the "key column list".

#### PROCEDURE

    setRowTag(ctxType, VARCHAR2)

Names the tag used in the XML doc., to enclose the XML elements corresponding to db.

#### PROCEDURE

    setUpdateColumn(ctxType, VARCHAR2)

Adds a column to the "update column list".

#### PROCEDURE

    getExceptionContent(ctxType, NUMBER, VARCHAR2)

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

#### PROCEDURE

    propagateOriginalException(ctxType, BOOLEAN)

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

## Functions and Procedures:

### newContext

#### FUNCTION

    newContext(targetTable IN VARCHAR2)

#### RETURN

    ctxType

It creates a save context, and it returns the context handle.

#### Parameters

targetTable - the target table into which to load the XML doc

#### Returns

The context handle.

### closeContext

#### PROCEDURE

    closeContext(ctxHdl IN ctxType)

It closes/deallocates a particular save context

#### Parameters

    ctxHdl - context handle

### setRowTag

#### PROCEDURE

    setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)

Names the tag used in the XML doc., to enclose the XML elements corresponding to db. records.

#### Parameters

ctxHdl - context handle

tag - tag name

### setIgnoreCase

#### PROCEDURE

    setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER)

The XSU does mapping of XML elements to db. columns/attrs. based on the element names (xml tags). This function tells the XSU to do this match case insensitive.

#### Parameters

ctxHdl - context handle

flag - ignore tag case in the XML doc? 0-false 1-true

### setDateFormat

#### PROCEDURE

    setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)

Describes to the XSU the format of the dates in the XML document. The syntax of the date format patern (i.e. 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 - the date mask

### setBatchSize

#### PROCEDURE

    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. Note that 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, then set the batch size to 1.

#### Parameters

ctxHdl - context handle

batchSize - batch size

See Also:

DEFAULT_BATCH_SIZE

### setCommitBatch

#### PROCEDURE

    setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER);

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

#### Parameters

ctxHdl - context handle

batchSize - commit batch size

### setUpdateColumn

#### PROCEDURE

    setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2);

Adds a column to the "update column list". In case of insert, the default is to insert values to all the columns in the table; on the other hand, in case of 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

#### PROCEDURE

    clearUpdateColumnList(ctxHdl IN ctxType)

Clears the update column list.

#### Parameters

ctxHdl - context handle

See Also:

setUpdateColumn

### setKeyColumn

#### PROCEDURE

    setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2)

This methods adds a column to the "key column list". In case of 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

#### PROCEDURE

    clearKeyColumnList(ctxHdl IN ctxType)

Clears the key column list.

#### Parameters

ctxHdl - context handle

See Also:

setKeyColumn

### insertXML

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

### insertXML

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

### updateXML

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

### updateXML

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

### deleteXML

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

### deleteXML

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

#### PROCEDURE

    propagateOriginalException(ctxHdl IN ctxType, flag IN 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.

#### Parameters

ctxHdl - context handle

flag - propagate original exception? 0-false 1-true

### getExceptionContent

#### PROCEDURE

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

Via its arguments, this method returns the thrown exception's error code and error message (i.e. sql error code) This is to get around the fact that the jvm throws an exception on top of whatever exception was raised; thus, rendering pl/sql unable to access the original exception.

#### Parameters

ctxHdl - context handle

errNo - error number

errMsg - error message

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