Skip Headers

Oracle9i XML Reference
Release 1 (9.0.1)

Part Number A88899-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

13
XML SQL Utility (XSU) PL/SQL API

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

DBMS_XMLQuery

This API provides DB_to_XML type functionality.

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 (that is, 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 contain any XML metadata (for example, no DTD).

DTD

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

SCHEMA

Used to specify that the generation of the XML SCHEMAis 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)

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

FUNCTION getXML(VARCHAR2, NUMBER := NONE) RETURN CLOB

Generates the XML doc.

FUNCTION getXML(CLOB, NUMBER := NONE) RETURN CLOB

Generates the XML doc.

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 (that is,

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 setXSLTParam(ctxType, VARCHAR2, VARCHAR2)

Sets the value of a top-level stylesheet parameter.

PROCEDURE removeXSLTParam(ctxType, VARCHAR2)

Removes a particular top-level stylesheet parameter.

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

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

Syntax

FUNCTION newContext(sqlQuery IN VARCHAR2) RETURN ctxType

Parameters

Returns

The context handle.

newContext

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

Syntax

FUNCTION newContext(sqlQuery IN CLOB) RETURN ctxType

Parameters

Returns

The context handle.

closeContext

It closes/deallocates a particular query context

Syntax

PROCEDURE closeContext(ctxHdl IN ctxType)

Parameters

setRowsetTag

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

Syntax

PROCEDURE setRowsetTag(ctxHdl IN ctxType, tag IN VARCHAR2)

Parameters

setRowTag

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

Syntax

PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)

Parameters

setErrorTag

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

Syntax

PROCEDURE setErrorTag(ctxHdl IN ctxType, tag IN VARCHAR2)

Parameters

setRowIdAttrName

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.

Syntax

PROCEDURE setRowIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)

Parameters

setRowIdAttrValue

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 (that is, 0, 1, 2 and so on).

Syntax

PROCEDURE setRowIdAttrValue(ctxHdl IN ctxType, colName IN VARCHAR2)

Parameters

setCollIdAttrName

Syntax

PROCEDURE setCollIdAttrName(ctxHdl IN ctxType, attrName IN VARCHAR2)

Parameters

useNullAttributeIndicator

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.

Syntax

PROCEDURE useNullAttributeIndicator(ctxHdl IN ctxType, flag IN BOOLEAN)

Parameters

setTagCase

Specified the case of the generated XML tags.

Syntax

PROCEDURE setTagCase(ctxHdl IN ctxType, tCase IN NUMBER)

Parameters

setDateFormat

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

Syntax

PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)

Parameters

setMaxRows

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

Syntax

PROCEDURE setMaxRows (ctxHdl IN ctxType, rows IN NUMBER)

Parameters

setSkipRows

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

Syntax

PROCEDURE setSkipRows(ctxHdl IN ctxType, rows IN NUMBER)

Parameters

setStylesheetHeader

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

Syntax

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

Parameters

setXSLT

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.

Syntax

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

Parameters

setXSLT

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.

Syntax

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

Parameters

setXSLTParam

Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression (note that string literal values would therefore have to be explicitly quoted). NOTE: if no stylesheet is registered, this method is a no op.

Syntax

PROCEDURE setXSLTParam(ctxHdl IN ctxType, name IN VARCHAR2, value IN VARCHAR2)

Parameters

removeXSLTParam

Sets the value of a top-level stylesheet parameter. The parameter value is expected to be a valid XPath expression (note that string literal values would therefore have to be explicitly quoted). NOTE: if no stylesheet is registered, this method is a no op.

Syntax

PROCEDURE removeXSLTParam(ctxHdl IN ctxType, name IN VARCHAR2, value IN 
VARCHAR2)

Parameters

setBindValue

Sets a value for a particular bind name.

Syntax

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

Parameters

setMetaHeader

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.

Syntax

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

Parameters

setDataHeader

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.

Syntax

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

Parameters

setRaiseException

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.

Syntax

PROCEDURE setRaiseException(ctxHdl IN ctxType, flag IN BOOLEAN)

Parameters

setRaiseNoRowsException

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.

Syntax

PROCEDURE setRaiseNoRowsException(ctxHdl IN ctxType, flag IN BOOLEAN)

Parameters

propagateOriginalException

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.

Syntax

PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN)

Parameters

getExceptionContent

Via its arguments, this method 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; thus, rendering pl/sql unable to access the original exception.

Syntax

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

Parameters

getDTD

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

Syntax

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

Parameters

Returns

The DTD.

getDTD

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

Syntax

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

Parameters

getXML

This is a convenience function. One doesn't have to explicitly open a context and close the context. This function creates the new context, executes the query, gets the xml back and closes the context..

Syntax

FUNCTION  getXML(sqlQuery IN VARCHAR2, metaType IN NUMBER := NONE) RETURN CLOB

Parameters

Returns

The XML document.

getXML

This is a convenience function. One doesn't have to explicitly open a context and close the context. This function creates the new context, executes the query, gets the xml back and closes the context..

Syntax

FUNCTION getXML(sqlQuery IN CLOB, metaType IN NUMBER := NONE) RETURN CLOB

Parameters

Returns

The XML document.

getXML

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

Syntax

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

Parameters

Returns

The XML document.

getXML

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

Syntax

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

Parameters

DBMS_XMLSave

This API provides XML_to_DB type functionality.

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

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

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

Syntax

FUNCTION newContext(targetTable IN VARCHAR2) RETURN ctxType

Parameters

Returns

The context handle.

closeContext

It closes/deallocates a particular save context

Syntax

PROCEDURE closeContext(ctxHdl IN ctxType)

Parameters

setRowTag

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

Syntax

PROCEDURE setRowTag(ctxHdl IN ctxType, tag IN VARCHAR2)

Parameters

setIgnoreCase

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.

Syntax

PROCEDURE setIgnoreCase(ctxHdl IN ctxType, flag IN NUMBER)

Parameters

setDateFormat

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

Syntax

PROCEDURE setDateFormat(ctxHdl IN ctxType, mask IN VARCHAR2)

Parameters

setBatchSize

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.

Syntax

PROCEDURE setBatchSize(ctxHdl IN ctxType, batchSize IN NUMBER);

Parameters

setCommitBatch

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.

Syntax

PROCEDURE setCommitBatch(ctxHdl IN ctxType, batchSize IN NUMBER);

Parameters

setUpdateColumn

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.

Syntax

PROCEDURE setUpdateColumn(ctxHdl IN ctxType, colName IN VARCHAR2);

Parameters

clearUpdateColumnList

Clears the update column list.

Syntax

PROCEDURE clearUpdateColumnList(ctxHdl IN ctxType)

Parameters

setKeyColumn

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.

Syntax

PROCEDURE setKeyColumn(ctxHdl IN ctxType, colName IN VARCHAR2)

Parameters

clearKeyColumnList

Clears the key column list.

Syntax

PROCEDURE clearKeyColumnList(ctxHdl IN ctxType)

Parameters

insertXML

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

Syntax

FUNCTION  insertXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER

Parameters

Returns

The number of rows inserted.

insertXML

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

Syntax

FUNCTION  insertXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER

Parameters

Returns

The number of rows inserted.

updateXML

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

Syntax

FUNCTION  updateXML(ctxHdl IN ctxType, xDoc IN VARCHAR2) RETURN NUMBER

Parameters

Returns

The number of rows updated.

updateXML

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

Syntax

FUNCTION  updateXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER

Parameters

Returns

The number of rows updated.

deleteXML

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

Syntax

FUNCTION  deleteXML(ctxHdl IN ctxPType, xDoc IN VARCHAR2) RETURN NUMBER

Parameters

Returns

The number of rows deleted.

deleteXML

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

Syntax

FUNCTION  deleteXML(ctxHdl IN ctxType, xDoc IN CLOB) RETURN NUMBER

Parameters

Returns

The number of rows deleted.

propagateOriginalException

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.

Syntax

PROCEDURE propagateOriginalException(ctxHdl IN ctxType, flag IN BOOLEAN)

Parameters

getExceptionContent

Through its arguments, this method 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; thus, rendering pl/sql unable to access the original exception.

Syntax

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

Parameters


Go to previous page Go to next page
Oracle
Copyright © 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